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
/etc/mysql/my.cnf file & scroll down to
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 like
join_buffer_size from 2M to 4M, it can shoot-up mysql’s max memory utilization by 300M memory (as per default 150 mysql’s
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
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.
- 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
max_heap_table_sizevariable, 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 of
- 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 reduce
max_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)