Posted by mikeytown2 on February 5, 2015 at 3:04am
Just a heads up that the beta 1 of the Asynchronous Prefetch Database Query Cache module https://www.drupal.org/project/apdqc is now available.
From my testing this will eliminate all deadlocks & metadata locks for all cache tables.
While testing I had the MySQL CPU load go over 40 due to requests from Drupal; during this time SHOW PROCESSLIST
and SHOW ENGINE innodb STATUS
had zero indications of long standing deadlocks and/or metadata locks. I did get some MySQL server has gone away errors; I no longer get those due to the reconnection logic that I added. Long story short I've done my best to make this as bulletproof as possible.
Feedback from your own testing would be appreciated.
Comments
Hey Mike, Thanks for posting,
Hey Mike,
Thanks for posting, looks interesting! Could you explain a bit more what the module does? Are there specific situations in which this module would be used?
Why you need APDQC for every site
Biggest win is that fact that the cache_field table is well behaved and no longer deadlocks and/or causes metadata locks. This is also true of any cache table. Clearing the Drupal cache is no longer a nail biting experience; cache clears are about 2x faster than what core does. Cache writes are lighting fast; usually faster than cache reads. All thanks to the asynchronous MySQL connection.
Smart cache prefetching. Using the various drupal hooks APDQC can detect what cached data will be requested and get it before it is needed. Thanks to the asynchronous MySQL connection prefetching, this is non blocking; thus prefetching data has a very low cost in terms of milliseconds (almost zero). This means that getting cached data when prefetched is fast; similar to cache writes.
Better enforcement of the minimum cache lifetime; similar to https://www.drupal.org/project/adbc
Locks and cache operations happen outside of a transaction so when a lock is taken, it happens ASAP; with core this is not a guarantee https://www.drupal.org/node/2347867.
Spent a good amount of time working with the devel query logging output; will output prefetch hits and misses; also displaying how far along (in ms) into the request the apdqc query was executed.
The status report does quite a bit of analysis; checks the following
realpath_cache_size
realpath_cache_ttl
sees if you can use a unix_socket
tx_isolation level
semaphore table
max_allowed_packet
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
query cache
innodb_lock_wait_timeout
wait_timeout
drupal cache backend usage
page_cache_invoke_hooks
cache table collation
cache table indexes