How to find MySQL queries worth optimizing ?
pt-query-digest mysql-slow.log # More info here: https://www.percona.com/blog/2012/09/11/how-to-find-mysql-queries-worth-optimizing/
Disable query cache by setting query_cache_size = 0
the query cache is a well-known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your MySQL application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it. https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/
yum install mysqltuner
mysqltuner
mysqltuner not available in the repositories installed on this server?
wget http://mysqltuner.pl/ -O mysqltuner.pl perl mysqltuner.pl
innodb
innodb_file_per_table By all means, set this. If you do not, the InnoDB system file, known as ibdata1, will grow uncontrollably. If you already have tables loaded inside ibdata1, you will need to cleanup the InnoDB infrastructure in order to keep ibdata1 as small as possible.
innodb buffer pool
/etc/my.cnf: innodb-buffer-pool-size = 2G innodb-buffer-pool-instances = 2
Max_used_connections
The maximum number of connections that have been in use simultaneously since the server started.
Max_used_connections_time
The time at which Max_used_connections reached its current value.