InnoDB Memcached’s read only query performance in 5.7 has already achieved a remarkable 1.1 million QPS record. Now, the read only query bottleneck shifts to the memcached client itself. Thus anything that can batch the queries and shorten the query strings helps.
Multiple Get
In the new 5.7 InnoDB Lab Release, we add support for the “multi-get” option within InnoDB Memcached. This functionality enables users to send multiple keys in a single “get” command. In this way, for client-server communication, the package size is reduced as multiple keys are package within a single “get” call. For InnoDB, it will no longer need to start a transaction and open the table for each key if they are packaged together. The lookup of a key and its corresponding values is done by a single open table within a single transaction. It is now similar to a “select … where … in()” query.
Here’s a quick example showing how to retrieve 4 key/value pairs in a single get call:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
telnet 127.0.0.1 11211 Trying 127.0.0.1... Connected to 127.0.0.1. Escape character is '^]'. get D B H C VALUE D 0 9 Darmstadt VALUE B 0 6 Berlin VALUE H 0 7 Hamburg VALUE C 0 7 Cottbus END |
Notice that it returns all 4 key/value pairs in a single response.
For this feature, the performance evaluation has yet to be conducted. We will blog more about that once the performance results are available.
Range Queries with InnoDB Memcached
Another interesting memcached feature added in this Lab release is that memcached now supports range queries. This is a good example to show how flexible the InnoDB API and InnoDB Memcached can be.
Initially we support a single compare operator placed directly before a key, and prefixed with a special marker of “@”.
For example, if we have following table:
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select c1, c2 from t1; +----+-----------+ | c1 | c2 | +----+-----------+ | B | Berlin | | C | Cottbus | | D | Darmstadt | | H | Hamburg | +----+-----------+ 4 rows in set (0.00 sec) |
Then in order to get all results that are less than “H”, the following query can be issued:
|
1 |
get @<H |
To include values with “H”, you can use “<=”:
|
1
2
3
4
5
6
7
8
9
10
|
get @<=H VALUE B 0 6 Berlin VALUE C 0 7 Cottbus VALUE D 0 9 Darmstadt VALUE H 0 7 Hamburg END |
To find rows larger than “B”:
|
1
2
3
4
5
6
7
8
|
get @>B VALUE C 0 7 Cottbus VALUE D 0 9 Darmstadt VALUE H 0 7 Hamburg END |
In this Lab release, we only support single key point range searches. It can be easily extended moving forward, however, to range searches bounded by both sides (key1 < key < key2 etc.).
Summary
By supporting simple range searches we demonstrate the flexibility and versatility of the InnoDB APIs and InnoDB Memcached. In addition, it shows that InnoDB Memcached can take full advantage of the storage engine’s indexes and support functionalities beyond traditional memcached capabilities, all with superior performance.
Please let us know what you think of this new feature! We’d love to hear your feedback on what else you’d like to see in the InnoDB Memcached plugin moving forward. If you encounter any problems with the new feature, please let us know here in the comments, open a bug report at bugs.mysql.com, or open a support ticket. Thank you for using MySQL!