Mysql optimization tips (key_buffer_size setting)

Mysql optimization tips (key_buffer_size settings) 1: For mysql, when logging on, add a parameter [SQL] # mysql-u root-p -- prompt = "\ u @ \ d \ r: \ m: \ s> "-- prompt adds some login parameters. The above parameter u is the username for logon, and d is the database table for use dbname after logon, r m s is time 2: key_buffer_size has a great impact on the performance of MyISAM tables. [SQL] mysql> show variables like 'key _ buffer_size '; + ----------------- + ------------ + | Variable_name | Value | + ----------------- + ---------- + | key_buffer_size | 536870912 | + ----------------- + ------------ + Allocated MB of memory to mysql key_buffer_size. Let's take a look at the usage of key_buffer_size: [SQL] mysql> show global status like 'key _ read % '; + bytes + ------------- + | Variable_name | Value | + ------------------------ + ------------- + | Key_read_requests | 27813678764 | Key_reads | 6798830 | + bytes + ------------- + A total of 27813678764 index read requests, 6798830 requests are not found in the memory and are directly read from the hard disk. Reference: calculate the probability that the index does not hit the cache: key_cache_miss_rate = Key_reads/Key_read_requests * 100%. For example, if the above data is set to key_cache_miss_rate to 0.0244%, only one direct read hard disk is available for the first, the key_cache_miss_rate is very good at lower than 0.1% (each 1000 requests have a direct read hard disk). If the key_cache_miss_rate is less than 0.01%, too many key_buffer_size allocation can be reduced as appropriate. The MySQL server also provides the key_blocks _ * parameter: [SQL] mysql> show global status like 'key _ blocks_u % '; + buffers + ------------- + | Variable_name | Value | + cached + ------------- + | Key_blocks_unused | 0 | Key_blocks_used | 413543 | + cached + ------------- + cached indicates unused cache clusters (blocks) number, Key_blocks_used indicates the maximum number of blocks used in the past. For example, on this server, all the caches are used, either key_buffer_size is increased or the transitional index is used. The cache is full. Ideal settings: Key_blocks_used/(Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%