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: Possible to Optimize?

Discussion in 'Workarounds and Optimization' started by ChristsFlock, Apr 15, 2013.

  1. ChristsFlock

    ChristsFlock Registered

    Joined:
    Apr 15, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hello,

    I have a server with 4 GB RAM. Our site receives about 8,000-10,000 visits per day, has over 12,000 pages, and the database is about 3-4 GB.

    I don't want to have to upgrade or get a dedicated MySQL server unless I have to. However, MySQL is taking a lot of memory, and the site keeps hanging. The load has been hovering around 1.5-2, and sometimes up to 3 or 4. I've run mysqltuner, and adjusted the settings. It told me to set innodb_buffer_pool_size to 3G, but of course that is most of our memory. Even lower values does not really help, however.

    Here is some informatio about the resources it is using:

    Code:
    USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
    mysql     5950 38.6 52.4 3794756 1877204 pts/1 Sl   12:40  36:48 /usr/sbin/mysql
    Excerpt from top output, ordered by swap:

    Code:
    top - 14:27:32 up 6 days, 16:40,  1 user,  load average: 1.82, 1.91, 1.83
    Tasks: 144 total,   2 running, 142 sleeping,   0 stopped,   0 zombie
    Cpu(s): 34.1%us,  2.2%sy,  0.0%ni, 17.5%id, 46.1%wa,  0.2%hi,  0.0%si,  0.0%st
    Mem:   3579200k total,  3567792k used,    11408k free,    14872k buffers
    Swap:  1959920k total,   917208k used,  1042712k free,   313368k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  SWAP COMMAND      
     5950 mysql     15   0 3706m 1.9g 4868 S 43.9 56.3  39:55.73 1.7g mysqld        
    
    From /etc/my.cnf:

    Code:
    [mysqld]
    max_connections = 25
    max_allowed_packet=64M
    skip-external-locking
    key_buffer = 10M
    open_files_limit=11454
    table_cache = 1536
    sort_buffer_size = 128K
    read_buffer_size = 128K
    read_rnd_buffer_size = 128K
    myisam_sort_buffer_size = 128K
    query_cache_size= 16M
    join_buffer_size = 256K
    max_heap_table_size = 48M
    tmp_table_size = 48M
    wait_timeout = 45
    interactive_timeout = 300
    thread_cache_size = 8
    innodb_buffer_pool_size = 3G
    innodb_thread_concurrency = 4
    
    character-set-server=utf8
    innodb_file_per_table=1
    default-storage-engine=MyISAM
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [mysql]
    no-auto-rehash
    
    [isamchk]
    key_buffer = 10M
    read_buffer = 128K
    write_buffer = 128K
    
    [myisamchk]
    key_buffer = 10M
    sort_buffer_size = 128K
    read_buffer = 128K
    write_buffer = 128K
    
    mysqltuner, though it's not been 24 hours. It isn't stable for that long:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 169M (Tables: 961)
    [--] Data in InnoDB tables: 2G (Tables: 975)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 6)
    [!!] Total fragmented tables: 350
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 50m 27s (6M q [927.872 qps], 966 conn, TX: 2B, RX: 1B)
    [--] Reads / Writes: 67% / 33%
    [--] Total buffers: 3.1G global + 896.0K per thread (25 max threads)
    [!!] Maximum possible memory usage: 3.1G (91% of installed RAM)
    [OK] Slow queries: 0% (7/6M)
    [OK] Highest usage of available connections: 24% (6/25)
    [OK] Key buffer size / total MyISAM indexes: 10.0M/46.2M
    [OK] Key buffer hit rate: 100.0% (13M cached / 3K reads)
    [OK] Query cache efficiency: 34.9% (1M cached / 3M selects)
    [!!] Query cache prunes per day: 12858098
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 295K sorts)
    [!!] Joins performed without indexes: 91
    [OK] Temporary tables created on disk: 0% (254 on disk / 38K total)
    [OK] Thread cache hit rate: 99% (6 created / 966 connections)
    [OK] Table cache hit rate: 43% (363 open / 830 opened)
    [OK] Open file limit used: 0% (33/11K)
    [OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
    [OK] InnoDB data size / buffer pool: 2.3G/3.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 16M)
        join_buffer_size (> 256.0K, or always use indexes with joins)
    
    I don't see any other option besides getting more RAM, but I wanted to check here first. I might just split off MySQL onto its own server.

    Anything I can possibly do?
     
  2. caisc

    caisc Active Member

    Joined:
    Oct 5, 2011
    Messages:
    34
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Get memory on your server upgraded to atleast 8GB then optimize, you are running out of RAM.
     
  3. ChristsFlock

    ChristsFlock Registered

    Joined:
    Apr 15, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks. We ended up getting a new server, and currently a bit over 6 GB RAM is doing the trick.
     
  4. phankhue

    phankhue Registered

    Joined:
    Apr 23, 2013
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    We ended up getting a new server, and currently a bit over 6 GB RAM is doing the trick.

    - - - Updated - - -

    We ended up getting a new server, and currently a bit over 6 GB RAM is doing the trick.
     
Loading...

Share This Page