pero on anything

A lot of people keep asking me about the status of HSCALE so I thought it is best to just write about it.
Since I am waiting for the next GPL release of MySQL Proxy I concentrate on other things, both project-related and not project-related.

Continuous integration and test strategy enhancements

First of all there is a CI server up for almost 3 months now. Check out teamcity.hscale.org (Login as guest user). I also introduced a lint-process to discover bugs in my LUA code which arise mostly by mistyping variable names. By the way: You should definitely have a lint-like process in place if you are doing LUA programming or other languages of that type. It helps a lot.

What’s in svn trunk?

Multiple backends

The current status is this: Spreading across multiple backends is fully implemented and there are a lot of tests for that. Check out the latest code at http://svn.hscale.org/trunk. The tests running against multiple backends are “cheating” a little bit: Since the proxy still does not allow for ad-hoc allocation of backend connections I just create a lot of “dummy” connections to the proxy and use the approach of connection pooling the way the proxy does it. See an example. Note: This will not work in production!

Dictionary partition lookup and auto-partitioning

Besides the modulus partition lookup, which is only used in tests, there is now the dictionary partition lookup which allows for explicit partition definition. Detailed documentation can be found in the project wiki. It works as described here.
Another feature implemented is auto-partitioning. Depending on the partitioning function used it is possible to create new partitions on the fly. This way you can automatically spread the load across your MySQL servers. Another benefit is that you have a fine grained partition set-up right from the start which makes re-partitioning a lot easier afterwards.
Please take a look at the code and especially the tests to see how it works.

Parallel setup of HSCALE servers

In order to eliminate the SPOF (single point of failure) and possible bottleneck that directing all traffic through a single proxy would imply, HSCALE is designed to run in parallel. This means you can set up multiple proxies running HSCALE in parallel. This way you can easily implement fail-over scenarios using heartbeat or your favourite high availability solution and spread the load.
Why should running HSCALE in parallel be a problem in the first place? The easy answer: Because partition information is cached within each instance. As soon as the partition information changes your HSCALE instances would run out of sync using different partition information which would be disastrous to your data integrity. To avoid this HSCALE (more precise the dictionary partition lookup) works in two different modes: “NORMAL” and “FORCE”. If the mode is set to “NORMAL” then partition information is cached internally and only refreshed in a configurable time interval (configuration parameter “reloadInterval”). Whenever a change is made to the partition set up the mode is changed to “FORCE” which forces all HSCALE instances to re-fetch the partition information prior executing any query. After a configurable amount of time the system switches back to “NORMAL”. This is only the big picture. The implementation itself is a bit more complicated.
This approach is simple and robust because no other components are involved (like message queues) and it is guaranteed by design that no HSCALE instance is able to run with a wrong partition mapping. Changes made to the partition information involve a little overhead since all HSCALE instances will reload the partition mapping quite often. But taken into account that the partition information does not change that often (100 times a day would be huge!) it is an affordable price to pay for data integrity.

What’s next

Currently HSCALE is almost feature complete. The thing that’s missing - and making HSCALE production ready - is a MySQL Proxy version with different backend handling (Jan, please do not hate me!). Currently we (our company) do not have the resources digging into MySQL Proxy ourself and HSCALE is currently(!) not top-priority. So we will just wait and see.

Part of the problems we intended to solve with HSCALE are now moved to a Hadoop cluster since we have huge masses of log-style, read-only data which has to be analysed in multiple dimensions. HSCALE will gain focus right after that or as soon as a “suitable” GPL version of MySQL Proxy comes out.

If you are in need for a production ready, proxy-based sharding solution, please take a look at Spock Proxy. They use a different approach - they actually forked MySQL Proxy and implemented everything into it, thus no LUA is used - but the idea behind it is basically the same. They also offer some features HSCALE will not offer in the near future like handling of auto_increment columns across partitions. Some features are not there mostly because of the different design approach like arbitrary partitioning functions (they only offer range-based partitioning which is ok for many scenarios) or query hinting.

Speaking of Hadoop cluster - an idea that is ringing in my head for a while is to implement a MySQL Proxy LUA script that enables running (basic) queries against a cluster. It would be a little fun project.

While evaluating LuaSQL as backend connection replacement I came across this. I did a quick performance test using mysqlslap and it showed that just reading and copying the result can be significantly faster with LuaSQL.

Benchmark details

What I did was just sending the query to the backend and building up a new result-set in LUA.

This is the code for LuaSQL:

And this is the code for using MySQL Proxy only:

As you can see we do nothing but copy the result-set in LUA. This mimics the result-set aggregation HSCALE does if a full partition scan is necessary.

Results

Using LuaSQL:

Using MySQL Proxy only:

For comparison: Using empty read_query and read_query_result functions:

Versions used: MySQL Proxy 0.7.0 (svn-rev 511), LuaSQL 2.1.1, MySQL server 5.0.51a, mysqlslap 5.1.26rc

Of course I repeated the tests several times to verify the results.

Without digging too deep into the source of both MySQL Proxy and LuaSQL the biggest difference is that LuaSQL pushes the result-set row-by-row onto the LUA-stack whereas MySQL Proxy puts the whole result.

Update: As Jan points out below this is not true. MySQL Proxy puts the result row by row onto th LUA stack, too.

Update #2: The tests above ran on my Notebook (MacBookPro 2.4GHz, 4GB RAM running Ubuntu 8.04 64 bit). They are reproducible. Running the same tests on an 8-core-server putting the MySQL database on another server results in the MySQL Proxy version running slightly faster (about 2-5%) than the LuaSQL version.

Conclusions

Even though this tiny benchmark showed that the speed of LuaSQL seems to be feasible, there are still drawbacks. First of all: Depending on your workload only a fraction of your queries need result-set altering. Namely it’s only full partition scans that need this. Most of the time you just need to change the table name or the backend. And then LuaSQL is 100% slower than MySQL Proxy alone.

Another downside of LuaSQL is that it does not return the mysql field types but only the LUA types. This makes it impossible to build up a correct result-set that can be sent back to the client.

So still we need suitable (for HSCALE) backend connection handling in MySQL Proxy if we want higher performance.

Built-in result-set merging would be a big win, too. Then we could even have streaming combined result-sets taking the memory pressure from the proxy (since every result-set has to be fully loaded into memory).

That said I think about using LuaSQL for configuration handling since it is a lot easier than doing it via proxy.queries:append -> read_query_result -> proxy.queries:append -> ....

After working on build and test improvements (for example incorporating lualint and LuaCov) as well as other lua “side-projects” (i.e. Log4LUA) we are running towards HSCALE 0.3.

The focus of the forthcoming version 0.3 of HSCALE is Dictionary Based Partition Lookup. Using this partition lookup module lets you take full control over how your partitions are created and where they are actually located.

Update: Dictionary Based Partition Lookup is fully implemented. See this blog post and the wiki page about it.

Please note: Due to the problems with backend connection handling version 0.3 will still focus on single server backends. Even though support for multiple backends is already implemented in HSCALE. Please look at the proxyUnit tests for a glimpse on how multi-server backends will be handled.

Please check out the current development snapshot at svn.hscale.org to see what is already there. Currently the partition lookup is fully functional but the administrative commands and some further hashing functions are missing (see below).

How does dictionary partition lookup work?

As the name implies partitions are looked up in a dictionary. The dictionary itself is stored in the main database and cached internally. So now you can freely move partitions around and create new ones.

What is done internally is this:

1. Apply a hashing function (read further) to the value.
2. Lookup the partition based on the hashed value.
3. If no partition has been found use the default partition created for every partitioned table.
4. Return the partition (and assigned backend)

Hashing functions

To reduce the number of partitions to be created and the overall administration overhead, a hashing function may be applied to the partition value before the partition is looked up.
Currently there are 3 hashing functions available:

• MOD(X): A modulus function grouping X partition values together. Works only for numbers of course. If you have *lots* of different partition values with a smaller number of rows each, then it might be better to group them together instead of creating *lots* of partitions.
  Example: Using MOD(3) the values 1, 4 and 7 will end up in the same partition.
• PREFIX(length) Partition values are grouped together by the first length characters. Works on everything (everything is treated as string).
  Example: Using PREFIX(3) the values “foo“, “foobar” and “footaliciuos” will end up in the same partition.
• NONE(): This function does … nothing! Use it if you really want a 1:1 relationship between partition values and partitions.

Further hashing functions are planned (and might make it to version 0.3):

• DIV(X): As opposite to MOD(X) this function divides the partition value by X. So this like a fixed range function. While MOD(X) creates at most X partitions DIV(X) creates infinite number of partitions.
• DATE(pattern): Enables date-range based partitions.
  Example: Using DATE("%Y-%m") will group by (year-)month.

Administrative commands

Because handling of partitions is a delicate thing, creation and maintenance of partitions should not be left to some obscure SQL-statements. Therefor the dictionary partition lookup will provide administrative commands that try to avoid mis-configuration. It will be checked whether partitions overlap etc.

Because this is still work in progress the commands might change.

Table setup

First of all your table has to be set up:

The table has to exist before and will be renamed to the default table name ('users_default' in our example). SETUP_TABLE can only be called once per table.

Add partitions

The example above creates a partition with the name 'nick1' for table 'users' and partition value 'mar' (users with nickname 'marvel', 'martin' etc.). The partition name will directly reflect to the table name of the partition. So the table for this partition will be users_nick1. Usually you would use the partition value ('mar') as partition name but you don’t have to. You are able store multiple partitions in the same table. This sounds strange but it makes sense to define a finer partitioning scheme upfront and actually use a wider scheme until you really need to split up data. This makes it a lot easier to split things up afterwards.

Moving partition data

In version 0.3 partition data will not be moved to a newly created partition. You will have to do it by hand. This will be implemented as soon as multiple backends are fully supported because that implies a different approach since data has to be moved between different servers then. An administrative command to move partitions will also be available then (HSCALE MOVE_PARTITION(...)).

Multiple instances of HSCALE working on the same data

HSCALE is designed to support multiple instances of it running in parallel mostly to avoid to be the bottleneck and single point of failure. Every instance of HSCALE periodically refreshes the internal partition configuration to reflect changes made by another instance. In the case of creating and moving partitions, partitions will be locked inside HSCALE so all clients will wait until the operation finishes. This guarantees data integrity.

Version 0.3 will be released within the next few weeks but definitely after MySQL Proxy 0.7 has been released so we can thoroughly test it against the newest version.

Please feel free to discuss certain features and design decisions shown above. Any feedback is welcome!

UPDATE Log4LUA 0.2 released. Go to the project page.

After the dust about backend connection handling in MySQL Proxy had settled, I begun working on other parts of HSCALE again. (I’ll return to the backend handling later after talking to Jan Kneschke about their plans.)

One of the issues that bothered me the most was logging. Until now I just had used a simple debug function that could be enabled or disabled using an environment variable. The pretty straightforward approach a lot of people use. But there is more to logging than to print debug messages. So I searched the web and of course I found LuaLogging. It is more flexible, has different levels (DEBUG, INFO, WARN, ERROR, FATAL) and appenders. You can print your log messages to the screen, write them to a file or send an email.

After working with other logging frameworks in other languages (mostly Log4X like Log4J) I got used to a number of features missing in LuaLogging:

• There are no means of external configuration. The logger is configured inside the code. You cannot use different configurations for development and production out of the box. This is crucial in my opinion, since during development it is handy to enable all log levels and print everything on screen, while in production you want to log to a file and disable at least the DEBUG level.
• You get no information where the log event came from. Log messages are more meaningful if you know where the log message has been created, i.e. in which source file and line position. This makes it easier to track down bugs and errors.
• There is no log category concept. Having different log categories makes it easier to group log messages either by source or by meaning. As an example you could have one log category called “core” where all core messages go to and another “connection” where all connection related log messages go to.
• You cannot use multiple appenders. In production you might want to log all messages to a file and get an email for all errors.

Since adding all this functionality to LuaLogging would in fact mean to re-write it, I wrote yet another logging facility and called it: Log4LUA. I choose the name because this package behaves almost like Log4X.

Download Log4LUA

A detailed documentation on how to use it can be found here (luadoc).

Features

• External configuration. Configure your logging system via a config file.
• Logger categories Configure different categories for different logging tasks.
• Detailed information available: source file, line, function or the whole stack trace
• Console, file and smtp (email) appenders
• Multiple appenders per category Log to a file and get the worst errors by email
• Level threshold for smtp appender. Don’t get every log message by mail only the ones above a defined level.
• Log file rotations for file appender. Based on a date pattern

Basic usage

First write a configuration file (say log4lua.conf.lua):

In your lua source (say mycode.lua) use the following to get a logger:

Start your program with:

For more examples look at the HSCALE sources. Log4LUA is used everywhere.

Please feel free to comment on this. Or send usage or bug reports. I will setup a project page later on.

Recently I added the first code to support multiple MySQL backends in HSCALE (see svn.hscale.org). As a “side note” I have to thank Giuseppe Maxia for MySQL Sandbox which made multi server testing a bliss!

While coding this I started to feel that writing HSCALE on top of MySQL Proxy is no more as easy and clean as it started out to be. And finally I reached the point where I have to say:

HSCALE (and maybe other advanced multi-server applications) and (current) MySQL Proxy don’t fit very well.

Before I go into details please let me make clear that this is mostly due to the specific nature of MySQL Proxy being a connection and protocol based proxy. MySQL Proxy is great and there are a lot of cool applications that fit perfectly with it.

Aside from some other minor glitches (like missing tokens in SQL tokenizer) the biggest show stopper is:

Handling of multiple backends

The biggest problem I had to face is the handling of backend connections in MySQL Proxy. What I would need for HSCALE are dedicated connections to every backend for every proxy connection. So if a client connects to the proxy, a connection to each backend is opened and attached to this particular client connection. This way I easily could maintain the state of the connection by sending commands like SET variable or USE database to all backends.
Dedicated connections are vital for XA or transactions in general, too.

The way the proxy handles backend connections right now is somewhat cumbersome (See an example.). The way you have to maintain your own connection pool is hard to understand and uses too much “magic” in my opinion. And the pool only grows with the number of connections made to the proxy. People are having problems with this approach (read the MySQL Proxy forum, for instance this thread).

The current experimental multi-server code in HSCALE uses this connection pooling technique since it is the only way to establish connections to other backend servers.

So what can we do now to implement multi-server support in HSCALE?

1. Wait until MySQL Proxy supports dedicated backend connections This would be the easiest and most elegant solution. But it could be that Jan Kneschke and the other developers decide that this is not what they intended to do with the proxy. And this would be totally ok from their point of view! Even if they decide that this is a cool feature it could take a long while until it is implemented. (Hint: I would gladly help out here ).
2. Work around this using luasql While possible this solution would not be preferable since we are using two technologies for the same thing.
3. Switch to another proxy There are quite a few. This would be no fun and I did not take a look at them all to see if it is even possible. It would be complete rewrite though.
4. Fork MySQL Proxy or implement a plugin The new plugin technology could be used to implement the multi-backend connection stuff myself. As a last resort, I could fork the whole project (like Spock Proxy did, see below). This would still mean a lot of work though and loss of the MySQL efforts put into the proxy.
5. Use other sharding technologies and eventually abandon HSCALE As an example Spock Proxy, a fork of MySQL Proxy does a great deal of what we intend to do with HSCALE.
6. Re-implement HSCALE as a JDBC driver Since our applications are written in Java exclusively we could do that.

Of course I would love to continue HSCALE as a MySQL Proxy application! The main reasons are the efforts MySQL is putting into MySQL Proxy, the extensibility we gain combining multiple LUA scripts (there are more things we do with the proxy apart from HSCALE) and last but not least the community echo HSCALE already received.

As a next step I will take a deeper look into the proxy code and evaluate the efforts needed to add dedicated connections.

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!