Saturday, March 9, 2019

TTL - Perfect Accuracy by using an insertable VIEW

One more comment regarding TTL in MySQL:
If you are looking for perfect accuracy and never want to access rows that are older than the defined TTL you can hide the table t (from my previous post) behind a view. This view will automatically select only rows within TTL lifespan:
CREATE VIEW ttl as SELECT id, content, created_at FROM t 
       WHERE created_at >= NOW() - INTERVAL 10 SECOND;
This view is insertable, so you can fully use this view and you are not distracted by the additional column "bucket".
INSERT INTO ttl VALUES (NULL, "This is a test", NULL);
You could even exclude column "created_at" from the view definition, if there was not bug #94550. 'created_at' could be fully handled internally.
This view does not affect performance much. In my simple test it did not show any affect. Just better usability and better accuracy of TTL.

Limitations

You cannot use foreign keys with your ttl'ed table and view. This is because partitioning and foreign keys are mutually exclusive. If you need foreign keys go with the simple delete event procedure and forget about the view.
Due to bug #94550 you have to set explicit_defaults_for_timestamp to OFF and you always have to insert NULL into column 'created_at'.
In this whole setup the TTL is mentioned in four locations: In the partitioning definition, in the definition of the generated column 'bucket', in the cleaning event procedure and in the WHERE clause of the view. This makes it easier to screw up the setup. Make sure you use the same value everywhere. Same applies for the number of partitions in the table definition as well as the cleaning event procedure.

Friday, March 8, 2019

TTL - Time-to-Live in MySQL

A customer recently asked for a TTL feature in MySQL. The idea is to automatically delete rows from a certain table after a defined lifespan, e.g. 60 seconds. This feature is common in many NoSQL databases, but it is not available in MySQL. However MySQL offers all you need to implement this. And due to partitioning much more efficient than only deleting rows. Let's test it.

tl;dr

Partition the table and truncate partitions in a regular event procedure, that does the trick and comes at a fraction of the cost of regularly deleting rows.

The test case

The table needs a column to keep track of row age. This can be either a "created_at" column or an "expires_at" column. ("expires_at" has the additional advantage that each row can have an individual lifespan. Not possible in many NoSQL solutions.)
So my table is
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `content` varchar(42) DEFAULT NULL,
);
I tested two variants to implement a 10 seconds TTL on my table "t":

The simple solution

Run an event every 10 seconds to delete rows that have been created more than 10s ago.
DELIMITER |
CREATE EVENT ttl_delete
        ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE
        DO BEGIN
                DELETE FROM t WHERE created_at < NOW() - INTERVAL 10 SECOND;
        END |
DELIMITER ;
And index on "created_at" might improve performance for the DELETE job. But in any case it is quite expensive to scan the table and remove roughly 50% of the rows of a table, at least if the INSERT rate is high.

The efficient solution

Instead of DELETing we can use the much faster TRUNCATE operation. Obviously we do not want to TRUNCATE the whole table but if we distribute the inserted rows into partitions it is safe to truncate any partition that contains outdated rows. Let's define three partitions (or buckets): One that is currently being written to, one that holds rows of the last 10 seconds and one partition that can be truncated because the rows are older than 10 seconds. Key is to calculate the bucket from the current time. This can be done with the expression FLOOR(TO_SECONDS(NOW()/10)) % 3, or more generic FLOOR(TO_SECONDS(NOW()/ttl))% number_of_buckets 
Now we can partition the table by this expression. For that we add a generated column to calculate the bucket from the column "created_at" and partition the table by column "bucket". The table now looks like this:
CREATE TABLE `t` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `content` varchar(42) DEFAULT NULL,
  `bucket` tinyint(4) GENERATED ALWAYS AS     

       (floor(TO_SECONDS(`created_at`) / 10) % 3) STORED NOT NULL,
  PRIMARY KEY (`id`,`bucket`)
)  PARTITION BY LIST (`bucket`)
(PARTITION p0 VALUES IN (0),
 PARTITION p1 VALUES IN (1),
 PARTITION p2 VALUES IN (2));

And the event procedure is like this:
DELIMITER |
CREATE EVENT ttl_truncate
    ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE
    DO BEGIN
            CASE FLOOR(TO_SECONDS(NOW())/10)%3
            WHEN 0 THEN ALTER TABLE test.t TRUNCATE PARTITION p1;
            WHEN 1 THEN ALTER TABLE test.t TRUNCATE PARTITION p2;
            WHEN 2 THEN ALTER TABLE test.t TRUNCATE PARTITION p0;
            END CASE;
        END|
DELIMITER ;

Watching the rows come and go

To verify that the procedure works as expected I created a small monitor procedure that displays each second the number of rows per partition. Then it is easy to follow in which partition data is currently added and when a partition gets truncated.
DELIMITER |
CREATE PROCEDURE monitor()
BEGIN  
  WHILE 1=1 DO
   SELECT "p0" AS "part", count(*) FROM t PARTITION (p0)
           UNION SELECT "p1", count(*) FROM t PARTITION (p1)
           UNION SELECT "p2", count(*) FROM t PARTITION (p2);

   SELECT now() AS "NOW", floor(to_seconds(now())/10)%3 AS "Bucket";
   SELECT sleep(1);
  END WHILE;
END|
DELIMITER ;

This procedure is not ideal. Too many count(*) will create quite some locking. But it is accurate. The alternative is to read data from INFORMATION_SCHEMA.partitions, but this does not give the exact row count, which I needed for verification. 

Increasing Accuracy

If TTL is 10 seconds, deleting or truncating every 10 seconds means you have at least 10 seconds of rows available. In reality you will have 10 to 20 seconds worth of data, so on average 15 seconds (assuming a constant INSERT rate). If you run the cleaner job more often (say once per second) the average number of rows is 10.5 seconds worth of data. This comes at the cost of running the cleaning event more often. But it might be very beneficial to increase this accuracy because all other queries benefit from less data to operate on and less memory consumed by expired rows.
If you go with the simple solution of a regular DELETE event, it is sufficient to schedule the event more often.
If you prefer the TRUNCATE PARTITION option, it is necessary to increase the number of partitions or buckets to 12 (= 2 + TTL / how often to run the cleaning job).
The expression for the calculated bucket column will be
`bucket` tinyint(4) GENERATED ALWAYS AS     
       (floor(TO_SECONDS(`created_at`) / TTL) % #buckets) STORED NOT NULL
and the partitioning needs to be adapted as well.
And the CASE construct in the cleaner event must be extended for each newly existing bucket/partition:
        WHEN n THEN ALTER TABLE test.t TRUNCATE PARTITION p(n+1);

What happens if...

... the event stops?
Then you keep all your rows which will likely create some follow-up problems. As always: Proper monitoring is key. Think about MySQL Enterprise Monitor for example.

... the event procedure runs at inaccurate timing due to overall system load?
No big problem. It will never run too early. So it will never remove rows too early. If it runs too late it will clean rows too late so you have more garbage in your table which might affect other queries. The real TTL is increased if this happens.

Performance Considerations

 By no means I am able to run proper performance tests. I am running on a Win10 laptop, VirtualBox with Oracle Linux and MySQL runs inside a Docker container. So plenty of reason to achieve bad numbers. But to compare the two implementations it should be sufficient.
I have extended the cleaner events to report the time needed to execute the event procedure. Here the example of the simple cleaner job:
CREATE EVENT ttl_delete
  ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE
  DO BEGIN
    DECLARE t1,t2 TIME(6);
    SET t1=current_time(6);

    DELETE FROM t WHERE created_at < NOW() - INTERVAL 10 SECOND;

    SET t2=current_time(6);
    INSERT INTO ttl_report VALUES ("DELETE simple", now(),
                                   timediff(t2,t1));

  END|
DELIMITER ;

The load was generated by mysqlslap, which only inserted rows in the table. Each test run starts the respective cleaner event, runs the mysqlslap load and then stops the cleaner event.
mysql -h 127.0.0.1 -uroot -pXXX -e \
        "USE test; ALTER event ttl_delete ENABLE;"
 

mysqlslap -h 127.0.0.1 -uroot -pXXX --create-schema=test \   
   --concurrency=5 --iterations=20 --number-of-queries=10000 
   --query="INSERT INTO test.t (created_at, content) VALUES 
            (NULL,md5(id));"
 

mysql -h 127.0.0.1 -uroot -pXXX -e \
    "USE test; ALTER event ttl_delete DISABLE;"

mysql -h 127.0.0.1 -uroot -pXXX -e \

      "USE test; ALTER event ttl_truncate ENABLE;"
 

mysqlslap -h 127.0.0.1 -uroot -pXXX --create-schema=test 
   --concurrency=5 --iterations=20 --number-of-queries=10000 
   --query="INSERT INTO test.t (created_at, content) VALUES 
           (NULL,md5(id));"
mysql -h 127.0.0.1 -uroot -pXXX -e \

    "USE test; ALTER event ttl_truncate DISABLE;"

The results are clearly in favor of truncating partitions. And the difference should be even higher the higher the INSERT rate gets. My poor setup achieved only less than 1000 inserts per second... 

select who, avg(how_long) from ttl_report GROUP BY who;
+---------------+--------------------+
| who           |avg(how_long)       |
+---------------+--------------------+
| DELETE simple | 1.1980474444444444 |

| truncate      | 0.0400163333333333 |
+---------------+--------------------+
3 rows in set (0.0014 sec)

 

Side note

You might wonder why my test load is
  INSERT INTO test.t (created_at, content) VALUES (NULL,'foo');"
Why do I mention the column "created_at" but then store NULL to give it the default of current_timestamp? If I omit the created_at column in this INSERT statement I get an error from the generated column due to bug #94550. Setting explicit_defaults_for_timestamp to OFF and then mentioning the timestamp column during INSERT is a workaround.

Friday, July 28, 2017

Node.js and MySQL on the Oracle Cloud

Let's explore how to deploy a node.js app with MySQL backend on the Oracle Cloud. I want to cover several aspects:
  • How to deploy and initialize a MySQL instance
  • How to adapt the source code
  • How to deploy the application
  • How to scale the application
There are different ways to configure this. I tested the easiest deployment with MySQL Cloud Service and the Application Container Cloud for the node.js part. All configurations are done via the cloud web GUI. There is also a REST interface available. But let's keep that for later.
If you don't have access to the Oracle Cloud you can get a trial access here.

How to deploy a MySQL instance

Once you logged into the Oracle cloud you can create new instances from the dashboard. The following screenshots describe the process.


On the next screen we upload the public key for admin access to the instance. Either upload your own public key or generate a new key pair. (If you generate a new key pair you need to download the private key to your local machine.)

I skipped the backup and monitoring configurations for this demo. Let's focus on the application instead. After creating the instance (approx. 10 min) you can navigate via the dashboard to this instance and get the IP address. This is needed for the next step.
To initialize the database I ran this little script that runs ssh to the instance (using the private key), switch user to "oracle" and then call the MySQL CLI to run a few SQL statements.

How to adapt the source code

The Application Container Cloud sets a few environment variables that should be used inside the application to adapt to the environment. In my case this are the following variables:
  • PORT is the port number that the application should listen on
  • MYSQLCS_USER_NAME is the MySQL user name for the database backend
  • MYSQLCS_USER_PASSWORD is the corresponding password
  • MYSQLCS_CONNECT_STRING is the hostname and port of the database backend
I could have hardcoded the database connection parameters but that is inflexible if the architecture changes. So let's use these variables. The Node.js code looks like this:


How to deploy the application

There are two simple steps needed: Creating an application and defining service bindings. In my case the only service binding is the MySQL backend. But one step after the other. First let's create the application. First you need to create a manifest.json file to describe the application. Here is mine:

Ideally you create a zip archive with the source code, the manifest.json file and all other resources that your application needs. If you want to use my zip archive, feel free. You find it on GitHub.
From the Oracle Cloud Dashboard click on "create instance -> application container" and then select "Create Instance" and "Node.js". (Java SE, Java EE, Python, Ruby and PHP are available as well.)

On the pop-up you define the application artifacts, number of application instances and the memory per instance. After you click "create" the application is deployed automatically within a few minutes.
The last step is to connect the application service with the database backend. To achieve that, click on the application in the application overview page. Here you find the URL under which your application will be available. And on the left hand side you see three tabs:

Overview, Deployments and Administration. Click on "Deployments". Here you can add the service binding as described in the following screenshot:

After modifying the service bindings you have to click "Apply changes". This will restart the application instances. Obviously needed because now the environment variables for the database backend are set correctly.
That's it. We have an application. The URL to access the new app is listed in the application overview tab. Because this URL is not so nice for offering a short url service, I registered a new domain and forwarded that to the anota application. Maybe it is still running? Check here.

How to scale the application

This is really easy. On the application overview tab you can just increase the number of instances and the memory per instance. After applying the changes, the Application Container Cloud platform will deploy new instances, stop spare instances or reconfigure the existing instances. If you use my ANOTA application, go to the report page. The last line prints the hostname of the application server. Requests are automatically load balanced between the available application instances. 

Summary

There are some minor changes to the application to run on the Oracle Cloud Platform: Reading the port variable and database connection parameters from the provided environment variables and that's it. Deployment is really easy via the GUI. And scalability is so simple now that the full Oracle Cloud Plattform is available and can be provisioned within minutes. 


Thursday, June 1, 2017

MySQL Shell - Easy scripting

With the introduction of MySQL InnoDB Cluster we also got the MySQL Shell (mysqlsh) interface. The shell offers scripting in Javascript (default), SQL or Python. This offers a lot more options for writing scripts on MySQL, for example it is much easier now to use multiple server connections in a single script.
A customer recently asked for a way to compare the transaction sets between servers. That is useful when setting up replication or identifying the server that has most transactions applied already. So I wrote this little script which can be executed from the OS shell:

 #!/usr/bin/mysqlsh -f  
 // it is important to connect to the X protocol port,  
 // usually it is the traditional port + "0"  
 //  
 var serverA="root:root@localhost:40010"  
 var serverB="root:root@localhost:50010"  
 shell.connect(serverA)  
 var gtidA=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 shell.connect(serverB)  
 var gtidB=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]  
 //  
 // If you want to use pure XdevAPI the former statements should be  
 //  
 // gtid = session.getSchema("performance_schema").global_variables.select("VARIABLE_VALUE").where("VARIABLE_NAME='gtid_executed'").execute().fetchOne()[0]  
 //  
 println(" ")  
 println ("Transactions that exist only on "+serverA)  
 println (session.sql("SELECT gtid_subtract('"+gtidA+"','"+gtidB+"')").execute().fetchOne()[0])  
 println(" ")  
 println ("Transactions that exist only on "+serverB)  
 println (session.sql("SELECT gtid_subtract('"+gtidB+"','"+gtidA+"')").execute().fetchOne()[0])  
 

Wednesday, April 13, 2016

Query Rewrite Plugin and Binlog for Replication

Starting with MySQL 5.7 we introduced the Query Rewrite Plugin. That tool is really useful for changing queries. Of course the best location to modify the query is the source code of the application, but this is not always possible. Either the application is not under your control or queries are generated from a framework like Hibernate and sometimes it is hard to change the query generation.
If you are interested in details about the Query Rewrite Plugin, I recommend this blogpost from the MySQL Engineering: http://mysqlserverteam.com/the-query-rewrite-plugins/
Recently I was asked how this works in replication environments. Which query goes into the binlog?

If you are using the Rewriter plugin that comes with MySQL 5.7, the answer is easy: This plugin only supports rewriting SELECT queries. SELECT queries don't get into the binlog at all. Simple.

But you might write your own preparse or postparse plugin. In that case you can define the behavior with the server option --log-raw. See documentation here: https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-raw
You can either bring the original query to the binlog or the rewritten query. So all flexibility you need. However be aware that --log-raw also affects logging of passwords in the general log file. With --log-raw passwords are written in plain text to the log files. So consider this side effect when switching --log-raw on or off.

Monday, April 4, 2016

MySQL 5.7: Optimizer finds best index by expression

The optimizer in MySQL 5.7 leverages generated columns. Generated columns will physically store data in two cases: Either the column is defined as STORED or you create an index on a virtual column. The optimizer will leverage such an index automatically if it encounters the same expression in a statement. Let's see an example:

mysql> DESC squares;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| dx    | int(10) unsigned | YES  |     | NULL    |       |
| dy    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM squares;
+----------+
| COUNT(*) |
+----------+
|  2097152 |
+----------+
1 row in set (0.77 sec)


We have a large table with 2 million rows. Selecting rows by the surface area of squares can hardly leverage an index on dx or dy:

mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Now let's add an index over a generated, virtual column that defines the area:

mysql> ALTER TABLE squares ADD COLUMN (area INT AS (dx*dy));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE squares ADD INDEX (area);
Query OK, 0 rows affected (5.24 sec)
Records: 0  Duplicates: 0  Warnings: 0


Now we can run query again:

mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ref
possible_keys: area
          key: area

      key_len: 5
          ref: const
         rows: 18682
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

 I did not change the query! The WHERE condition is still dx*dy. Nevertheless the optimizer finds the generated column, sees the index and decides to leverage that.
So you can add complex indexes and without changing the application code you can benefit from these indexes. That makes life much easier.

One limitation though: It seems the optimizer recognizes expressions only in the WHERE clause. It will not use the generated column and index for the SELECT expression:

mysql> EXPLAIN SELECT SUM(dx*dy) FROM squares\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL

      key_len: NULL
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT SUM(area) FROM squares\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
   partitions: NULL
         type: index
possible_keys: NULL
          key: area
      key_len: 5
          ref: NULL
         rows: 2092860
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)


CHECK constraint for MySQL - NOT NULL on generated columns

During our recent TechTour event the idea came up to implement JSON document validation not necessarily via foreign keys (as I have shown here) but to define the generated column as NOT NULL. The generation expression must be defined in a way that it returns NULL for invalid data.
DISCLAIMER: This has already been explored by yoku0825 in his blogpost. He deserves all credit!

Let's do a short test:

mysql> CREATE TABLE checker ( 
    i int, 
    i_must_be_between_7_and_12 BOOLEAN 
         AS (IF(i BETWEEN 7 AND 12, true, NULL))  
         VIRTUAL NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO checker (i) VALUES (11);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO checker (i) VALUES (12);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO checker (i) VALUES (13);
ERROR 1048 (23000): Column 'i_must_be_between_7_and_12' cannot be null




As you can see I used the column name to create a meaningful error message when inserting invalid data. It is perfectly possible to add a generated validation column for each data column so that you run several check constraints.
Or you can even check a combination of columns:

mysql> CREATE TABLE squares (
     dx DOUBLE, 
     dy DOUBLE, 
     area_must_be_larger_than_10 BOOLEAN 
           AS (IF(dx*dy>10.0,true,NULL)) NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO squares (dx,dy) VALUES (7,4);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO squares (dx,dy) VALUES (2,4);

ERROR 1048 (23000): Column 'area_must_be_larger_than_10' cannot be null

As generated columns are virtual by default this costs no extra storage. Data volume is the same. The expression is evaluated when inserting or updating data.
If you add a validation column to an already existing table and want to verify all existing rows, you could define the validation column as STORED (instead of the default VIRTUAL). This will fail if there are any invalid rows in your existing data set. However in normal operation a virtual column seems more appropriate for performance reasons. So I recommend to always use VIRTUAL validation columns and check pre-existing data separately with a small procedure.