Use asynchronous MySQLi connection to avoid RECORD LOCKS (Deadlocks) on cache tables

Looking into the failures and I believe it is caused by the async query. cache_set no longer blocks until the query has completed; thus for tests where it does a cache_set followed by a cache_get the cached object isn't there yet. I might be able to reduce this issue by using this connection for cache_get as I set the isolation level to READ-UNCOMMITTED.

I have not tested it with entity cache yet.

Main issue with the last patch was database prefixes; I've fixed that (along with some other minor things) and I now get intermittent failures on the cache tests (same test rarely fails twice in a row). To get this to work 100% of the time with simpletest I would need to use the same db connection for the cache_gets. With the new patch, speedup is only 2x instead of 5x; but cache_set seems to be working 100% of the time (was getting random failures before excluding simpletest).

cache_get now uses the async connection if a cache_set was done; should make the test bot happy.

One small change

Latest patch failed with this message.
Field is translatable. Other field.test 3190 FieldTranslationsTestCase->testTranslatableFieldSaveLoad()

Patch passes 2nd time around.

$mysqli->reap_async_query can be super slow in rare cases. Will see what I can do to address this issue.

Ended up using a connection pool to work around the slow queries. I've recorded some cachegrinds from cache clears and it shows that drupal will sometimes spend more time in cache_get than in cache_set with this patch; mind blown because writes are effectively faster than reads.

Real patch

Set the wait_timeout to 45 seconds and it will ping the DB after 40 seconds to make sure the connection is up before using it again.

I tried using persistent connections but $mysqli->close() seems to stall so getting that working seems like a no go.

Looks like the wait_timeout and reconnect logic needs some help. I'll up the timeout to 90 seconds as testing takes longer than 45 once I get the reconnect stuff figured out.

The big performance gain comes from asynchronous queries; which is not available with PDO
http://php.net/manual/en/mysqlinfo.api.choosing.php

Writing a DB driver for async mysqli is essentially what I've done to a very limited extent here. I'll work on the reconnect logic and post a new patch once I have that working.

The wait_timeout and reconnect is now working at 1 second; so that bug has now been fixed. This patch uses 90 seconds as that seems to be a good happy medium; in the future the current wait_timeout should be taken into account when setting this value. I also fixed some issues with the connection pool management; the $db_connections array is now keyed off of $mysqli->thread_id. This allows for way better house keeping when using mysqli_poll(); luckily $mysqli->thread_id seems to be a noop from my testing.

pg_send_query() seems to be the way to do this using PostgreSQL; I don't use PostgreSQL though.

It knocks 5 seconds off of a cache clear (on our test server), so yeah things that use cache_set will see the biggest improvements

Gave everything proper documentation & did some micro optimizations (1 less query per connection if # of connections is greater than 1). The only place that has hard coded values is now inside of the advanced drupal_static() pattern; thus those can be overwritten by modifying drupal_static('drupal_mysqli_get_object') if it's really needed.

Little bit more refactoring; created 2 new functions and reduced code duplication... Looks like I need to merge this in better next time; some of the documentation was accidentally changed. Will wait for the testbot to pass before fixing it. Should also handle the $error array from mysqli_poll.

This one handles errors (like dropped connections) now. This is in it's final form as a D7 core patch. Future goals for asynchronous MySQLi connection pools is to combine it with Cache MultiGet & Alternative Database Cache to create the best DB backed cache backend. This is needed even when things like memcache/APC are in use do to the nature of the cache_field table in D7.

Getting an asynchronous connection pool into D8 core would be nice; unfortunately I won't be the one creating the patch for it. I hope someone picks up the torch as all five issues listed here https://groups.drupal.org/node/415883 make drupal core scale way better by its self.

I recently created a cache module. It works around the cache_field race condition by using the core db connection so that operation happens inside of the transaction.
https://www.drupal.org/project/apdqc

I'll be creating a lock.inc for apdqc (#2387371: Create a lock back end that uses the APDQC connection) which can have some logic so that the lock doesn't get released until the write to the db is complete.