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.
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.
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference: Pinal Dave (http://blog.SQLAuthority.com)