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.

VPS Server slowdown - mySQL problem

Discussion in 'Workarounds and Optimization' started by moowalker, Dec 28, 2012.

  1. moowalker

    moowalker Registered

    Dec 27, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator

    I am experiencing a problem with mySQL on my VPS and I would like some help. I am experiencing major server slowdowns, load average can go up to 30, when a mySQL operation is performed. I have tried to tweak the setting for mySQL but with no result.

    VPS Spec:
    RAM: 1GB (2GB Burst)

    Server version: 5.1.66-cll


    cat /etc/my.cnf
    max_allowed_packet = 1M
    table_open_cache = 300
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 3M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    mysqltuner output

    ./ mysqltuner
    System MySQL monitoring stats - 0.4.2
    compiled by George Liu (eva2000)
    Report Generated:
    Fri Dec 28 17:22:31 EET 2012
    mysqltuner output
    ------------------------------------------------- [found]
     >>  MySQLTuner 1.2.1 mod - Major Hayden <>
     >>  Bug reports, feature requests, and downloads at
     >>  Modified by George Liu (eva2000) at
     >>  Run with '--help' for additional options and output filtering
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.66-cll
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 321M (Tables: 768)
    [--] Data in InnoDB tables: 7M (Tables: 86)
    [!!] Total fragmented tables: 99
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 6h 12m 57s (6M q [59.249 qps], 35K conn, TX: 4B, RX: 1B)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 346.0M global + 8.2M per thread (300 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.7G (136% of installed RAM)
    [OK] Slow queries: 0% (188/6M)
    [OK] Highest usage of available connections: 30% (90/300)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/292.7M
    [OK] Key buffer hit rate: 100.0% (38B cached / 343K reads)
    [OK] Query cache efficiency: 86.9% (5M cached / 5M selects)
    [!!] Query cache prunes per day: 91159
    [OK] Sorts requiring temporary tables: 1% (168 temp sorts / 13K sorts)
    [OK] Temporary tables created on disk: 0% (2K on disk / 287K total)
    [OK] Thread cache hit rate: 98% (407 created / 35K connections)
    [!!] Table cache hit rate: 2% (300 open / 14K opened)
    [OK] Open file limit used: 33% (499/1K)
    [OK] Table locks acquired immediately: 98% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 8.0M/8.0M
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
      *** Please note this recommendation is not entirely accurate.
          The formula used to calculate max memory usage assumes all queries utilise
          all memory buffers simultaneously. When in reality it is very rare for a
          query to engage & utilise all memory buffers simultaneously.
          Formula also assumes all predefined max_connections are reached.
          You could have set max_connections = 1000 and in a whole year
          of usage never hit beyond 50 max_used_connections. So your real MySQL
          memory usage is only 1/20th of theorectical max memory usage reported.
          So real max memory usage will never reach this peak.
          So do not be too concerned with this warning.
          It is better to monitor your real MySQL max_used_connection and MySQL
          memory usage over time and adjust accordingly.
          You can use tools such as Cacti, Munin or at to
          monitor your MySQL memory usage over time.
          MySQL performs optimally when its required amount of memory is met.
          Reducing and starving MySQL memory allocation to adhere to this
          artificial max memory warning - of which in reality will never be reached,
          will only reduce MySQL performance in many cases ***
        query_cache_size (> 64M)
        table_cache (> 300, table_open_cache hit rate <20%)
    Report Complete:
    Fri Dec 28 17:22:32 EET 2012
    Dont hesitate to ask my any info I can provide.

    Thank you in advance,
  2. LaceHost-Ishan

    LaceHost-Ishan Active Member

    Dec 6, 2008
    Likes Received:
    Trophy Points:
    Pune, India
    cPanel Access Level:
    DataCenter Provider
    [OK] Highest usage of available connections: 30% (90/300)

    As you are using <90 connections , lower max_connections to 125 or so to reduce MySQL memory usage.

    Also, disable mysql logging in my.cnf . It is good to find the slow queries but once you know them , you can disable the log.
  3. moowalker

    moowalker Registered

    Dec 27, 2012
    Likes Received:
    Trophy Points:
    cPanel Access Level:
    Root Administrator

    I just wanted to report that my problem was solved. The problem was opencart and some heavy (and wrong) querys.

    Thank you

Share This Page