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 
