Справочная информация: У меня есть веб-сайт, который я перехожу с автономного сервера MariaDB на кластер MariaDB galera. Частично это заставило меня преобразовать таблицы из большого количества MyISAM в InnoDB. Как бы то ни было, веб-сайт представляет собой большую установку Joomla.
Внешняя часть веб-сайта работает нормально. Там нет проблем. Раздел администратора мучительно медленно сохраняет что-либо - 25–30 секунд с момента нажатия кнопки сохранения до его завершения.
Настройка 3-узловая установка galera - 12-ядерный процессор Intel (R) Xeon (R) E5-1650 v4 @ 3,60 ГГц 64 ГБ с твердотельным накопителем на Raid
HA-прокси для балансировки нагрузки
/etc/my.cnf
[mysqld]
innodb_buffer_pool_siz=32Gb
innodb_log_file_size = 2Gb
innodb_flush_method=O_DIRECT
max_connections=750
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=2Mb
query_cache_size=0
skip_name_resolve
sync-binlog=0
Прямо сейчас у меня подключен только один сайт разработки, поэтому я могу надежно измерить, что происходит. Вот что я заметил с ожиданиями при выполнении одного сохранения:
До сохранения:
MariaDB [(none)]> show global status like "%waits%";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_log_waits | 0 |
| Innodb_mutex_os_waits | 4 |
| Innodb_mutex_spin_waits | 4 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_s_lock_os_waits | 9 |
| Innodb_s_lock_spin_waits | 11 |
| Innodb_x_lock_os_waits | 1 |
| Innodb_x_lock_spin_waits | 0 |
| Tc_log_page_waits | 0 |
+-------------------------------+-------+
10 rows in set (0.01 sec)
После сохранения:
MariaDB [(none)]> show global status like "%waits%";
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_log_waits | 0 |
| Innodb_mutex_os_waits | 177 |
| Innodb_mutex_spin_waits | 1580 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_s_lock_os_waits | 164 |
| Innodb_s_lock_spin_waits | 687 |
| Innodb_x_lock_os_waits | 656 |
| Innodb_x_lock_spin_waits | 905 |
| Tc_log_page_waits | 0 |
+-------------------------------+-------+
Я знаю, что с innodb больше накладных расходов, чем с myisam, но я подумал об отключении sync-binlog и установке innodb_flush_log_at_trx_commit значение 2 помогло бы, но на самом деле я наблюдаю снижение производительности записи примерно на 1000%.
Просто ищу руководство о том, что проверять и что может быть не так. Я не уверен, что это число os_waits велико при одном сохранении в приложении, мне кажется, что это так, но я действительно не понимаю, что могло бы вызвать это или как это исправить.
После установки медленного журнал запросов до 2 с, это единственное, что он уловил при попытке сохранить:
# Time: 180126 18:03:38
# User@Host: dev[dev] @ [192.168.10.200]
# Thread_id: 136 Schema: dev QC_hit: No
# Query_time: 6.306918 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 109438
# Rows_affected: 82677
use dev;
SET timestamp=1517007818;
UPDATE jos_assets
SET lft = lft + 2
WHERE lft > 337711;
# Time: 180126 18:03:48
# User@Host: dev[dev] @ [192.168.10.200]
# Thread_id: 136 Schema: dev QC_hit: No
# Query_time: 9.985669 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 109438
# Rows_affected: 82683
SET timestamp=1517007828;
UPDATE jos_assets
SET rgt = rgt + 2
WHERE rgt >= 337711;
Спасибо заранее.
Эта проблема оказалась очень специфичной для Joomla и связана с неэффективностью таблицы _assets. Думаю, мне следовало проверить журнал медленных запросов раньше, но, как вы можете видеть в исходной публикации, было 2 запроса, которые обновляли таблицу за одно «сохранение» при публикации новой статьи, в результате чего обновлялось 82677 строк. Даже при самом быстром развертывании на это потребуется время.
Почему Joomla нужно делать это каждый раз при сохранении поста, я не совсем уверен, но некоторые исследования показали, что было безопасно удалить все связанные со статьями записи в этой таблице, в результате чего количество строк сократилось до 4000. Теперь намного быстрее.
Для справки, если у вас есть сайт Joomla, который работает медленно после преобразования в Innodb, это страница, на которой я нашел некоторую информацию в таблице ресурсов: