Slow site during peak times... help!

Hello, I run a chat/community site which has grown a lot lately and now, during peak times, there are about 400 simultaneous users. Good? I think so but this is also where the problems begin: during peak times in fact the site’s response time is pretty slow and this is becoming really a big issue (the site is slow to navigate and it’s slow to send/receive chat messages ( ).

My current configuration is a linux Server (CentOS 4) with Xeon CPUs (dual dual cores, 8 CPUs 2,66ghz), 8gb of RAM, Apache 1.3.39, PHP 5.2.4 and MySQL 5.0.45. The PHP code is very optimized and so are all of my queries (I optimized every single query using the EXPLAIN command and now they are fast and efficient, no filesort, no temp tables, etc.). The CPU usage during peak times is also very low (around 35%) and the RAM seems to be ok.

I’m pretty sure that my problems lie in some MySQL parameters bad set and I hope that some “mysql guru” here can help me to figure out the problems… (

Being a chat community, I’m using InnoDB tables for almost all of my tables since my site does a terrific use of insert/updates/deletes and every seconds there are hundreds of them. I also use persistent connections as I heard they help a lot if you have thousands of mysql connections per second. The site runs fine until there are about 300 simultaneous users then it starts to slow down a lot when it grows more (370+)!

Here’s my current my.cnf file. Please note that I disabled the “query cache” because I noticed that it slowed down things even more since it was caching thousands of similar queries (almost all of my queries are dynamic and I read that query caching is not helpful in these cases but can be a big bottleneck…):

[mysqld]skip-lockingskip-bdblog-binkey_buffer_size = 32Mjoin_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 3Msort_buffer_size = 6Mmyisam_sort_buffer_size = 32Mtmp_table_size = 32Mtable_cache = 1024thread_cache_size = 128thread_concurrency = 16max_allowed_packet = 16Mmax_connect_errors = 10max_connections = 800max_user_connections = 800connect_timeout = 10interactive_timeout = 30wait_timeout = 30server_id = 1long_query_time = 2log_slow_queries = /var/log/mysqld.slow.log#InnoDB settingsinnodb_data_home_dir = /var/lib/mysql/innodb_data_file_path = ibdata1:100M:autoextendinnodb_buffer_pool_size = 1800Minnodb_additional_mem_pool_size = 20Minnodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 0innodb_lock_wait_timeout = 30innodb_log_files_in_group = 2innodb_log_file_size = 512Minnodb_log_buffer_size = 16M[safe_mysqld]open_files_limit = 8192err-log = /var/log/mysqld.log[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[isamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M[myisamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M[mysqlhotcopy]interactive-timeout

I re-started the mysql service 7 hours ago. What I can notice right now is that phpMyAdmin is showing me in red color the following lines (and I suppose that “red” means “bad”, isn’t it?):

  • Innodb_buffer_pool_pages_dirty 89
  • Innodb_buffer_pool_reads 3,896
  • Innodb_row_lock_time_avg 17
  • Innodb_row_lock_time_max 208
  • Innodb_row_lock_waits 34
  • Handler_read_rnd 165 k
  • Handler_read_rnd_next 7,112 k
  • Opened_tables 219

Here’s my “show status”

Aborted_clients 1806Aborted_connects 0Binlog_cache_disk_use 0Binlog_cache_use 371629Bytes_received 493Bytes_sent 7930Com_admin_commands 0Com_alter_db 0Com_alter_table 0Com_analyze 0Com_backup_table 0Com_begin 0Com_call_procedure 0Com_change_db 1Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_function 0Com_create_index 0Com_create_table 0Com_create_user 0Com_dealloc_sql 0Com_delete 0Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_function 0Com_drop_index 0Com_drop_table 0Com_drop_user 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 0Com_insert_select 0Com_kill 0Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 0Com_replace 0Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_savepoint 0Com_select 2Com_set_option 4Com_show_binlog_events 0Com_show_binlogs 0Com_show_charsets 1Com_show_collations 1Com_show_column_types 0Com_show_create_db 0Com_show_create_table 0Com_show_databases 1Com_show_errors 0Com_show_fields 0Com_show_grants 1Com_show_innodb_status 0Com_show_keys 0Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 0Com_show_privileges 0Com_show_processlist 0Com_show_slave_hosts 0Com_show_slave_status 0Com_show_status 1Com_show_storage_engines 0Com_show_tables 0Com_show_triggers 0Com_show_variables 2Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_fetch 0Com_stmt_prepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Variable_name ValueCom_unlock_tables 0Com_update 0Com_update_multi 0Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 6126Created_tmp_disk_tables 0Created_tmp_files 5Created_tmp_tables 6Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Handler_commit 0Handler_delete 0Handler_discover 0Handler_prepare 0Handler_read_first 0Handler_read_key 0Handler_read_next 0Handler_read_prev 0Handler_read_rnd 0Handler_read_rnd_next 172Handler_rollback 0Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 0Handler_write 299Innodb_buffer_pool_pages_data 5285Innodb_buffer_pool_pages_dirty 96Innodb_buffer_pool_pages_flushed 225639Innodb_buffer_pool_pages_free 109573Innodb_buffer_pool_pages_latched 0Innodb_buffer_pool_pages_misc 342Innodb_buffer_pool_pages_total 115200Innodb_buffer_pool_read_ahead_rnd 7Innodb_buffer_pool_read_ahead_seq 42Innodb_buffer_pool_read_requests 1480925552Innodb_buffer_pool_reads 3896Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 4158714Innodb_data_fsyncs 43869Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 0Innodb_data_pending_writes 0Innodb_data_read 83382272Innodb_data_reads 4319Innodb_data_writes 195833Innodb_data_written 3377671680Innodb_dblwr_pages_written 225639Innodb_dblwr_writes 4985Innodb_log_waits 0Innodb_log_write_requests 713163Innodb_log_writes 31431Innodb_os_log_fsyncs 33894Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 277638656Innodb_page_size 16384Innodb_pages_created 329Innodb_pages_read 4956Innodb_pages_written 225639Innodb_row_lock_current_waits 0Innodb_row_lock_time 607Innodb_row_lock_time_avg 17Innodb_row_lock_time_max 208Innodb_row_lock_waits 35Innodb_rows_deleted 77092Innodb_rows_inserted 93587Innodb_rows_read 476763718Innodb_rows_updated 192109Key_blocks_not_flushed 0Key_blocks_unused 28992Key_blocks_used 3Key_read_requests 26434Key_reads 45Key_write_requests 2100Key_writes 42Last_query_cost 10.499000Max_used_connections 572Ndb_cluster_node_id 0Ndb_config_from_host Ndb_config_from_port 0Ndb_number_of_data_nodes 0Not_flushed_delayed_rows 0Open_files 20Open_streams 0Open_tables 196Opened_tables 0Prepared_stmt_count 0Qcache_free_blocks 0Qcache_free_memory 0Qcache_hits 0Qcache_inserts 0Qcache_lowmem_prunes 0Variable_name ValueQcache_not_cached 0Qcache_queries_in_cache 0Qcache_total_blocks 0Questions 17392379Rpl_status NULLSelect_full_join 0Select_full_range_join 0Select_range 0Select_range_check 0Select_scan 6Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 0Sort_merge_passes 0Sort_range 0Sort_rows 0Sort_scan 0Table_locks_immediate 18202418Table_locks_waited 0Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 47Threads_connected 525Threads_created 1284Threads_running 1Uptime 25531Uptime_since_flush_status 25531

Here’s my “show variables”

auto_increment_increment 1auto_increment_offset 1automatic_sp_privileges ONback_log 50basedir /binlog_cache_size 32768bulk_insert_buffer_size 8388608character_set_client utf8character_set_connection utf8character_set_database latin1character_set_filesystem binarycharacter_set_results utf8character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection utf8_unicode_cicollation_database latin1_swedish_cicollation_server latin1_swedish_cicompletion_type 0concurrent_insert 1connect_timeout 10datadir /var/lib/mysql/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4engine_condition_pushdown OFFexpire_logs_days 0flush OFFflush_time 0ft_boolean_syntax + -><()~*:“”&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 1024have_archive YEShave_bdb NOhave_blackhole_engine YEShave_compress YEShave_crypt YEShave_csv YEShave_dynamic_loading NOhave_example_engine YEShave_federated_engine YEShave_geometry YEShave_innodb YEShave_isam NOhave_merge_engine YEShave_ndbcluster DISABLEDhave_openssl NOhave_ssl NOhave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YESinit_connect init_file init_slave innodb_additional_mem_pool_size 20971520innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 1887436800innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_data_file_path ibdata1:100M:autoextendinnodb_data_home_dir /var/lib/mysql/innodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_io_threads 4innodb_file_per_table OFFinnodb_flush_log_at_trx_commit 0innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 30innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir innodb_log_archive OFFinnodb_log_buffer_size 16777216innodb_log_file_size 536870912innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_rollback_on_timeout OFFinnodb_support_xa ONinnodb_sync_spin_loops 20innodb_table_locks ONinnodb_thread_concurrency 16innodb_thread_sleep_delay 10000interactive_timeout 30join_buffer_size 2093056Variable_name Valuekey_buffer_size 33554432key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlarge_page_size 0large_pages OFFlc_time_names en_USlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin ONlog_bin_trust_function_creators OFFlog_error log_queries_not_using_indexes OFFlog_slave_updates OFFlog_slow_queries ONlog_warnings 1long_query_time 2low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 16776192max_binlog_cache_size 4294967295max_binlog_size 1073741824max_connect_errors 10max_connections 800max_delayed_threads 20max_error_count 64max_heap_table_size 16777216max_insert_delayed_threads 20max_join_size 18446744073709551615max_length_for_sort_data 1024max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 4294967295max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 800max_write_lock_count 4294967295multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 2147483647myisam_recover_options OFFmyisam_repair_threads 1myisam_sort_buffer_size 33554432myisam_stats_method nulls_unequalndb_autoincrement_prefetch_sz 32ndb_force_send ONndb_use_exact_count ONndb_use_transactions ONndb_cache_check_time 0ndb_connectstring net_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold_passwords OFFopen_files_limit 4000optimizer_prune_level 1optimizer_search_depth 62port 3306preload_buffer_size 32768profiling OFFprofiling_history_size 15protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 0query_cache_type ONquery_cache_wlock_invalidate OFFquery_prealloc_size 8192range_alloc_block_size 2048read_buffer_size 2093056read_only OFFread_rnd_buffer_size 3141632relay_log_purge ONrelay_log_space_limit 0rpl_recovery_rank 0secure_auth OFFsecure_file_priv server_id 1skip_external_locking ONskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_load_tmpdir /tmp/slave_net_timeout 3600slave_skip_errors OFFslave_transaction_retries 10slow_launch_time 2socket /var/lib/mysql/mysql.socksort_buffer_size 6291448sql_big_selects ONVariable_name Valuesql_mode sql_notes ONsql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAMsync_binlog 0sync_frm ONsystem_time_zone CESTtable_cache 1024table_lock_wait_timeout 50table_type MyISAMthread_cache_size 128thread_stack 126976time_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 33554432tmpdir /tmp/transaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READupdatable_views_with_limit YESversion 5.0.45-community-logversion_comment MySQL Community Edition (GPL)version_compile_machine i686version_compile_os pc-linux-gnuwait_timeout 30

Thank you very much to anyone who can help me!


Best Regards,
Marco

… anyone? (

  1. Do you use some PHP opcode cache (like APC, eAccelerator, xcaxche)?

  2. Try to move away from permanent connections because whey could cause described problems when you have too many simultaneous connections.

“every seconds there are hundreds of them”

need to do an iostat -xn 3 (options may be different on your os)
and pay attention to %b %w, asvc_t and actv

%b = how busy the disk is
%w = how many waits
asvc_t = average service time
actv = average io’s waiting

%w should be very small less than 1-3% or so
%b shouldn’t be large less than about 60% or so
asvc_t should be less than 10 milliseconds
actv should be less than 5 or so

If you are doing hundreds of them a second, chances are the disk
is overloaded. The best remedy is to fix the application
so it isn’t doin so much DML.

Run iostat during peak time and
also run top to make sure you aren’t swapping.

First of all thank you for replying me!

Well… what do you mean by saying “fix the application”? Unfortunately I can’t do much to fix it or optimize it further:( It’s just the typology of my site (a community site with hundreds of chat messages every minute, photo galleries, ecc.) which makes it so heavy. A community site needs a lot of resources (apache / php / mysql) and the queries are a lot because they need to be a lot. I’ve already done a lot of optimizations removing some not needed queries and/or speeding up them! Do you think any hardware upgrade (i.e. buying a second server to use exclusively for MySQL) could help me? What would you upgrade in my Server to gain more performance? Thank you very much!

As posted before, run iostat or top during peak time to find out more if your problem is CPU or I/O.

Bit since you wrote somewhere that you had about 30% cpu load which should indicate that the problem is I/O I’m going to assume that that is the problem.

And if it is IO then you basically have these two options:
1.
Increase cache
I noticed that you have 8GB RAM but are only using 1800MB for InnoDB and my guess is that you are using a 32bit mysql version.
Change to a 64bit version and increase the innodb buffer pool to about 6GB.

Buy faster disks
Depending on what kind of disks you have now look at trying to buy even faster ones.

But if you need to do large things like this anyway, then aim for buying and installing a separate new DB server with 64bit platform with more RAM than now, install a 64bit OS and 64bit version of mysql.

That way you can perform all these installations without any downtime for your site.
Then when the time is write, you bring down your current webserver and mysql server, copy the db over to the new server.
Fire the new server up.
Change the php login settings to use the new servers ip instead of localhost and start the website again.
Leaving the old server to only handle the webserver/php and the new server handling all DB.

Hi,

First I’d do what Sterin suggests…

If this isn’t enough, I’d look at upgrading to an external RAID
storage array dedicated to the db server alone.

Something like this:

http://www.dell.com/content/products/productdetails.aspx/pva ul_md3000?c=us&l=en&s=bsd&cs=04

or the Apple XServe RAID.

Basically you want write cache and lots of spindles like these
products offer so that writes happen very quickly. You must
also think about a UPS with these products and turn
off write caching on the disks to preserve data integrity.

Switch from internal disk to a RAID array and you will be
amazed at the performance improvement.

Thank you all! I bought and I’m going to install another dedicated Server just for the DB (mysql 5) to balance the load. In this way I hope to get a good speed improvement…

How many DB queries do you have per second?
What is your avg. page generation time?

What you can do:

  • Create a mysql replication to other server and read from slave server
  • Implement full page caching for X seconds (when it is possible)
[B]sterin wrote on Tue, 02 October 2007 05:31[/B]

But if you need to do large things like this anyway, then aim for buying and installing a separate new DB server with 64bit platform with more RAM than now, install a 64bit OS and 64bit version of mysql.

Here we go, as suggested by sterin I bought a second dedicated Server (a dual core machine with 8gb of RAM, faster hard disks, etc.) and I installed on it Centos and MySQL, both 64bit versions. I changed some variables in the “my.cnf” file following the suggestions that you people gave me (i.e. innodb_buffer_pool_size = 6000M) and I must say that the performance boost has been really great and the site is now pretty fast! )

I have only a few doubts though… Using the “tuning-primer.sh” tool to check if my “my.cnf” variables were correct (I read good things about this tool so I decided to give it a try) I’ve noticed something wrong in the “Memory usage” section… here it is:

MEMORY USAGEMax Memory Ever Allocated : 12 GConfigured Max Per-thread Buffers : 14 GConfigured Max Global Buffers : 5 GConfigured Max Memory Limit : 20 GTotal System Memory : 9.73 GMax memory limit exceeds 85% of total system memory

Hmmm… is maybe a value of 6000M for “innodb_buffer_pool_size” too much? I don’t know but according to that tool it looks like I’m using too much mysql memory (even if, I repeat, everything seems to run great). Here’s the current “my.cnf” file which I’m using on the new dedicated DB Server:

[mysqld]skip-lockingskip-bdblog-binkey_buffer_size = 32Mjoin_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 2Msort_buffer_size = 6Mmyisam_sort_buffer_size = 32Mtmp_table_size = 32Mtable_cache = 1024thread_cache_size = 128thread_concurrency = 16max_allowed_packet = 16Mmax_connect_errors = 10max_connections = 1200max_user_connections = 1200connect_timeout = 10interactive_timeout = 30wait_timeout = 30server_id = 1long_query_time = 2#log_queries_not_using_indexes = 1log_slow_queries = /var/log/mysqld.slow.log#InnoDB settingsinnodb_data_home_dir = /var/lib/mysql/innodb_data_file_path = ibdata1:100M:autoextendinnodb_buffer_pool_size = 6000Minnodb_additional_mem_pool_size = 20Minnodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 0innodb_lock_wait_timeout = 30innodb_log_files_in_group = 2innodb_log_file_size = 1000Minnodb_log_buffer_size = 16M[safe_mysqld]open_files_limit = 8192err-log = /var/log/mysqld.log[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[isamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M[myisamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M[mysqlhotcopy]interactive-timeout

Do you guys suggest me to change anything in the “my.cnf” file (I have only one MyISAM table, a very small one. Everything else is InnoDB)? Thank you very much for any help you can give!

The reason you get that high figures from tuning-primer is because you allow a maximum of 1200 connections and some parameters affect on a per thread level.
Paramters like:
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 6M
etc.

And if you take these and multiply them with 1200 you get a very high figure.

But since this is the worst case scenario which most probably will never happen it is still pretty safe to run with these settings although tuning-primer is complaining.

So if you see that during normal working periods the server is not swapping then the settings are fine.

Thanks sterin for the answers! Another thing: do you suggest me to use persistent connections? I have hundreds and hundreds of mysql connections per second and googling around I read that persistent connections are recommended in some situations and above all when you have an external DB Server (I have no clue why…). I know that persistent connections are “memory eaters” but maybe in my case hundreds of mysql connections are going to eat memory even more… or not? What do you think?

Persistent connections is recommended because you avoid the overhead to set up a new connection.

But at the same time MySQL is very fast compared to other DBMS to set up a new connection. Which means that you very often can run mysql applications where you perform new connections all the time.

And that said a lot of people that try to use persistent connections has had problems with it. Where the connections on the mysql server is piling up so they need to set a short timeout value to kill the stale connections so that they don’t block new connections with queries.