MySQL keep on crashing

v3
#1

Hi everyone !

I have a problem with one of my server. I have the latest version of EE installed on it, on a droplet with 1Go and 30go of disk. The mysql service keep on stopping and I can’t understand why at all. This is my my.cnf : http://pastebin.com/fDWM8CaA (at least the part that I tried to tweak.

I’ve tried ee debug --mysql --import-slow-log but I have this reply : MySQL slow log file not found, so not imported slow logs

I even tried mysqltuner, but the mysql service is not up long enough to really have good informations with mysqltuner …

Do you have any idea for where to begin ?

Thanks a lot !!

Best regards,

Mat

#2

This is the result of mysqltuner : [OK] Logged in using credentials from debian maintenance account. >> MySQLTuner 1.6.4 - Major Hayden [email protected] >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with ‘–help’ for additional options and output filtering [–] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 10.1.16-MariaDB-1~trusty [OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in InnoDB tables: 12M (Tables: 36)
[--] Data in MyISAM tables: 571M (Tables: 52)
[!!] Total fragmented tables: 1

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3m 15s (2K q [14.323 qps], 129 conn, TX: 566M, RX: 428K)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 584.0M global + 7.5M per thread (30 max threads)
[OK] Maximum reached memory usage: 779.8M (78.45% of installed RAM)
[OK] Maximum possible memory usage: 810.0M (81.49% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[!!] Highest connection usage: 86%  (26/30)
[OK] Aborted connections: 0.00%  (0/129)
[OK] Query cache efficiency: 27.3% (805 cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 256 sorts)
[!!] Temporary tables created on disk: 28% (102 on disk / 356 total)
[OK] Thread cache hit rate: 79% (26 created / 129 connections)
[OK] Table cache hit rate: 27% (51 open / 187 opened)
[OK] Open file limit used: 8% (92/1K)
[OK] Table locks acquired immediately: 99% (1K immediate / 1K locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.4% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/42.8M
[!!] Read Key buffer hit rate: 46.9% (160K cached / 85K reads)
[!!] Write Key buffer hit rate: 0.0% (116 cached / 116 writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/12.3M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 10.65% (1745 used/ 16383 total)
[OK] InnoDB Read buffer efficiency: 99.73% (273392 hits/ 274128 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 313 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    max_connections (> 30)
    wait_timeout (< 300)
    interactive_timeout (< 300)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_buffer_pool_instances (=1)
#3

Maybe that can help. My mysql logs are empty, but I do have some informations in the syslog :

Jul 26 06:01:34 etabli-srv mysqld: 2016-07-26  6:01:34 140044338588416 [ERROR] mysqld: Table './etabli/eta_options' is marked as crashed and should be repaired
Jul 26 06:01:34 etabli-srv mysqld: 2016-07-26  6:01:34 140044338588416 [ERROR] mysqld: Table 'eta_options' is marked as crashed and should be repaired
Jul 26 06:01:34 etabli-srv mysqld: 2016-07-26  6:01:34 140044338588416 [Warning] Checking table:   './etabli/eta_options'

I’ve tried to repair the table and mysql told my that worked :

MariaDB [etabli]> REPAIR TABLE eta_options;
+-----------------------------+--------+----------+----------+
| Table                       | Op     | Msg_type | Msg_text |
+-----------------------------+--------+----------+----------+
| etabli.eta_options | repair | status   | OK       |
+-----------------------------+--------+----------+----------+
1 row in set (27.25 sec)

But I still have that error when I restart mysql.

Another thing, I have some maybe useful informations :

Jul 26 05:59:22 etabli-srv kernel: [ 7212.865588] Out of memory: Kill process 19053 (mysqld) score 87 or sacrifice child
Jul 26 05:59:22 etabli-srv kernel: [ 7212.869330] Killed process 19053 (mysqld) total-vm:1480900kB, anon-rss:7472kB, file-rss:0kB
Jul 26 05:59:22 etabli-srv kernel: [ 7212.946056] php-fpm5.6 invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
Jul 26 05:59:22 etabli-srv kernel: [ 7212.946061] php-fpm5.6 cpuset=/ mems_allowed=0
Jul 26 05:59:22 etabli-srv kernel: [ 7212.946065] CPU: 0 PID: 19228 Comm: php-fpm5.6 Not tainted 3.13.0-71-generic #114-Ubuntu 

   Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
    Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [ERROR] Plugin 'InnoDB' init function returned error.
    Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [Note] Plugin 'FEEDBACK' is disabled.
    Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [ERROR] Unknown/unsupported storage engine: InnoDB
    Jul 26 06:04:14 etabli-srv mysqld: 2016-07-26  6:04:14 139816844740544 [ERROR] Aborting
    Jul 26 06:04:14 etabli-srv mysqld:
    Jul 26 06:04:14 etabli-srv mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
    Jul 26 06:04:16 etabli-srv kernel: [ 7506.415304] php-fpm5.6 invoked oom-killer: gfp_mask=0x200da, order=0, oom_score_adj=0
    Jul 26 06:04:16 etabli-srv kernel: [ 7506.415321] php-fpm5.6 cpuset=/ mems_allowed=0

Free -m :

 total       used       free     shared    buffers     cached
Mem:           994        108        885          2          2         36
-/+ buffers/cache:         69        924
Swap:         1023         88        935
#4

It looks like you ran out of memory. You need to edit/update your mysql conf file to reduce memory usage by mysql or get larger VPS instance

#5

Hmm. After that message, I updated my droplet to a 2Go of ram. It went way better (even if I don’t understand how a woocommerce with not so many customers can’t run on a 1Go droplet). The thing is, the server went down at the end of the day. I restarted it and everything is up this morning. But the people are going to come !

This is wht mysqltuner is saying :

tmp_table_size (> 32M) max_heap_table_size (> 32M) innodb_buffer_pool_instances (=1)

What’s the recommandation on a 2Go and 2 cores for the 2 first line ? 32M ? 64 ?

Thanks !

#6

It did crash again …

I’ve checked the syslog and it seems that some tables are crashed and should be repaired. I keep on repairing them but even with a OK, it does not look like they are repaired …

And in the syslog :

Jul 27 05:34:32 etabli-srv kernel: [73857.413573] Out of memory: Kill process 14567 (php-fpm5.6) score 24 or sacrifice child
Jul 27 05:34:32 etabli-srv kernel: [73857.413704] Killed process 14567 (php-fpm5.6) total-vm:452856kB, anon-rss:21592kB, file-rss:2900kB

Strange because it’s mysql that crashes :confused:

#7

Copy and paste your /etc/mysql/my.cnf configuration into this site http://www.omh.cc/mycnf/

then post your result here

#8

Hi,

This is the result :

max_allowed_packet 16.0 MB
sort_buffer_size 4.0 MB
net_buffer_length 16.0 KB
thread_stack 192.0 KB
read_rnd_buffer_size 1.0 MB
read_buffer_size 2.0 MB
join_buffer_size 128.0 KB
Total (per session)23.3 MB
Global variables
innodb_log_buffer_size 8.0 MB
query_cache_size 64.0 MB
innodb_buffer_pool_size 256.0 MB
innodb_additional_mem_pool_size 1.0 MB
key_buffer_size 128.0 MB
Total 457.0 MB

The mysql service is less unstable, but still, today it crashed 2 times …

Oh and I still have, in my syslog, the “wp_options is marked as crashed and should be repaired” even when I DID repaired it :confused:

#9

Your MySQL memory footprint is just too much for the VPS, you have set it to use max 82% of the entire server memory – this is way too much, your system doesn’t have enough resources to perform its duties.

You need to reduce it to at least 50-60%, not more.

For example it looks like you don’t really use InnoDB so you’re wasting memory by allocating memory to the engine you don’t use. I’d disable it completely, at least for the time being or reduce its memory usage to lower values.

If you can post your current my.cnf file I can be more specific in recommendations but based on your previous mysqltuner results:

Your Query cache efficiency is only 27.3%, you need to increase the size of Query cache.

myisam_sort_buffer_size is currently 512M, that is way too much. Make it 4 or 8M and see how it goes.

Key buffer used: 18.4% (24M used / 134M cache), needs to be decreased you’re wasting 110M, so make it like 50M, and run another test to see the usage %. Amount of USED should be close to TOTAL cache, using example above, it would (44M used / 50MB cache).

table_open_cache increase to 800.

try to increase both: tmp_table_size and max_heap_table_size to say to 48M and see the results and watch the overall memory footprint size, remember it should not exceed 50-60% of total server memory.

#10

Thank you for your reply tyrro,

Some tables are MyISAM and other are InnoDB, I’ve seen that during the mysqlcheck -r (or -o). Should I correct that ?

This it the my.cnf :

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
max_connections		= 100
connect_timeout		= 5
wait_timeout		= 600
max_allowed_packet	= 16M
thread_cache_size       = 64
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
interactive_timeout     = 300
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 128K
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
log_bin			= /var/log/mysql/mariadb-bin
log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
max_binlog_size         = 100M
# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size	= 50M
innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
#11

And with your recommandations, this is the new result of mysqltuner.

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    query_cache_limit (> 128K, or use smaller result sets)
    innodb_buffer_pool_instances (=1)
root@etabli-srv:~# mysqltuner
[OK] Logged in using credentials from debian maintenance account.
 >>  MySQLTuner 1.6.4 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.16-MariaDB-1~trusty
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in InnoDB tables: 13M (Tables: 36)
[--] Data in MyISAM tables: 571M (Tables: 52)
[!!] Total fragmented tables: 3

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1m 10s (1K q [20.514 qps], 150 conn, TX: 6M, RX: 271K)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 562.0M global + 7.5M per thread (100 max threads)
[OK] Maximum reached memory usage: 599.7M (29.95% of installed RAM)
[OK] Maximum possible memory usage: 1.3G (65.70% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 5% (5/100)
[OK] Aborted connections: 0.00%  (0/150)
[OK] Query cache efficiency: 22.1% (317 cached / 1K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 115 sorts)
[OK] Temporary tables created on disk: 24% (52 on disk / 214 total)
[OK] Thread cache hit rate: 96% (5 created / 150 connections)
[OK] Table cache hit rate: 95% (180 open / 188 opened)
[OK] Open file limit used: 16% (174/1K)
[OK] Table locks acquired immediately: 100% (762 immediate / 762 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 19.9% (10M used / 52M cache)
[OK] Key buffer size / total MyISAM indexes: 50.0M/42.9M
[!!] Read Key buffer hit rate: 22.6% (55K cached / 43K reads)
[!!] Write Key buffer hit rate: 0.0% (35 cached / 35 writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 256.0M/14.0M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 3.14% (514 used/ 16383 total)
[OK] InnoDB Read buffer efficiency: 96.93% (16192 hits/ 16705 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    innodb_buffer_pool_instances (=1)
#12

Decrease connections from 100 back to 30, you’re not using that much. If you see stable traffic increase you can then raise this to more connections but gradually unless you expect some spike in traffic.

Try to increase query_cache_size even more. try these values

wait_timeout  = 100
interactive_timeout = 100
query_cache_limit  = 2M
query_cache_size  = 128M
innodb_buffer_pool_size	= 64M
myisam_sort_buffer_size    = 32M

And test with tuner again tomorrow.

#13

If you follow http://www.omh.cc/mycnf/ then you will notice 2 things: Session variables and Global variables.

Global variables group value is fixed while Session variables group value is dynamic (depends on how many mysql connection).

My configuration below just use MySQL default value:

max_allowed_packet	= 4M
sort_buffer_size	= 256K
read_rnd_buffer_size	= 256K
read_buffer_size	= 128K
key_buffer_size		= 8M
query_cache_size	= 32M
innodb_buffer_pool_size	= 128M

This configuration, according to http://www.omh.cc/mycnf/ , will use approx 5 MB per session + 177 MB (global variables) = 182 MB

If there are 100 connections then 5 MB per session * 100 connections = 500 MB + 177 MB (global variables) = 677 MB

you may also add/edit this

performance_schema = off
max_connections = 30
wait_timeout  = 120
#14

Thank you all for your help ! I’m going to test the configurations and tell you what worked ! While doing that, I’ve also tighten the security with fail2ban. Now I just have to understand how to permanently block “forever” the repeating attackers, and all will be well.

Thank you again !

#15