MySQL crashes every other day, tuning ideas?

[unicornication]

I'm trying to ready my web server to deploy some production code, however, my mysql instance is unreliable as it crashes every few days, likely due to an overuse of memory.

My server has 30GB available disk, working swapfile and 1GB memory- it's running an instance of ZPanel (similar to CPanel). MySQL set has 137 InnoDB tables and 37 ISAM tables.

24 hours after a restart, mysql has the following stats:

PID USER PR NI VIRT RES SHR S %CPU %MEM COMMAND
1961 mysql 20 0 851m 73m 7520 S 0.0 7.4 mysqld


After running `mysqltuner.pl` - the following issues it raised look pretty bad to me:

[!!] Total fragmented tables: 137
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[!!] InnoDB buffer pool / data size: 128.0M/139.5M
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

Its suggestions were:

Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
innodb_buffer_pool_size (>= 139M) if possible.

I have no idea how to make any of these modifications!

My solution thus far has to create a mysql.sh in /etc/cron.hourly with the line service mysql restart - this doesn't seem to work all too well though and isn't a great solution for production.

These are the values in my.cnf

# * Fine Tuning
key_buffer = 8M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 12
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 20
#table_cache = 64
#thread_concurrency = 12
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * 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 log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# 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
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * 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!


[mysqldump]
quick
quote-names
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 16M

Any ideas how to improve mysql performance? Or get it to stop crashing (or at least, crashing less!) I know more memory would probably fix this issue outright, but I think my config could use some help.

 

[cPanelMichael]

Hello

Is there any specific output in the MySQL error log (/var/lib/mysql/$hostname.err) when MySQL fails?

Thank you.

 

[unicornication]

Hi!

There is no error, but upon doing a little digging- it looks like MySQL is consuming too much memory and being killed by the OOM killer. I have approximately 130 InnoDB tables with a couple hundred users accessing per hour- assuming 20-50 max-connections, I don't think my.cnf is configured to do this in the most effective way?

 

[cPanelMichael]

You may want to run a MySQL tuner script again after letting MySQL run for 24 hours and implement any additional recommendations it provides.

Thank you.