Thursday, April 9, 2015

Secondary Indexes on XML BLOBs in MySQL 5.7

When storing XML documents in a BLOB or TEXT column there was no way to create indexes on individual XML elements or attributes. With the new auto generated columns in MySQL 5.7 (1st Release Candidate available now!) this has changed! Let me give you an example. Let's work on the following table:
 mysql> SELECT * FROM country\G  
 *************************** 1. row ***************************  
 docid: 1  
  doc: <country>  
     <city name="Berlin"><population></population></city>  
     <city name="Frankfurt"><population>643821</population></city>  
     <city name="Hamburg"><population>1704735</population></city>  
 *************************** 2. row ***************************  
 docid: 2  
  doc: <country>  
     <city name="Paris"><population>445452</population></city>  
     <city name="Lyon"></city>  
     <city name="Brest"></city>  
 *************************** 3. row ***************************  
 docid: 3  
  doc: <country>  
     <city name="Brest"><population></population></city>  
 *************************** 4. row ***************************  
 docid: 4  
  doc: <country>  
 4 rows in set (0,00 sec)  

The table has only two columns: docid and doc. Since MySQL 5.1 it is possible to extract the population value thanks to the XML functions like ExtractValue(...). But sorting the documents by the population of a country was impossible because population is not a dedicated column in the table. Starting with MySQL 5.7.6 DMR we can add an auto generated column that contains only the population. Let’s create that column:

 mysql> ALTER TABLE country ADD COLUMN population INT UNSIGNED AS (CAST(ExtractValue(doc,"/country/population") AS UNSIGNED INTEGER)) STORED;
  Query OK, 4 rows affected (0,21 sec)   
  Records: 4 Duplicates: 0 Warnings: 0   
  mysql> ALTER TABLE country ADD INDEX (population);   
  Query OK, 0 rows affected (0,22 sec)   
  Records: 0 Duplicates: 0 Warnings: 0   
  mysql> SELECT docid FROM country ORDER BY population ASC; 
  | docid |   
  |     4 |   
  |     3 |   
  |     2 |   
  |     1 |   
  4 rows in set (0,00 sec)  

The population value is extracted automatically from each document, stored in a dedicated column and the index is maintained. Really simple now. Note that the population value of the cities is NOT extracted.

What happens if we want to look for city names? Each document may contain several city names. First let’s extract the city names with the XML function and store it in an auto generated column again:

 mysql> ALTER TABLE country ADD COLUMN cities TEXT AS (ExtractValue(doc,"/country/city/@name")) STORED;  
 Query OK, 4 rows affected (0,62 sec)  
 Records: 4 Duplicates: 0 Warnings: 0  
 mysql> SELECT docid,cities FROM country;  
 | docid | cities                   |  
 |     1 | Berlin Frankfurt Hamburg |  
 |     2 | Paris Lyon Brest         |  
 |     3 | Brest                    |  
 |     4 |                          |  
 4 rows in set (0,01 sec)  

The XML function ExtractValue extracts the name attribute of all cities and concatenates these with whitespace. That makes it easy for us to leverage the FULLTEXT index in InnoDB:

 mysql> ALTER TABLE country ADD FULLTEXT (cities);  
 mysql> SELECT docid FROM country WHERE MATCH(cities) AGAINST ("Brest");  
 | docid |  
 |     2 |  
 |     3 |  
 2 rows in set (0,01 sec)  

All XML calculations are done automatically when storing data. Let’s add another XML document and query again:

 mysql> INSERT INTO country (doc) VALUES ('<country><name>USA</name><city name="New York"/><population>278357000</population></country>');  
 Query OK, 1 row affected (0,00 sec)  
 mysql> SELECT * FROM country WHERE MATCH(cities) AGAINST ("New York");  
 | docid | doc                                                                                          | population | cities   |  
 |     5 | <country><name>USA</name><city name="New York"/><population>278357000</population></country> |  278357000 | New York |  
 1 row in set (0,00 sec)  

Does this also work with JSON documents? There are JSON functions available in a labs release. These functions are currently implemented as user defined functions (UDF) in MySQL. UDFs are not supported in auto generated columns. So we have to wait until JSON functions are built-in to MySQL.
UPDATE: See this blogpost. There is a first labs release to use JSON functional indexes.

What did we learn? tl;dr

With MySQL 5.7.6 it is possible to automatically create columns from XML elements or attributes and maintain indexes on this data. Search is optimized, MySQL is doing all the work for you. And Brest is not only in France but also a city in Belarus.

Tuesday, March 24, 2015

Profiling Stored Procedures in MySQL 5.7

With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure. Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:

mysql> UPDATE setup_consumers SET ENABLED="YES" 
           WHERE NAME = "events_statements_history_long"; 

Then let's call the stored procedure that we want to inspect:

mysql> CALL test.massinsert(400,405); 

To avoid that we overwrite data from the events_statements_history_long table with the following queries, let's deactivate that consumer ASAP. If you have some concurrent load running on your system, it may be wise to leverage the filter options in performance_schema like setup_actors and/or setup_objects.

mysql> UPDATE setup_consumers SET ENABLED="NO" 
          WHERE NAME = "events_statements_history_long"; 

Next step is to find our CALL statement in the events_statements_history_long table:

mysql> SELECT event_id,sql_text, 
              CONCAT(TIMER_WAIT/1000000000,"ms") AS time 
                 FROM events_statements_history_long 
       WHERE event_name="statement/sql/call_procedure"; +----------+-------------------------------+-----------+ 
| event_id | sql_text                      | time      | +----------+-------------------------------+-----------+ 
| 144      | call massinsert(100,105)      | 0.2090ms  |
| 150      | call massinsert(100,105)      | 79.9659ms | 
| 421      | CALL test.massinsert(400,405) | 74.2078ms | +----------+-------------------------------+-----------+ 
3 rows in set (0,03 sec) 

You see: I tried this stored procedure three times. The one I want to inspect in detail is event_id 421. Let's look at all nested statement events that came from 421:

        CONCAT(TIMER_WAIT/1000000000,"ms") AS time   
     FROM events_statements_history_long   
     WHERE nesting_event_id=421 ORDER BY event_id;   
 | EVENT_NAME               | SQL_TEXT                          | time      |  
 | statement/sp/stmt        | SET @i = first                    | 0.0253ms  |   
 | statement/sp/stmt        | SET @i = @i + 1                   | 0.0155ms  |   
 | statement/sp/stmt        | INSERT INTO a VALUES (@i,MD5(@i)) | 45.6425ms |   
 | statement/sp/jump_if_not | NULL                              | 0.0311ms  |   
 | statement/sp/stmt        | SET @i = @i + 1                   | 0.0297ms  |   
 | statement/sp/stmt        | INSERT INTO a VALUES (@i,MD5(@i)) | 4.9695ms  |   
 | statement/sp/jump_if_not | NULL                              | 0.0726ms  |   
 | statement/sp/stmt        | SET @i = @i + 1                   | 0.0365ms  |   
 | statement/sp/stmt        | INSERT INTO a VALUES (@i,MD5(@i)) | 6.8518ms  |   
 | statement/sp/jump_if_not | NULL                              | 0.0343ms  |   
 | statement/sp/stmt        | SET @i = @i + 1                   | 0.0316ms  |   
 | statement/sp/stmt        | INSERT INTO a VALUES (@i,MD5(@i)) | 9.9633ms  |   
 | statement/sp/jump_if_not | NULL                              | 0.0309ms  |   
 | statement/sp/stmt        | SET @i = @i + 1                   | 0.0274ms  |   
 | statement/sp/stmt        | INSERT INTO a VALUES (@i,MD5(@i)) | 5.6235ms  |   
 | statement/sp/jump_if_not | NULL                              | 0.0308ms  |
 16 rows in set (0,06 sec)   
Now we have the statements that were executed in the stored procedure "massinsert(400,405)" with their individual execution times and in order of execution. We have all other information available as well, not only execution time. We can access number of rows affected, sql error text, used algorithms, ... All information that performance_schema offers for statement events. This is a great way to analyze your stored procedures. find the most costly statements and improve performance of your stored programs. That is really a great enhancement to performance_schema.

Friday, March 13, 2015

Auto Generated Columns in MySQL 5.7: Two Indexes on one Column made easy

One of my customers wants to search for names in a table. But sometimes the search is case insensitive, next time search should be done case sensitive. The index on that column always is created with the collation of the column. And if you search with a different collation in mind, you end up with a full table scan. Here is an example:

The problem

 mysql> SHOW CREATE TABLE City\G  
 *************************** 1. row ***************************  
 Table: City  
 Create Table: CREATE TABLE `City` (  
 `Name` char(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  
 `CountryCode` char(3) NOT NULL DEFAULT '',  
 `District` char(20) NOT NULL DEFAULT '',  
 `Population` int(11) NOT NULL DEFAULT '0',  
 KEY `CountryCode` (`CountryCode`),  
 KEY `Name` (`Name`),  
 1 row in set (0,00 sec)  

The collation of the column `Name` is utf8_bin, so case sensitive. Let's search for a City:
 mysql> SELECT Name,Population FROM City WHERE Name='berlin';  
 Empty set (0,00 sec)  
 mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin';  
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |  
 | 1  | SIMPLE      | City  | NULL       | ref  | Name          | Name | 106     | const |  1   |  100.00  | NULL  |  
 1 row in set, 1 warning (0,00 sec)  

Very efficient statement, using the index. But unfortunately it did not find the row as the search is based on the case sensitive collation.
Now let's change the collation for the WHERE clause:
 mysql> SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;  
 | Name   | Population |  
 | Berlin |    3386667 |  
 1 row in set (0,00 sec)  
 mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;  
 | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
 | 1  | SIMPLE      | City  | NULL       | ALL  | Name          | NULL | NULL    | NULL | 4108 |  10.00   | Using where |  
 1 row in set, 3 warnings (0,00 sec)  

The result is what we wanted but the query creates a full table scan. Not good. BTW: The warnings point you to the fact that the index could not be used.

The solution

Now let's see how auto generated columns in the new MySQL 5.7 Development Milestone Release can help us. First let's create a copy of the Name column but with a different collation:
 mysql> ALTER TABLE City ADD COLUMN Name_ci char(35) CHARACTER SET utf8 AS (Name) STORED;  
 Query OK, 4079 rows affected (0,50 sec)  
 Records: 4079 Duplicates: 0 Warnings: 0  

"AS (Name) STORED" is the new stuff: In the brackets is the expression to calculate the column value. Here it is a simple copy of the Name column. The keyword STORED means that the data is physically stored and not calculated on the fly. This is necessary to create the index now:
 mysql> ALTER TABLE City ADD INDEX (Name_ci);  
 Query OK, 0 rows affected (0,13 sec)  
 Records: 0 Duplicates: 0 Warnings: 0  

As utf8_general_ci is the default collation with utf8, there is no need to specify this with the new column. Now let's see how to search:

mysql> SELECT Name, Population FROM City WHERE Name_ci='berlin';
| Name   | Population |
| Berlin |    3386667 |
1 row in set (0,00 sec)
mysql> EXPLAIN SELECT Name, Population FROM City WHERE Name_ci='berlin';nbsp;
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
|  1 | SIMPLE      | City  | NULL       | ref  | Name_ci       | Name_ci | 106     | const |    1 |   100.00 | NULL  |
1 row in set, 1 warning (0,00 sec)  

Now we can search case sensitive (...WHERE Name=...) and case insensitive (WHERE Name_ci=...) and leverage indexes in both cases.


Use auto generated columns in MySQL 5.7 to create an additional index with a different collation. Now you can search based on different indexes.

Wednesday, March 19, 2014

MySQL Cluster on Raspberry Pi - Sub-second failover

MySQL Cluster claims to achieve sub-second failover without any data loss for commited transactions. And I always wanted to show this in a demo. Now we created that demo finally. See Mark's blog and Keith's blog for setting up MySQL Cluster on RaspberryPi.
The nice thing about the RPis is that you can easily pull the plug to test failover. Ok, that is only one possible failure scenario but for sure the most obvious and more impressive than "kill -9".

That demo application is constantly using the database for storing new lines, removing old lines and reading all line data for the graphical view. There is no caching. It uses JDBC directly.
To document the setup here is the config.ini file for MySQL Cluster:

[ndbd default]


I made the cluster diskless so it will not write any logs and table spaces to disk. The SD card performance was not great and it does not affect failover behavior.
I also reduced the HeatbeatIntervallDbDb so that nodes detect immediately (well, 10ms) if a heartbeat is missed. After a few missed heartbeats cluster reconfigures and the remaining node takes responsibility and service continues.
BTW: Pulling the plug is nice but every now and then I had to manually fsck the root-fs during reboot.

Tuesday, March 12, 2013

New in 5.6: --innodb_read_only, running MySQL from DVD

I recently met two distinct customers who want to use MySQL as a read-only database, stored on a read-only medium like a DVD. With MyISAM this was easily possible. Starting with MySQL 5.6 it is also possible to run this setup with InnoDB based databases. There is a new option:


See details in the reference manual. This options opens all tablespaces and InnoDB log files as read-only. A small test showed that you need to set some more options in the my.cnf to avoid any write access to @@datadir:


I was a bit surprised why I had to disable the event scheduler. But on the other hand, what use has a regularly running statement that cannot store any data? After all your database is read only ;-)

And together with the new fulltext indexes in InnoDB and maybe compressed table spaces you can now deploy catalogue applications or reference manuals on DVD.

Tuesday, May 29, 2012

What is ndb doing?

In MySQL cluster each SQL statement is translated inside the NDB storage engine to NDB low level protocol that is sent to the data nodes. For the performance it is most interesting how many data is moved between data nodes and MySQL. To monitor this there are several NDB status variables that you can monitor. See this link for more documentation.

(There are also the NDBINFO tables that reflect cluster status. But these are only global values. The status variables also show session status. More about NDBINFO is here.)

To easily report the NDB status on an individual SQL statement, I wrote a little script that gives you the ndb status variables and automatically calculates the diffs before and after the statement in question:

#! /bin/bash
./mysql -t -h olga64 test <<EOF

And here are some small examples of understanding what NDB does:

[root@olga64 bin]# ./ "SELECT COUNT(*) FROM t;"
| COUNT(*) |
|    32771 |
| VARIABLE_NAME                              | VALUE    |
| NDB_API_WAIT_NANOS_COUNT_SESSION           | 19495775 |
| NDB_API_BYTES_SENT_COUNT_SESSION           |      132 |
| NDB_API_TRANS_START_COUNT_SESSION          |        1 |
| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |
| NDB_API_TABLE_SCAN_COUNT_SESSION           |        1 |
| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |
| NDB_API_READ_ROW_COUNT_SESSION             |        2 |

 So SELECT COUNT(*) only returns two rows (NDB_API_READ_ROW_COUNT_SESSION). Probably one row per fragment. (I have a two node cluster). COUNT(*) is optimized! But if you add a WHERE condition:

[root@olga64 bin]# ./ "SELECT COUNT(*) FROM t WHERE a<100;"
| COUNT(*) |
|       99 |
| VARIABLE_NAME                              | VALUE    |
| NDB_API_WAIT_NANOS_COUNT_SESSION           | 18267962 |
| NDB_API_BYTES_SENT_COUNT_SESSION           |      140 |
| NDB_API_TRANS_START_COUNT_SESSION          |        1 |
| NDB_API_TRANS_CLOSE_COUNT_SESSION          |        1 |
| NDB_API_RANGE_SCAN_COUNT_SESSION           |        1 |
| NDB_API_SCAN_BATCH_COUNT_SESSION           |        2 |
| NDB_API_READ_ROW_COUNT_SESSION             |       99 |

 'a' is the primary key. This statement sends all 99 rows, well only the primary keys and mysqld is counting. This one can get more expensive, depending on the row count and primary key size. Look at NDB_API_BYTES_RECEIVED_COUNT_SESSION: 3248 bytes sent. This is roughly 32 bytes per row that is sent.

Monday, March 12, 2012

Why should I consider memcached plugin?

My last post explained what to expect from memcached plugin in MySQL 5.6 (labs release). But I want to take a step back and think about "why" first. Why is memcached plugin of interest at all? What can I gain from using this instead of plain SQL?

First: I don't see this as a replacement for memcached. If you want memory caching with memcached then use memcached.

But the memcached plugin to MySQL is a replacement or addition to the SQL interface to MySQL. So instead of using SQL queries in your application to persist or retrieve data from MySQL you can use the memcached interface. And what are the benefits?
  • Much higher performance
  • Easier scalability via sharding
  • Simpler application coding

1. Performance

Performance is always good. But there are two different aspects of performance: Latency (or runtime) of a specific query and throughput. And the relation of these two measures is not easy and simple to explain. So we need to discuss both:

1.1 Latency

Latency is the time it takes to execute an individual query. If using SQL the server needs to parse that SQL query (which is not so easy because SQL is quite a complex language), then optimize the execution plan of a query and finally execute it. Memcached interface is much simpler so parsing is close to nothing and accessing a single row by an index does not require any optimization. Only the real query execution is the same. I did a test in a VM with one virtual disk. (Not the best benchmark environment ;-) I inserted single rows in multiple threads via C-API (using SQL) and via libmemcachd (using the new plugin). The mean latency for a single query was 20%-30% higher for SQL queries. Considering that my IO configuration was bad, in a better environment the share of query execution would be smaller so the benefit of memcached plugin (no parsing, no optimization) would be even bigger.

1.2 Throughput

Usually lower latency helps improving the throughput. If each query is finished earlier, the server can already start working on the next query. This is true only if the bottleneck is really the CPU or RAM. If latency is mainly due to disk IO, it is not so helpful. During my test I achieved up to 50% higher throughput with memcached plugin. Again, a suitable IO config can probably improve this advantage even further.

2. Scalability

If a single server is no longer enough one solution may be sharding. With SQL you usually have two options: Switch to MySQL cluster, which offers auto-sharding. The other alternative is to implement sharding on your own in your application, which requires some effort.
With memcached plugin it is easier: For connecting to the database you would usually use some library like libmemcached. And many of these libraries offer ... a sharding feature. This is a natural feature for memcached libraries as the goal of memcached is to offer a distributed cache. Depending on the key value (or a hash of it) the library determines which instance to talk to. There is no need for you to extend your application code and manage different database connections. In libmemcached you can simply define your connectstring as "SERVER=donald:11211 SERVER=dagobert:11211 SERVER=daisy:11211". And that's it. Libmemcached will automatically shard your data to all three database servers.
Well, one thing must be mentioned: If you want to query your data via SQL and do aggregation, you have to aggregate in your application. The SQL interface is still not auto-sharded. If you need this kind of functionality, you should consider MySQL Cluster. Cluster will shard your data automatically, wether coming via memcached API or SQL. See more in Andrew's blog. And for MySQL Cluster the memcache interface is already recommended for production use in MySQL Cluster 7.2.

3. Ease of use

The typical architecture is very often application - memcache layer - MySQL database. And for read access the application talks to memcache first, and if this results in a "miss", the application will re-do the query but with a different protocol to the MySQL server. You must implement both protocols: memcache API and SQL. If you use the memcached plugin, you can now access the database directly with the same API functions that you already use for memcache. That makes development much easier.
The sharding feature in the memcached library will make it easier to scale the database. You do not have to implement it on your own.
"Easy of use" only applies if your application is happy with doing only low level data access to a key value store. Once you also need higher level data access like JOINs or aggregation queries, you will again use memcache API and SQL in the same application. So nothing will change to before.

4. Availability?

Not a huge argument. By using the memcached plugin you essentially work with MySQL and you will leverage all possible HA options that are available for MySQL to make your data highly available. It is worth mentioning that memcached plugin supports replication. You have to define daemon_memcached_enable_binlog = 1. That's it.

Code examples

Just for your reference here is the shortcut version of the code that I used to test SQL and memcache API in connecting to MySQL server.


MYSQL *mysql;
char stmt[256];
int key = 0;
mysql = mysql_init(NULL);
if (mysql_real_connect(mysql,"localhost","user","password",
"schema",3306,NULL,0L) == NULL) {
for (;;) {
sprintf (stmt, "INSERT INTO kvstore VALUES('key%d','%s',0,0,0)",
if (mysql_query (mysql, stmt) != 0) {goodbye();}


char *config_string = "--SERVER=localhost:11211";
memcached_st *memc;
memcached_return_t rc;
memc = memcached(config_string, strlen(config_string));
if (memc == NULL) {goodbye();}
for (;;) {
rc= memcached_set(memc, key, strlen(key), value, strlen(value),
(time_t)0, (uint32_t)0);
if (rc != MEMCACHED_SUCCESS) goodbye();