[drupal-devel] Menu cache
We found that on drupal.org, the SQL query to load the menu cache sometimes takes 50 ms or more to complete (normally, a query takes 0.3 ms to complete): mysql> SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:<uid>:en' Further investigation showed that these rows are _big_. If we look at the size of chx's menu cache, we see that it is 120 KB even though we have a fairly simple menu on drupal.org! The largest menu cache is 180 KB, the smallest menu cache is 67 KB. If we look at the total size of the menu cache, we see that we host more than 36 MB worth of menu caches (= the menu cache of 483 users). I'm sure there are times we keep more than 483 users' cache ... mysql> SELECT LENGTH(data) FROM cache WHERE cid = 'menu:9446:en'; mysql> SELECT SUM(LENGTH(data)) FROM cache WHERE cid LIKE 'menu:%'; I know we benchmarked this extensively at the time we committed the menu cache patch (though only on a single user setup). Nonetheless, it is worth investigating/revisiting. Maybe we are caching too much, or maybe it is worth compressing the cache by using shorter variable names in the $_menu-array, or ... ? -- Dries Buytaert :: http://www.buytaert.net/
On Tue, 10 May 2005, Dries Buytaert wrote:
We found that on drupal.org, the SQL query to load the menu cache sometimes takes 50 ms or more to complete (normally, a query takes 0.3 ms to complete):
mysql> SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:<uid>:en'
According to database.mysql there is no index on cid. Can you try adding one? Length 15 or so should do. Cheers, Gerhard
On Tue, 10 May 2005, Gerhard Killesreiter wrote:
On Tue, 10 May 2005, Dries Buytaert wrote:
We found that on drupal.org, the SQL query to load the menu cache sometimes takes 50 ms or more to complete (normally, a query takes 0.3 ms to complete):
mysql> SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:<uid>:en'
According to database.mysql there is no index on cid. Can you try adding one? Length 15 or so should do.
For assitional sql-karma we could also split up the cache table into several tables as I suggested a few times in the past: cache_menu cache_locale cache_filter cache_page cache_misc Selects from smaller tabels _are_ faster. Cheers, Gerhard
On Tue, 10 May 2005, Karoly Negyesi wrote:
According to database.mysql there is no index on cid. Can you try adding one? Length 15 or so should do.
What's this in database.mysql then?
CREATE TABLE cache ( ... PRIMARY KEY (cid)
Darn, next time I'll look for my glasses before reading email.
Splitting cache table is a good idea though.
Yeah. Dries, want a patch? Cheers, Gerhard
On 10 May 2005, at 14:53, Gerhard Killesreiter wrote:
mysql> SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:<uid>:en'
According to database.mysql there is no index on cid. Can you try adding one? Length 15 or so should do.
We have an index: mysql> EXPLAIN SELECT data FROM cache WHERE cid = 'menu:9446:en'; +-------+-------+---------------+---------+---------+-------+------ +-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+---------+---------+-------+------ +-------+ | cache | const | PRIMARY | PRIMARY | 255 | const | 1 | | +-------+-------+---------------+---------+---------+-------+------ +-------+ -- Dries Buytaert :: http://www.buytaert.net/
Is the problem a) slow queries, b) a large database, or c) both? Splitting the cache table and optimizing queries will help with "a", but if the problem is "b" then we need to think of other solutions. Nic On May 10, 2005, at 3:52 AM, Dries Buytaert wrote:
We found that on drupal.org, the SQL query to load the menu cache sometimes takes 50 ms or more to complete (normally, a query takes 0.3 ms to complete):
mysql> SELECT data, created, headers, expire FROM cache WHERE cid = 'menu:<uid>:en'
Further investigation showed that these rows are _big_. If we look at the size of chx's menu cache, we see that it is 120 KB even though we have a fairly simple menu on drupal.org! The largest menu cache is 180 KB, the smallest menu cache is 67 KB. If we look at the total size of the menu cache, we see that we host more than 36 MB worth of menu caches (= the menu cache of 483 users). I'm sure there are times we keep more than 483 users' cache ...
mysql> SELECT LENGTH(data) FROM cache WHERE cid = 'menu:9446:en'; mysql> SELECT SUM(LENGTH(data)) FROM cache WHERE cid LIKE 'menu:%';
I know we benchmarked this extensively at the time we committed the menu cache patch (though only on a single user setup). Nonetheless, it is worth investigating/revisiting. Maybe we are caching too much, or maybe it is worth compressing the cache by using shorter variable names in the $_menu-array, or ... ?
-- Dries Buytaert :: http://www.buytaert.net/
On 10 May 2005, at 17:13, Nicholas Ivy wrote:
Is the problem a) slow queries, b) a large database, or c) both?
Depends on what is meant with 'large' ... Sometimes the queries are slow, sometimes the queries are fast. Whether this is related to the amount of data in the cache-table, I don't know (yet). At any point, there seems to be at least 30 MB of data in the cache-table. -- Dries Buytaert :: http://www.buytaert.net/
participants (4)
-
Dries Buytaert -
Gerhard Killesreiter -
Karoly Negyesi -
Nicholas Ivy