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. (You're looking for stuff like innodb errors, disk space issues, connection messages, etc)

View MySQL Variables

You can view the variables that MySQL is using with:

mysql -e "show variables like 'max_connections%';" - This shows the max_connections variable. You can substitute any MySQL variable here.

This is helpful for database connection errors, or if a change to /etc/my.cnf isn't 'sticking' (In that case, look for other my.cnf files)

View Currently Running Queries

mysqladmin process stat - This will show the queries that are running, how many threads, Open tables, slow queries, and a bunch of other helpful stuff. (Note that you can "tail -f" by running: watch mysqladmin process stat )