I have a production environment where about once a week I'm finding the database needs to be repaired and restarted. The MariaDB database contains some InnoDB and some MyISAM tables (the mixture being mainly for legacy reasons, rather than something considered).
- Server is a VM with 1GB RAM and 40GB disk space and 1 CPU
- Memory usage is typically 50% and CPU about 20%
- OS CentOS Linux release 7.5
- MariaDB version is 5.5.56-MariaDB
- The database is being accessed by PHP using the CodeIgniter 3 framework, using database sessions.
We are experiencing roughly weekly table crashes.
The log has nothing in it today until 13:44 when it starts with an apparent restart followed by a number of tables being reported as crashed:
180725 13:44:20 mysqld_safe Number of processes running now: 0
180725 13:44:20 mysqld_safe mysqld restarted
180725 13:44:21 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
180725 13:44:21 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 11977 ...
180725 13:44:21 InnoDB: The InnoDB memory heap is disabled
180725 13:44:21 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180725 13:44:21 InnoDB: Compressed tables use zlib 1.2.7
180725 13:44:21 InnoDB: Using Linux native AIO
180725 13:44:21 InnoDB: Initializing buffer pool, size = 128.0M
180725 13:44:21 InnoDB: Completed initialization of buffer pool
180725 13:44:21 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 15064581, file name ./mysql-bin.000014
180725 13:44:22 InnoDB: Waiting for the background threads to start
180725 13:44:23 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 355063376
180725 13:44:23 [Note] Plugin 'FEEDBACK' is disabled.
180725 13:44:23 [Note] Recovering after a crash using mysql-bin
180725 13:44:23 [Note] Starting crash recovery...
180725 13:44:23 [Note] Crash recovery finished.
180725 13:44:23 [Note] Server socket created on IP: '0.0.0.0'.
180725 13:44:23 [Warning] 'proxies_priv' entry '@ root@prod3' ignored in --skip-name-resolve mode.
180725 13:44:23 [Note] Event Scheduler: Loaded 0 events
180725 13:44:23 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
180725 13:44:33 [ERROR] mysqld: Table './prod/products' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/products'
180725 13:44:33 [ERROR] mysqld: Table './prod/product_images' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/product_images'
180725 13:44:33 [ERROR] mysqld: Table './prod/products_linked_groups' is marked as crashed and should be repaired
180725 13:44:33 [Warning] Checking table: './prod/products_linked_groups'
180725 13:44:34 [ERROR] mysqld: Table './prod/pricer_items' is marked as crashed and should be repaired
180725 13:44:34 [Warning] Checking table: './prod/pricer_items'
180725 13:45:03 [ERROR] mysqld: Table './prod/email_queue' is marked as crashed and should be repaired
180725 13:45:03 [Warning] Checking table: './prod/email_queue'
180725 13:45:20 [ERROR] mysqld: Table './prod/stock' is marked as crashed and should be repaired
180725 13:45:20 [Warning] Checking table: './prod/stock'
180725 13:45:56 [ERROR] mysqld: Table './prod/wrpm_users' is marked as crashed and should be repaired
180725 13:45:56 [Warning] Checking table: './prod/wrpm_users'
180725 13:45:56 [ERROR] mysqld: Table './prod/users_files' is marked as crashed and should be repaired
180725 13:45:56 [Warning] Checking table: './prod/users_files'
180725 13:50:50 [ERROR] mysqld: Table './prod/faqs' is marked as crashed and should be repaired
180725 13:50:50 [Warning] Checking table: './prod/faqs'
180725 13:51:20 [ERROR] mysqld: Table './prod/orders' is marked as crashed and should be repaired
180725 13:51:20 [Warning] Checking table: './prod/orders'
180725 13:51:20 [ERROR] mysqld: Table './prod/orders_products' is marked as crashed and should be repaired
180725 13:51:20 [Warning] Checking table: './prod/orders_products'
180725 13:56:41 [Note] /usr/libexec/mysqld: Normal shutdown
I know how recover this and have used all of the following commands:
systemctl status mariadb
mysqlcheck --auto-repair -A -u root -p
myisamchk -o /var/lib/mysql/prod/*.MYI (used with caution!)
What I would like help with is how to begin to diagnose the cause of the problem. Perhaps there is something obviously wrong in the configuration?
The config file /etc/my.cnf.d/server.cnf contains:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
# Skip reverse DNS lookup of clients
skip_name_resolve = 1
# key_buffer_size = 256M
# max_allowed_packet = 1M
# table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
wait_timeout=30
max_allowed_packet = 16M
max_connections = 100 #default is 151
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Best Answer
Your server is crashing for some reason. It is during the recovery that these messages are appearing. Look further back in the mariadb log to see if there are messages before the crash. If there are no message indicating a crash/safe shutdown it might be that the mariadb is getting killed by an OOM condition.