Frequent large load spikes

[Mouth]

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.

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

 

[Mouth]

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?

 

[Walter]

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

 

[craigiri]

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.

 

[Mouth]

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

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

[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)

 

[Mouth]

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.

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

 

[craigiri]

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....

[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

 

[Walter]

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

 

[Mouth]

Thank-you, appreciate the feedback.

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

 

[dono]

Thank-you, appreciate the feedback.

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

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