Поставил Bitrix Virtual Appliance, все считай по стандартным конфигам.
На нем сайт, который выступает как API с движком Bitrix управление сайтом.
Сайт использует mysql для работы самого себя, а все данные с которыми работает API на отдельном posgres сервере, поэтому нагрузку на БД этот сайт давать не должен.
Но через какое-то время стала падать mysql по innodb errno 12.
Начитался советов в интернете, накрутил настройки my.cnf
сейчас он выглядит так:
my.cnf#
# Basic mysql configuration. Use bvat for advanced settings.
# Parameters set by bvat are stored in /etc/mysql/conf.d/bvat.cnf.
# If you want to change any parameter, you'll have to redefine it in /etc/mysql/conf.d/z_bx_custom.cnf
#
[client]
port = 3306
socket = /var/lib/mysqld/mysqld.sock
default-character-set = utf8
[mysqld_safe]
nice = 0
socket = /var/lib/mysqld/mysqld.sock
[mysqld]
skip-name-resolve
performance_schema=off
# Basic mysql server configuration
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysqld/mysqld.sock
skip-external-locking
default-storage-engine = innodb
pid-file = /var/run/mysqld/mysqld.pid
transaction-isolation = READ-COMMITTED
max_allowed_packet = 16M
myisam-recover-options = BACKUP
explicit_defaults_for_timestamp = 1
expire_logs_days = 10
max_binlog_size = 100M
sql_mode = ""
# Cache parameters
query_cache_size = 0
query_cache_limit = 0
table_open_cache = 12288
thread_cache_size = 64
thread_pool_size = 16
key_buffer_size = 16M
thread_stack = 128K
join_buffer_size = 6M
sort_buffer_size = 6M
# Parameters for temporary tables
tmpdir = /tmp
max_heap_table_size = 64M
tmp_table_size = 8M
# InnoDB parameters
innodb_file_per_table
innodb_buffer_pool_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 8M
innodb_flush_method = O_DIRECT
innodb_strict_mode = OFF
innodb_log_buffer_size = 8M
# Database charset parameters
character-set-server = utf8
collation-server = utf8_unicode_ci
init-connect = "SET NAMES utf8 COLLATE utf8_unicode_ci"
#skip-character-set-client-handshake
skip-name-resolve
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8
[mysql]
[isamchk]
key_buffer = 16M
# Include additional settings
!includedir /etc/mysql/conf.d/
и этого момента БД перестала падать каждые пол часа с errno 12
вместо этого стала падать раз в 2-4 часа.
В логе следующее:
error.logVersion: '5.7.34-37' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 37, Revision 7c516e9
2021-12-13T11:37:30.809690Z 13 [Note] Aborted connection 13 to db: 'sitemanager' user: 'bitrix0' host: 'localhost' (Got an error reading communication packets)
2021-12-13T11:37:44.306291Z 17 [Note] Aborted connection 17 to db: 'sitemanager' user: 'bitrix0' host: 'localhost' (Got an error reading communication packets)
2021-12-13T15:03:07.072167Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 16238)
2021-12-13T15:03:07.072555Z 0 [Warning] Changed limits: table_open_cache: 2477 (requested 8096)
2021-12-13T15:03:07.188510Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2021-12-13T15:03:07.190748Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.34-37) starting as process 11771 ...
2021-12-13T15:03:07.203986Z 0 [Note] InnoDB: PUNCH HOLE support available
2021-12-13T15:03:07.204036Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-12-13T15:03:07.204041Z 0 [Note] InnoDB: Uses event mutexes
2021-12-13T15:03:07.204046Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2021-12-13T15:03:07.204050Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2021-12-13T15:03:07.204053Z 0 [Note] InnoDB: Using Linux native AIO
2021-12-13T15:03:07.204382Z 0 [Note] InnoDB: Number of pools: 1
2021-12-13T15:03:07.204584Z 0 [Note] InnoDB: Using CPU crc32 instructions
2021-12-13T15:03:07.209713Z 0 [Note] InnoDB: Initializing buffer pool, total size = 64M, instances = 1, chunk size = 64M
2021-12-13T15:03:07.213290Z 0 [Note] InnoDB: Completed initialization of buffer pool
2021-12-13T15:03:07.215568Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-12-13T15:03:07.233520Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2021-12-13T15:03:07.241512Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2021-12-13T15:03:07.278486Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 779555565
2021-12-13T15:03:07.278515Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 779555678
2021-12-13T15:03:07.282969Z 0 [Note] InnoDB: Database was not shutdown normally!
2021-12-13T15:03:07.282990Z 0 [Note] InnoDB: Starting crash recovery.
2021-12-13T15:03:07.382465Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2021-12-13T15:03:08.074836Z 0 [Warning] InnoDB: Resizing redo log from 2*4096 to 2*512 pages, LSN=779555920
2021-12-13T15:03:08.179883Z 0 [Warning] InnoDB: Starting to delete and rewrite log files.
2021-12-13T15:03:08.212100Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 8 MB
2021-12-13T15:03:08.229013Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 8 MB
2021-12-13T15:03:08.253707Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2021-12-13T15:03:08.257445Z 0 [Warning] InnoDB: New log files created, LSN=779556373
2021-12-13T15:03:08.297996Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-12-13T15:03:08.298019Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-12-13T15:03:08.298061Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-12-13T15:03:08.343596Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2021-12-13T15:03:08.344709Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2021-12-13T15:03:08.344737Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2021-12-13T15:03:08.346318Z 0 [Note] InnoDB: Waiting for purge to start
2021-12-13T15:03:08.397120Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.34-37 started; log sequence number 779555678
2021-12-13T15:03:08.397302Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2021-12-13T15:03:08.397545Z 0 [Note] Plugin 'FEDERATED' is disabled.
2021-12-13T15:03:08.417885Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2021-12-13T15:03:08.417924Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2021-12-13T15:03:08.421703Z 0 [Warning] CA certificate ca.pem is self signed.
2021-12-13T15:03:08.421770Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2021-12-13T15:03:08.424637Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2021-12-13T15:03:08.424703Z 0 [Note] IPv6 is available.
2021-12-13T15:03:08.424723Z 0 [Note] - '::' resolves to '::';
2021-12-13T15:03:08.424730Z 0 [Note] Server socket created on IP: '::'.
2021-12-13T15:03:08.517015Z 0 [Note] InnoDB: Buffer pool(s) load completed at 211213 19:03:08
2021-12-13T15:03:08.602198Z 0 [Note] Event Scheduler: Loaded 0 events
2021-12-13T15:03:08.602615Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.34-37' socket: '/var/lib/mysqld/mysqld.sock' port: 3306 Percona Server (GPL), Release 37, Revision 7c516e9
что блин ему снова не нравится? Как заставить его не умирать?
