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