1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Help optimizing mysql high server load!

Discussion in 'General PHP and MySQL Discussions' started by NeoCHI, Jun 8, 2014.

  1. NeoCHI

    NeoCHI Active Member

    Recently I've noticed that my server load sometimes gets pretty high and will result in "unexpected database error has occurred" and using Chrome inspect shows "already reached max_connections".

    I'm on HostGator's Standard dedicated server. With only one fairly active Xenforo forum, usually around 800-1k users on at any given time.

    Here is my my.cnf settings:
    Code:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    symbolic-links=0
    
    open_files_limit=3072
    query_cache_size=64M
    max_connections=150
    max_user_connections=25
    wait_timeout=20
    tmp_table_size=119M
    max_heap_table_size=119M
    thread_cache_size=64
    key_buffer_size=32M
    max_allowed_packet=16M
    table_cache=768
    table_definition_cache=3072
    
    myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
    
    collation_server=utf8_unicode_ci
    character_set_server=utf8
    
    slow_query_log=1
    log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
    long_query_time=5 # select * from mysql.slow_log order by start_time desc limit 10;
    
    innodb_file_per_table=1
    innodb_flush_method=O_DIRECT
    innodb_buffer_pool_size=119M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
    innodb_additional_mem_pool_size=29M
    innodb_log_buffer_size=29M
    innodb_thread_concurrency=8 # Number of physical + virtual CPU's, preset when server is provisioned to have correct # of cores
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    
    Running mysql tuner gives:
    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 348M (Tables: 26)
    [--] Data in InnoDB tables: 1G (Tables: 361)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 8M (Tables: 8)
    [!!] Total fragmented tables: 65
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 22h 7m 18s (19M q [241.694 qps], 1M conn, TX: 862B, RX: 5B)
    [--] Reads / Writes: 69% / 31%
    [--] Total buffers: 392.0M global + 2.8M per thread (150 max threads)
    [OK] Maximum possible memory usage: 804.5M (21% of installed RAM)
    [OK] Slow queries: 0% (1K/19M)
    [OK] Highest usage of available connections: 52% (78/150)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/317.4M
    [OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
    [OK] Query cache efficiency: 71.3% (10M cached / 14M selects)
    [!!] Query cache prunes per day: 85252
    [OK] Sorts requiring temporary tables: 0% (761 temp sorts / 445K sorts)
    [!!] Joins performed without indexes: 177065
    [!!] Temporary tables created on disk: 47% (190K on disk / 398K total)
    [OK] Thread cache hit rate: 99% (78 created / 1M connections)
    [OK] Table cache hit rate: 47% (768 open / 1K opened)
    [OK] Open file limit used: 4% (126/3K)
    [OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
    [!!] Connections aborted: 6%
    [!!] InnoDB  buffer pool / data size: 119.0M/1.8G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 119M)
        max_heap_table_size (> 119M)
        innodb_buffer_pool_size (>= 1G)
    
     
  2. Sheratan

    Sheratan Well-Known Member

    How much your free RAM? In normal and peak time.
     
  3. MattW

    MattW Well-Known Member

    You haven't hit the max number of connections, you've only used half according to that output.

    You also need to look at increasing your innodb_buffer_pool_size . You want to look at setting that value to 2G based on the below output

    InnoDB buffer pool / data size: 119.0M/1.8G

    I guess you have 4GB of RAM on the VPS?
     
  4. NeoCHI

    NeoCHI Active Member

    Hmm I might have gotten that error message wrong. Looking at my server logs it says I'm exceeding my
    max_user_connections. This is currently set at 25, what do you guys suggest I set this at considering my server and the number of members I on usually. Btw, how is possible that it was limited at 25 when I often have over 1k members on?

    Normally when I htop it looks like:

    Mem[||||||||||||||||||||896/3830MB]
    Swp[||| 273/4095MB]
     
  5. MattW

    MattW Well-Known Member

    You have loads of free ram left. Adjust the InnoDB buffer pool as the the above, and restart MySQL

    The limits are simultaneous connection at the same time. With 1,000 people online, they aren't all going to access the DB at the same time.
     
    Luke F likes this.
  6. NeoCHI

    NeoCHI Active Member

    What's the difference between max_connections and max_user_connections?
     
  7. MattW

    MattW Well-Known Member

  8. NeoCHI

    NeoCHI Active Member

    I have max_user_connections set at 100 now, is that a good number based on my server specs?

    Also, in my mysql tuner recommendations:

    Code:
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
    Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 669 (#1)
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 10h 25m 36s (32M q [260.404 qps], 1M conn, TX: 1190B, RX: 9B)
    [--] Reads / Writes: 70% / 30%
    [--] Total buffers: 2.3G global + 2.8M per thread (150 max threads)
    [OK] Maximum possible memory usage: 2.7G (72% of installed RAM)
    [OK] Slow queries: 0% (1K/32M)
    [OK] Highest usage of available connections: 65% (98/150)
    [!!] Cannot calculate MyISAM index size - re-run script as root user
    [OK] Query cache efficiency: 71.1% (17M cached / 24M selects)
    [!!] Query cache prunes per day: 11597
    [OK] Sorts requiring temporary tables: 0% (183 temp sorts / 820K sorts)
    [!!] Joins performed without indexes: 306245
    [!!] Temporary tables created on disk: 48% (380K on disk / 790K total)
    [OK] Thread cache hit rate: 99% (342 created / 1M connections)
    [OK] Table cache hit rate: 40% (764 open / 1K opened)
    [OK] Open file limit used: 4% (138/3K)
    [OK] Table locks acquired immediately: 99% (18M immediate / 19M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Add skip-innodb to MySQL configuration to disable InnoDB
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 200M)
        max_heap_table_size (> 200M)
    
    It mentions "Adjust your join queries to always utilize indexes" along with "join_buffer_size (> 128.0K, or always use indexes with joins)". What does that mean exactly?
     
  9. MattW

    MattW Well-Known Member

    Can you run the script as the root MySQL user, as you are missing settings from the most recent output?
     
  10. NeoCHI

    NeoCHI Active Member

    That last run was with root. I have noticed the second time I run more settings show up though. Here is the output the second time I run it but it's done with mysql only running for couple minutes since I just upped tmp_table_size and max_heap_size to 384M, max_user_connection to 100 and restarted.

    Code:
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 404M (Tables: 26)
    [--] Data in InnoDB tables: 1G (Tables: 361)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 621K (Tables: 8)
    [!!] Total fragmented tables: 65
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 13m 4s (192K q [245.298 qps], 9K conn, TX: 7B, RX: 58M)
    [--] Reads / Writes: 70% / 30%
    [--] Total buffers: 2.5G global + 2.8M per thread (150 max threads)
    [OK] Maximum possible memory usage: 2.9G (76% of installed RAM)
    [OK] Slow queries: 0% (3/192K)
    [OK] Highest usage of available connections: 19% (29/150)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/338.5M
    [OK] Key buffer hit rate: 97.6% (330K cached / 7K reads)
    [OK] Query cache efficiency: 70.2% (100K cached / 143K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 4K sorts)
    [!!] Joins performed without indexes: 1648
    [!!] Temporary tables created on disk: 48% (2K on disk / 4K total)
    [OK] Thread cache hit rate: 99% (29 created / 9K connections)
    [OK] Table cache hit rate: 98% (355 open / 362 opened)
    [OK] Open file limit used: 1% (40/3K)
    [OK] Table locks acquired immediately: 99% (112K immediate / 112K locks)
    [!!] InnoDB  buffer pool / data size: 1.8G/1.8G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 128.0K, or always use indexes with joins)
        innodb_buffer_pool_size (>= 1G)
    
     
  11. woei

    woei Well-Known Member

    You should run the script after more time so the statistics will be more accurate. But it says you have 65 fragmented tables. You could try to run optimize tables/db?

    But see what this script does after a day. Then the results will be more useful.
     
  12. Solidus

    Solidus Well-Known Member

    thread_stack = 256k
    read_buffer_size = 512k
    join_buffer_size = 512k
    sort_buffer_size = 1M

    add these.
     
    RoldanLT likes this.
  13. NeoCHI

    NeoCHI Active Member

    I've attached a htop screenshot of when my server is going nuts...

    Here's the mysql tuner output:
    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 407M (Tables: 26)
    [--] Data in InnoDB tables: 1G (Tables: 361)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 1M (Tables: 8)
    [!!] Total fragmented tables: 64
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 13h 54m 5s (39M q [291.432 qps], 1M conn, TX: 1421B, RX: 11B)
    [--] Reads / Writes: 71% / 29%
    [--] Total buffers: 2.5G global + 2.8M per thread (150 max threads)
    [OK] Maximum possible memory usage: 2.9G (76% of installed RAM)
    [OK] Slow queries: 0% (5K/39M)
    [!!] Highest connection usage: 100%  (151/150)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/343.5M
    [OK] Key buffer hit rate: 100.0% (5B cached / 2M reads)
    [OK] Query cache efficiency: 68.9% (20M cached / 30M selects)
    [!!] Query cache prunes per day: 10111
    [OK] Sorts requiring temporary tables: 0% (522 temp sorts / 962K sorts)
    [!!] Joins performed without indexes: 362760
    [!!] Temporary tables created on disk: 47% (404K on disk / 851K total)
    [OK] Thread cache hit rate: 99% (732 created / 1M connections)
    [!!] Table cache hit rate: 16% (768 open / 4K opened)
    [OK] Open file limit used: 2% (70/3K)
    [OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
    [!!] InnoDB  buffer pool / data size: 1.8G/1.9G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        Increasing the query_cache size over 128M may reduce performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
    Variables to adjust:
        max_connections (> 150)
        wait_timeout (< 20)
        interactive_timeout (< 28800)
        query_cache_size (> 256M) [see warning above]
        join_buffer_size (> 128.0K, or always use indexes with joins)
        table_cache (> 768)
        innodb_buffer_pool_size (>= 1G)
    
    What's causing this crazy server load? Weird thing is most of the time it's fine.
     

    Attached Files:

  14. NeoCHI

    NeoCHI Active Member

    FYI, whenever this happens, I can usually fix it by restarting mysql but I want to avoid the root cause...
     
  15. Solidus

    Solidus Well-Known Member

    Do you have persistent connections enabled? Disable it.
    Adjust the following values:

    max_connections = 250
    wait_timeout = 10
    interactive_timeout = 10
    query_cache_limit = 2M
    table_cache = 2048
    innodb_buffer_pool_size = 2.5G
    max_heap_table_size = 128M

    and also add these,

    query_cache_type = 1
    tmp_table_size = 128M

    And adjust the values as I suggested above, then restart MySQL.
     
  16. NeoCHI

    NeoCHI Active Member

    How do I check if I have persistent connections enabled?
     
  17. Solidus

    Solidus Well-Known Member

    "KeepAlive" in httpd.conf
     
  18. NeoCHI

    NeoCHI Active Member

    Oh..not the mysql.allow_persistent in php.ini?
     
  19. NeoCHI

    NeoCHI Active Member

    I didn't see KeepAlive set in my httpd.conf. Is it on by default?
     
  20. NeoCHI

    NeoCHI Active Member


    Made the changes and apparently I have KeepAlive off (found it set in my WHM).

    Btw, do I have to worry about:

    Joins performed without indexes: 362760
     

Share This Page