Tweaking MySQL is something you need to do regularly. Unlike PHP & Nginx tweaking, this is not a set & forget job!
We will use mysqltuner for tweaking mysql on a regular basis.
Tweaking MySQL default config first
Open /etc/mysql/my.cnf
file & scroll down to [mysqld]
section.
You will see many settings & some config variables. Some values are global while some are per-thread values. Its important because if you change something likejoin_buffer_size
from 2M to 4M, it can shoot-up mysql’s max memory utilization by 300M memory (as per default 150 mysql’s max_connections
value)
To start with, adjust following values:
max_connections = 50 #default is 150 wait_timeout = 30 #default is 28800
You can leave remaining as it is. Mysqltuner will guide you further.
Don’t forget to restart mysql. Command: service mysql restart
Using mysqltuner
If you are following our setup, you may already have mysqltuner installed. Otherwise run apt-get install mysqltuner
on Ubuntu. Non-ubuntu guys can get it from here. It’s just a perl script!
When you run mysqltuner, it will show you a report with many suggestions. Just follow them. Exact suggestion will vary so its hard to cover all of them here. Rather I will give you some notes some of them are offered by mysqltuner itself.
Notes:
- Run mysqltuner after 24 hours. It you don’t, it will remind you by showing “MySQL started within last 24 hours – recommendations may be inaccurate.” Reason: mysqltuner recommendation may prove inaccurate.
- If it asks you to change value of
tmp_table_size
ormax_heap_table_size
variable, make sure you change both and keep them equal. These are global values so feel free to increase them by large chunks (provided you have enough memory on server) - If it asks you to tweak
join_buffer_size
, tweak in small chunks as it will be multiplied by value ofmax_connections
. - If it asks you to increase
innodb_buffer_pool_size
, make it large. Ideally, it should be large enough to accomodate your all innodb databases. If you do not have enough RAM consider buying some. Otherwise try to delete unwanted database. Do not ignore this as it can degrade performance significantly.
Apart from above, always keep an eye on following lines in Performance Metrics
section of mysqltuner report:
[--] Total buffers: 2.6G global + 130.6M per thread (100 max threads) [OK] Maximum possible memory usage: 15.3G (48% of installed RAM) [OK] Highest usage of available connections: 81% (81/100)
Try to keep maximum possible memory less than 50%. Other lines can tell you, if your site is using too “less” mysql connections. In that case, you can reducemax_connections
and increase other buffers more generously.
Also, whenever you make changes to mysql config and restart mysql server, always run mysqltuner immediately to check if by mistake you haven’t made maximum possible memory usage too high! Ignore any other suggestion it will give for next 24-hours!
mysqltuner & automatic password
As we use mysqltuner many times, it will be convenient use something like this.
Tweaking MySQL default config first…
I open /etc/mysql/my.cnf to tweak as you suggest, but there’s no wait_timeout setting in it. Please where to find it, or can i add this to /etc/mysql/my.cnf ?
Sorry, for my bad English.
You need to add new lines if you don’t find one. So please add “wait_timeout” line.
I cant really remember the export command before running mysqltuner.pl, it is exporting mysql variable first before the script execution. You have any idea?
I never ran any export command before running mysqltuner. You can run it directly.
you might want to mention that this will uninstall percona and should only be used if your using MySQL.
First, mysqltuner doesn’t uninstall percona.
Second mysqltuner can be used on oracle-mysql as well as percona-mysql (tested on this server only where we are using percona-mysql 5.6)