Назад | Перейти на главную страницу

Настройка мариадб - медленная запись

Задний план: У меня есть веб-сайт, который я перехожу с автономного сервера MariaDB на кластер MariaDB galera. Частично это заставило меня преобразовать таблицы из большого количества MyISAM в InnoDB. Как бы то ни было, сайт представляет собой большую установку Joomla.

Передняя часть веб-сайта работает нормально. Там нет проблем. Раздел администратора мучительно медленно сохраняет что-либо - 25-30 секунд с момента нажатия кнопки сохранения до его завершения.

Настроить Установка с 3 узлами Galera - 12-ядерный процессор Intel (R) Xeon (R) E5-1650 v4 @ 3,60 ГГц, 64 ГБ с твердотельным накопителем на Raid

HA Proxy для балансировки нагрузки

/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, это страница, на которой я нашел некоторую информацию в таблице ресурсов:

https://www.itoctopus.com/creating-new-articles-on-your-joomla-website-is-taking-a-long-time-clean-your-assets-table