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 spikes and server crashes

Discussion in 'Workarounds and Optimization' started by modom, Jun 11, 2013.

  1. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Hi,

    I am having a terrible time with one of my ecommerce stores with over 10,000 customers and has downloadable products.

    One of my ecommerce (prestashop) stores has a sale and the server is hit all at once it seems and mysql spikes and server crashes.
    I have checked the logs and ran mysqltuner.
    When I run mysqltuner it wants me to keep on increasing all the values it recommends in /etc/my.cnf and the problem persists.

    This is my.cnf file now after removing the other listings:
    [mysqld]
    # add skip-innodb to disable InnoDB
    skip-innodb
    #open_files_limit=26520
    local-infile=0
    default-storage-engine=MyISAM

    After the crash I get several of these emails:
    /etc/cron.hourly/modsecparse.pl:

    DBI connect('modsec:localhost','modsec',...) failed: Too many connections at /etc/cron.hourly/modsecparse.pl line 19
    Unable to connect to mysql database at /etc/cron.hourly/modsecparse.pl line 19.

    Server specs are:

    Base System - Intel Dual Clovertown (5310) System 1.60GHz
    Two Intel Xeon 5310 "Clovertown" Quad Core processors (8-core)
    /dev/sda - 250GB HDD - 64-bit
    CentOS release 6.4 (Final)

    Server information: Linux #1 SMP Tue Apr 23 18:13:20 UTC 2013 i686 64-bit
    Server software version: Apache 2.2.24
    PHP version: 5.5
    MySQL 5.5
    Memory limit: 4GB
    Max execution time: 120

    I have 20 ecommerce stores all using prestashop for the store.

    Please help ...
     
    #1 modom, Jun 11, 2013
    Last edited: Jun 11, 2013
  2. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: my.cnf Optimisation - 24GB RAM

    Please post result of mysqltuner
     
  3. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Re: my.cnf Optimisation - 24GB RAM

    About an hour ago I changed the my.cnf file to:

    [mysqld]
    # add skip-innodb to disable InnoDB
    skip-innodb

    wait_timeout = 30
    connect_timeout = 1
    local-infile=0

    open_files_limit=26520
    default-storage-engine=MyISAM

    max_connections=500

    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes


    mysqltuner.pl says:
    Also, while running top -c I see this for the CPU and then it goes down:
    20285 mysql 20 0 1532m 207m 4532 S 781.3 11.1 60:58.46 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6
    781.3 for %CPU?
     
    #3 modom, Jun 11, 2013
    Last edited: Jun 11, 2013
  4. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: my.cnf Optimisation - 24GB RAM

    set it like this
    Code:
    [mysqld]
    # add skip-innodb to disable InnoDB
    skip-innodb
    
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0
    
    open_files_limit=26520
    default-storage-engine=MyISAM
    
    max_connections=250
    
    key_buffer_size = 1G
    max_allowed_packet=20M
    
    query_cache_size=30M
    query_cache_limit=1M
    tmp_table_size=50M
    max_heap_table_size=50M
    thread_cache_size=50
    
    table_open_cache = 3000
    table_definition_cache = 1000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=4M
    
    low_priority_updates=1
    concurrent_insert=2
    
     
  5. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Re: my.cnf Optimisation - 24GB RAM

    Thank you so much! I will monitor it today and check during the client's sale.
     
  6. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Re: my.cnf Optimisation - 24GB RAM

    Hi,

    My client posted her sale on facebook and the server was running ok until it got several hits over and over again and the /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib6
    781.3 for %CPU went up again and the server crashed.

    I restarted the server and her store is active again with the load low but don't expect this to last long if it repeats the load increase again.

    After it started up I ran mysqltuner again and got this:
     
    #6 modom, Jun 11, 2013
    Last edited: Jun 11, 2013
  7. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Re: my.cnf Optimisation - 24GB RAM

    mysqltuner says that you have 2GB RAM, not 4GB as you wrote

    here is updated my.cnf, better for 2GB
    Code:
    [mysqld]
    # add skip-innodb to disable InnoDB
    skip-innodb
    
    wait_timeout = 30
    connect_timeout = 1
    local-infile=0
    
    open_files_limit=26520
    default-storage-engine=MyISAM
    
    max_connections=200
    
    key_buffer_size = 600M
    max_allowed_packet=20M
    
    query_cache_size=30M
    query_cache_limit=1M
    tmp_table_size=50M
    max_heap_table_size=50M
    thread_cache_size=50
    
    table_open_cache = 3000
    table_definition_cache = 1000
    
    slow_query_log=1
    slow_query_log_file=mysql-slow.log
    long_query_time=0.1
    log-queries-not-using-indexes
    
    sort_buffer_size=256K
    join_buffer_size=2M
    read_rnd_buffer_size=2M
    
    max_write_lock_count = 10
    concurrent_insert=2
    
    remotely we can't check much what happens there,
    if mysql went 781.3 for %CPU so you probably had some queries stuck,
    they might be too slow, or you might have some cache miss storm (cache refresh of slow query when many people quering at once)

    I've added "max_write_lock_count = 10" to my.cnf to prevent too long insert/update lock on MyISAM
    you can also convert the tables to InnoDB, but thats all in mysql

    remember that you need to have also script optimized (caching etc)

    to get info on slow queries you can:
    wget http://percona.com/get/pt-query-digest
    chmod +x pt-query-digest

    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt
    and in slow.txt you can info on slow queries


    Regards
     
  8. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Re: my.cnf Optimisation - 24GB RAM

    Wow I didn't even see what the ram said. I'm suppose to have 4 so will contact the server company.
     
  9. BillyS

    BillyS Active Member

    Joined:
    Mar 22, 2013
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    Re: my.cnf Optimisation - 24GB RAM

    key_buffer_size = 600M

    [OK] Key buffer size / total MyISAM indexes: 1.0G/444.7M

    I'd suggest you save 100M and go with:

    key_buffer_size = 500M

    [OK] Highest usage of available connections: 5% (14/250)

    BTW - If you use the recommendation provided by thinkbot, it'll consume around 1,600 MB of RAM. I'd suggest:


    max_connections=100

    Unless the 14 is an unusually low number...
     
  10. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Re: my.cnf Optimisation - 24GB RAM

    Thanks so much! This post has been so helpful and saved me from losing a client.

    I found out that I do have 4GB ram but the slot alignment was not followed so it was only registering 2GB instead. Even though I have 4GB I still set the settings for 2GB and it looks good.

    I figured out the other listings but what exactly does this mean:
    Highest usage of available connections: 5% (14/250)
    Is that the highest usage is 14 but it was using 250?

    If my client's store stays up tonight I'll be happy. It looks very good right now.
     
  11. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    "I'd suggest you save 100M and go with:"

    there is nothing to save, couse this mean MAX value that MySQL can use, if it doesn't need it, it won't use it

    "[OK] Highest usage of available connections: 5% (14/250)
    BTW - If you use the recommendation provided by thinkbot, it'll consume around 1,600 MB of RAM. I'd suggest:"

    6,6 * 250 = 1650, of course this is assuming all queries will be running at the same time with each buffer used max per connection, which is impossible

    In the next my.cnf, max was lowered to 200, and per connection buffers also smaller, so it will use it worse case scenario (like the one above which is almost impossible) 920 MB


    So you can leave those values as I wrote,
    BillyS suggestions are purely theoretical calculations, in practise it works a bit different, and depends on load, queries and more

    You can post slow.txt and new mysqltuner
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello :)

    Remember to try to let MySQL run at least 24 hours before each use of the MySQL tuner script to allow for the most accurate results.

    Thank you.
     
  13. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Yes I will post tomorrow.

    I am trying to figure out which my.cnf settings (2GB or 4GB) to use though. It seems the load was lower on the 2GB one.

    My server only has a total of 4GB RAM. The swap is being used now and is very low.
    3921500k is memory and about 3515656k pretty consistently used.

    Would the 2GB settings be better? Or should I consider getting more ram?
     
  14. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Use last one I presented, and post mysqltuner later and/or slow log
     
  15. BillyS

    BillyS Active Member

    Joined:
    Mar 22, 2013
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    You're correct, I did take the time to actually calculate the values. You're also correct, I did look at load and ask if the 14 was normal.

    From the last restart time, you had a maximum of 14 concurrent connections being served. Based on his practical experience, thinkbot thinks the number should be closer to 200, not the 100 I suggested.
     
  16. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    I have used the 2GB settings for my.cnf.

    As of this morning the mysqltuner says:
    I have the slow.txt file but it shows all the databases of all my clients.
    When you say "info on slow queries" what am I looking for?
     
  17. BillyS

    BillyS Active Member

    Joined:
    Mar 22, 2013
    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    You have a lot of tables (over 4,000), and there are a lot of query cache prunes per day. I would suggest the following. See if increasing the query cache size lowers the number of prunes... best to keep watch over time.

    Code:
    table_definition_cache=4096
    table_open_cache=6000
    query_cache_size=64M
     
  18. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    Ok thanks! I will post the changes this weekend.
     
  19. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    We had a harddrive failure so could not post.

    I went back to msqld listings below in my.cnf for now.

    In the mysql error log I see these. How do I fix this?

    I see these in the horde database:

    I ran the check table, repair table, analyze table, and optimize table and everything says it's ok but not sure.

     
    #19 modom, Jun 21, 2013
    Last edited: Jun 21, 2013
  20. modom

    modom Well-Known Member

    Joined:
    May 18, 2002
    Messages:
    74
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Arkansas
    My server specs are:
    Two Intel Xeon 5310 "Clovertown" Quad Core processors (8-core)
    One 250 GB SATA hard drive
    4 GB Fully Buffered DDR2 RAM
    Supermicro Motherboard

    The slow query log for the one store is attached.

    Here's the information from last night.

    This is the my.cnf file I have now since a change earlier this morning. It has been running for about 5 hours.


    The mysqltuner from 8am 6/22/13 is:

    I had set the query_cache_size to 64 which did bring the prunes down and last night the mysqltuner said to increase it so I increased it to 84M. Is this too large?

    The load still shoots up over 20 when the one store, nobbieneezkids, posts a sale and lots of people come all at once but I would think this server could handle that. This was one reason I set the timeout to 10 instead of 30.

    Thanks so much.
     

    Attached Files:

Loading...

Share This Page