On MySQL
October 19th, 2008 Radim Marek
This week I had fantastic opportunity to attend a MySQL training delivered by George Trujillo. As a long time PostgreSQL user, you might understand my hesitations about this database. But as it goes, first impression can be misleading. And don’t get me wrong if you would think I haven’t been using MySQL at all. As it’s sometimes the only choice with some hosting solutions, my applications are often relying upon it; and of course, event this blog is backed by it.
The most important thing I got from this training, is the fact I now understand how important introduction of SQL Modes in the version 5.0 was. Many of the MySQL Gotchas you can find on-line are deprecated by the selection of the appropriate mode. Probably due to historic reasons, the ‘dumb’ mode is still default setup.
Second important fact I learned is related to ‘love-or-hate it’ MyISAM storage engine. Although I wouldn’t ever vest my data into it’s reach, there are ways how to benefit from it’s features. The answer comes together with replication. I expect we’re all well acquainted with the master-slave RDBMS replication scenarios. Especially used for read-only usage. So far no problem there. But only this training allowed me to understand how different setup, where different storage engine is used on the master and the slave machine, can improve performance and scalability of such a solution. Imagine for example situation where data from InnoDB/PBXT/Falcon table are replicated to fast MyISAM/Maria storage on the other node used for retrieving data. Not only you’ll get benefit of added CPU/IO, but also much faster storage for the read-only scenarios. And with multiple slaves your options are unlimited.
So if I would use George’s words, it is important to understand that MySQL is not aircraft carrier as Oracle, but much rather a speedboat. Got this right and the solution for it’s use it’s there.
With this knowledge and information how to tune-up MySQL installation from other resources I didn’t resist and performed some performance tests to compare it with PostgreSQL. As far as my current projects concerned, MyISAM solutions hugely outperformed everything I was expecting in predominately read scenarios. PostgreSQL setup then delivered excellent results in mixture of read/write scenarios, and comfortably leaving InnoDB engine behind - except for speed of INSERTs (even with use of prepared statements). PostgreSQL strengths were at ease with the consistency and scaling-in of the setup. Out-of-the box simplicity of MySQL than showed perfect setup when solution scaled-out to the second server.
Now you can ask the question if I do have an answer for my question what open source database should I choose? No. It’s back to evaluation of which solution would perform better in particular scenario. But this training provided great overview of MySQL, it’s architecture and benefits; and most importantly it showed me how to use MySQL weaknesses to get benefits out of it. Momentum the MySQL is now gaining by Sun acquisition certainly can’t be overlooked.
Although I was already conformable with around 80% of presented information, I would strongly recommend MySQL Training range to everybody. Please, check our pages for the scheduled dates or contact me directly to discuss private training.
Please, performance results in this post are subjective - related to my existing projects and point of view from which I evaluate database server.
Links:
Posted in MySQL, Uncategorized | No Comments »