We are running Drupal 8.5.6 on a SLES12 SP3 server with MariaDB 10.0.35. We recently cut over to a new server with a similar setup (same version of Drupal and MySQL but latest OS patches) and performed a dump/load of the database beforehand. The dump was performed with
$ drush sql-dump --structure-tables-list='cache*' --ordered-dump --result-file=/var/tmp/drupal.sql
We also updated the new server with the latest drush and performed the following:
$ v2web00453$ composer update webflo/drupal-finder
$ v2web00453$ composer require drush/drush
Things worked fine for a day but then our /var/log/mysql partition filled up with a 19G /var/log/mysql/mysqld.log file filled with the following:
InnoDB: Warning: a long semaphore wait:
--Thread 139656937518848 has waited at btr0cur.cc line 592 for 241.00 seconds th
e semaphore:
S-lock on RW-latch at 0x55d08adb9a78 '&new_index->lock'
a writer (thread id 139656907708160) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: fffffffffff00000
Last time read locked in file btr0cur.cc line 592
Last time write locked in file /home/abuild/rpmbuild/BUILD/mariadb-10.0.35/stora
ge/xtradb/ibuf/ibuf0ibuf.cc line 411
InnoDB: Warning: a long semaphore wait:
--Thread 139656907708160 has waited at fut0fut.ic line 51 for 241.00 seconds the semaphore:
X-lock on RW-latch at 0x7f046fdb8fe0 '&block->lock'
a writer (thread id 139656936023808) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 5640
Last time write locked in file /home/abuild/rpmbuild/BUILD/mariadb-10.0.35/storage/xtradb/buf/buf0buf.cc line 3863
InnoDB: Warning: a long semaphore wait:
--Thread 139656557487872 has waited at ibuf0ibuf.cc line 2821 for 241.00 seconds the semaphore:
Mutex at 0x55d08a4a6980 '&ibuf_mutex', lock var 1
waiters flag 1
InnoDB: Warning: semaphore wait:
--Thread 139656937518848 has waited at btr0cur.cc line 592 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x55d08adb9a78 '&new_index->lock'
a writer (thread id 139656907708160) has reserved it in mode exclusive
number of readers 0, waiters flag 1, lock_word: fffffffffff00000
Last time read locked in file btr0cur.cc line 592
Last time write locked in file /home/abuild/rpmbuild/BUILD/mariadb-10.0.35/storage/xtradb/ibuf/ibuf0ibuf.cc line 411
InnoDB: Warning: Writer thread is waiting this semaphore:Aside from the default /etc/my.cnf, we have the following MySQL configuration variables set:
[mysqld]
bind-address = 127.0.0.1
max_allowed_packet = 256M
innodb_log_file_size = 281M
innodb_adaptive_hash_index = 0
ignore-db-dir = lost+found
I did some searching and found others with similar MySQL issues (though not necessarily related to Drupal) and set:
innodb_adaptive_hash_index=0
However, the issue remains. Anyone else experienced this? Anyone with an idea for a solution? Based on this, I might also try setting:
innodb_purge_threads=4