pero on anything

The main focus of version 0.2 was to improve handling of almost all of SQL. So now you can issue DESC TABLE tbl_name or RENAME TABLE tbl_name TO another_tbl_name on a partitioned table and you get correct results for SHOW TABLES etc.
Other statements are rejected and we settled down for the feature set we want to provide for full partition scans.
In addition to that there are some performance improvements.

See the full list of changes.

For the next release (0.3) we focus on the dictionary based partition lookup module and further performance improvements.

Finally, there is a new project home page: http://www.hscale.org.

Earlier today I posted these benchmark results testing HSCALE and MySQL Proxy performance.

As Jan Kneschke (the author of MySQL Proxy) pointed out there are quite some improvements in the current development version (svn trunk). So I gave revision 369 a try.

Tests were all the same as mentioned in my previous post. And indeed we see quite dramatic improvements. While the performance of the Lua script stayed almost the same the footprint of the proxy itself sank to only 50 to 65%. Here are the numbers:

(all values are “time in ms”)

Looking at the highlighted rows (concurrency = 10) you see that the difference between the MySQL server and MySQL Proxy is much smaller for the svn version. This is a great step forward!

If you compare all the other numbers and relate them to the execution time of the MySQL Proxy you see that the overhead stayed pretty much the same. So we see great improvements in general footprint but not in Lua execution.

And still the test does not scale well beyond 10 parallel threads. As Kay Roepke (co-author of MySQL Proxy) pointed out MySQL Proxy is currently single threaded and thus improvements on this were not expected (but would have been fine ).

I hope version 0.7.0 is to be released quite soon (last commit in SVN is from February??? according to http://svn.mysql.com/fisheye/browse/mysql-proxy) since the performance improvement is simply great and this would help MySQL Proxy gaining more acceptance as the “latency” is often the number one “reason” not to try out MySQL Proxy.

As part of developing HSCALE, a partitioning / sharding solution, I set up a benchmark test suite. I made it scripted and thus repeatable to monitor the progress and performance regressions during the development.

Test Suite

The test suite uses mysqlslap to benchmark the overhead of MySQL Proxy itself in real life scenario as well as the different components of HSCALE - query analyzing and query rewriting. The complete test suite is available in the svn trunk at http://svn.hscale.org under hscale/test/performance/mysqlslap. There you find a build.xml - an Ant buildfile that is used to set up the test environment and perform the tests.

Test Strategy

There are several things we want to find out using this benchmark:

1. How much overhead adds MySQL Proxy in a multiple server setup?
2. Does using Lua scripts add substantial overhead?
3. How much resources does the proxy.tokenizer use?
4. How does HSCALE perform on unpartitioned tables?
5. How does HSCALE perform on partitioned tables?

As stated above mysqlslap is used to generate multi-threaded load. mysqlslap is used to fire this statement:

SELECT
id, category
FROM small
WHERE
small.category='books'
/* Added */ /* some */ /* comments */
/* to */ /* produce */ /* a */ /* higher */
/* tokenizer */ /* load */


against this table and content:

CREATE TABLE small (
id INT UNSIGNED NOT NULL,
category ENUM('books', 'hardware', 'software') NOT NULL,
PRIMARY KEY(id)
) ENGINE=HEAP;

INSERT INTO small (id, category) VALUES (1, 'books');
INSERT INTO small (id, category) VALUES (2, 'hardware');
INSERT INTO small (id, category) VALUES (3, 'software');


Each run sends 10,000 queries to the MySQL Server or MySQL Proxy respectively.

Test Setup

1. A MySQL server instance (5.0.54-enterprise-gpl-log) on a DELL PowerEdge 2850, 2xQuadCore 2.8GHz, 12GB RAM
2. A server running MySQL Proxy (version 0.6.1) instances exclusively (DELL PowerEdge 2950, 2xQuadCore 2.33GHz, 8GB RAM)
3. A test runner on a DELL PowerEdge 1950, 2xQuadCore 1.8GHz, 8GB RAM.

The test suite is totally CPU and memory bound so the IO system doesn’t matter here.

Results

Each test means:

1. MySQL: Test ran directly against a mysql server
2. MySQL Proxy: Test ran directly against a MySQL Proxy server with no additional configuration / script
3. Empty Lua: A Lua script with an empty function read_request(packet) has been used
4. Tokenizer: Each query has been tokenized using proxy.tokenizer
5. QueryAnalyzer: Tokenizer and query analyzer are used but no query rewriting
6. HSCALE w/o partitions: HSCALE is used but the table is not partitioned
7. HSCALE w/ partitions: HSCALE is used against a partitioned table

Conclusions

First of all: Please note that these benchmarks measure the maximum overhead of each component and that overhead is constant meaning that a statement that takes 1 minute to complete on the MySQL server does not take 2 minutes when using MySQL Proxy.

CPU As Limiting Factor

As you can see with a concurrency of 20 or more everything gets worse and worse. This is because the MySQL Proxy / Lua performance becomes CPU bound. In addition to that you can see that the time is spent anywhere but within the Lua scripts: While we see quite distinct performance values for lower concurrencies (HSCALE w/ and w/o partitions show a huge difference) every benchmarks takes almost the same time at 20 or 40 parallel threads.

Looking at top the MySQL Proxy seems to be using a single CPU out of 8 available. If this is the case it would be extremely desirable to have MySQL Proxy use all available resources.

MySQL Proxy Overhead

As we can see putting a plain MySQL Proxy between application and MySQL server adds about 100% to 150% to the average overall performance. This is what we could have expected because of the added latency - packets are going through 2 hops instead of 1.

With higher concurrency the overhead grows until it totally drops at 40 parallel threads. Here CPU seems to be the limiting factor.

Lua Scripts

Adding an empty Lua script to the configuration results in little overhead up to a concurrency of 10. With higher concurrency everything gets worse. Again CPU seems to be the limiting factor.

Tokenizer

The SQL tokenizer adds about 75% compared to an empty Lua script. So we should avoid it as much as we can. With the results of this benchmark we were able to improve the overall HSCALE performance for non-partitioned tables (see this Issue).

QueryAnalyzer

Since the QueryAnalyzer utilizes the tokenizer it implies its overhead and adds additional 50% (at a concurrency of 10). Here is a lot of room for improvement. Currently the analyzer is almost complete so we can concentrate on performance. First of all the algorithm could be optimized (anticipating the fastest path) and then more hinting could be added.

HSCALE w/o Partitions

After implementing an improvement for this Issue (avoiding tokenizer) we see that performance for queries against non-partitioned tables is almost as good as for empty Lua scripts.

HSCALE w/ Partitions

Looking at the concurrency level of 10 we see that HSCALE performs 10 times slower that the MySQL server and 5 times slower than an empty MySQL Proxy. Needless to say that this is quite a huge number. With performance improvements we might lower this to a factor of 2 or 3 times slower than MySQL Proxy itself. This is ok since we are still able to perform more than 3,000 statements / s. And finally we are able to use multiple proxies to spread the load.

Final Thoughts

This benchmark showed us mainly 3 things:

1. MySQL Proxy adds the expected latency overhead - but not more. Average is about 0.035 milliseconds per query.
2. Scaling of MySQL Proxy could be improved - using all CPUs
3. HSCALE adds a maximum overhead of about 0.24 ms per query (against a partitioned table).

Please feel free to comment on the results or run the tests on your own.

UPDATE: Corrected the number of milliseconds MySQL Proxy and HSCALE add per query: Old were 0.35 ms for proxy and 2.4 ms for HSCALE. The correct numbers are 0.035 ms for proxy and 0.24 ms for HSCALE.

No, these slides are not fresh from the User Conference in Santa Clara…

Today, I held a presentation in front of all developers and support engineers of our technical department about database partitioning, MySQL Proxy, HSCALE and the progress we are making.

Download the presentation slides here.

As written here and here I’ve been working on a MySQL Proxy Lua module that transparently splits up tables into multiple partitions and rewriting all queries to go to the right partition.

I finally got everything together to release a 0.1 version. Go on and download, try and read more about HSCALE 0.1.

All this started out as a prototype just to see if it could be done. And after adopting parts of our main product to use partitions via HSCALE + MySQL Proxy (which was an easy task, we just had to rewrite a few out of hundreds of statements) I really think that this could work out in a larger scale.

What Will Come Next?

Just a few notes on what I am working on right now:

Project Page And Issue Tracker

In a few days there will be a “real” project page with more documentation and an issue tracker ready. Since we already have both in use internally this should be an easy task.

Write Another Partition Lookup Module

A partition lookup module decides the partition(s) to use for a particular query. In the current release there is only a ModulusPartitionLookup integrated. Since the partition lookup module is pluggable it is easy to write other modules doing other things. The main focus now is to implement a DictionaryLookupService which will store the information of which partition is where inside the database. This allows you to add and move partitions “on the fly”. At the end you just have more control over the partition scheme.

Along with the new partition lookup module there will be more administrative SQL commands like:
HSCALE ADD PARTITION ..., HSCALE MOVE PARTITION ... and so on.

Full Partition Scans For Queries With Multiple Partitioned Tables

In the current version HSCALE is already capable of performing full partition scans for queries that don’t use the partition column and thus don’t provide a partition key like:

(Just a side note: Results returned from this query are not in natural order due to the fact that the data is spread over multiple tables. Thus your application cannot rely on the natural order for statements against partitioned tables (if full partition scan is performed). You should not rely on natural order anyway.)

Even though you should avoid full partition scans where you can sometime you just have to look into every partition. And even worse sometimes you join multiple partitioned tables like:

Currently HSCALE rejects queries of this kind. In future it will join every partition of my_partitioned_table with every partition of my_other_partitioned_table. In most cases this is evil but sometimes you just have to.

Finally it is up to the partition lookup module to find out the combinations of partitions to use so we can optimize here for tables that use the same partitioning scheme.

Performance Profiling And Optimization

I was really astonished by the performance of the Lua scripting inside MySQL Proxy. I was able to analyze more than 100,000 statements in just a few seconds (without network overhead). This is already pretty good but can be improved. First of all I will have to find out the performance patterns to be used when scripting with Lua like “Is it better to inline functions?”, “Does ‘OO’ hurt?” and so on. Then performance tests analyzing both, speed and memory consumption, will have to be implemented to see if there is progression.

Distribute Partitions Across Multiple MySQL Servers

Right now we need to just split up huge tables but later on we want to distribute partitions over multiple MySQL server instances to have real horizontal scale out. The hardest part will be dealing with transactions where we have to use distributed transactions (XA) or disallow transactions involving partitions on different hosts. The latter one works well for parts of (our) application since they just don’t use transactions. Other parts will have to use XA. At this point I am not sure about the overhead XA will add but this has to be worked out once we come to this.

So, any feedback is welcome!

Yesterday I went through hell while testing the MySQL Proxy partition Lua scripts I am working on in a high concurrency environment. I am sending multiple queries to the server and build up a combined result set in read_query_result - something like this. The proxy returned weird results complaining about multiple result sets being sent from time to time at totally different places even though I was sure that for each query only one result set has been sent. And some of the results were just wrong, a lot of our tests failed unexpectedly.

After long hours flicking almost every switch I simply removed the Lua module declaration from the main LUA script passed to the proxy via proxy-lua-script since it was the only thing left that changed. And then everything worked just fine!

I guess defining the proxy Lua script as a module messes up the scope of the script, but I haven’t researched that yet. So for now I just don’t declare the main proxy Lua script as a module and everything works fine.