Периодически высокое значение Load Average. Перестают отдаваться страницы клиенту, Помогите правильно определить настройки и выявить источник проблемы.
При значении выше 10 в Load Average перестают отдаваться страницы. Скрин #htop: https://yadi.sk/i/qvG_nGCV3Rbiqj Нормально ли выделять такое количество памяти для mysqld?
Version: '5.5.42' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL) by Remi
180119 13:26:15 [Note] /usr/libexec/mysqld: Normal shutdown
180119 13:26:16 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 13:26:16 [Note] Event Scheduler: Purging the queue. 0 events
180119 13:26:18 [Warning] /usr/libexec/mysqld: Forcing close of thread 11 user: 'root'
180119 13:26:18 [Warning] /usr/libexec/mysqld: Forcing close of thread 4 user: 'root'
180119 13:26:18 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 13:26:20 InnoDB: Starting shutdown...
180119 13:26:20 InnoDB: Waiting for 200 pages to be flushed
180119 13:26:22 InnoDB: Shutdown completed; log sequence number 4376971352523
180119 13:26:22 [Note] /usr/libexec/mysqld: Shutdown complete
180119 13:26:22 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180119 13:26:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180119 13:26:24 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
180119 13:26:24 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
180119 13:26:24 [Note] Plugin 'FEDERATED' is disabled.
180119 13:26:24 InnoDB: The InnoDB memory heap is disabled
180119 13:26:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180119 13:26:24 InnoDB: Compressed tables use zlib 1.2.3
180119 13:26:24 InnoDB: Using Linux native AIO
180119 13:26:24 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
180119 13:26:24 InnoDB: Initializing buffer pool, size = 1.0G
180119 13:26:24 InnoDB: Completed initialization of buffer pool
180119 13:26:24 InnoDB: highest supported file format is Barracuda.
180119 13:26:24 InnoDB: Waiting for the background threads to start
180119 13:26:25 InnoDB: 5.5.42 started; log sequence number 4376971352523
180119 13:26:25 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
180119 13:26:25 [Note] - '0.0.0.0' resolves to '0.0.0.0';
180119 13:26:25 [Note] Server socket created on IP: '0.0.0.0'.
180119 13:26:25 [Warning] 'user' entry 'root@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 13:26:25 [Warning] 'user' entry '@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 13:26:25 [Warning] 'proxies_priv' entry '@ root@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 13:26:26 [Note] Event Scheduler: Loaded 0 events
180119 13:26:26 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.42' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL) by Remi
180119 14:47:19 [Note] /usr/libexec/mysqld: Normal shutdown
180119 14:47:19 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:19 [Note] Event Scheduler: Purging the queue. 0 events
180119 14:47:20 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:20 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:20 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:21 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 81 user: 'root'
180119 14:47:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 11 user: 'root'
180119 14:47:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 10 user: 'root'
180119 14:47:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 7 user: 'root'
180119 14:47:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 6 user: 'root'
180119 14:47:22 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:29 [ERROR] /usr/libexec/mysqld: Sort aborted: Query execution was interrupted
180119 14:47:30 InnoDB: Starting shutdown...
180119 14:47:31 InnoDB: Waiting for 200 pages to be flushed
180119 14:47:32 InnoDB: Shutdown completed; log sequence number 4377009068860
180119 14:47:32 [Note] /usr/libexec/mysqld: Shutdown complete
180119 14:47:32 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
180119 14:47:34 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180119 14:47:34 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
180119 14:47:34 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
180119 14:47:34 [Note] Plugin 'FEDERATED' is disabled.
180119 14:47:34 InnoDB: The InnoDB memory heap is disabled
180119 14:47:34 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180119 14:47:34 InnoDB: Compressed tables use zlib 1.2.3
180119 14:47:34 InnoDB: Using Linux native AIO
180119 14:47:34 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
180119 14:47:34 InnoDB: Initializing buffer pool, size = 1.0G
180119 14:47:34 InnoDB: Completed initialization of buffer pool
180119 14:47:34 InnoDB: highest supported file format is Barracuda.
180119 14:47:37 InnoDB: Waiting for the background threads to start
180119 14:47:38 InnoDB: 5.5.42 started; log sequence number 4377009068860
180119 14:47:38 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
180119 14:47:38 [Note] - '0.0.0.0' resolves to '0.0.0.0';
180119 14:47:38 [Note] Server socket created on IP: '0.0.0.0'.
180119 14:47:38 [Warning] 'user' entry 'root@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 14:47:38 [Warning] 'user' entry '@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 14:47:38 [Warning] 'proxies_priv' entry '@ root@bitrix.localdomain' ignored in --skip-name-resolve mode.
180119 14:47:38 [Note] Event Scheduler: Loaded 0 events
180119 14:47:38 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.42' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL) by Remi
Использую постоянное соединение с БД.
Т.к. опыта у меня немного - прошу указать где может быть ошибка.
По скрину не видно, кто загружал или загружает процессор, до десятикратной нагрузки. памяти у вас MySQL хватает вполне. Стандартно - хоть визуально ловить, какой процесс нагружает процессор, и смотреть логи за это время: что запрашивали, откуда. Если это апач и MySQL - то slow-query-log = 1 и смотреть там. Да и саму страницу тоже. Бывает, элементарное "Показать все" в разделе на десяток тысяч позиций рушит всё.
по описанным синдромам с большой долей вероятности проблема у вас не с бд, у вас высокое значение la при нулевой загрузке процов, нужно смотреть обычный top (не помню просто какими ключами в htop это вызвать)