Parallel Ltd. was founded in 1998 and is based in Milton Keynes, United Kingdom. Our main product is SatManage, a web-based application for managing satellite networks.
At Parallel we've used Partitioning since the very first release of 5.1 (sometime December 05). We're now on MySQL 5.1.20 and 5.1.22 and we started development using partitioning with MySQL.5.1.3.
Purpose of our application
SatManage is the world's only Management System specifically designed for Satellite Networks. In analysing satellite circuits rapid access to significant amounts of history is essential - we need direct CGI speed access to historical data sets in up to 10 tables, each one many tenths of gigabytes in size and with 500M+ rows in. Our development systems run on CentOS 5.2. The application is written in Perl, Java and C++ and is deployed on CentOS 5.2 using a Dell R900 Server, 8*15k SAS Drives in RAID 10 array.
How partitioning meets our needs
Our product historically used an Oracle backend, and after implementing simple date based range partitioning we got 20-30 time performance increase in our queries. We had used MySQL in other more minor products we had developed, but we couldn’t move to it until partitioning was implemented. In Dec 05 we started to test MySQL partitioning, where partitioning syntax was supported but the optimisations were not in place. As soon as the optimisations we put in, we were able to run a side by side comparison against unpartitioned tables and Oracle partitions. Not only did we get in MySQL a 30+ times performance increase, but the MySQL version ran nearly twice as fast as the same Oracle configuration in a like for like test.
The documentation has been pretty useful documenting syntax and method, although it would have been useful to have more examples of partitioning schemas used in the real world and the performance gains. However I believe that setting up the ‘correct’ set of portioning and index configurations in a particular situation is as much of an art than a science, and really forces DBAs and Developers to understand exactly what MySQL does behind the scenes, and how best to ‘help’ with the right partitioning setup. It is really down to real MySQL users to detail their situation, what partitioning schemas they tested and how they performed. I don’t know if there is currently a place for this, but if not it would be a very useful addition to help new DBAs with partitioning schemas.
We have tested other databases, but have not found anything as fast as well partitioned MySQL for our data sets. It may be the case that in some other situations Oracle is faster, but MySQL is the database for us! It is also worth mentioning that in 3 years of using MySQL in one form or another, to my knowledge we have never lost a row of data even with hard crashes and unscheduled power outages.
We have built our own engine to automatically merge older partitions e.g. to keep a 1 day, 1 week, 1 month per partition going backwards through time. We are very much looking forward to being able to change the Engine type for each partition. The ability to change the engine types to get ACID compliance on the ‘current’ partition, but compressed, fast read-only access to older data will be enormously powerful.
We have been using versions of 5.1 in production environments for very high profile clients (e.g. the United Nations) for over 18 months, after carefully testing each release in our dev environment. We have found a few bugs in certain releases, all of which already had bugs opened for them and were all fixed in the subsequent release. We maintain a test script which has examples of all the functionality we use in various queries so we can easily each new release and be fairly sure that if a new release passes our test script.
Full article...
