Load

MySQL

Performance Tuning

1. First, Check innodb and myisam settings

Run the below to get the size of data in INNODB and MYISAM tables:

mysql -e 'SELECT SQL_NO_CACHE Engine, count(*) "# of Tables", concat(round(sum(table_rows)/1000,2),"K") "Rows", concat(round(sum(data_length)/(1024*1024),2),"M") "Size of Data", concat(round(sum(index_length)/(1024*1024),2),"M") "Size of Indexes", concat(round(sum(data_length+index_length)/(1024*1024),2),"M") "Total Size", concat(round(sum(index_length)/sum(data_length)*(100),2), "%") "% Data in Indexes" FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC LIMIT 10;'

In /etc/my.cnf, change:

innodb_buffer_pool_size - set this to the value from the Total column, innodb row. (Note: if server has lots of RAM, and lots of data, set the innodb_buffer_pool_size = 1G, then set innodb_buffer_pool_instances = 4 (or smaller, or larger, depending on RAM))

key_buffer_size - set this to the value from the Index column, myisam row.

MySQL Error log

To check the MySQL error log, run:
mysql -e "show variables like '%log_error%';"

This will show the location of the MySQL error log, which you can then grep, tail, etc.