Simple optimization to answer MySQL database during interview

Using the show status command to see the running state of MySQL, plus the like command to view a certain state, MySQL optimization is not theoretically based on the actual running state of the company database to tune the parameters. In fact, we can also add memcache to the previous section of MySQL to cache the SQL statements that have been used. Memcache, we'll talk later.

(1) If Opened_tables is much larger than Open_tables, the Table_cache in my.cnf should be bigger

(2) If the key_reads is too large, it should be my.cnf in the key_buffer_size to become larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.

(3) If the handler_read_rnd is too large, then you write a lot of SQL statement query is to scan the entire table, you should pay attention to the SQL statement is reasonable, because scanning the entire table is very resource-intensive system.

(4) If the threads_created is too large, it is necessary to increase the value of thread_cache_size in MY.CNF.

(5) If the created_tmp_disk_tables is too large, close to created_tmp_tables, it is necessary to increase the value of tmp_table_size and Max_heap_table_size in MY.CNF.

(6) If the sort_merge_passes is too large, you should increase the value of sort_buffer_size in the MY.CNF

(7) If a lot of qcache_hits, then querycache useful, if it is small, it means that Querycache no need to use

(8) If the qcache_lowmem_prunes is large, increase the value of query_cache_size

Simple optimization to answer MySQL database during interview