С ростом нагрузки на сайт, 350K в месяц периодически, (раз в неделю) перезагружается сервер базы данных
Сервер:
4 CPU 8 GB RAM (SSD) centos7
Панель производительности битрикс: оптимально
Конфиг mySql
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 16M
innodb_buffer_pool_size = 3072M
innodb_lru_scan_depth = 256
max_connections = 80
table_open_cache = 12288
thread_cache_size = 128
max_heap_table_size = 128M
tmp_table_size = 128M
key_buffer_size = 96M
join_buffer_size = 14M
sort_buffer_size = 14M
bulk_insert_buffer_size = 2M
myisam_sort_buffer_size = 14M
Битрикс версия: 17.0.9, в ближайшее время постараемся обновить...
Может кто подскажет в чем дело, куда копать?
лог mysql:
2021-03-09T12:38:44.874556Z 1909809 [Note] Got an error reading communication packets
2021-03-09T12:39:55.331874Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4790ms. The settings might not be optimal. (flushed=201, during the time.)
2021-03-09T12:39:59.939286Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4396ms. The settings might not be optimal. (flushed=201, during the time.)
2021-03-09T12:40:24.838299Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5195ms. The settings might not be optimal. (flushed=200, during the time.)
2021-03-09T12:40:49.236420Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12590ms. The settings might not be optimal. (flushed=157, during the time.)
2021-03-09T12:41:24.632250Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 32204ms. The settings might not be optimal. (flushed=151, during the time.)
2021-03-09T12:42:15.632831Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-03-09T12:42:15.636133Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.30-33) starting as process 7881 ...
2021-03-09T12:42:15.662204Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-03-09T12:42:15.662261Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-03-09T12:42:15.662270Z 0 [Note] InnoDB: Uses event mutexes
2021-03-09T12:42:15.662278Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-03-09T12:42:15.662285Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2021-03-09T12:42:15.662293Z 0 [Note] InnoDB: Using Linux native AIO
2021-03-09T12:42:15.662631Z 0 [Note] InnoDB: Number of pools: 1
2021-03-09T12:42:15.662796Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-03-09T12:42:15.665996Z 0 [Note] InnoDB: Initializing buffer pool, total size = 3G, instances = 8, chunk size = 128M
2021-03-09T12:42:15.770442Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-03-09T12:42:15.793812Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-03-09T12:42:15.816482Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2021-03-09T12:42:15.855568Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-03-09T12:42:15.876199Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 218488904554
2021-03-09T12:42:16.043434Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 218494147072
2021-03-09T12:42:16.047457Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 218494735481
2021-03-09T12:42:16.048055Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-03-09T12:42:16.048082Z 0 [Note] InnoDB: Starting crash recovery.
2021-03-09T12:42:16.657109Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 31457280 bytes
2021-03-09T12:42:16.881197Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-03-09T12:42:17.682185Z 0 [Note] InnoDB: Apply batch completed
2021-03-09T12:42:18.711226Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-03-09T12:42:18.711288Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-03-09T12:42:18.711354Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-03-09T12:42:18.740801Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-03-09T12:42:18.741889Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-03-09T12:42:18.741913Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-03-09T12:42:18.742815Z 0 [Note] InnoDB: Waiting for purge to start
2021-03-09T12:42:18.792948Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com ) 5.7.30-33 started; log sequence number 218494735481
2021-03-09T12:42:18.796261Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-03-09T12:42:18.810229Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-03-09T12:42:18.859179Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-03-09T12:42:18.859202Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-03-09T12:42:18.861685Z 0 [Warning] CA certificate ca.pem is self signed.
2021-03-09T12:42:18.861753Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-03-09T12:42:18.862802Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-03-09T12:42:18.862854Z 0 [Note] IPv6 is available.
2021-03-09T12:42:18.862876Z 0 [Note] - '::' resolves to '::';
2021-03-09T12:42:18.862942Z 0 [Note] Server socket created on IP: '::'.
2021-03-09T12:42:18.980598Z 0 [Note] Event Scheduler: Loaded 0 events
2021-03-09T12:42:18.997817Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.30-33' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 33, Revision 6517692
2021-03-09T12:43:15.822613Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210309 15:43:15
Сервер:
4 CPU 8 GB RAM (SSD) centos7
Панель производительности битрикс: оптимально
Конфиг mySql
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 16M
innodb_buffer_pool_size = 3072M
innodb_lru_scan_depth = 256
max_connections = 80
table_open_cache = 12288
thread_cache_size = 128
max_heap_table_size = 128M
tmp_table_size = 128M
key_buffer_size = 96M
join_buffer_size = 14M
sort_buffer_size = 14M
bulk_insert_buffer_size = 2M
myisam_sort_buffer_size = 14M
Битрикс версия: 17.0.9, в ближайшее время постараемся обновить...
Может кто подскажет в чем дело, куда копать?
лог mysql:
2021-03-09T12:38:44.874556Z 1909809 [Note] Got an error reading communication packets
2021-03-09T12:39:55.331874Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4790ms. The settings might not be optimal. (flushed=201, during the time.)
2021-03-09T12:39:59.939286Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4396ms. The settings might not be optimal. (flushed=201, during the time.)
2021-03-09T12:40:24.838299Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5195ms. The settings might not be optimal. (flushed=200, during the time.)
2021-03-09T12:40:49.236420Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 12590ms. The settings might not be optimal. (flushed=157, during the time.)
2021-03-09T12:41:24.632250Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 32204ms. The settings might not be optimal. (flushed=151, during the time.)
2021-03-09T12:42:15.632831Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-03-09T12:42:15.636133Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.30-33) starting as process 7881 ...
2021-03-09T12:42:15.662204Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-03-09T12:42:15.662261Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-03-09T12:42:15.662270Z 0 [Note] InnoDB: Uses event mutexes
2021-03-09T12:42:15.662278Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-03-09T12:42:15.662285Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2021-03-09T12:42:15.662293Z 0 [Note] InnoDB: Using Linux native AIO
2021-03-09T12:42:15.662631Z 0 [Note] InnoDB: Number of pools: 1
2021-03-09T12:42:15.662796Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-03-09T12:42:15.665996Z 0 [Note] InnoDB: Initializing buffer pool, total size = 3G, instances = 8, chunk size = 128M
2021-03-09T12:42:15.770442Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-03-09T12:42:15.793812Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-03-09T12:42:15.816482Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2021-03-09T12:42:15.855568Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-03-09T12:42:15.876199Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 218488904554
2021-03-09T12:42:16.043434Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 218494147072
2021-03-09T12:42:16.047457Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 218494735481
2021-03-09T12:42:16.048055Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-03-09T12:42:16.048082Z 0 [Note] InnoDB: Starting crash recovery.
2021-03-09T12:42:16.657109Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 31457280 bytes
2021-03-09T12:42:16.881197Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2021-03-09T12:42:17.682185Z 0 [Note] InnoDB: Apply batch completed
2021-03-09T12:42:18.711226Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-03-09T12:42:18.711288Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-03-09T12:42:18.711354Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-03-09T12:42:18.740801Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-03-09T12:42:18.741889Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-03-09T12:42:18.741913Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-03-09T12:42:18.742815Z 0 [Note] InnoDB: Waiting for purge to start
2021-03-09T12:42:18.792948Z 0 [Note] InnoDB: Percona XtraDB (
2021-03-09T12:42:18.796261Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-03-09T12:42:18.810229Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-03-09T12:42:18.859179Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-03-09T12:42:18.859202Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-03-09T12:42:18.861685Z 0 [Warning] CA certificate ca.pem is self signed.
2021-03-09T12:42:18.861753Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-03-09T12:42:18.862802Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-03-09T12:42:18.862854Z 0 [Note] IPv6 is available.
2021-03-09T12:42:18.862876Z 0 [Note] - '::' resolves to '::';
2021-03-09T12:42:18.862942Z 0 [Note] Server socket created on IP: '::'.
2021-03-09T12:42:18.980598Z 0 [Note] Event Scheduler: Loaded 0 events
2021-03-09T12:42:18.997817Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.30-33' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 33, Revision 6517692
2021-03-09T12:43:15.822613Z 0 [Note] InnoDB: Buffer pool(s) load completed at 210309 15:43:15