MySQL Tweaking

MySQL Tweaking

Login to the server and check out your my.cnf file.. To check whether your server is using the correct resourses perform the below commands and checks !!

Access the mysql prompt,

mysql> show status like %Open%_tables;
mysql> show variables like table_cache;

Run the above two commands and check Open_tables and Opened_tables If Opened_tables is big, then your table_cache variable is probably too small. So increase the table_cache variable. Open /etc/my.cnf and change/add table_cache=newvalue.

Run the following commands to check key_buffer_size, key_read_requests and key_reads

mysql> show variables like %key_buffer_size%;
mysql> show status like %key_read%;

If key_reads / key_read_requests is < 0.01, key_buffer_size is enough. Otherwise key_buffer_size should be increased.

mysql> show status like %key_write%;

command to check key_write_requests and key_writes, If key_writes / key_write_requests is not less than 1 (near 0.5 seems to be fine), increase key_buffer_size.

Continue reading…