The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL crashes every other day, tuning ideas?

Discussion in 'Workarounds and Optimization' started by unicornication, Oct 20, 2015.

  1. unicornication

    unicornication Registered

    Joined:
    Oct 20, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Irvine, CA
    cPanel Access Level:
    Root Administrator
    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

    Code:
    # * 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.
     
    #1 unicornication, Oct 20, 2015
    Last edited by a moderator: Oct 21, 2015
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. unicornication

    unicornication Registered

    Joined:
    Oct 20, 2015
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Irvine, CA
    cPanel Access Level:
    Root Administrator
    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?
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
Loading...

Share This Page