MySQL is one of most known and popular open source database engine. It helps application owners to run their applications or sites on a reliable platform in a cost affective way.

I have been thinking from some time to do some write up on Mysql Optimization as I have found that many people keep running the stock form of Mysql service and they are missing the great benefits they can achieve by doing some extra effort and Optimizing Mysql according to their server/vps or application behavior and usage.

The first main area for optimization is good usage of Mysql Variables and adjusting their values to gain performance. Secondly slow query logging is another major area for Mysql Optimization and thus achieving lower loads on your servers. I wrote about enabling slow query logging in one of my earlier post here , you can check it to see the steps for enabling slow query logging. Thirdly the use of some tuning application/scripts that help tune settings for your Mysql instance by findings the highest resource consuming users and queries.

I came across a video by Dani Wallace from cPanel few weeks back which was specifically prepared on Mysql Optimization and it covered most of the areas I was looking to explain, in such a good and comprehensive way, that I changed my mind to write a lengthy write up myself and instead to link the video here, a video presentation helps getting a good grasp on the topic.

The video discusses following areas :

  • Introduction :  i.e what exactly is Mysql Optimization and why one should do it
  • Default Settings :  default setup of /etc/my.cnf configuration file and desc of those variables
  • Mysql Variables : commands to check the variables and introduction to common variables
  • Logging : Type of logging and its role in Optimization
  • Monitoring : Commands and tools ideal for monitoring Mysql activity
  • Templates : Introduction and opinion about templates provided by Mysql
  • Diagnostic Tools : Tools to diagnose users/queries taking highest resources to help tune and optimize Mysql accordingly
  • Test Results : Some test results of performing Optimization based on different tools and improving server performance by reducing server loads

I will do another write up for installation and usage of all Diagnostic tools soon.

Tags: , , ,

Leave a Reply