Asynchronous Prefetch Database Query Cache module

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
mikeytown2's picture

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,

Martijn Houtman's picture

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

mikeytown2's picture

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