I’ve finally decided to learn everything I could about MySQL performance tuning; we’re working on highly tuned appliances and this is a must for high-traffic environments.
I’d like to share my first findings on interesting stuff and encourage comments on the matter, which seems as deep as any science.
These last days we’ve discussing about this at the office and we couldn’t agree about the type of database configuration using MySQL was optimal for the broadest range of users.
It’s much easier to tune everything if you already know the exact environment, available hardware and so on, rather than trying to tune a database for a broad range of people going to install a product.
Enough of introduction anyway, I want to get to the bottom of this issue once and for all, so for a beginning I ordered these three books:
- MySQL Administrator’s Guide Paul du Bois (Autor)
- MySQL High Performance Jeremy D. Zawodny (Autor), Derek J. Balling (Autor)
- MySQL Clustering Alex Davies (Autor), Harrison Fisk (Autor)
Besides the books, which should arrive in less than a week, I’ve been googling quite a lot. I really have to try out mirroring an event table into memory from where events are being fetched and writing a copy into disk. I also see that a lot of index and key optimization can be done in all the involved databases.
Anyway, it’s too early to reach a conclusion, in the meantime here’s a very interesting piece of reading about index optimizations.
Other interesting options:
- Clustering using mysql blackholes
- Vertical or Horizontal partitioning
- Caching certain tables in order to greatly improve performance (ossim_event, event, plugin_sid and some others with heavy usage)
- Correctly assign indexes to all the involved tables without increasing disk usage too much
Anyway, if you’ve got an opinion, suggestion or any sort of input about all of this, please share it on the comments section or send me a mail and I’ll post conclusions here.
