I'm running a Ubuntu 14 server with LAMP on virtual cloud hosting (CityCloud).
The server is usually doing very well with response time<0.5s. However, the problems start at peak hours, mainly Monday-Wednesday/Thursday, with response times at around 7-8 seconds or more. These times were more or less halved by passing to worker MPM and php-fpm, thanks to @drew010 over at stackoverflow.
Now, I would like to see if there are any last tweaks I could do to get rid of those remaining 3s of avg load time during busy hours.
According to Google Analytics: The website has got some ~5 million page views per month, and during peak hour some 12-13.000 thousand page views per hour.
Here's my setup:
- Ubuntu 14.04.3 LTS
- PHP Version 5.5.9, FPM/FastCGI
- 4 CPU cores
- 8 RAM
- Limited to 1 Gbps bandwidth and 1 TB transfer/month
- < 6% CPU usage during peak hours
- Memory used < 2GB
- ~2M txbps during peak hours
- Static content is served from a separate subdomain (same machine) with expires headers "access plus 1 month".
- MySQL running on the same machine.
- MySQL queries have been measured to be fast and not an issue.
Obviously, 4 cores/8 RAM is excessive. My intentions are to fall back to at most 2 cores/4 RAM, if performance is good.
This is a netstat output during peak hour:
$ netstat -ant | awk '{print $6}' | sort | uniq -c | sort -n 3 CLOSING 5 LISTEN 8 FIN_WAIT1 69 LAST_ACK 80 SYN_RECV 117 ESTABLISHED 147 FIN_WAIT2 1028 TIME_WAIT
Some server-info:
In file: /etc/apache2/mods-enabled/mpm_worker.conf 13: StartServers 2 14: MinSpareThreads 25 15: MaxSpareThreads 75 16: ThreadLimit 64 17: ThreadsPerChild 25 18: MaxRequestWorkers 150
Some kernel option values:
/proc/sys/net/core/somaxconn: 128 /proc/sys/net/core/netdev_max_backlog: 1000 /proc/sys/net/ipv4/tcp_max_syn_backlog: 256 /proc/sys/net/ipv4/ip_local_port_range: 32768 61000
MySQL "SHOW GLOBAL STATUS":
Aborted_clients 7 Aborted_connects 14 Binlog_cache_disk_use 0 Binlog_cache_use 0 Binlog_stmt_cache_disk_use 0 Binlog_stmt_cache_use 0 Bytes_received 7324492238 Bytes_sent 33143229342 Com_admin_commands 0 Com_assign_to_keycache 0 Com_alter_db 0 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 2 Com_alter_tablespace 0 Com_analyze 0 Com_begin 0 Com_binlog 0 Com_call_procedure 0 Com_change_db 1277679 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_event 0 Com_create_function 0 Com_create_index 0 Com_create_procedure 0 Com_create_server 0 Com_create_table 0 Com_create_trigger 0 Com_create_udf 0 Com_create_user 0 Com_create_view 0 Com_dealloc_sql 0 Com_delete 19 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_event 0 Com_drop_function 0 Com_drop_index 0 Com_drop_procedure 0 Com_drop_server 0 Com_drop_table 0 Com_drop_trigger 0 Com_drop_user 0 Com_drop_view 0 Com_empty_query 0 Com_execute_sql 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 1301 Com_insert_select 0 Com_install_plugin 0 Com_kill 0 Com_load 0 Com_lock_tables 18 Com_optimize 1 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_release_savepoint 0 Com_rename_table 0 Com_rename_user 0 Com_repair 0 Com_replace 7 Com_replace_select 0 Com_reset 0 Com_resignal 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_rollback_to_savepoint 0 Com_savepoint 0 Com_select 8939751 Com_set_option 2555998 Com_signal 0 Com_show_authors 0 Com_show_binlog_events 0 Com_show_binlogs 94 Com_show_charsets 0 Com_show_collations 0 Com_show_contributors 0 Com_show_create_db 18 Com_show_create_event 0 Com_show_create_func 0 Com_show_create_proc 0 Com_show_create_table 356 Com_show_create_trigger 0 Com_show_databases 28 Com_show_engine_logs 0 Com_show_engine_mutex 0 Com_show_engine_status 0 Com_show_events 5 Com_show_errors 0 Com_show_fields 381 Com_show_function_status 5 Com_show_grants 5 Com_show_keys 40 Com_show_master_status 43 Com_show_open_tables 0 Com_show_plugins 0 Com_show_privileges 0 Com_show_procedure_status 5 Com_show_processlist 1099 Com_show_profile 0 Com_show_profiles 0 Com_show_relaylog_events 0 Com_show_slave_hosts 0 Com_show_slave_status 1118 Com_show_status 1120 Com_show_storage_engines 0 Com_show_table_status 366 Com_show_tables 77 Com_show_triggers 324 Com_show_variables 1135 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_uninstall_plugin 0 Com_unlock_tables 18 Com_update 41612 Com_update_multi 0 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 1277451 Created_tmp_disk_tables 731 Created_tmp_files 9 Created_tmp_tables 3602 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 284 Handler_delete 19 Handler_discover 0 Handler_prepare 0 Handler_read_first 485 Handler_read_key 7814753 Handler_read_last 48790 Handler_read_next 55898210 Handler_read_prev 457247 Handler_read_rnd 223213 Handler_read_rnd_next 4231962515 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 40888 Handler_write 377375 Innodb_buffer_pool_pages_data 202 Innodb_buffer_pool_bytes_data 3309568 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_bytes_dirty 0 Innodb_buffer_pool_pages_flushed 37 Innodb_buffer_pool_pages_free 7989 Innodb_buffer_pool_pages_misc 0 Innodb_buffer_pool_pages_total 8191 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_ahead 0 Innodb_buffer_pool_read_ahead_evicted 0 Innodb_buffer_pool_read_requests 2817 Innodb_buffer_pool_reads 196 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 86 Innodb_data_fsyncs 39 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 5394432 Innodb_data_reads 206 Innodb_data_writes 67 Innodb_data_written 1235456 Innodb_dblwr_pages_written 37 Innodb_dblwr_writes 9 Innodb_have_atomic_builtins ON Innodb_log_waits 0 Innodb_log_write_requests 35 Innodb_log_writes 10 Innodb_os_log_fsyncs 21 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 17408 Innodb_page_size 16384 Innodb_pages_created 7 Innodb_pages_read 195 Innodb_pages_written 37 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 0 Innodb_rows_inserted 0 Innodb_rows_read 59 Innodb_rows_updated 7 Innodb_truncated_status_writes 0 Key_blocks_not_flushed 0 Key_blocks_unused 10770 Key_blocks_used 2626 Key_read_requests 167086090 Key_reads 2430 Key_write_requests 376494 Key_writes 303134 Last_query_cost 0 Max_used_connections 8 Not_flushed_delayed_rows 0 Open_files 141 Open_streams 0 Open_table_definitions 73 Open_tables 137 Opened_files 5062 Opened_table_definitions 80 Opened_tables 150 Performance_schema_cond_classes_lost 0 Performance_schema_cond_instances_lost 0 Performance_schema_file_classes_lost 0 Performance_schema_file_handles_lost 0 Performance_schema_file_instances_lost 0 Performance_schema_locker_lost 0 Performance_schema_mutex_classes_lost 0 Performance_schema_mutex_instances_lost 0 Performance_schema_rwlock_classes_lost 0 Performance_schema_rwlock_instances_lost 0 Performance_schema_table_handles_lost 0 Performance_schema_table_instances_lost 0 Performance_schema_thread_classes_lost 0 Performance_schema_thread_instances_lost 0 Prepared_stmt_count 0 Qcache_free_blocks 2242 Qcache_free_memory 7441152 Qcache_hits 68078488 Qcache_inserts 8868017 Qcache_lowmem_prunes 4451717 Qcache_not_cached 71091 Qcache_queries_in_cache 6797 Qcache_total_blocks 16056 Queries 82178544 Questions 82178543 Rpl_status AUTH_MASTER Select_full_join 0 Select_full_range_join 0 Select_range 2041371 Select_range_check 0 Select_scan 1151738 Slave_heartbeat_period 0 Slave_open_temp_tables 0 Slave_received_heartbeats 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 0 Sort_merge_passes 5 Sort_range 878229 Sort_rows 236531 Sort_scan 869 Ssl_accept_renegotiates 0 Ssl_accepts 0 Ssl_callback_cache_hits 0 Ssl_cipher Ssl_cipher_list Ssl_client_connects 0 Ssl_connect_renegotiates 0 Ssl_ctx_verify_depth 0 Ssl_ctx_verify_mode 0 Ssl_default_timeout 0 Ssl_finished_accepts 0 Ssl_finished_connects 0 Ssl_session_cache_hits 0 Ssl_session_cache_misses 0 Ssl_session_cache_mode NONE Ssl_session_cache_overflows 0 Ssl_session_cache_size 0 Ssl_session_cache_timeouts 0 Ssl_sessions_reused 0 Ssl_used_session_cache_entries 0 Ssl_verify_depth 0 Ssl_verify_mode 0 Ssl_version Table_locks_immediate 9116984 Table_locks_waited 178 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 6 Threads_connected 2 Threads_created 8 Threads_running 1 Uptime 423398 Uptime_since_flush_status 423398
iostat
executed during peak hour:
$ iostat -xdk 1 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 4.00 0.00 4.00 4.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 2.00 0.00 8.00 8.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 17.00 0.00 96.00 11.29 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 34.00 0.00 3.00 0.00 152.00 101.33 0.00 1.33 0.00 1.33 1.33 0.40 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 12.00 24.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 20.00 0.00 152.00 15.20 0.02 1.20 0.00 1.20 0.60 1.20 vda 0.00 44.00 0.00 2.00 0.00 188.00 188.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 2.00 0.00 8.00 8.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 4.00 0.00 4.00 4.00 0.40 vda 0.00 74.00 0.00 146.00 0.00 908.00 12.44 0.30 2.08 0.00 2.08 0.05 0.80 vda 0.00 46.00 0.00 3.00 0.00 200.00 133.33 0.00 1.33 0.00 1.33 1.33 0.40 vda 0.00 0.00 0.00 4.00 0.00 16.00 8.00 0.00 1.00 0.00 1.00 1.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 20.00 0.00 232.00 23.20 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 41.00 0.00 2.00 0.00 176.00 176.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 17.82 0.00 118.81 13.33 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 42.00 0.00 4.00 0.00 188.00 94.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 12.00 0.00 60.00 10.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 39.00 0.00 2.00 0.00 168.00 168.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 16.00 0.00 76.00 9.50 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 32.00 0.00 2.00 0.00 140.00 140.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 18.00 0.00 100.00 11.11 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 30.00 0.00 2.00 0.00 132.00 132.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 12.00 0.00 48.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 37.00 0.00 3.00 0.00 164.00 109.33 0.01 2.67 0.00 2.67 2.67 0.80 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 48.00 0.00 130.00 0.00 744.00 11.45 0.24 1.88 0.00 1.88 0.06 0.80 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 35.00 0.00 4.00 0.00 160.00 80.00 0.00 1.00 0.00 1.00 1.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 19.00 0.00 172.00 18.11 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 4.00 0.00 4.00 4.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 39.00 0.00 2.00 0.00 168.00 168.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 25.00 0.00 184.00 14.72 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 48.00 0.00 2.00 0.00 204.00 204.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 18.00 0.00 104.00 11.56 0.00 0.22 0.00 0.22 0.22 0.40 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.02 24.00 0.00 24.00 24.00 2.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 39.00 0.00 4.00 0.00 176.00 88.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 1.00 0.00 4.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 17.00 0.00 100.00 11.76 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 38.00 0.00 3.00 0.00 168.00 112.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 2.00 0.00 8.00 8.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 16.00 0.00 100.00 12.50 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 40.00 0.00 2.00 0.00 172.00 172.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 10.00 0.00 64.00 12.80 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 34.00 0.00 2.00 0.00 144.00 144.00 0.00 2.00 0.00 2.00 2.00 0.40 vda 0.00 0.00 0.00 2.00 0.00 12.00 12.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 55.00 0.00 117.00 0.00 732.00 12.51 0.28 2.43 0.00 2.43 0.10 1.20 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Any help is appreciated! :-)
sysstat
and enable thesar
for statistics collection? Also, see withiostat -xdk 1
at the peak period how busy are your block devices. Other thing, post theshow global status
of your mysql server here for analysis, please! Maybe your mysql are not properly tuned so your VFS and block devices may be doing to much work.munin
running since last week, is that enough for the statistics part or do you prefersysstat
?io_wait
on system graphs and how busy are your disks on the storage graphs.show global status
and theiostat -xdk 1
results to the question. As for sysstat/munin, I was going to add some graphs to the question but I need more rep before I can do so... Current values during peak hour atAverage latency
isDevice IO time: 391.32u
,IO Wait time: 3.73m
,Read IO Wait time: 0.00
,Write IO Wait time: 3.73
. Device and Write are stable thru 24h, Read has got sporadic peaks independent of peak hours. IO Wait is not visible on the graph.net.core.somaxconn
: 128 => 1024,net.core.netdev_max_backlog
: 1000 => 65536,net.ipv4.tcp_max_syn_backlog
: 256 => 2048. Also inmpm_worker.conf
,StartServers
3,MinSpareThreads
75,MaxSpareThreads
250,ThreadLimit
64,ThreadsPerChild
25,MaxRequestWorkers
400. Those remaining peaks have now been removed during busy hours. :-) If you add an answer, I will accept it.