0

MariaDB service automatically shuts down after 30 minutes.

It's a VPS, 12 vCPU, 12 GB RAM. CentOS 7, Webmin, Virtualmin, 5.5.60-MariaDB, PHP 7.2, Apache 2.

No errors in log, CPU load average 10%.

I created a bash script which checks and restarts it if down but this generates a about a minute of downtime for a busy site.

Any suggestions and ideas are welcome! 

Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server 
181215 10:11:46 [Note] /usr/libexec/mysqld: Normal shutdown 
181215 10:11:46 [Note] Event Scheduler: Purging the queue. 0 events 
181215 10:11:46  InnoDB: Starting shutdown... 
181215 10:11:51  InnoDB: Shutdown completed; log sequence number 62284182262 
181215 10:11:51 [Note] /usr/libexec/mysqld: Shutdown complete

181215 10:11:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
181215 10:11:52 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 
181215 10:11:52 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 3233 ... 
181215 10:11:52 [Warning] Could not increase number of max_open_files to more than 1024 (request: 2035) 
181215 10:11:52 InnoDB: The InnoDB memory heap is disabled 
181215 10:11:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins 181215 10:11:52 InnoDB: Compressed tables use zlib 1.2.7 
181215 10:11:52 InnoDB: Using Linux native AIO 181215 10:11:52 InnoDB: Initializing buffer pool, size =
128.0M 
181215 10:11:52 InnoDB: Completed initialization of buffer pool 
181215 10:11:52 InnoDB: highest supported file format is Barracuda. 
181215 10:11:52  InnoDB: Waiting for the background threads to start 
181215 10:11:53 Percona XtraDB (http://www.percona.com)
5.5.59-MariaDB-38.11 started; log sequence number 62284182262 
181215 10:11:53 [Note] Plugin 'FEEDBACK' is disabled. 
181215 10:11:53 [Note] Server socket created on IP: '0.0.0.0'. 
181215 10:11:53 [Note] Event Scheduler: Loaded 0 events 
181215 10:11:53 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.60-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server 181215 10:42:51 [Note] /usr/libexec/mysqld: Normal shutdown 
181215 10:42:51 [Note] Event Scheduler: Purging the queue. 0 events 
181215 10:42:51  InnoDB: Starting shutdown... 
181215 10:42:53  InnoDB: Shutdown completed; log sequence number 62285727771 
181215 10:42:53 [Note] /usr/libexec/mysqld: Shutdown complete

181215 10:42:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
181215 10:42:55 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release. 
181215 10:42:55 [Note] /usr/libexec/mysqld (mysqld 5.5.60-MariaDB) starting as process 21213 ... 
181215 10:42:55 [Warning] Could not increase number of max_open_files to more than 1024 (request: 2035) 
181215 10:42:55 InnoDB: The InnoDB memory heap is disabled 
181215 10:42:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins 
181215 10:42:55 InnoDB: Compressed tables use zlib 1.2.7 181215 10:42:55 InnoDB: Using Linux native AIO 181215 10:42:55 InnoDB: Initializing buffer pool, size =
128.0M 
181215 10:42:55 InnoDB: Completed initialization of buffer pool 
181215 10:42:55 InnoDB: highest supported file format is Barracuda. 
181215 10:42:55  InnoDB: Waiting for the background threads to start 
181215 10:42:56 Percona XtraDB (http://www.percona.com)
5.5.59-MariaDB-38.11 started; log sequence number 62285727771 
181215 10:42:56 [Note] Plugin 'FEEDBACK' is disabled. 
181215 10:42:56 [Note] Server socket created on IP: '0.0.0.0'. 
181215 10:42:56 [Note] Event Scheduler: Loaded 0 events 181215 10:42:56 [Note] /usr/libexec/mysqld: ready for connections.

/etc/my.cnf content:

[mysqld]
open_files_limit=1024000 
open-files-limit = 1024000 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
innodb_buffer_pool_size=64M 
symbolic-links=0 
innodb_file_per_table = 1 
thread_concurrency = 8 
query_cache_size = 32M 
thread_cache_size = 8 
myisam_sort_buffer_size = 64M 
read_rnd_buffer_size = 8M 
read_buffer_size = 2M 
sort_buffer_size = 2M 
table_open_cache = 512 
max_allowed_packet=256M 
key_buffer_size = 384M 
big-tables 
default-storage-engine = MyISAM 
tmp_table_size=256M 
max_heap_table_size=512M 
local-infile=0 
max_connections=100 
wait_timeout=60


[mysqld_safe] 
log-error=/var/log/mariadb/mariadb.log 
pid-file=/var/run/mariadb/mariadb.pid 
open_files_limit = 102400 
open-files-limit = 102400 
socket=/var/lib/mysql/mysql.sock 
innodb_buffer_pool_size=64M

!includedir /etc/my.cnf.d
Alexander Tolkachev
  • 4,513
  • 3
  • 14
  • 23
Dan D.
  • 1
  • 1
  • I have such problem and it was caused by script in crontab, which shutdown MySQL every 15 minutes. Check system log. – Alexander Tolkachev Dec 15 '18 at 11:56
  • I checked and there's nothing in crontab stopping the service. The service is shutdown after 30 minutes from being started. A cronjob running every half hour won't be able to that... if it's not started at the same time mariadb is started. – Dan D. Dec 15 '18 at 16:01
  • @DanD. Please post content of your php and apache configuration files for analysis. Also, please to pastebin.com or here TEXT results of SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; to dig a little deeper that information already provided. Do you have any SSD or NVME storage devices used for your MySQL data? You could turn on your General Log at 29 1/2 minutes to possibly get an idea. Normally General Log is OFF to avoid filling storage media. – Wilson Hauck Dec 27 '18 at 14:56
  • @DanD. Please post text results of Centos Command Prompt ulimit -a for information about limits AND complete report from MySQLTuner.pl (perl script). Remove the second request related to Open Files Limit and TAKE three of the trailing zeros off the first request of Open_Files_Limit to 1,024 rather than 1 million files to be opened. There are other weird Open Files request that need correction as well. – Wilson Hauck Dec 27 '18 at 17:17
  • @DanD. Please also post results of SHOW GLOBAL VARIABLES LIKE 'log_warning%'; and SHOW GLOBAL VARIABLES LIKE 'log_error%'; – Wilson Hauck Dec 30 '18 at 21:32
  • Turn on the "General" log 28 minutes after starting. Maybe you can catch the command and where it came from. – Rick James Jan 02 '19 at 05:11

1 Answers1

0

Without seeing your system and/or user crontab files, or any query logs, and given that the shutdown does not appear to happen almost exactly 31 minutes after startup (and not at exactly on or close to the minute, e.g. 10:42:00, as it would from cron or at), I'd imagine there is a query being issued either locally on the server or remotely from a client or app.

Robert Barabas from Percona wrote up an excellent diagnostic post about hunting down what can appear to randomly shut down a MySQL daemon; following that, and turning up the volume on your query logs, should help you find the culprit.

neuro
  • 385
  • 2
  • 7