MySQL Performance – Slow Query and innodb_buffer_pool_size

While primarily I help my client with SQL Server Performance Tuning via Comprehensive Database Performance Health Check, there have been moments where I have helped my clients with MySQL Performance when I know the solution. Let us discuss Slow Query and innodb_buffer_pool_size.

MySQL Performance - Slow Query and innodb_buffer_pool_size bufferpool-800x225

During a recent consulting engagement, the client had a query running very fast in SQL Server but running very slow in MySQL. The power of the hardware was very much the same for both the installation. They wanted me to help them understand why the MySQL query was running so slow whereas the other was running very fast.

We did quite a few tests and changed lots of configuration parameters. However, we were not much successful. After a while, we took a look at the MySQL config file. You can find the MySQL config file at the location here in Linux: /etc/mysql/my.conf

In this file, we found a value innodb_buffer_pool_size and it was set to a very slow value. We changed the value of this variable to a higher value (from MB to GB) and we were immediately able to see the performance improvement of the query. It was such a simple solution to a problem that we all spent lots of time solving it.

You can also find the value of a variable by running the following script:

show variables like 'inno%'

Do not forget to restart MySQL services after changing the value of the config file or it will not take any effect.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
Experience – My Very First YouTube Live
Next Post
MySQL Performance – 5 Parameters from Config File

Related Posts

Leave a Reply

Menu