MariaDB/Mysql crashing and I cannot figure out why :(

[lynxus]

Hi Guys,

My DB just randomly stops. I cant see why. or find any hint as to why.. Can anyone make anything of this?
I restart normally fixes it after fixing some crashed tables.

Its not running out of space or memory. I just cant see why it just dies like it does. Is there a dodgy select? insert or something?

Thanks
G

/var/log.mariadb/mariadb.log
141117 18:08:29 mysqld_safe Number of processes running now: 0
141117 18:08:29 mysqld_safe mysqld restarted
141117 18:08:30 InnoDB: The InnoDB memory heap is disabled
141117 18:08:30 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141117 18:08:30 InnoDB: Compressed tables use zlib 1.2.7
141117 18:08:30 InnoDB: Using Linux native AIO
141117 18:08:30 InnoDB: Initializing buffer pool, size = 128.0M
141117 18:08:30 InnoDB: Completed initialization of buffer pool
141117 18:08:31 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!
141117 18:08:31 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
141117 18:08:31 InnoDB: Waiting for the background threads to start
141117 18:08:32 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-34.0 started; log sequence number 1668781
141117 18:08:32 [Note] Plugin 'FEEDBACK' is disabled.
141117 18:08:32 [Note] Server socket created on IP: '0.0.0.0'.
141117 18:08:32 [Note] Event Scheduler: Loaded 0 events
141117 18:08:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.37-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
141117 18:08:32 [ERROR] mysqld: Table './hnwordpress/wp_options' is marked as crashed and should be repaired
141117 18:08:32 [ERROR] mysqld: Table './hnwordpress/wp_options' is marked as crashed and should be repaired
141117 18:08:32 [ERROR] mysqld: Table './hnwordpress/wp_options' is marked as crashed and should be repaired
141117 18:08:32 [ERROR] mysqld: Table './hnwordpress/wp_options' is marked as crashed and should be repaired
141117 18:08:32 [Warning] Checking table: './hnwordpress/wp_options'
141117 18:08:35 mysqld_safe Number of processes running now: 0
141117 18:08:35 mysqld_safe mysqld restarted
141117 18:08:35 InnoDB: The InnoDB memory heap is disabled
141117 18:08:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141117 18:08:35 InnoDB: Compressed tables use zlib 1.2.7
141117 18:08:35 InnoDB: Using Linux native AIO
141117 18:08:35 InnoDB: Initializing buffer pool, size = 128.0M
141117 18:08:35 InnoDB: Completed initialization of buffer pool
141117 18:08:35 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

[aks]

So that log is as useful as a 10 dollar computer! All it says is we are running, we've started from crash. OIt is VERY unlikley to be SQL, as it would just not execute if the SQL cant be parsed. It must be something else. Can you re-create the problem? For example, does it happen at a certain time or with a certain machine?

[lynxus]

yeah, figured that error log meant naff all.

I cant find anything thats causing it, Its just random.. Some weeks it doesnt crash at all. then others its every few hours.
The process just shows that its stopped / crashed.

So i need to do a restart of it.

No errors on any logs in /var/log/*

What else can be done to see whats killing the process? Any more logging i can install? This is driving me insane.. I cant see any reason for it.

[aks]

Yeah, we need a clue. Are you 100% sure there's nothing in /var/log/messages and/or dmesg?

So in MariaDB/MySQL there are 3 different logds (there's a lot more than 3, but 3 I've chosen to mention). These are:
1) The Error Log - contains information about errors that occur while the server is running (also server start and stop messages)
2) The General Query Log. - a record of what mysqld is doing (connect, disconnect, queries)
3) The Slow Query Log - slow SQL statements - you define what "slow" means.

In your config. file:
General (note performance may suffer with this on):
general_log_file = /var/log/mysql/mysql.log
general_log = 1
Slow Query (mentioned as you where concerned about SQL statements):
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Unless you've made (quite sigfnificant changes) to the database system, mysqld_safe is used to start mysqld and if mysqld dies unexpectedly, mysqld_safe notices this, restarts mysqld, and writes a restarted mysqld message to the error log. So I'm wondering if mysqld_safe is dying in your case.

So I'd add to the config. files:
[mysqld_safe]
log_error=/var/log/mysql/mysql_error.log
and in the mysql section:
[mysqld]
log_error=/var/log/mysql/mysql_error.log

BTW, I spun up a fairly large database and executed some SQL that would overflow (in this particular experiment) the sort_buffer (i.e.: sort a large result set and I reduced the sort_buffer value). All that happened ois that my session was killed, the database did not die.

[lynxus]

Thanks for the info,

Ill put these extra logs on ( The server its failing on isnt important, but I am worried what may be causing it and if when I move an older system to the newer mysql server, it may also have issues.. so certainly need to find the cause here.. )

Ill also remove my auto restart from crontab and see what happens when it dies next..


Ill be back!

[TrevorH]

Also, how confident are you that this is not a hardware problem? Random crashes in server processes that are otherwise stable for other people might be explained by, for example, bad RAM. Perhaps booting and running memtest86+ overnight might be informative, if only to rule it out.

[gerald_clark]

Also, how much RAM do you have, how much swap, and what else is running.
If your RAM is too small you might be experiencing OOM kills.

[lynxus]

Hi Guys,

Im confident its not hardware as its running on a VPS ( And since moved suppliers yet the problem persists )
Its happened again, This is all the logs I can find:

Last set of querys:

316875 Query SELECT option_value FROM wp_options WHERE option_name = 'akismet_comment_nonce' LIMIT 1
316878 Query SELECT option_value FROM wp_options WHERE option_name = 'akismet_comment_nonce' LIMIT 1
316876 Query SELECT option_value FROM wp_options WHERE option_name = 'akismet_comment_nonce' LIMIT 1
316864 Quit
316873 Query SELECT option_value FROM wp_options WHERE option_name = 'akismet_comment_nonce' LIMIT 1
316860 Quit


Last error.log messages:
141123 09:25:40 mysqld_safe Number of processes running now: 0
141123 09:25:40 mysqld_safe mysqld restarted
141123 9:25:41 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.
141123 9:25:42 InnoDB: The InnoDB memory heap is disabled
141123 9:25:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141123 9:25:42 InnoDB: Compressed tables use zlib 1.2.7
141123 9:25:42 InnoDB: Using Linux native AIO
141123 9:25:42 InnoDB: Initializing buffer pool, size = 128.0M
141123 09:25:42 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended


Last slow log error:

# User@Host: root[root] @ localhost []
# Thread_id: 316915 Schema: hnwordpress QC_hit: No
# Query_time: 0.003951 Lock_time: 0.000180 Rows_sent: 180 Rows_examined: 195
SET timestamp=1416731136;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
# User@Host: root[root] @ localhost []
# Thread_id: 316916 Schema: hnwordpress QC_hit: No
# Query_time: 0.001292 Lock_time: 0.000156 Rows_sent: 180 Rows_examined: 195
SET timestamp=1416731136;
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';


Note huge amount of ram ( 1g ) and 128 of swap.. But its not "busy" just a wordpress site.
[root@vps95737 log]# free -m
total used free shared buffers cached
Mem: 1024 208 815 53 0 18014398509481970
-/+ buffers/cache: 223 800
Swap: 128 58 69

[gerald_clark]

What is the output of 'uname -a' ?

[aks]

Still we have not enough information to troubleshoot. Seeming the DB just magically dies. This is not good. The (slow) SQL you posted is just "keep alive" kind of thing. Have you checked dmesg and/or /var/log/messages/and/jounrnal?