We have a webapplication for about 5 years that is created by programmers in india.
It is started as a small apllication and turned into someting larger.
We have a few performance issues for some queries. Our programmers a now optimizing a few individual queries.
But i want to optimize the overall performance. I just logged into phpmyadmin and checked the myql status.
There are a lot of red values, listed below.
Can someone give me some advice based on information below and the attached documents what to do for optimalisation?
· Innodb_buffer_pool_reads 1,949 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.
· Handler_read_rnd 7,183 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.
· Handler_read_rnd_next 2,470.36 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
· Created_tmp_disk_tables 8,271 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
· Select_full_join 2,091 The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
· Sort_merge_passes 154 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
· Opened_tables 6,052 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
· Table_locks_waited 1,242 The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.