It was a step we had to take. It took us quite a while to decide, even though it was something we knew would eventually cause problems, as it finally did.
A couple of weeks ago, on an ordinary day, a routine process in the warehouse management program took our website down. It was not especially serious, since 20 seconds later the site was back up and running without problems, but it was the last straw and pushed us to change the engine of the MySQL tables.
The problem was that the default engine in osCommerce, MyISAM, is much faster when performing read queries, but on the other hand it locks a table for the time it takes to run a query, and if it is a gigantic table with 135 queries queued up waiting to access that same table, we have a problem and, as a result, the website locks up. Once the “blocking” query is killed everything returns to normal, but… until when? And worse still, what if we are not there to notice?
So we decided to cut our losses and, on the recommendation of the person responsible for our servers, switched to InnoDB. To do that, we prepared a battery of tests and checks that we ran before and after the engine change on our local servers and then in preproduction. Although there was a risk with joins between tables, the huge number of them in our code did not allow us to analyze them one by one in detail, so we chose to run this fairly complete battery of tests.
Once we had checked in preproduction that everything was going as expected, keyboard in hand and eyes glued to the screen, at 5:00 in the morning—when there are hardly any visitors—we gathered our courage and started with the database backup and immediately afterwards with the change of table engine. First the heaviest ones one by one and then the lighter ones in groups, so as not to penalize the performance of those few brave souls who might be visiting us at such ungodly hours. In just over an hour and a half we already had everything changed and the battery of tests run. No errors, no issues, nothing strange. Strange, right? Well, it’s true: this time Murphy was on our side.
The results we have obtained are the expected ones: no table locks at the cost of slightly worse performance in 10% of cases, something we hope to solve shortly by splitting the two or three mastodonic tables we have into parts. In a few days—or weeks—I’ll tell you how that split went too. I encourage you to read it then, but meanwhile I’ll leave you a couple of links for anyone who wants to go deeper into the subject:
- http://www.arsys.info/programacion/myisam-o-innodb-elige-tu-motor-de-almacenamiento-mysql/
- http://dev.mysql.com/doc/refman/5.0/es/converting-tables-to-innodb.html
- http://stackoverflow.com/questions/7335515/migrating-from-myisam-to-innodb
Leave a Reply