MySQL Forums
Forum List  »  Performance

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
2856
November 08, 2012 01:01PM
1040
November 09, 2012 05:54AM
1268
November 11, 2012 12:45PM
1018
November 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.