MariaDB 10.3 crashing and restarting intermittently - segfault at 0
Hello,
We are using a Debian 10 server with MariaDB 10.3.27. It use to work nice, but since 1 week, we are facing some regular crashes after a few hours of run. Then applications (zabbix, etc...) loss the DB connections and some transactions are broken.
System specs : - 2 vCPU - 10G of RAM - Disks are some LUNs on an EMC VNX
Here is an example of the syslog messages:
Dec 14 16:05:08 mysqlbddvprd1 kernel: [503847.749484] show_signal_msg: 18 callbacks suppressed Dec 14 16:05:08 mysqlbddvprd1 kernel: [503847.749487] mysqld[60145]: segfault at 0 ip 0000557197badfb3 sp 00007f2dbbe2d310 error 6 in mysqld[5571973f0000+80a000] Dec 14 16:05:08 mysqlbddvprd1 kernel: [503847.749491] Code: c7 45 00 00 00 00 00 8b 7d cc 4c 89 e2 4c 89 f6 e8 52 2f 84 ff 49 89 c7 49 39 c4 0f 84 06 01 00 00 e8 21 18 00 00 41 8b 4d 00 <89> 08 85 c9 74 37 49 83 ff ff 0f 84 ad 00 00 00 f6 c3 06 75 28 4d Dec 14 16:05:08 mysqlbddvprd1 systemd[1]: mariadb.service: Main process exited, code=killed, status=11/SEGV Dec 14 16:05:08 mysqlbddvprd1 systemd[1]: mariadb.service: Failed with result 'signal'. Dec 14 16:05:13 mysqlbddvprd1 systemd[1]: mariadb.service: Service RestartSec=5s expired, scheduling restart. Dec 14 16:05:13 mysqlbddvprd1 systemd[1]: mariadb.service: Scheduled restart job, restart counter is at 5. Dec 14 16:05:13 mysqlbddvprd1 systemd[1]: Stopped MariaDB 10.3.27 database server. Dec 14 16:05:13 mysqlbddvprd1 systemd[1]: Starting MariaDB 10.3.27 database server... Dec 14 16:05:14 mysqlbddvprd1 sssd[be[ad.mediapost.fr]]: GSSAPI Error: Unspecified GSS failure. Minor code may provide more information (Server not found in Kerberos database) Dec 14 16:05:14 mysqlbddvprd1 mysqld[52124]: 2020-12-14 16:05:14 0 [Note] /usr/sbin/mysqld (mysqld 10.3.27-MariaDB-0+deb10u1) starting as process 52124 ... Dec 14 16:05:23 mysqlbddvprd1 systemd[1]: Started MariaDB 10.3.27 database server. Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52181]: Upgrading MySQL tables if necessary. Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: Looking for 'mysql' as: /usr/bin/mysql Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: Version check failed. Got the following error when calling the 'mysql' command line client Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52184]: FATAL ERROR: Upgrade failed Dec 14 16:05:23 mysqlbddvprd1 /etc/mysql/debian-start[52197]: Checking for insecure root accounts. Dec 14 16:05:23 mysqlbddvprd1 debian-start[52179]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Dec 14 16:09:01 mysqlbddvprd1 CRON[52474]: (root) CMD ( [ -x /usr/lib/php/sessionclean ] && if [ ! -d /run/systemd/system ]; then /usr/lib/php/sessionclean; fi) Dec 14 16:09:01 mysqlbddvprd1 systemd[1]: Starting Clean php session files... Dec 14 16:09:02 mysqlbddvprd1 systemd[1]: phpsessionclean.service: Succeeded. Dec 14 16:09:02 mysqlbddvprd1 systemd[1]: Started Clean php session files.
And here is a part of the conf file we use: /etc/mysql/mariadb.conf.d/50-server.cnf
# # * Fine Tuning # key_buffer_size = 20M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed the first time they are touched myisam_recover_options = BACKUP max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Fine Tuning for InnoDB # innodb_buffer_pool_size = 7G # Go up to 70% to 80% of your available RAM innodb_buffer_pool_instances = 4 # Bigger if huge InnoDB Buffer Pool or high concurrency innodb_file_per_table = 1 # Is the recommended way nowadays innodb_flush_method = O_DIRECT innodb_write_io_threads = 8 # If you have a strong I/O system or SSD innodb_read_io_threads = 8 # If you have a strong I/O system or SSD innodb_io_capacity = 1000 # If you have a strong I/O system or SSD innodb_flush_log_at_trx_commit = 1 # 1 for durability, 0 or 2 for performance innodb_log_buffer_size = 8M # Bigger if innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 128M # Bigger means more write throughput but longer recovery time # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M
Any comments are welcome.
Best regards,
Answer Answered by Daniel Black in this comment.
Please do a bug report on jira. Information from the mariadb error log (journalctl -n 40 mariadb.server) that includes from the mariadb start until the crash will be required. Data structures (`show create table {tablename}`) or any query causing the crash along with `EXPLAIN {query}` will assist our investigation.
Note on your config, its generally recommended to disable the query_cache unless you have tested and can show a benefit.
mysql/debian-start errors can be fixed by ensuring the root user has unix_socket authentication or have a user,password in /etc/mysql/debian.cnf for the use by this script.