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.

High Server Load -- Would Appreciate Any Help Please

Discussion in 'Workarounds and Optimization' started by GasMan320, Sep 30, 2013.

  1. GasMan320

    GasMan320 Member

    Joined:
    Oct 2, 2012
    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi,

    Thank you for reading. My server is having lots of issues with MySQL and although I am very good at following directions, I am not quite sure what needs to be done.

    I went ahead and ran mysqltuner.pl and this was the output:

    Code:
    >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
     
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.30-30.2
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 4G (Tables: 1928)
    [--] Data in InnoDB tables: 16M (Tables: 108)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 31)
    [!!] Total fragmented tables: 155
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 7d 3h 0m 45s (68M q [110.800 qps], 3M conn, TX: 1160B, RX: 17B)
    [--] Reads / Writes: 83% / 17%
    [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
    [OK] Maximum possible memory usage: 583.2M (9% of installed RAM)
    [OK] Slow queries: 0% (9K/68M)
    [OK] Highest usage of available connections: 60% (91/151)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/1.1G
    [OK] Key buffer hit rate: 98.1% (10B cached / 196M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (165K temp sorts / 1B sorts)
    [!!] Joins performed without indexes: 14052
    [OK] Temporary tables created on disk: 19% (324K on disk / 1M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (400 open / 401K opened)
    [OK] Open file limit used: 5% (698/12K)
    [OK] Table locks acquired immediately: 99% (84M immediate / 84M locks)
    [OK] InnoDB data size / buffer pool: 16.3M/128.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        thread_cache_size (start at 4)
        table_cache (> 400)
    When I run "top" I see mysqld process jumping from using around 15 to 20% CPU usage all the way up to 250% CPU usage. I am on a dedicated box with the following specs:

    4 quad core CPUs - Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
    6gb memory
    raid drives
    httpd 2.2.24 (Unix)
    mysql 5.0.96


    Here is my /etc/my.cnf file contents:

    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /home/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default_storage_engine         = InnoDB
    socket                         = /home/mysql/mysql.sock
    pid_file                       = /home/mysql/mysql.pid
    
    # MyISAM #
    #key_buffer_size                = 1G
    
    
    # SAFETY #
    
    skip_name_resolve
    innodb                         = FORCE
    
    # DATA STORAGE #
    datadir                        = /home/mysql/
    
    
    # INNODB #
    
    
    # LOGGING #
    
    open_files_limit=12256
    

    Also, I ran MySQL Tuning Primer and the results are as follows:

    Code:
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.30-30.2 x86_64
    
    Uptime = 7 days 2 hrs 32 min 53 sec
    Avg. qps = 110
    Total Questions = 68011600
    Threads Connected = 3
    
    Server has been running for over 48hrs.
    It should be safe to follow these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 9446 out of 68011621 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 0
    Current threads_cached = 0
    Current threads_per_sec = 2
    Historic threads_per_sec = 5
    Threads created per/sec are overrunning threads cached
    You should raise thread_cache_size
    
    MAX CONNECTIONS
    Current max_connections = 151
    Current threads_connected = 3
    Historic max_used_connections = 91
    The number of used connections is 60% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 21 M
    Current InnoDB data space = 16 M
    Current InnoDB buffer pool free = 68 %
    Current innodb_buffer_pool_size = 128 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 402 M
    Configured Max Per-thread Buffers : 415 M
    Configured Max Global Buffers : 152 M
    Configured Max Memory Limit : 567 M
    Physical Memory : 5.81 G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    Current MyISAM index space = 1.14 G
    Current key_buffer_size = 8 M
    Key cache miss rate is 1 : 53
    Key buffer free ratio = 67 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is supported but not enabled
    Perhaps you should set the query_cache_size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 132.00 K
    You have had 14024 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 12256 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 400 tables
    Current table_definition_cache = 400 tables
    You have a total of 2108 tables
    You have 400 open tables.
    Current table_cache hit rate is 0%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 1346151 temp tables, 19% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 8364 : 1
    You have a high ratio of sequential access requests to SELECTs
    You may benefit from raising read_buffer_size and/or improving your use of indexes.
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 192
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=ALWAYS'.
    

    Would love any assistance any of you could provide. Thank you very much!
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    uncomment this
    #key_buffer_size = 1G

    to
    key_buffer_size = 1200M

    and add under it
    max_connections = 100
    query_cache_type = 1
    query_cache_size = 50M
    query_cache_limit = 1M
    thread_cache_size = 30
    table_cache = 2048
    join_buffer_size=1M
    read_rnd_buffer_size=1M

    then restart
     
Loading...

Share This Page