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

Frequent large load spikes

Discussion in 'Server Configuration and Hosting' started by Mouth, Nov 11, 2012.

  1. Mouth

    Mouth Well-Known Member

    Hi,

    I migrated to XF ~10 days ago, and am getting frequent large load spikes (anywhere between 12 and 40) up to several times a day, but with no apparent fixed time each day. They also do not appear to be related to times when the site would have the most visitors.

    I'm on a dedicated quad-core with 4Gb RAM. Nginx on Debian, with php5-fpm and memcache. Outside these spikes, performance is good with load status sitting between 0.1 and 0.5. XF page load times are fast.

    I was not getting such spikes prior to XF migration, and due to the frequent but inconsistent nature of them I'm thinking cron jobs running as part of XF.

    My server error log is showing an issue with memcache and elastic search (see below), but I don't believe them to be the cause, but more a symptom of the high peak load?

    What is the recommended/suggested method to use for tracking this issue down?

    Thanks.

    Code:
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    54 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    54 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Failed to uncompress data
    54 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    55 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    55 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    55 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    55 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    56 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    Elasticsearch server returned no response. Is it running?
    56 minutes ago - library/XenES/Search/SourceHandler/ElasticSearch.php:721
     
    MemcachePool::get(): Failed to uncompress data
    57 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    Elasticsearch server returned no response. Is it running?
    57 minutes ago - library/XenES/Search/SourceHandler/ElasticSearch.php:721
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    57 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    58 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    59 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    59 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Failed to unserialize data
    59 minutes ago - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    Today at 9:32 AM - library/Zend/Cache/Backend/Memcached.php:180
     
    Elasticsearch server returned no response. Is it running?
    Today at 9:32 AM - library/XenES/Search/SourceHandler/ElasticSearch.php:721
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    Today at 9:30 AM - library/Zend/Cache/Backend/Memcached.php:180
     
    MemcachePool::get(): Server localhost (tcp 11211, udp 0) failed with: Network timeout (0)
    Today at 9:29 AM - library/Zend/Cache/Backend/Memcached.php:180
     
  2. Mouth

    Mouth Well-Known Member

    Bump!
    No thoughts or advice on where to start? No way to log XF cron tasks to see when they start and finish, and if system load is high during?
     
  3. Walter

    Walter Well-Known Member

    Have you activated Innodb cache in your Mysql-config file? Post your my.cnf.
     
  4. craigiri

    craigiri Well-Known Member

    Slavik, one of the mods here, has written some good threads on making sure ES is working properly. From those errors - this could be one problem....or, you can PM him and for a very reasonable sum he will do all the config.
     
  5. Mouth

    Mouth Well-Known Member

    Yes. Here's my modifications from standard/packaged, within conf.d/

    Code:
    [mysqld]
    bind-address            = 127.0.0.1
    default-storage-engine  = InnoDB
     
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links          = 0
     
    # When tuning a MySQL server, the two most important variables to configure
    # are key_buffer_size and table_open_cache. You should first feel confident
    # that you have these set appropriately before trying to change any other
    # variables.
    key_buffer_size        = 768M
    table_open_cache        = 1280
    table_definition_cache  = 768
    key_buffer              = 256M
    sort_buffer_size        = 2M
    read_buffer_size        = 2M
    read_rnd_buffer_size    = 2M
    thread_cache_size      = 16
    table_cache            = 1536
     
    query_cache_type        = 1
    query_cache_size        = 64M
    query_cache_limit      = 4M
    query_prealloc_size    = 65K
    query_alloc_block_size  = 64K
     
    max_connect_errors      = 10
    max_connections        = 150
    max_user_connections    = 100
    max_heap_table_size    = 96M
     
    #wait_timeout          = 15
    connect_timeout        = 8
    thread_concurrency      = 2
    interactive_timeout    = 20
    join_buffer            = 1M
    join_buffer_size        = 1M
    tmp_table_size          = 96M
    transaction_isolation  = READ-COMMITTED
    bulk_insert_buffer_size = 8M
    max_allowed_packet      = 128M
     
    tmpdir                  = /var/tmp
     
    myisam_repair_threads = 1
    myisam_recover
     
    innodb_additional_mem_pool_size = 16M
    # Set buffer pool size to 50-80% of your computer’s memory
    innodb_buffer_pool_size = 1280M
    innodb_file_per_table = 1
    innodb_thread_concurrency = 0
    innodb_flush_log_at_trx_commit = 2
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 2M
    innodb_log_file_size = 128M
    innodb_log_files_in_group = 2
    innodb_max_dirty_pages_pct = 80
    innodb_open_files = 2000
     
    [mysqld_safe]
    open_files_limit        = 8192
     
    [isamchk]
    key_buffer              = 128M
    sort_buffer_size        = 128M
    read_buffer            = 16M
    write_buffer            = 16M
     
    [mysqldump]
    quick
    max_allowed_packet = 32M
     
    [mysql]
    no-auto-rehash
     
    [mysqlhotcopy]
    interactive-timeout

    >> MySQLTuner 1.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.28-1~dotdeb.0
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 409)
    [--] Data in InnoDB tables: 2G (Tables: 436)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 9M (Tables: 13)
    [!!] Total fragmented tables: 63

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 12h 15m 21s (6M q [49.461 qps], 263K conn, TX: 101B, RX: 1B)
    [--] Reads / Writes: 60% / 40%
    [--] Total buffers: 1.7G global + 7.2M per thread (150 max threads)
    [OK] Maximum possible memory usage: 2.7G (70% of installed RAM)
    [OK] Slow queries: 0% (866/6M)
    [OK] Highest usage of available connections: 38% (57/150)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/563.7M
    [OK] Key buffer hit rate: 97.4% (43K cached / 1K reads)
    [OK] Query cache efficiency: 73.6% (3M cached / 4M selects)
    [!!] Query cache prunes per day: 61728
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 360K sorts)
    [!!] Temporary tables created on disk: 27% (15K on disk / 57K total)
    [OK] Thread cache hit rate: 99% (728 created / 263K connections)
    [OK] Table cache hit rate: 23% (1K open / 6K opened)
    [OK] Open file limit used: 12% (1K/8K)
    [OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
    [!!] InnoDB data size / buffer pool: 2.0G/1.2G

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    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 (> 64M)
    tmp_table_size (> 96M)
    max_heap_table_size (> 96M)
    innodb_buffer_pool_size (>= 2G)
     
  6. Mouth

    Mouth Well-Known Member

    Thanks, I'll take a look and/or contact him(her)
     
  7. craigiri

    craigiri Well-Known Member

    I think some of your mysql values may be too high - I have 8 G RAM and have my innodb buffer set lower than yours - because you need room for Elasticsearch and httpd and all the other stuff too....

    Not much harm in setting that a bit lower....unless you are going to add RAM. I have mine at 900M and it works like a dream with a pretty large forum and heavy traffic.

    Walter is more of an expert on this stuff...but it seems to me you may be running out of RAM and buffers. Mysql isn't too bad at going to disk, so no need to buffer and cache everything...

    I don't know why your max_allowed_packet needs to be so large. Mine is 1M and I don't see any problems.

    My key buffer size is 1/3 of yours.......

    I think you may have a combo problem - you have to make sure ES is working properly and memcache too - or turn memcache off....it shouldn't make much difference if your load is that small (for now). Then lower the memory profile of mysql and ES so that together they are only using maybe 60% of your RAM, leaving the other 40% for httpd, unix buffering, etc....

    Does your "top" command show RAM available when these spikes are happening?

    Here are the basics of my cnf file - again, I'm using 8G RAM as well as ES....
    Code:
    [mysqld]
    port        = 3306
    socket        = /var/lib/mysql/mysql.sock
    skip-locking
    key_buffer =256M
    max_allowed_packet = 1M
    table_cache = 700
    max_connections = 350
    sort_buffer_size = 4M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 64M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
     
    skip-networking
     
    # Disable Federated by default
    skip-federated
     
     
    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /var/lib/mysql/
    #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    #innodb_log_group_home_dir = /var/lib/mysql/
    #innodb_log_arch_dir = /var/lib/mysql/
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 900M
    innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 100M
    #innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 2
    innodb_lock_wait_timeout = 100
     
    [mysqldump]
    quick
    max_allowed_packet = 16M
     
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
     
    [isamchk]
    key_buffer 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M
     
    [myisamchk]
    key_buffer = 128M
    sort_buffer_size = 128M
    read_buffer = 2M
    write_buffer = 2M
     
  8. Walter

    Walter Well-Known Member

    The key sentence is here:
    [OK] Maximum possible memory usage: 2.7G (70% of installed RAM)
    And thats only for Mysql!

    The scripts probably says "OK" because it assumes you are using your server as a database only server. If you have a Webserver, Elasticsearch and the database its obviously not ok. And you need some RAM for Linux too :)
    Its very likely that your server goes out of memory when the site becomes busy, so try reducing the Inndob buffer
     
  9. Mouth

    Mouth Well-Known Member

    Thank-you, appreciate the feedback.

    I will halve the innodb_buffer_pool_size down to 768M and see how performance continues
     
  10. dono

    dono Member


    is everything fine after that ? I have same problem right now
     

Share This Page