Skip navigation links

MySQL Forums :: Performance :: How to see key_buffer in action


Advanced Search

How to see key_buffer in action
Posted by: Stefan Frömken ()
Date: November 08, 2012 01:01PM

Hello,

I just try to understand key_buffer. OK...in www you can read "Set it to 25%", "Set it to 50%", "Don't set it higher than the sum of all of your MYI files" and so on. That is not my problem. My problem is, how to see key_buffer in action. But, if I reduce this value nothing happens. If I set a higher value nothing happens. All durations are nearly the same. So...what I have to do, to see some differences? There are NO changes to my.cnf! I have set key_buffer_size with:

SET GLOBAL key_buffer_size = 32 * 1024 * 1024;

In my current database I have around 47.000 records
The data size is 270MB
The Index size is 90MB

Explain: Yes...my key is called "all" :-) But it contains only title(255), uid(11), pid(11), orig_uid(11), orig_pid(11):

+----+-------------+-------------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tx_kesearch_index | range | all | all | 603 | NULL | 189 | Using where |
+----+-------------+-------------------+-------+---------------+------+---------+------+------+-------------+

Here is my benchmark:

key_buffer_size = 1MB
| 55 | 0.00131525 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 56 | 0.00128900 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 57 | 0.00127325 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 58 | 0.00127675 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 59 | 0.00129450 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 60 | 0.00126400 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 61 | 0.00127600 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 62 | 0.00133950 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 4MB
| 46 | 0.00130050 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 47 | 0.00130575 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 48 | 0.00089600 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 49 | 0.00128825 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 50 | 0.00127825 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 51 | 0.00127700 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 52 | 0.00129825 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 53 | 0.00131575 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 8MB
| 37 | 0.00134475 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 38 | 0.00127625 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 39 | 0.00142700 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 40 | 0.00129175 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 41 | 0.00128600 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 42 | 0.00131650 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 43 | 0.00127475 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 44 | 0.00067525 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 16MB
| 1 | 0.00137225 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 2 | 0.00131125 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 3 | 0.00062550 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 4 | 0.00128300 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 5 | 0.00129950 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 6 | 0.00127425 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 7 | 0.00128050 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 8 | 0.00128400 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 32MB
| 10 | 0.00133725 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 11 | 0.00129925 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 12 | 0.00134650 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 13 | 0.00126625 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 14 | 0.00125850 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 15 | 0.00064925 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 16 | 0.00123850 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 17 | 0.00134125 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 64MB
| 19 | 0.00138225 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 20 | 0.00128225 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 21 | 0.00127625 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 22 | 0.00129850 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 23 | 0.00133575 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 24 | 0.00128575 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 25 | 0.00129125 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 26 | 0.00127025 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

key_buffer_size = 128MB
| 28 | 0.00141575 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 29 | 0.00132875 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 30 | 0.00135075 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 31 | 0.00131750 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 32 | 0.00132425 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 33 | 0.00136500 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 34 | 0.00131875 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |
| 35 | 0.00131600 | SELECT SQL_NO_CACHE LEFT(title, 50), LEFT(content, 100) FROM tx_kesearch_index WHERE title LIKE "Abkommen%" |

Stefan

Options: ReplyQuote


Subject Views Written By Posted
How to see key_buffer in action 1168 Stefan Frömken 11/08/2012 01:01PM
Re: How to see key_buffer in action 523 Stefan Frömken 11/09/2012 05:54AM
Re: How to see key_buffer in action 619 Rick James 11/11/2012 12:45PM
Re: How to see key_buffer in action 555 karen wang 11/11/2012 06:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.