by Mikayel Vardanyan
| Nov 26, 2006
MySQL is a widely used opensource DB. So it is vital for services to find some info in a very short period to sustain a huge load of requests, in case of many users. If your’re using MySQL and your application accesses records of the tables using mostly primary keys, it should be worth to consider buffering primary keys, which can be done by MySQL. This makes accessing records using primary keys (which most should do) run much faster. By typing the “show variables” command in mysql mode, you can see the list of variables together with their values. Take a look at the following ones:
key_read_requests – this is the total number of requests making use of an index
key_reads – this is the total number of requests that had to be read to disk
When you divide key_read_requests / key_reads, the ratio should be higher than 100 to 1. The higher it is the better will be the performance of accessing the records of a table using primary keys. To adjust this, increase the key_buffer_size. Typical rule of thumb is to take your system memory and multiply by .25. Some example values are 768M on a system with 3GB and 16M on a system with 64M.
