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 oftmp_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.

mysqlcheck with cron to optimize automatically

PREVIOUS

Emails

NEXT

Comments

Peter

February 19, 2013

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.
1. Rahul Bansal
  
  February 19, 2013
  
  You need to add new lines if you don’t find one. So please add “wait_timeout” line.
Dendi Pudge

October 25, 2013

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?
1. Rahul Bansal
  
  October 25, 2013
  
  I never ran any export command before running mysqltuner. You can run it directly.
KJ

March 22, 2014

you might want to mention that this will uninstall percona and should only be used if your using MySQL.
1. Rahul Bansal
  
  March 22, 2014
  
  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)

Features

Useful Links

Product

Other Products

GoDAM

Subscribe for curated product updates, tips, and announcements.

"*" indicates required fields

This field is for validation purposes and should be left unchanged.

Email*

SIGN ME UP