Many users in 'sleep' mode

[FFXIOnline]

My website, ffxionline has been running quickly since last night, when I figured out how to change the mysql max users, I upped it to 400 and now its fast. However, its very weird, and I believe it has something to do with MySQL. Before, during peak hours (200-300 users at once) the website was just slow, very very slow. Since I did the max user trick the website acts weirdly, going very fast than slow, than very fast and slow. This pattern goes on.. I have no idea what is causing this. I am running vBulletin 2.2.9.
This is the deal with the 'sleeping' users.. we have many many users like this, how do we resolve?

http://www.ffxionline.com/chat/thread-problem.jpg

Also,
// use persistant connections to the database
// 0 = don't use
// 1 = use
$usepconnect = 0;
is set.


Thank you,

FFXIOnline

[eva2000]

can you post the info asked at http://www.vbulletin.com/forum/showthread.php?t=70117

thanks :)

[FFXIOnline]

Okay,

- Dedicated server
- CPU: P4 2.0GHz
- RAM: 1024MB [ 1GB ]
- HDD: SCSI 60GB
- Redhat: 7.3
- Apache v: 1.3
- PHP v: 4.2.2
- MySQL: 4.0.16
- mysqladmin -u root -p variables:
MySQL Variables
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /var/lib/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | ./ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | OFF |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 400 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2010 |
| pid_file | /var/run/mysqld/mysqld.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 157286400 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | CST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.16-standard |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- mysqladmin -u root -p extended-status:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 99 |
| Aborted_connects | 21 |
| Bytes_received | 3500184677 |
| Bytes_sent | 4210873237 |
| Com_admin_commands | 0 |
| Com_alter_table | 93 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 421547 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 44 |
| Com_delete | 32702 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 46 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 234362 |
| Com_insert_select | 75621 |
| Com_kill | 4 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 45 |
| Com_optimize | 2 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 191008 |
| Com_replace_select | 2021 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 3541008 |
| Com_set_option | 96 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 97 |
| Com_show_databases | 223 |
| Com_show_fields | 259 |
| Com_show_grants | 0 |
| Com_show_keys | 130 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 90021 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 396 |
| Com_show_variables | 228 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 43 |
| Com_update | 1950882 |
| Connections | 421455 |
| Created_tmp_disk_tables | 16 |
| Created_tmp_tables | 151339 |
| Created_tmp_files | 1 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 64073 |
| Handler_read_first | 467308 |
| Handler_read_key | 32798325 |
| Handler_read_next | 2136948330 |
| Handler_read_prev | 2590024 |
| Handler_read_rnd | 6961573 |
| Handler_read_rnd_next | 1394811155 |
| Handler_rollback | 2 |
| Handler_update | 6002656 |
| Handler_write | 18488046 |
| Key_blocks_used | 7793 |
| Key_read_requests | 748788580 |
| Key_reads | 1990930 |
| Key_write_requests | 4663360 |
| Key_writes | 3661852 |
| Max_used_connections | 167 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 87 |
| Open_streams | 0 |
| Opened_tables | 667503 |
| Questions | 14199261 |
| Qcache_queries_in_cache | 14315 |
| Qcache_inserts | 3162392 |
| Qcache_hits | 7237001 |
| Qcache_lowmem_prunes | 70640 |
| Qcache_not_cached | 404252 |
| Qcache_free_memory | 130652856 |
| Qcache_free_blocks | 9618 |
| Qcache_total_blocks | 38550 |
| Rpl_status | NULL |
| Select_full_join | 16 |
| Select_full_range_join | 0 |
| Select_range | 523045 |
| Select_range_check | 0 |
| Select_scan | 1091518 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 9668 |
| Slow_queries | 50848 |
| Sort_merge_passes | 0 |
| Sort_range | 365679 |
| Sort_rows | 7425613 |
| Sort_scan | 61289 |
| Table_locks_immediate | 6616746 |
| Table_locks_waited | 580472 |
| Threads_cached | 0 |
| Threads_created | 421454 |
| Threads_connected | 8 |
| Threads_running | 6 |
| Uptime | 226916 |
+--------------------------+------------+
- vB is the only main thing on the server, all the other websites get less than 50 hits per day
- vB has about 200-300 connections off-peak and we hit 400 or so high peak time last week
- phpInfo file - http://www.cogn.net/~dan/phpinfo.php
- KeepAlive: ON
- MaxKeepAliveRequests: 100
- KeepAliveTimeout: 15
- MinSpareServers: 5
- MaxSpareServers: 10
- StartServers: 5
- MaxClients: 150
- vB: 2.2.9


That should be ALL of the info.

-FFXIOnline

[HossMon]

Ok it would help if you posted the contents of your my.cnf file. but as my server is almost identical to your's and my board runs about 300 people at peak here's what I've found works great.

And Eva if you're reading this, I'll post more about it later, but I had an epiphany of sorts and found a solution to my problem of too many SQL Processes running all the time. You have to set the thread_cache_size variable lower!

It works like this....
thread_cache_size + default # Sql process's (about 7 on my server) ='s Max # of Mysql processes currently running

With my thread_cache_size set at 64 the number of Mysql processes currently running will top out at about 70 and STAY there. Vastly reducing my memory and better yet my server load. It even helped lower my swap file size.

But back to this guy's ?

Try this my.cnf you may need to tweak a few settings but it should get you close

##########################
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
set-variable = max_connections=300
set-variable = key_buffer=24M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=64
set-variable = wait_timeout=300
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
set-variable = query_cache_limit=1M
set-variable = query_cache_size=24M
set-variable = query_cache_type=1
skip-innodb
log-slow-queries = /var/lib/mysql/slow_query.log

[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
open_files_limit=8192
pid-file=/var/run/mysqld/(location and name of YOUR .pid file)

[mysqldump]
quick
set-variable = max_allowed_packet=32M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[mysqlhotcopy]
interactive-timeout
#############################

Now I've found that few of the query's running or sleeping take longer then 60 sec's to close/finish so a wait_timeout=300 works well for me. You may want to change that for your server

You can also try turning up the thread_cache_size=
to 80 or even 128 later on if you like, but I'd try 64 to start with.

***And remember anytime you change your my.cnf file you neeed to stop & restart Mysql do they'll take affect

/etc/rc.d/init.d/mysql stop
/etc/rc.d/init.d/mysql start

or use the mysqld_safe command
______________________________________________


And these settings in httpd.conf

################################

Timeout 300

KeepAlive On

MaxKeepAliveRequests 100

KeepAliveTimeout 10

MinSpareServers 10
MaxSpareServers 20

StartServers 60

MaxRequestsPerChild 1000

MaxClients 180

(*note - I've found 150 to be too low for my loads)

######################################

One more quick note....
If your server is running WHM/cpanel and you made the upgrade form mysql v3.x.xx to v4.x.xx and never ran this command ...
mysql_fix_privilege_tables

You WILL need to do so.
Read this thread
http://www.vbulletin.com/forum/showthread.php?t=69070


Good Luck!

[eva2000]

FFXIOnline sorry for the delay having ADSL internet access/ISP outages and dropouts still :(

i'd try this new /etc/my.cnf to replace your existing one... if you don't have one create a new /etc/my.cnf file and place this in and restart mysql

[mysqld]
max_connections = 550
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[FFXIOnline]

I did just replaced my file. I'll see if it fixes the problem. The query_cache_size seems a little small, doesn't it? Anyways, I will get back to you if this resolves the problem.

-FFXIOnline.com

[FFXIOnline]

Problem fixed. Yay, but now how high can I turn up that query cache to? [Server specs posted above] What about the other query settings?

-lieb39