Server Opt Request

[bigsoccer tech]

1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)

Dedicated.


2. your server specs. For example:

4x Web servers: Intel Dual Xeon 2.8 GHz, 2 GB Ram, 2 X 80 GB with Raid 1
2x DB servers: Intel Dual Xeon 2.8 GHz, 8 GB Ram, 3 X 73 GB SCSI U320 10K RPM with Raid 5
2x Load balancers: Dual P4 3.0 ghz, 1gb Ram

Apache/Linux
PHP 4.4.1
MySQL 4.0.24

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

Don't think so.

4. if possible how mysql was compiled/installed

5. your top stats

WEB SERVER 1:

top - 18:00:51 up 14 days, 1:15, 2 users, load average: 0.94, 0.73, 0.62
Tasks: 176 total, 1 running, 174 sleeping, 0 stopped, 1 zombie
Cpu(s): 4.5% us, 0.2% sy, 0.0% ni, 95.1% id, 0.0% wa, 0.1% hi, 0.1% si
Mem: 2074328k total, 1231124k used, 843204k free, 167748k buffers
Swap: 2048276k total, 1232k used, 2047044k free, 409260k cached


6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf so post the contents inside of my.cnf (minus any passwords of course). If on Windows server if you don't have that file you need to log into telnet and as root user type

Variable Session value Global value
back log 100 100
basedir /virtual/mysql/ /virtual/mysql/
binlog cache size 32768 32768
bulk insert buffer size 16777216 16777216
character set latin1 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 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 ON
connect timeout 10 10
convert character set
datadir /usr/local/mysql/data/ /usr/local/mysql/data/
default week format 0 0
delay key write ON ON
delayed insert limit 100 100
delayed insert timeout 300 300
delayed queue size 1000 1000
flush OFF OFF
flush time 0 0
ft boolean syntax + -><()~*:""&| + -><()~*:""&|
ft min word len 4 4
ft max word len 254 254
ft max word len for sort 20 20
ft stopword file (built-in) (built-in)
have bdb NO NO
have crypt YES YES
have innodb DISABLED DISABLED
have isam YES YES
have raid NO NO
have symlink YES YES
have openssl NO NO
have query cache YES YES
init file
innodb additional mem pool size 1048576 1048576
innodb autoextend increment 8 8
innodb buffer pool size 8388608 8388608
innodb data file path
innodb data home dir
innodb file io threads 4 4
innodb force recovery 0 0
innodb thread concurrency 8 8
innodb flush log at trx commit 1 1
innodb fast shutdown ON ON
innodb flush method
innodb lock wait timeout 50 50
innodb log arch dir
innodb log archive OFF OFF
innodb log buffer size 1048576 1048576
innodb log file size 5242880 5242880
innodb log files in group 2 2
innodb log group home dir
innodb mirrored log groups 1 1
innodb max dirty pages pct 90 90
innodb max purge lag 0 0
innodb table locks ON ON
interactive timeout 28800 28800
join buffer size 2093056 2093056
key buffer size 402653184 402653184
language /virtual/mysql/share/mysql/english/ /virtual/mysql/share/mysql/english/
large files support ON ON
license GPL GPL
local infile ON ON
locked in memory OFF OFF
log OFF OFF
log update OFF OFF
log bin ON ON
log slave updates OFF OFF
log slow queries ON ON
log warnings 1 1
long query time 10 10
low priority updates OFF OFF
lower case file system OFF OFF
lower case table names 0 0
max allowed packet 50330624 50330624
max binlog cache size 4294967295 4294967295
max binlog size 1073741824 1073741824
max connections 2048 2048
max connect errors 10 10
max delayed threads 20 20
max insert delayed threads 20 20
max heap table size 16777216 16777216
max join size 4294967295 4294967295
max relay log size 0 0
max seeks for key 4294967295 4294967295
max sort length 1024 1024
max user connections 0 0
max tmp tables 32 32
max write lock count 4294967295 4294967295
myisam max extra sort file size 268435456 268435456
myisam max sort file size 2147483647 2147483647
myisam repair threads 1 1
myisam recover options OFF OFF
myisam sort buffer size 67108864 67108864
net buffer length 16384 16384
net read timeout 30 30
net retry count 10 10
net write timeout 60 60
new OFF OFF
open files limit 10250 10250
pid file /usr/local/mysql/data/hd-a1813cl.pid /usr/local/mysql/data/hd-a1813cl.pid
log error ./hd-a1813cl.err ./hd-a1813cl.err
port 3306 3306
protocol version 10 10
query alloc block size 16384 16384
query cache limit 2097152 2097152
query cache size 268435456 268435456
query cache type ON ON
query cache wlock invalidate OFF OFF
query prealloc size 16384 16384
range alloc block size 2048 2048
read buffer size 2093056 2093056
read only OFF OFF
read rnd buffer size 520192 520192
rpl recovery rank 0 0
server id 1 1
slave net timeout 3600 3600
skip external locking ON ON
skip networking OFF OFF
skip show database OFF OFF
slow launch time 2 2
socket /tmp/mysql.sock /tmp/mysql.sock
sort buffer size 3145720 3145720
sql mode 0 0
table cache 1800 1800
table type MYISAM MYISAM
thread cache size 16 16
thread stack 126976 126976
tx isolation REPEATABLE-READ REPEATABLE-READ
timezone EST EST
tmp table size 268435456 268435456
tmpdir /tmp/ /tmp/
transaction alloc block size 8192 8192
transaction prealloc size 4096 4096
version 4.0.24-standard-log 4.0.24-standard-log
version comment Official MySQL-standard binary Official MySQL-standard binary
version compile os pc-linux-gnu pc-linux-gnu
wait timeout 900 900


7. your mysql extended-status output which shows entire server's mysql activity status (preferably with a day or 2 worth of mysql uptime for better indication of real activity) either:

This MySQL server has been running for 0 days, 1 hours, 38 minutes and 22 seconds. It started up on Jan 18, 2006 at 04:14 PM.

* Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 152,442 KB 92,984 KB
Sent 186,447 KB 113,726 KB
Total 338,888 KB 206,709 KB

Connections ø per hour %
Failed attempts 4,349 2,652.73 3.84 %
Aborted 857 522.74 0.76 %
Total 113,114 68,995.32 100.00 %
* Query statistics: Since its startup, 545,601 queries have been sent to the server.
Total ø per hour ø per minute ø per second
545,601 332,796.27 5,546.60 92.44
Query type ø per hour %
admin commands 2 1.22 0.00 %
alter table 0 0.00 0.00 %
analyze 0 0.00 0.00 %
backup table 0 0.00 0.00 %
begin 0 0.00 0.00 %
change db 107,262 65,425.82 24.80 %
change master 0 0.00 0.00 %
check 0 0.00 0.00 %
commit 0 0.00 0.00 %
create db 0 0.00 0.00 %
create function 0 0.00 0.00 %
create index 0 0.00 0.00 %
create table 0 0.00 0.00 %
delete 2,664 1,624.94 0.62 %
delete multi 0 0.00 0.00 %
drop db 0 0.00 0.00 %
drop function 0 0.00 0.00 %
drop index 0 0.00 0.00 %
drop table 0 0.00 0.00 %
flush 0 0.00 0.00 %
grant 0 0.00 0.00 %
ha close 0 0.00 0.00 %
ha open 0 0.00 0.00 %
ha read 0 0.00 0.00 %
insert 25,065 15,288.72 5.80 %
insert select 0 0.00 0.00 %
kill 0 0.00 0.00 %
load 0 0.00 0.00 %
load master data 0 0.00 0.00 %
load master table 0 0.00 0.00 %
lock tables 0 0.00 0.00 %
optimize 0 0.00 0.00 %
purge 0 0.00 0.00 %
rename table 0 0.00 0.00 %

Query type ø per hour %
repair 0 0.00 0.00 %
replace 3,086 1,882.34 0.71 %
replace select 0 0.00 0.00 %
reset 0 0.00 0.00 %
restore table 0 0.00 0.00 %
revoke 0 0.00 0.00 %
rollback 0 0.00 0.00 %
savepoint 0 0.00 0.00 %
select 84,834 51,745.58 19.62 %
set option 0 0.00 0.00 %
show binlog events 0 0.00 0.00 %
show binlogs 9 5.49 0.00 %
show create 0 0.00 0.00 %
show databases 9 5.49 0.00 %
show fields 6 3.66 0.00 %
show grants 0 0.00 0.00 %
show keys 0 0.00 0.00 %
show logs 0 0.00 0.00 %
show master status 0 0.00 0.00 %
show new master 0 0.00 0.00 %
show open tables 0 0.00 0.00 %
show processlist 12 7.32 0.00 %
show slave hosts 2 1.22 0.00 %
show slave status 0 0.00 0.00 %
show status 3 1.83 0.00 %
show innodb status 0 0.00 0.00 %
show tables 91 55.51 0.02 %
show variables 6 3.66 0.00 %
slave start 0 0.00 0.00 %
slave stop 0 0.00 0.00 %
truncate 0 0.00 0.00 %
unlock tables 0 0.00 0.00 %
update 51,093 31,164.83 11.81 %
update multi 0 0.00 0.00 %
* More status variables
Variable Value
Created tmp disk tables 1584
Created tmp tables 3423
Created tmp files 0
Delayed insert threads 0
Delayed writes 3
Delayed errors 0
Flush commands 1
Handler commit 0
Handler delete 6975
Handler read first 16425
Handler read key 2614656
Handler read next 169960347
Handler read prev 130126
Handler read rnd 951759
Handler read rnd next 6781267
Handler rollback 0
Handler update 53877
Handler write 221140
Key blocks used 200562
Key read requests 22062849

Variable Value
Key reads 198805
Key write requests 65779
Key writes 71241
Max used connections 1019
Not flushed key blocks 0
Not flushed delayed rows 0
Open tables 1800
Open files 1879
Open streams 0
Opened tables 49817
Qcache queries in cache 5559
Qcache inserts 77986
Qcache hits 163394
Qcache lowmem prunes 0
Qcache not cached 6766
Qcache free memory 243425968
Qcache free blocks 1855
Qcache total blocks 13584
Rpl status NULL

Variable Value
Select full join 1005
Select full range join 0
Select range 26842
Select range check 0
Select scan 13804
Slave open temp tables 0
Slave running OFF
Slow launch threads 1726
Slow queries 2354
Sort merge passes 0
Sort range 21522
Sort rows 162722456
Sort scan 8119
Table locks immediate 259693
Table locks waited 17868
Threads cached 4
Threads created 11813
Threads connected 108
Threads running 103

8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

Several VBs, but yes.

9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?

15 minutes, 1200-2000 between the four sites.

10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

www.bigsoccer.com/phpinfo.php

11. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 2048

AND Maxrequestsperchild value = 1500

12. what version of vB are you running ?

3.5.3

13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log


[root@hd-a1809cl root]# find / -size +2000000k
find: /proc/11497/task/11497/fd/4: No such file or directory
[root@hd-a1809cl root]#

[eva2000]

can you post your my.cnf contents on db servers ?

for now 2 changes i can see

1. reduce MaxClients 2048 to 512 and restart apache
2. change thread_cache_size in my.cnf from 16 to 512 and max_allowed_packet increase to 64M and then restart mysql

see how that helps

Also for large forums try to disable these 4 options:

Admin CP -> vBulletin Options -> Server Settings and Optimization Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

[bigsoccer tech]

will get

will do

the first three were off, i have turned off similar therads now

[bigsoccer tech]

can you post your my.cnf contents on db servers ?


Bigsoccer.com (http://bigsoccer.com/) is now running off your new clustered-MySQL reads setup.


[mysqld]
back_log = 100
skip-innodb
skip-name-resolve
skip-host-cache
#max_connections = 2048
max_connections = 1024
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 384
thread_cache = 16
#thread_concurrency = 8
thread_concurrency = 4
wait_timeout = 900
connect_timeout = 10
#tmp_table_size = 256M
tmp_table_size = 512M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
max_allowed_packet = 48M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
log-bin
log-error
log-slow-queries
server-id = 1

[mysqld_safe]
open_files_limit = 16384

[mysqldump]
quick
max_allowed_packet = 32M

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

[eva2000]

if you have innodb disabled do you really need mysql binary log ? or you using replication ? if not you can disable log-bin and try this my.cnf


[mysqld]
safe-show-database
back_log = 100
skip-innodb
skip-name-resolve
skip-host-cache
max_connections = 1024
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 512
thread_concurrency = 4
wait_timeout = 300
connect_timeout = 10
tmp_table_size = 1024M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 6M
query_cache_size = 512M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
#log-bin
#log-error
#log-slow-queries
server-id = 1

[mysqld_safe]
open_files_limit = 16384

[mysqldump]
quick
max_allowed_packet = 32M

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

[mysqlhotcopy]
interactive-timeout

[bigsoccer tech]

yes we are using replication

[eva2000]

just uncomment this line then

#log-bin

from my my.cnf suggested

[bigsoccer tech]

Eva,

Since goign to 353 we've had tons of max threads issues that have cause POST index corruption. I think it may have to do with turning all ajax on, so we went back to the middle option (partial ajax). We've been fine for about 24 hours. We literally have crashed every day for 2 weeks.

Our sys admin wants your advice:
----------
indeed the high load problem seems to arise when the host cache of the
DB server is full. This seems to happen when too many connections are
made but are kept unclosed.

Here is some informations I have found on a forum:
---
Generaly you have a lot of mysql process that are sleeping because
wait_timeout are not set low. So I make sure that the wait_timeout is
set to a very low value: 15 seconds (for me) . That means MySQL would
close any connection that was idle for more than 15 seconds.

The problem is you also have to increment your max_connexion (mine is
set to 300) to be sure there is not a lot of idle clients holding
connections and blocking out new clients from connecting and getting
real work done.
---

The current wait_timeout variable is set at 15 MINUTES on your db
server. We believe it may be too high but we wanted you to check with
vB first since they are the ones who suggested that value along with
the my.cnf you sent earlier last week.

Let us know what they say about this.

[eva2000]

hmm i only recommended wait_timeout of 5min for you did you change my.cnf to 15min since last ?

repost your my.cnf settings as well as output for top and

mysqladmin -u root -p ext stat ver

[bigsoccer tech]

hmm i only recommended wait_timeout of 5min for you did you change my.cnf to 15min since last ?

repost your my.cnf settings as well as output for top and

mysqladmin -u root -p ext stat ver

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1719 |
| Aborted_connects | 62 |
| Bytes_received | 3596826564 |
| Bytes_sent | 3806807713 |
| Com_admin_commands | 2 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3966677 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 16 |
| Com_delete | 108982 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 7 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 909295 |
| 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_purge | 1 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 78646 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 8108378 |
| Com_set_option | 32 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 1 |
| Com_show_create | 0 |
| Com_show_databases | 10 |
| Com_show_fields | 211 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 5 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 6827 |
| Com_show_slave_hosts | 2 |
| Com_show_slave_status | 1 |
| Com_show_status | 12 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 140 |
| Com_show_variables | 84 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 20 |
| Com_unlock_tables | 0 |
| Com_update | 2347143 |
| Com_update_multi | 0 |
| Connections | 3974529 |
| Created_tmp_disk_tables | 51038 |
| Created_tmp_tables | 288477 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 174 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 251672 |
| Handler_read_first | 1610528 |
| Handler_read_key | 293919635 |
| Handler_read_next | 1374189366 |
| Handler_read_prev | 12622963 |
| Handler_read_rnd | 96267288 |
| Handler_read_rnd_next | 1887634016 |
| Handler_rollback | 0 |
| Handler_update | 3144008 |
| Handler_write | 15552955 |
| Key_blocks_used | 375052 |
| Key_read_requests | 1558189446 |
| Key_reads | 1671765 |
| Key_write_requests | 2916957 |
| Key_writes | 3432144 |
| Max_used_connections | 1000 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1800 |
| Open_files | 2065 |
| Open_streams | 0 |
| Opened_tables | 80106 |
| Questions | 33807354 |
| Qcache_queries_in_cache | 32202 |
| Qcache_inserts | 8040862 |
| Qcache_hits | 14305541 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 67477 |
| Qcache_free_memory | 192700088 |
| Qcache_free_blocks | 24214 |
| Qcache_total_blocks | 90056 |
| Rpl_status | NULL |
| Select_full_join | 171018 |
| Select_full_range_join | 1 |
| Select_range | 2218437 |
| Select_range_check | 0 |
| Select_scan | 980093 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 619 |
| Sort_merge_passes | 0 |
| Sort_range | 2008890 |
| Sort_rows | 1015647873 |
| Sort_scan | 405424 |
| Table_locks_immediate | 22482658 |
| Table_locks_waited | 168253 |
| Threads_cached | 14 |
| Threads_created | 33386 |
| Threads_connected | 7 |
| Threads_running | 4 |
| Uptime | 154545 |
+--------------------------+------------+
Uptime: 154545 Threads: 7 Questions: 33807355 Slow queries: 619 Opens: 80106 Flush tables: 1 Open tables: 1800 Queries per second avg: 218.754
./mysqladmin Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 4.0.24-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 18 hours 55 min 45 sec

Threads: 7 Questions: 33807355 Slow queries: 619 Opens: 80106 Flush tables: 1 Open tables: 1800 Queries per second avg: 218.754
hd-a1813cl:/usr/local/mysql/bin#



DB SERVER TOP

top - 09:47:08 up 5 days, 9:56, 5 users, load average: 0.66, 0.67, 0.70
Tasks: 93 total, 1 running, 92 sleeping, 0 stopped, 0 zombie
Cpu(s): 10.2% us, 1.9% sy, 0.0% ni, 85.1% id, 2.2% wa, 0.2% hi, 0.5% si
Mem: 8309956k total, 7994716k used, 315240k free, 16828k buffers
Swap: 979956k total, 0k used, 979956k free, 7098896k cached




WEB SERVER TOP

top - 09:48:04 up 33 days, 17:02, 2 users, load average: 0.62, 0.52, 0.36
Tasks: 144 total, 2 running, 142 sleeping, 0 stopped, 0 zombie
Cpu(s): 16.9% us, 1.7% sy, 0.0% ni, 81.1% id, 0.1% wa, 0.0% hi, 0.2% si
Mem: 2074328k total, 1319324k used, 755004k free, 169140k buffers
Swap: 2048276k total, 272k used, 2048004k free, 606176k cached

[bigsoccer tech]

hd-a1813cl:/usr/local/mysql/data# cat my.cnf
[mysqld]
back_log = 100
skip-innodb
skip-name-resolve
skip-host-cache
#max_connections = 2048
#max_connections = 1024
max_connections = 1000
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 384
thread_cache = 16
#thread_concurrency = 8
thread_concurrency = 4
wait_timeout = 900
connect_timeout = 10
#tmp_table_size = 256M
tmp_table_size = 512M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
max_allowed_packet = 48M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
log-bin
log-error
log-slow-queries
server-id = 1

[mysqld_safe]
open_files_limit = 16384

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
hd-a1813cl:/usr/local/mysql/data#

[eva2000]

strange only a small amount threads aren't being cached in thread_cache from the output stats...

can you postoutput for

mysqladmin -u root -p var

to confirm what you think you have in my.cnf that mysql sees and what it really is

haven't seen such a command in my.cnf before what's hd-a1813cl:/usr/local/mysql/data# ??

4.0.24 bit dated now ... might want to upgrade to MySQL 4.1.16 see http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html

best way to ensure full compatibility is to mysqldump the databases to sql file while on mysql 4.0.24 or 4.0.26 (if you go via incremental upgrade before 4.1.16), then upgrade to mysql 4.1.16 and import the sql files into newly created empty databases on 4.1.16

[bigsoccer tech]

-
---------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+---------------------------------+----------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------+
| back_log | 100
|
| basedir | /virtual/mysql/
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 16777216
|
| character_set | latin1
|
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru l
atin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
|
| connect_timeout | 10
|
| convert_character_set |
|
| datadir | /usr/local/mysql/data/
|
| 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 | DISABLED
|
| 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_autoextend_increment | 8
|
| innodb_buffer_pool_size | 8388608
|
| innodb_data_file_path |
|
| 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
|
| innodb_max_purge_lag | 0
|
| innodb_table_locks | ON
|
| interactive_timeout | 28800
|
| join_buffer_size | 2093056
|
| key_buffer_size | 402653184
|
| language | /virtual/mysql/share/mysql/english/
|
| large_files_support | ON
|
| license | GPL
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_update | OFF
|
| log_bin | ON
|
| log_slave_updates | OFF
|
| log_slow_queries | ON
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 50330624
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 1000
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|

| max_delayed_threads | 20
|
| max_insert_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 | 67108864
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| open_files_limit | 5010
|
| pid_file | /usr/local/mysql/data/hd-a1813cl.pid
|
| log_error | ./hd-a1813cl.err
|
| port | 3306
|
| protocol_version | 10
|
| query_alloc_block_size | 16384
|
| query_cache_limit | 2097152
|
| query_cache_size | 268435456
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 16384
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 2093056
|
| read_only | OFF
|
| read_rnd_buffer_size | 520192
|
| rpl_recovery_rank | 0
|
| server_id | 1
|
| slave_net_timeout | 3600
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer_size | 3145720
|
| sql_mode | 0
|
| table_cache | 1800
|
| table_type | MYISAM
|
| thread_cache_size | 16
|
| thread_stack | 126976
|
| tx_isolation | REPEATABLE-READ
|
| timezone | EST
|
| tmp_table_size | 536870912
|
| tmpdir | /tmp/
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| version | 4.0.24-standard-log
|
| version_comment | Official MySQL-standard binary
|
| version_compile_os | pc-linux-gnu
|
| wait_timeout | 900
|
+---------------------------------+----------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------+

[eva2000]

yup looks like the

thread_cache = 16

which is depreciated option is overriding thread_cache_size you set.. remove

thread_cache = 16

try this my.cnf

[mysqld]
back_log = 100
skip-innodb
skip-name-resolve
skip-host-cache
max_connections = 1500
key_buffer = 384M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 1024
wait_timeout = 360
connect_timeout = 10
tmp_table_size = 1024M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
thread_concurrency = 4
query_cache_limit = 4M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
log-bin
log-error
log-slow-queries
server-id = 1

[mysqld_safe]
open_files_limit = 16384

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 256M
sort_buffer = 256M
read_buffer = 64M
write_buffer = 64M
hd-a1813cl:/usr/local/mysql/data#

disabling log-error and log-slow-queries would help too

let the new my.cnf run and keep an eye on memory usage etc and see how it goes

[bigsoccer tech]

It lasted 36 hours. Then db crash:
mysql_connect(): Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug
/home/www/bigsoccer.com/forum /includes/class_core.php on line 279

[eva2000]

perror 11 = resource temporarily unavailable

basically means you possibly ran out of file descriptors http://dev.mysql.com/doc/refman/5.0/en/not-enough-file-handles.html

The problem here is that mysqld is trying to keep open too many files simultaneously. You can either tell mysqld not to open so many files at once or increase the number of file descriptors available to mysqld.

To tell mysqld to keep open fewer files at a time, you can make the table cache smaller by reducing the value of the table_cache system variable (the default value is 64). Reducing the value of max_connections also reduces the number of open files (the default value is 100).

To change the number of file descriptors available to mysqld, you can use the --open-files-limit option to mysqld_safe or (as of MySQL 3.23.30) set the open_files_limit system variable. See Section 5.2.2, “Server System Variables”. The easiest way to set these values is to add an option to your option file. See Section 4.3.2, “Using Option Files”. If you have an old version of mysqld that doesn't support setting the open files limit, you can edit the mysqld_safe script. There is a commented-out line ulimit -n 256 in the script. You can remove the ‘#’ character to uncomment this line, and change the number 256 to set the number of file descriptors to be made available to mysqld.

--open-files-limit and ulimit can increase the number of file descriptors, but only up to the limit imposed by the operating system. There is also a “hard” limit that can be overridden only if you start mysqld_safe or mysqld as root (just remember that you also need to start the server with the --user option in this case so that it does not continue to run as root after it starts up). If you need to increase the operating system limit on the number of file descriptors available to each process, consult the documentation for your system

for db server type in ssh telnet and post output for these 2 commands

ulimit -aH

and

ulimit -aS

to see the file descriptor limit for your server

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

MySQL is multi-threaded, so there may be many clients issuing queries for a given table simultaneously. To minimize the problem with multiple client threads having different states on the same table, the table is opened independently by each concurrent thread. This uses additional memory but normally increases performance. With MyISAM tables, one extra file descriptor is required for the data file for each client that has the table open. (By contrast, the index file descriptor is shared between all threads.)

The table_cache, max_connections, and max_tmp_tables system variables affect the maximum number of files the server keeps open. If you increase one or more of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. Many operating systems allow you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.

so let's try changing my.cnf to below

[mysqld]
back_log = 100
skip-innodb
skip-name-resolve
skip-host-cache
max_connections = 1200
key_buffer = 384M
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1200
thread_cache_size = 1024
wait_timeout = 360
connect_timeout = 10
tmp_table_size = 1024M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 16M
thread_concurrency = 4
query_cache_limit = 4M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
log-bin
log-error
log-slow-queries
server-id = 1

[mysqld_safe]
open_files_limit = 16384

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 256M
sort_buffer = 256M
read_buffer = 64M
write_buffer = 64M
hd-a1813cl:/usr/local/mysql/data#

[eva2000]

Also ensure all web servers connecting to mysql server have persistent connections in php.ini, vB config.php etc disabled

if you use phpadsnew or banner script with mysql also disable persistent connections in those scripts

[bigsoccer tech]

We don't use pconnect.

We moved all our searches off to a third box. Our admins found that the searches were locking our tables, which in turn was causing threads to back up which in turn was causing our db to crash. Now with searches on its own box, if it locks up, in theory our site shouldn't crash. but...

There are still locking issues on DB1 but so far they did not cause any
mysql downtime. Locks are normal, but what is not normal is that they last
so long.

Here is an output of the processlist when it locks. In this exemple, you
have about 85 queries that are waiting for the first to complete. So you
probably have about 85 visitors on your web sites waiting for a page to show
and that page will only show when the lock is released. This lock lasted
about 10-15 seconds I think. We are discussing about the MySQL upgrade to
v4.1 here. I think that is the next thing we need to do. Then we will need
to monitor the web sites for some days to see how it reacts and if we see
new downtimes.


Id User Host db Command Time State Info
1690 replicate 10.10.0.24:32978 (http://10.10.0.24:32978/) NULL Binlog Dump 9916
Has sent all binlog to slave; waiting for binlog to be updated NULL
1689 replicate 10.10.0.23:48525 (http://10.10.0.23:48525/) NULL Binlog Dump 9916
Has sent all binlog to slave; waiting for binlog to be updated NULL
306572 root 10.10.0.16:47014 (http://10.10.0.16:47014/) bigsoccer_vb_353 Query 21
Copying to tmp table SELECT\n\t\t\tDISTINCT thread.threadid\n\t\t\tFROM
thread AS thread \n\t\t\tINNER JOIN post AS post ON(thread.th (http://thread.th/)
306620 root 10.10.0.12:42527 (http://10.10.0.12:42527/) bigsoccer_vb_353 Query 19
Locked UPDATE thread SET^M\n\treplycount = replycount + 1,^M\n\tlastpost =
1139894200,^M\n\tlastposter = 'Iranian Mo
306626 root 10.10.0.11:44973 (http://10.10.0.11:44973/) bigsoccer_vb_353 Query 19
Locked UPDATE thread SET^M\n\treplycount = replycount + 1,^M\n\tlastpost =
1139894200,^M\n\tlastposter = 'Zagros'^M\nW
306634 root 10.10.0.12:42532 (http://10.10.0.12:42532/) bigsoccer_vb_353 Query 19
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t \t\tNOT
ISNULL(subscribethread.subscribethreadid)
306635 root 10.10.0.11:44978 (http://10.10.0.11:44978/) bigsoccer_vb_353 Query 19
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306682 root 10.10.0.12:42551 (http://10.10.0.12:42551/) bigsoccer_vb_353 Query 17
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\tNOT
ISNULL(subscribethread.subscribethreadid)
306685 root 10.10.0.12:42552 (http://10.10.0.12:42552/) bigsoccer_vb_353 Query 17
Locked SELECT \n\t\t\t\tthread.threadid, post.title, thread.replycount,
postusername, postuserid, thread.datelin
306688 root 10.10.0.15:55729 (http://10.10.0.15:55729/) bigsoccer_vb_353 Query 17
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\tNOT
ISNULL(subscribethread.subscribethreadid)
306702 root 10.10.0.12:42563 (http://10.10.0.12:42563/) bigsoccer_vb_353 Query 16
Locked SELECT\n\t\t\tuser.username,
reputation.whoadded,\n\t\t\treputation.postid as
postid,\n\t\t\treputation.reputati
306703 root 10.10.0.11:45006 (http://10.10.0.11:45006/) bigsoccer_vb_353 Query 16
Locked SELECT COUNT(*) AS total\n\t\t\tFROM attachment AS
attachment\n\t\t\tLEFT JOIN post AS post ON (post.postid
306704 root 10.10.0.15:55732 (http://10.10.0.15:55732/) bigsoccer_vb_353 Query 16
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306708 root 10.10.0.11:45009 (http://10.10.0.11:45009/) bigsoccer_vb_353 Query 16
Locked SELECT COUNT(*) AS threads,^M\n\t\tSUM(IF(thread.lastpost >=
1106029799 AND open <> 10, 1, 0)) AS newthr
306713 root 10.10.0.12:42569 (http://10.10.0.12:42569/) bigsoccer_vb_353 Query 16
Locked SELECT COUNT(*) AS threads,^M\n\t\tSUM(IF(thread.lastpost >=
1139893798 AND open <> 10, 1, 0)) AS newthr
306716 root 10.10.0.15:55734 (http://10.10.0.15:55734/) bigsoccer_vb_353 Query 16
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306719 root 10.10.0.11:45013 (http://10.10.0.11:45013/) bigsoccer_vb_353 Query 16
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306720 root 10.10.0.16:47034 (http://10.10.0.16:47034/) bigsoccer_vb_353 Query 16
Locked UPDATE user\n\t\t\t\t\t\tSET lastactivity =
1139894204\n\t\t\t\t\t\tWHERE userid = 38148
306721 root 10.10.0.12:42571 (http://10.10.0.12:42571/) bigsoccer_vb_353 Query 16
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306724 root 10.10.0.16:47035 (http://10.10.0.16:47035/) bigsoccer_vb_353 Query 16
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306729 root 10.10.0.11:45014 (http://10.10.0.11:45014/) bigsoccer_vb_353 Query 15
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306731 root 10.10.0.16:47037 (http://10.10.0.16:47037/) bigsoccer_vb_353 Query 15
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306735 root 10.10.0.11:45017 (http://10.10.0.11:45017/) bigsoccer_vb_353 Query 15
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306741 root 10.10.0.11:45021 (http://10.10.0.11:45021/) bigsoccer_vb_353 Query 15
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306742 root 10.10.0.11:45022 (http://10.10.0.11:45022/) bigsoccer_vb_353 Query 15
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306743 root 10.10.0.16:47039 (http://10.10.0.16:47039/) bigsoccer_vb_353 Query 15
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306744 root 10.10.0.12:42582 (http://10.10.0.12:42582/) bigsoccer_vb_353 Query 15
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306748 root 10.10.0.16:47040 (http://10.10.0.16:47040/) bigsoccer_vb_353 Query 15
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306753 root 10.10.0.11:45025 (http://10.10.0.11:45025/) bigsoccer_vb_353 Query 15
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306756 root 10.10.0.15:55738 (http://10.10.0.15:55738/) bigsoccer_vb_353 Query 14
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306758 root 10.10.0.12:42587 (http://10.10.0.12:42587/) bigsoccer_vb_353 Query 14
Locked SELECT \n\t\t\t\tthread.threadid, post.title, thread.replycount,
postusername, postuserid, thread.datelin
306760 root 10.10.0.12:42588 (http://10.10.0.12:42588/) bigsoccer_vb_353 Query 14
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306766 root 10.10.0.12:42591 (http://10.10.0.12:42591/) bigsoccer_vb_353 Query 14
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306768 root 10.10.0.11:45027 (http://10.10.0.11:45027/) bigsoccer_vb_353 Query 14
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306778 root 10.10.0.12:42595 (http://10.10.0.12:42595/) bigsoccer_vb_353 Query 13
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306780 root 10.10.0.12:42597 (http://10.10.0.12:42597/) bigsoccer_vb_353 Query 13
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306781 root 10.10.0.15:55745 (http://10.10.0.15:55745/) bigsoccer_vb_353 Query 13
Locked SELECT userid, usergroupid, membergroupids, username, password, salt
FROM user WHERE username = 'mar
306789 root 10.10.0.16:47046 (http://10.10.0.16:47046/) bigsoccer_vb_353 Query 13
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306796 root 10.10.0.15:55748 (http://10.10.0.15:55748/) bigsoccer_vb_353 Query 13
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306797 root 10.10.0.12:42605 (http://10.10.0.12:42605/) bigsoccer_vb_353 Query 13
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306813 root 10.10.0.11:45036 (http://10.10.0.11:45036/) bigsoccer_vb_353 Query 12
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306821 root 10.10.0.11:45041 (http://10.10.0.11:45041/) bigsoccer_vb_353 Query 11
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306823 root 10.10.0.11:45042 (http://10.10.0.11:45042/) bigsoccer_vb_353 Query 11
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306825 root 10.10.0.12:42620 (http://10.10.0.12:42620/) bigsoccer_vb_353 Query 11
Locked SELECT thread.threadid, thread.title,\n\t\t\tthread.postusername,
thread.dateline,\n\t\t\tforum.forumid,\n\t\t\t
306827 root 10.10.0.12:42621 (http://10.10.0.12:42621/) bigsoccer_vb_353 Query 11
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306833 root 10.10.0.11:45043 (http://10.10.0.11:45043/) bigsoccer_vb_353 Query 11
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306834 root 10.10.0.15:55755 (http://10.10.0.15:55755/) bigsoccer_vb_353 Query 11
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306840 root 10.10.0.12:42628 (http://10.10.0.12:42628/) bigsoccer_vb_353 Query 10
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306846 root 10.10.0.11:45044 (http://10.10.0.11:45044/) bigsoccer_vb_353 Query 10
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306850 root 10.10.0.12:42634 (http://10.10.0.12:42634/) bigsoccer_vb_353 Query 10
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306855 root 10.10.0.16:47053 (http://10.10.0.16:47053/) bigsoccer_vb_353 Query 10
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306856 root 10.10.0.15:55758 (http://10.10.0.15:55758/) bigsoccer_vb_353 Query 10
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306859 root 10.10.0.16:47055 (http://10.10.0.16:47055/) bigsoccer_vb_353 Query 10
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\
306856 root 10.10.0.15:55758 (http://10.10.0.15:55758/) bigsoccer_vb_353 Query 10
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306859 root 10.10.0.16:47055 (http://10.10.0.16:47055/) bigsoccer_vb_353 Query 10
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306866 root 10.10.0.12:42640 (http://10.10.0.12:42640/) bigsoccer_vb_353 Query 9
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306880 root 10.10.0.12:42648 (http://10.10.0.12:42648/) bigsoccer_vb_353 Query 9
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306887 root 10.10.0.11:45051 (http://10.10.0.11:45051/) bigsoccer_vb_353 Query 9
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306898 root 10.10.0.12:42661 (http://10.10.0.12:42661/) bigsoccer_vb_353 Query 8
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306908 root 10.10.0.12:42665 (http://10.10.0.12:42665/) bigsoccer_vb_353 Query 8
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306912 root 10.10.0.11:45057 (http://10.10.0.11:45057/) bigsoccer_vb_353 Query 7
Locked SELECT \n\t\t\t\tthread.threadid, post.title, thread.replycount,
postusername, postuserid, thread.datelin
306915 root 10.10.0.15:55767 (http://10.10.0.15:55767/) bigsoccer_vb_353 Query 7
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306930 root 10.10.0.16:47066 (http://10.10.0.16:47066/) bigsoccer_vb_353 Query 7
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306934 root 10.10.0.11:45063 (http://10.10.0.11:45063/) bigsoccer_vb_353 Query 6
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306944 root 10.10.0.15:55770 (http://10.10.0.15:55770/) bigsoccer_vb_353 Query 6
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306945 root 10.10.0.16:47067 (http://10.10.0.16:47067/) bigsoccer_vb_353 Query 6
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306955 root 10.10.0.12:42691 (http://10.10.0.12:42691/) bigsoccer_vb_353 Query 5
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306958 root 10.10.0.11:45071 (http://10.10.0.11:45071/) bigsoccer_vb_353 Query 5
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306963 root 10.10.0.11:45074 (http://10.10.0.11:45074/) bigsoccer_vb_353 Query 5
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306964 root 10.10.0.16:47070 (http://10.10.0.16:47070/) bigsoccer_vb_353 Query 5
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306965 root 10.10.0.12:42695 (http://10.10.0.12:42695/) bigsoccer_vb_353 Query 5
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306969 root 10.10.0.11:45077 (http://10.10.0.11:45077/) bigsoccer_vb_353 Query 4
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
306972 root 10.10.0.16:47072 (http://10.10.0.16:47072/) bigsoccer_vb_353 Query 4
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
306973 root 10.10.0.12:42698 (http://10.10.0.12:42698/) bigsoccer_vb_353 Query 4
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
306978 root 10.10.0.16:47074 (http://10.10.0.16:47074/) bigsoccer_vb_353 Query 4
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
307002 root 10.10.0.12:42712 (http://10.10.0.12:42712/) bigsoccer_vb_353 Query 3
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
307009 root 10.10.0.16:47077 (http://10.10.0.16:47077/) ads Sleep 3 NULL
307010 root 10.10.0.11:45092 (http://10.10.0.11:45092/) bigsoccer_vb_353 Query 3
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
307014 root 10.10.0.15:55772 (http://10.10.0.15:55772/) bigsoccer_vb_353 Query 3
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307016 root 10.10.0.15:55773 (http://10.10.0.15:55773/) bigsoccer_vb_353 Query 2
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
307024 root 10.10.0.16:47080 (http://10.10.0.16:47080/) bigsoccer_vb_353 Query 2
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
307029 root 10.10.0.11:45102 (http://10.10.0.11:45102/) bigsoccer_vb_353 Query 2
Locked SELECT COUNT(*) AS total\n\t\t\tFROM attachment AS
attachment\n\t\t\tLEFT JOIN post AS post ON (post.postid
307033 root 10.10.0.11:45103 (http://10.10.0.11:45103/) bigsoccer_vb_353 Query 2
Locked SELECT IF(visible = 2, 1, 0) AS
isdeleted,^M\n\t\t\t^M\n^M\n\t\t\t^M\n\t\t\tthread .*^M\n\t\t\tFROM thread AS
thread^M\n\t\t\t^M\n
307043 root 10.10.0.11:45113 (http://10.10.0.11:45113/) bigsoccer_vb_353 Query 2
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307048 root 10.10.0.11:45115 (http://10.10.0.11:45115/) bigsoccer_vb_353 Query 1
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
307049 root 10.10.0.11:45116 (http://10.10.0.11:45116/) bigsoccer_vb_353 Query 1
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307050 root 10.10.0.12:42730 (http://10.10.0.12:42730/) bigsoccer_vb_353 Query 1
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
307053 root 10.10.0.11:45118 (http://10.10.0.11:45118/) bigsoccer_vb_353 Query 1
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307054 root 10.10.0.11:45120 (http://10.10.0.11:45120/) bigsoccer_vb_353 Query 1
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307056 root 10.10.0.11:45121 (http://10.10.0.11:45121/) bigsoccer_vb_353 Query 1
Locked SELECT^M\n\t\t\tannouncementid, startdate, title,
announcement.views,^M\n\t\t\tuser.username, user.userid, use
307057 root 10.10.0.11:45122 (http://10.10.0.11:45122/) bigsoccer_vb_353 Query 1
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307066 root 10.10.0.16:47084 (http://10.10.0.16:47084/) bigsoccer_vb_353 Query 0
Locked SELECT \n\t\t\t\tthread.threadid, post.title, thread.replycount,
postusername, postuserid, thread.datelin
307070 root 10.10.0.11:45132 (http://10.10.0.11:45132/) bigsoccer_vb_353 Query 0
Locked SELECT ^M\n\t\t\tuserfield.*, usertextfield.*, user.*,
UNIX_TIMESTAMP(passworddate) AS passworddate,^M\n\t\t\t
307071 root 127.0.0.1:40808 (http://127.0.0.1:40808/) mysql Query 0 NULL show
processlist

[bigsoccer tech]

EVA - does this remain the best thread to discuss this or should I be in vb 3.5 problems? Also everyone on our team is (sorry) disagreeing with your my.cnf recommendations. Particularly they are concerned that your temp table size is far too large.

[eva2000]

Yeah best to keep it to this thread for your issue - the issue you have is file descriptors running out = crash

well locks for 20 seconds is fairly normal.. for you sized forums i'm amazed it's only around 20 seconds usually it would be around upto 120-180 seconds for such sized forums.

what's wrong with tmp_table_size... load more into ram is faster and better than on-disk - should help with table locks waiting less i think ?

#

tmp_table_size

If an in-memory temporary table exceeds this size, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size if you do many advanced GROUP BY queries and you have lots of memory.


is that table locks being held back by binlog there ? as i said ages ago for performance reasons disabling replication/binlog would help speed things up but then again you need replication heh

If you do upgrade 4.1.18 is the one to upgrade to and nice read http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html :)

might also want to check out this handy tool i just found http://www.vbulletin.com/forum/showthread.php?t=175177 man really helps highlight the important info to monitor !

[bigsoccer tech]

Offloading search to another box has given us 2 stable days. That was certainly locking us up.

Big question:

do we wait for 3.5.6 when the slave/master re-write will be complete (as you know it isn't properly implemented yet as it needs to separate slave read and master read)

OR...

do we try to implement memcache and, if so, what are your thoughts and suggestions concerns regarding it.

as always thanks,

bst

[bigsoccer tech]

ps we will upgrade mysql this weekend as well

[eva2000]

i know in theory memcache properly implemented would help out alot for your sized forums.. howardstern.com vB forums use memcached to handle 40,000 users online over a server farm including quad cpu mysql database servers

unfortunately memcached tech is out of my field... i believe our developer Scott is looking into memcached

[bigsoccer tech]

all our probs started when we switched to fulltext search. any thoughts why? are we too big? could something have gone wrong during the switch? should we reebuild the fulltext index? should we switch back?

[eva2000]

not sure to be honest if fulltext is the reason... but on mysql upgrading i'd try upgrading to MySQL 4.1.18 first before trying 5.0.18 as once you go to 5.0.18 you can't go backwards to earlier mysql 4.0.x and 4.1.x due to a bug in mysqldump in 5.0.x which needs --compatible=mysql40 option in mysqldump to get a proper dump to import into earlier versions, only prob is --compatible has a bug that strips auto_increment fields in databases rendering your forum unuseable

try full text search in 4.1.18 and see how it goes

[JonnyQuest]

So we upgraded to 4.1.18. The problem we are current seeing is that we are seeing too man connections on our non-search DB box. max-connections is at 750.

[eva2000]

for each web server that connects to mysql server, what are the respective httpd.conf values for keepalives, keepalive timeout value, min, start and max servers and maxclients and maxrequestsperchild ?

[JonnyQuest]

I'll try to get you that info shortly.

In the meantime, our host seems to feel that this is the process that gets locked and brings us to our max_connections limit:

6693045 root 10.10.0.12:49017 bigsoccer_vb_353 Query 32 Copying to tmp table SELECT DISTINCT user.userid, user.username, post.ipaddress\n\t\tFROM post AS post,\n\t\tuser AS user\n\t\tWHE
Any idea why this would lock for so long/so badly? Any way to avoid it?

[bigsoccer tech]

Here are the values in httpd.conf:
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 400
MaxRequestsPerChild 1500

[eva2000]

32 seconds ain't long.. it's due to size of your post table i guess

as to httpd.conf try reducing maxclients from 400 to 256 on each web server and maxrequestsperchild up it from 1500 to 5000 and restart apache and see what happens... i got your email too so will reply to that as well

[bigsoccer tech]

how can a user waiting 32 se4conds for a page to long not be long???

[bigsoccer tech]

how can a user waiting 32 se4conds for a page to long not be long???

This post made me think...
http://www.vbulletin.com/forum/showthread.php?t=154375&highlight=locking

InnoDB tends to be atad more resource intensive to the point they require more processing, however their tables support row level locking instead of entire table locking.

and this

I used InnoDB with vBulletin for close to 3 years without a hitch. It's slower on read operations, but inserting new forum posts was faster (due to the more flexible row level locking). Any forum will offcourse generally have much more need for reading from the DB than writing to it, so MyISAM will be clearly better for most forums (unless you start getting delays from the full table locking issues). Running with InnoDB instead of MyISAM might feel more complex for some as well... I liked it when I got used to it. But I went back to MyISAM a couple of months ago for performance issues.

and

In my case, I had to choose between more overhead that causes pages to load a tiny bit slower, vs. an entire forum being dead because one query was locking a table and holding up everything else. InnoDB on every table is overkill and unnecessary, IMHO.
Since table locking on POSTS is our issue is this a potential solution?

[bigsoccer tech]

From the 1mm+ vb thread users say InnoDb can help.....?


-------------
We run user and thread as innodb which solves 90% of our contention/locking problems. This means no fulltext searching on thread titles, but whatever, I'd rather have less table locking.
-------------
I've been battling locking issues for the last 3 weeks. When I'd hit 900 - 1200 users online mysql threads would back up becuase of locking, I'd hit max connections, and get vbull can not connect errors. After a lot of investigating and SHOW FULL PROCESSLIST during these times it was always queries running against the thread table. My guess is that table gets hit a lot for concurrent reads and writes, even more so than post.
-------------
You can't use innodb for post / thread as they dont support fulltext searching.
-------------
I think you might benefit most from:
Innodb Post / Thread table
Slave server running post / thread as myisam so you can use fulltext. In this case all searches would be deferred to the slave server not slowing down your board but being slightly out of date.
----
We decided to convert all our tables to InnoDB. The good news is the new post query is now down to 0.15 and feels snappy. Threads come up fast, the forum home comes up fast, we no longer see catastrophic lockups, reply is fast and our users think it went into hyper drive except....search. Searches are now, as expected, 15-45 seconds. Woof.

We offer a Google search option that most of our users like because it's so fast and does 2 & 3-letter words. We'll probably either optimize the queries that now use count(*) and so many table joins, or do the full-text search slave as the next step.
----
We ended up switching to INNODB as well... our locks were through the roof. Everything is running MUCH faster now, but we can't merge threads or delete users:
----------
We have a script that runs in cron once a minute and kills processes if there is a certain threshold of locked processes. It looks for queries that have been sending data or copying to temp table for over x seconds.

It’s a patch job and not a real fix.
--------
When I changed my table types to InnoDB, which use row-level locking, the queries were no longer locking up access to the database. I don't know if it has made the forum faster overall, BUT, one query is no longer holding up an entire queue of others waiting for access to the same table.
----------
Also, when I used InnoDB, I found it slower than MyISAM - it was only "faster" because of row-level locking - take away the locking issue and I found it to be slower overall.
-------
One thing I have read is that the MySQL master post table can be InnoDB and the slave post table can be MyISAM with the full text index. This is because how MySQL handles replication using binary logging. It’s worth a try on a couple of our large sites to eliminate the table locking problems.

[eva2000]

how can a user waiting 32 se4conds for a page to long not be long???
no 32s for that query is copying your post table data for that query to a tmp table... given your post table size 32s ain't long


BUT found your culprit after i logged into your db server! :)

2 issues found in mysql error log

1. you haven't updated mysql user privileges after upgrading to mysql 4.1.18 so for now i added in my.cnf under [mysqld]

old_passwords

you need to restart mysql for that to take affect

mysql error log shows

[Warning] mysql.user table is not updated to new password format; Disabling new password usage until mysql_fix_privilege_tables is run

read http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html to see http://dev.mysql.com/doc/refman/4.1/en/mysql-fix-privilege-tables.html

once you run mysql_fix_privileges_tables you can remove old_password in my.cnf

2. mysql error log entries

060222 18:21:29 [ERROR] Error in accept: Too many open files
060222 22:23:33 [ERROR] /usr/local/mysql/bin/mysqld: Can't find file: './dbname/editlog.frm' (errno: 24)

errorno 24 = too many open files.. which is you're running out of file descriptors as i suspected still this was last entry in the log 2hrs ago from this email

Now the problem is you hard limit for open files is very low at 1024

type

ulimit -aH

output is

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited

on my server open files is compiled to 8096 value 8x times what you have... that is the crux of your problem i see you need to increase that limit for mysql user i.e set it to maybe 16384 or 65536

So ask your web host for debian os to increase max open files limit for 2.6.x kernel

I'm fairly sure that's your problem right now fix this and you should be golden :)


George

[bigsoccer tech]

this didn't work. we are still crashing. intrestingsly, we never crashed with 3.0 - 3.5 has killed us. what chnged in 3.5 that we should be concerned about? any other ideas?

[eva2000]

what does ulimit -aH output show now on db server and web servers ?

vB 3.5. does use more resources than 3.0.x though

[forexfactory]

would be nice to hear the ending to this story for those of us having the same issues!