We run our Drupal site in an Amazon cloud environment where our AMI instances connect up to another database server within Amazon. We always have at least two instances of the site running at once and scale up as traffic dictates. We enabled this module, put the site into maintenance mode and made the database changes to the cache* and the semaphores table, and the following recommended changes to settings.php:

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";
//$databases['default']['default']['unix_socket'] = '/var/lib/mysql/mysql.sock'; // not set because mysql is not running on this server
$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';
$conf['cache_default_class'] = 'APDQCache';
$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc'; // we commented this out at one point when things were collapsing, but this did not help
$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';

Once this was done we saw connections to the MySQL table balloon up. When viewing the process list we saw many instances of queries that looked like:

------------
SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN ('variables', 'bootstrap_modules', 'lookup_cache', 'system_list', 'module_implements')

SELECT 'cache' AS bin, cid, data, created, expire, serialized FROM cache WHERE cid IN ('filter_list_format', 'image_styles', 'entity_property_info:en', 'ctools_plugin_files:ctools:access', 'ctools_plugin_files:ctools:content_types', 'ctools_plugin_files:ctools:relationships', 'theme_registry:runtime:mf_collabornation')
UNION ALL SELECT 'cache_rules' AS bin, cid, data, created, expire, serialized FROM cache_rules WHERE cid IN ('data:en')
UNION ALL SELECT 'cache_token' AS bin, cid, data, created, expire, serialized FROM cache_token WHERE cid IN ('info:en', 'field:info')
UNION ALL SELECT 'cache_menu' AS bin, cid, data, created, expire, serialized FROM cache_menu WHERE cid IN ('menu_custom', 'local_tasks:lms/certificate-check')
UNION ALL SELECT 'cache_admin_menu' AS bin, cid, data, created, expire, serialized FROM cache_admin_menu WHERE cid IN ('admin_menu:10071995:ll7HJ41Diabf3ZTa3T81Ml_OS4oQrxIYRpKJiMHa35w:en')
------------

Where are running PHP 5.4.45 and php-mysqlnd (because in our testing with PHP 5.3.3 from CentOS and php-mysql nothing was written to Watchdog) if this helps clear anything up.

We tested this module on a non-cloud instance on our local development and everything ran smoothly. It was not until it got into production that the module collapsed for us.

CommentFileSizeAuthor
#4 apdqc-2577717-3-no-union-query.patch4.87 KBmikeytown2

Comments

powrsurg created an issue. See original summary.

mikeytown2’s picture

Going to see if the UNION ALL is causing issues. Will create an option to have that disabled at the expense of using more database connections.

  • mikeytown2 committed 1a87244 on
    Issue #2577717 by mikeytown2: Default to not use union queries.
    
mikeytown2’s picture

Status: Active » Fixed
StatusFileSize
new4.87 KB

This patch was committed. Please report back on if this worked or not. I'm an optimist so this issue is marked as fixed. Please change if this is not the case.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.