 Slow site during peak times... help! [message #1948] |
Wed, 26 September 2007 03:38  |
 DDJCS
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
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-locking
skip-bdb
log-bin
key_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
sort_buffer_size = 6M
myisam_sort_buffer_size = 32M
tmp_table_size = 32M
table_cache = 1024
thread_cache_size = 128
thread_concurrency = 16
max_allowed_packet = 16M
max_connect_errors = 10
max_connections = 800
max_user_connections = 800
connect_timeout = 10
interactive_timeout = 30
wait_timeout = 30
server_id = 1
long_query_time = 2
log_slow_queries = /var/log/mysqld.slow.log
#InnoDB settings
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size = 1800M
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 30
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
[safe_mysqld]
open_files_limit = 8192
err-log = /var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_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 1806
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 371629
Bytes_received 493
Bytes_sent 7930
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 1
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 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 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 2
Com_set_option 4
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 1
Com_show_collations 1
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 1
Com_show_errors 0
Com_show_fields 0
Com_show_grants 1
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 2
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_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Variable_name Value
Com_unlock_tables 0
Com_update 0
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 6126
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 6
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 172
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 299
Innodb_buffer_pool_pages_data 5285
Innodb_buffer_pool_pages_dirty 96
Innodb_buffer_pool_pages_flushed 225639
Innodb_buffer_pool_pages_free 109573
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 342
Innodb_buffer_pool_pages_total 115200
Innodb_buffer_pool_read_ahead_rnd 7
Innodb_buffer_pool_read_ahead_seq 42
Innodb_buffer_pool_read_requests 1480925552
Innodb_buffer_pool_reads 3896
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 4158714
Innodb_data_fsyncs 43869
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 83382272
Innodb_data_reads 4319
Innodb_data_writes 195833
Innodb_data_written 3377671680
Innodb_dblwr_pages_written 225639
Innodb_dblwr_writes 4985
Innodb_log_waits 0
Innodb_log_write_requests 713163
Innodb_log_writes 31431
Innodb_os_log_fsyncs 33894
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 277638656
Innodb_page_size 16384
Innodb_pages_created 329
Innodb_pages_read 4956
Innodb_pages_written 225639
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 607
Innodb_row_lock_time_avg 17
Innodb_row_lock_time_max 208
Innodb_row_lock_waits 35
Innodb_rows_deleted 77092
Innodb_rows_inserted 93587
Innodb_rows_read 476763718
Innodb_rows_updated 192109
Key_blocks_not_flushed 0
Key_blocks_unused 28992
Key_blocks_used 3
Key_read_requests 26434
Key_reads 45
Key_write_requests 2100
Key_writes 42
Last_query_cost 10.499000
Max_used_connections 572
Ndb_cluster_node_id 0
Ndb_config_from_host
Ndb_config_from_port 0
Ndb_number_of_data_nodes 0
Not_flushed_delayed_rows 0
Open_files 20
Open_streams 0
Open_tables 196
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 0
Qcache_free_memory 0
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Variable_name Value
Qcache_not_cached 0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Questions 17392379
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 6
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 18202418
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 47
Threads_connected 525
Threads_created 1284
Threads_running 1
Uptime 25531
Uptime_since_flush_status 25531
Here's my "show variables"
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set_client utf8
character_set_connection utf8
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
collation_connection utf8_unicode_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
completion_type 0
concurrent_insert 1
connect_timeout 10
datadir /var/lib/mysql/
date_format %Y-%m-%d
datetime_format %Y-%m-%d %H:%i:%s
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
div_precision_increment 4
engine_condition_pushdown OFF
expire_logs_days 0
flush OFF
flush_time 0
ft_boolean_syntax + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (built-in)
group_concat_max_len 1024
have_archive YES
have_bdb NO
have_blackhole_engine YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading NO
have_example_engine YES
have_federated_engine YES
have_geometry YES
have_innodb YES
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl NO
have_ssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 20971520
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 1887436800
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:100M:autoextend
innodb_data_home_dir /var/lib/mysql/
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 0
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 30
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 16777216
innodb_log_file_size 536870912
innodb_log_files_in_group 2
innodb_log_group_home_dir ./
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 16
innodb_thread_sleep_delay 10000
interactive_timeout 30
join_buffer_size 2093056
Variable_name Value
key_buffer_size 33554432
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/share/mysql/english/
large_files_support ON
large_page_size 0
large_pages OFF
lc_time_names en_US
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin ON
log_bin_trust_function_creators OFF
log_error
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries ON
log_warnings 1
long_query_time 2
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16776192
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 800
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 18446744073709551615
max_length_for_sort_data 1024
max_prepared_stmt_count 16382
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_sp_recursion_depth 0
max_tmp_tables 32
max_user_connections 800
max_write_lock_count 4294967295
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 33554432
myisam_stats_method nulls_unequal
ndb_autoincrement_prefetch_sz 32
ndb_force_send ON
ndb_use_exact_count ON
ndb_use_transactions ON
ndb_cache_check_time 0
ndb_connectstring
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 4000
optimizer_prune_level 1
optimizer_search_depth 62
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 2093056
read_only OFF
read_rnd_buffer_size 3141632
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
server_id 1
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer_size 6291448
sql_big_selects ON
Variable_name Value
sql_mode
sql_notes ON
sql_warnings OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine MyISAM
sync_binlog 0
sync_frm ON
system_time_zone CEST
table_cache 1024
table_lock_wait_timeout 50
table_type MyISAM
thread_cache_size 128
thread_stack 126976
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.45-community-log
version_comment MySQL Community Edition (GPL)
version_compile_machine i686
version_compile_os pc-linux-gnu
wait_timeout 30
Thank you very much to anyone who can help me!
--
Best Regards,
Marco
[Updated on: Wed, 26 September 2007 09:47]
|
|
|
|
| Re: Slow site during peak times... help! [message #1954 is a reply to message #1951 ] |
Thu, 27 September 2007 20:49  |
|
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.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
| Re: Slow site during peak times... help! [message #1957 is a reply to message #1954 ] |
Fri, 28 September 2007 02:42 |
DDJCS
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
Yes I'm using xcache right now for php caching.
About persistent connections, what problems are you talking about? I used "normal" connections until a month ago then I switched to persistent connections because I read that they improve performances on sites which have many simultaneous connections (the mysql connection in fact doesn't need to be opened and closed everytime and this is much faster). Anyways, thank you for replying me!
[Updated on: Fri, 28 September 2007 02:44]
|
|
|
| Re: Slow site during peak times... help! [message #1977 is a reply to message #1948 ] |
Mon, 01 October 2007 03:19 |
mikec
Messages: 22
Registered: September 2007 |
Junior Member |
|
|
"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.
[Updated on: Mon, 01 October 2007 03:24]
|
|
|
|
|
|
| Re: Slow site during peak times... help! [message #1989 is a reply to message #1948 ] |
Tue, 02 October 2007 22:30 |
mikec
Messages: 22
Registered: September 2007 |
Junior Member |
|
|
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.
[Updated on: Tue, 02 October 2007 22:34]
|
|
|
| Re: Slow site during peak times... help! [message #1990 is a reply to message #1948 ] |
Thu, 04 October 2007 05:38 |
DDJCS
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
|
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...
[Updated on: Thu, 04 October 2007 05:39]
|
|
|
|
| Re: Slow site during peak times... help! [message #2081 is a reply to message #1987 ] |
Wed, 17 October 2007 10:12 |
DDJCS
Messages: 7
Registered: September 2007 |
Junior Member |
|
|
| sterin wrote on Tue, 02 October 2007 05:31 |
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 USAGE
Max Memory Ever Allocated : 12 G
Configured Max Per-thread Buffers : 14 G
Configured Max Global Buffers : 5 G
Configured Max Memory Limit : 20 G
Total System Memory : 9.73 G
Max 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-locking
skip-bdb
log-bin
key_buffer_size = 32M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 6M
myisam_sort_buffer_size = 32M
tmp_table_size = 32M
table_cache = 1024
thread_cache_size = 128
thread_concurrency = 16
max_allowed_packet = 16M
max_connect_errors = 10
max_connections = 1200
max_user_connections = 1200
connect_timeout = 10
interactive_timeout = 30
wait_timeout = 30
server_id = 1
long_query_time = 2
#log_queries_not_using_indexes = 1
log_slow_queries = /var/log/mysqld.slow.log
#InnoDB settings
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
innodb_buffer_pool_size = 6000M
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 30
innodb_log_files_in_group = 2
innodb_log_file_size = 1000M
innodb_log_buffer_size = 16M
[safe_mysqld]
open_files_limit = 8192
err-log = /var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_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!
[Updated on: Wed, 17 October 2007 12:21]
|
|
|
|
|
| Re: Slow site during peak times... help! [message #2089 is a reply to message #1948 ] |
Wed, 17 October 2007 17:41 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
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.
|
|
|
Members
Search
Help
Register
Login
Home
