3

The below query occasionally takes up to 24 sec and consumes the complete server resources.

SELECT cid, data, created, expire, serialized
FROM cache_bootstrap
WHERE cid IN ('variables');

I was informed today by my web hoster (I am on a shared server) that above select query brings down the whole node.

I have caching enable (with block caching disabled). JS- and CSS files are aggregated and cached pages are compressed. The site is running ok with no issues.

Any ideas how to track down the problem?

  • D7 or D6? Is the cache_bootstrap table InnoDB or MyISAM? If InnoDB what size is innodb_buffer_pool_size? – mikeytown2 Feb 26 '14 at 18:53
  • Thanks for taking the time, mikey! It's D7 (see title of question). It is InnoDB. How can I find/determine the innodb_buffer_pool_size? There is no such table in the database. – criscom Feb 26 '14 at 19:00
  • Run this Query: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; – mikeytown2 Feb 26 '14 at 19:07
  • Here is a screenshot of the largest tables in the database: evernote.com/shard/s272/sh/e4b2c5c3-a72e-42fa-a3f7-8bbb5a83c846/… – criscom Feb 26 '14 at 19:10
  • innodb_buffer_pool_size 68719476736 – criscom Feb 26 '14 at 19:11
5

If you can get memcache properly installed, that will help the most as it will shift queries from the cache tables in the db to your memcache server.

Try these 3 things (in order of importance):
Set the transaction isolation level to Read Committed - https://drupal.org/comment/8437127#comment-8437127 (look in your settings.php for the insertion point; it will be after the $databases variable has been set with the database credentials). This is helpful even if using memcache.

$databases['default']['default']['init_commands'] = array(
  'isolation' => "SET SESSION tx_isolation='READ-COMMITTED'"
);

Change your semaphore table to memory - https://drupal.org/comment/8469889#comment-8469889 (use the first code block "Have the name value be a primary key"). Note that this might not work if you are using MySQL 5.0 or lower due to BTREE indexes not working in Memory tables. Also noted that if you are using the memcache version of lock.inc this will not help.

ALTER TABLE semaphore ENGINE = MEMORY;
ALTER TABLE semaphore DROP PRIMARY KEY;
ALTER TABLE semaphore ADD PRIMARY KEY (name, value) USING BTREE;
ALTER TABLE semaphore ADD UNIQUE name (name) USING BTREE;
ALTER TABLE semaphore DROP INDEX value;
ALTER TABLE semaphore ADD INDEX value (value) USING BTREE;
ALTER TABLE semaphore DROP INDEX expire;
ALTER TABLE semaphore ADD INDEX expire (expire) USING BTREE;

Apply this core patch - https://drupal.org/node/2193149 This will help to reduce database deadlocks from occurring only after the above 2 have been done. If using memcache this core patch will not help as this fixes an issue when using the database as a cache backend.

  • I have adjusted my settings.php like this: evernote.com/shard/s272/sh/b5104e5c-393a-49aa-bea0-5bdf33d2b746/… – criscom Feb 26 '14 at 19:43
  • After changing the semaphore table to memory I got an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE' at line 1 – criscom Feb 26 '14 at 19:51
  • Put the init_commands part after you set the database user/pass etc. What about if you use the 2nd code block for changing the memory table? – mikeytown2 Feb 26 '14 at 19:54
  • When I run the 2nd code block I get also an error: #1091 - Can't DROP 'PRIMARY'; check that column/key exists – criscom Feb 26 '14 at 19:59
  • Do the rest of the SQL commands work? – mikeytown2 Feb 26 '14 at 20:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.