Re: Queries slow down daily, optimizing helps -> why?
Posted by:
aftab khan
Date: February 26, 2009 09:56AM
your query requires an index scan, which pushes out of the cache all the index blocks corresponding to valuable high-level B-tree nodes. thats why you observe slow and fast behavior.
try
SET GLOBAL key_cache_division_limit=25;
if possible use different cache for these tables...
mysql> SET GLOBAL hot_cache.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CACHE INDEX `person` , `user` IN hot_cache;
+-------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------------------+----------+----------+
| test.person | assign_to_keycache | status | OK |
| test.user | assign_to_keycache | status | OK |
+-------------+--------------------+----------+----------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL hot_cache.key_buffer_size=128*1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CACHE INDEX `person` , `user` IN hot_cache;
+-------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------------------+----------+----------+
| test.person | assign_to_keycache | status | OK |
| test.user | assign_to_keycache | status | OK |
+-------------+--------------------+----------+----------+
2 rows in set (0.00 sec)
mysql> LOAD INDEX INTO CACHE `person` , `user` IGNORE LEAVES;
+-------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+--------------+----------+----------+
| test.person | preload_keys | status | OK |
| test.user | preload_keys | status | OK |
+-------------+--------------+----------+----------+
2 rows in set (0.00 sec)
Subject
Views
Written By
Posted
4491
February 18, 2009 04:54AM
2295
February 18, 2009 09:04AM
2392
February 18, 2009 11:57AM
2382
February 19, 2009 01:00AM
2335
February 19, 2009 01:52AM
2349
February 19, 2009 03:32AM
1780
February 19, 2009 05:08AM
2225
February 19, 2009 12:24PM
2310
February 19, 2009 10:53PM
2426
February 20, 2009 08:34AM
2207
March 18, 2009 03:58AM
2324
February 21, 2009 06:15AM
2277
February 24, 2009 10:02AM
2487
February 26, 2009 02:30AM
Re: Queries slow down daily, optimizing helps -> why?
2108
February 26, 2009 09:56AM
2299
February 26, 2009 08:14PM
2317
February 27, 2009 01:38AM
2202
February 27, 2009 07:56PM
2208
February 28, 2009 06:20AM
2531
February 28, 2009 03:43PM
2327
February 27, 2009 04:21AM
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.