Tip #21: Optimizing MySQL’s InnoDB engine
The Guru was browsing through a list of old links the other day, and came across an article on optimizing MySQL’s InnoDB storage engine. It’s definitely worth a read if you’ve never seen it before, but if you’re just after the high level summary:
- Add more memory to your server. Memory is good.
- If you have lots of memory, don’t forget that you should be running a 64-bit O/S, with the 64-bit version of MySQL, if you are going to be able to utilize that memory!
- The OpenX ad server is a write intensive application, so RAID-5 is bad. Very, very bad. Use RAID 0, or RAID 10.
- Tune your InnoDB settings. Remember, tuning is a process of measurement, followed by making a change, and then re-measuring.
Just curious, does the Guru recommend InnoDB over MyISAM for OpenX?
InnoDB or MyISAM? Depends on your installation size. If you have a relatively small OpenX install, MyISAM is probably all you need, and using InnoDB will simply add additional complexity and optimization issues, meaning you’re likely to have a slower install (not as a result of MyISAM being slower, but as a result of lacking the time to be able to setup and optimize InnoDB properly).
However, once you start getting some relatively serious volume, and given that OpenX is a fairly write intensive application, then InnoDB will almost certainly be a better choice, as a result of not having table-level locking (which tends to cause issues with delivery of banners whenever maintenance runs, and locks the data tables, preventing OpenX from writing to these tables when banners are being delivered…)
Does the maintenance script do everything a database needs to be well kept, or do we have to optimize and flush tables every so often? We handle nearly 50 million impressions per day. Dedicated db server, innodb, raid 0, four frontend php/web servers, and two load balanced banner servers.
thanks in advance!!
How much the maintenance script does depends on which version of OpenX you are using — through the years, the script has added more and more functionality to try and keep the database in good shape.
However, you are right in thinking that the database will not be fully maintained by OpenX itself — part of successfully running a large scale database system will always involve monitoring your database, optimizing tables, etc. If you have any specific tables that are growing especially large, then it may be that your version of OpenX is not pruning out old data that is no longer required, and you will be able to reduce your database size by manually pruning the tables; or you may find that certain settings like the “grace period” in OpenX 2.4 and OpenX 2.6 can be reduced to help keep the database size down as well.
It’s always best to check which table(s) you want to prune, how much you are going to prune, and in which version of OpenX this is going to be, though, before removing data — and of course, taking a backup before pruning is never a bad idea.