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.

Issue with MySql optimization ?

Discussion in 'Workarounds and Optimization' started by szk, May 30, 2012.

  1. szk

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hello,
    We have issue with MySql db and I'll try to describe what happens
    Our site reaches about 11.000 visits per day and handles 2 MySql data bases,
    hosted on VPS :
    Total processors: 10
    Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
    Total memory: 2GB
    mysql 5.1.56

    Issue that happens is that in MySQL Process List we can see that it gets full with queries that needs more than
    160 secs of time to execute (connections are not "Sleep").
    That would be no problem if it is only one or couple of connections, but when that happen connections only accumulate and MySql stops responding.
    Sometimes it needs 2 - 5 minutes to clear and resolve queries and sometimes it needs 30 - 60 minutes.
    Usually we resolve this issue by restarting MySql service, but occasionally happens that restarting is not solving this issue because the same minute that MySql starts it gets full of processes again.
    Interesting thing is that this issue is not happening when we have visitors peek, we use google analytics and wecan see that, when we have 200 visitors at the same time, everything runs smoothly, also we do not use permanent connections, and everything on the site is cashed for 60 minutes.
    Here is our my.cnf

    [mysqld]
    thread_concurrency=4
    max_connections=200
    max_user_connections=10
    key_buffer=256M
    myisam_sort_buffer_size=64M
    join_buffer_size=2M
    read_buffer_size=1M
    sort_buffer_size=1M
    table_cache=2500
    thread_cache_size=128
    interactive_timeout=45
    wait_timeout=20
    connect_timeout=8
    max_allowed_packet=16M
    max_connect_errors=10
    query_cache_limit=1M
    query_cache_size=64M
    query_cache_type=1
    flush
    flush_time=60
    table_definition_cache = 512
    max_heap_table_size = 32M
    tmp_table_size = 32M

    [mysql.server]
    user=mysql

    [mysqld_safe]
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout

    We appreciate any help and idea what might be wrong
    Thank You
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Have you checked the MySQL error log at /var/lib/mysql/hostname.err location where hostname is the fully qualified hostname of the machine (returned by the "hostname -f" command without the quotes in command line)?

    For the existing /etc/my.cnf file, you'd want to remove the invalid entries and revise for the MySQL version you have:

    I've also commented out flush and flush_time:

    http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_flush

    I have no idea why you are using these in /etc/my.cnf file.
     
  3. szk

    szk Member

    Joined:
    May 30, 2012
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Thank You for the instructions, we have applied changes to config as You wrote.
    For now everything works and runs smoothly. In couple of days when we finish tests, we'll update with results.
    Greetings and thanks again
     
Loading...

Share This Page