Clutch
Thu 23rd Oct '03, 8:36pm
eva2000 - I desperately need your help :)
I have a popular board that regularly has around 200 people and during peak times gets around 250-350 people and that's when the connection problems start and the "vBulletin (forum) Database error!" emails start pouring in.
Link-ID == false, connect failed
mysql error: Too many connections
I had this fixed in the past with a my.cnf edit, but can't find the correct one now when I lost the previous one in a transfer. I have to shut down the board during high traffic times to keep this from happening.
I recognize that I probably need better hardaware to run the board, but could use your help in optimizing this server until I can find a better solution. Thank you.
1. is this on dedicated or shared virual server: Dedicated.
2. your server specs. For example:
cpu speed/type single or dual cpus): celeron 1.3ghz
how much memory installed: 512mb ram
hard drive type/configuration: 60GB 7200 HD
linux distributor or windows version: redhat linux 7.3
apache/IIS version: apache (not sure how to look up the version - it's using Ensim Site Administrator 3.1.9-2)
PHP version: php 4.2.2
MySQL version: mysql 3.23.54
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
4. if possible how mysql was compiled/installed. Not sure on this since it was done by the host company.
5. your top stats. Right now the board is experiencing a lot of heavy traffic and these are the numbers.
6:25pm up 1:11, 1 user, load average: 16.16, 10.08, 8.05
210 processes: 187 sleeping, 21 running, 2 zombie, 0 stopped
CPU states: 65.3% user, 10.8% system, 7.6% nice, 16.1% idle
Mem: 506024K av, 433960K used, 72064K free, 0K shrd, 3576K buff
Swap: 1020116K av, 0K used, 1020116K free 176384K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
5045 root 16 0 1156 1152 832 R 13.3 0.2 0:00 top
5025 apache 15 0 10732 10M 8700 S 9.1 2.1 0:00 httpd
5023 apache 15 0 10968 10M 8736 S 8.3 2.1 0:00 httpd
1160 apache 16 0 11512 11M 8672 S 4.9 2.2 0:47 httpd
2527 apache 15 0 10816 10M 8700 S 4.9 2.1 0:36 httpd
5007 apache 16 0 10748 10M 8708 S 4.9 2.1 0:00 httpd
4286 apache 16 0 11240 10M 8736 S 4.1 2.2 0:09 httpd
1131 apache 16 0 11308 11M 8760 S 3.3 2.2 0:38 httpd
4313 apache 16 0 11176 10M 8708 R 3.3 2.2 0:08 httpd
2523 mysql 25 10 24364 23M 2216 S N 2.4 4.8 0:06 mysqld
2526 apache 15 0 11476 11M 8708 S 2.4 2.2 0:32 httpd
4572 apache 16 0 11208 10M 8764 S 2.4 2.2 0:04 httpd
5024 apache 15 0 0 0 0 Z 2.4 0.0 0:00 httpd <defunct>
1159 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:06 mysqld
1404 apache 15 0 11160 10M 8676 S 1.6 2.2 0:44 httpd
2522 apache 15 0 11212 10M 8748 S 1.6 2.2 0:38 httpd
2541 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:04 mysqld
4637 apache 15 0 10804 10M 8748 S 1.6 2.1 0:03 httpd
5004 apache 16 0 10980 10M 8736 S 1.6 2.1 0:00 httpd
5014 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:00 mysqld
5030 apache 15 0 10828 10M 8732 S 1.6 2.1 0:00 httpd
1215 mysql 25 10 24364 23M 2216 R N 0.8 4.8 0:06 mysqld
1410 mysql 26 10 24364 23M 2216 R N 0.8 4.8 0:06 mysqld
2525 apache 15 0 10944 10M 8720 S 0.8 2.1 0:33 httpd
4312 apache 15 0 10940 10M 8764 S 0.8 2.1 0:11 httpd
4314 apache 15 0 10780 10M 8764 S 0.8 2.1 0:07 httpd
4351 mysql 25 10 24364 23M 2216 R N 0.8 4.8 0:01 mysqld
4640 apache 15 0 11216 10M 8764 S 0.8 2.2 0:04 httpd
4991 apache 16 0 10772 10M 8752 S 0.8 2.1 0:01 httpd
5005 apache 16 0 10948 10M 8736 S 0.8 2.1 0:00 httpd
5008 apache 16 0 0 0 0 Z 0.8 0.0 0:00 httpd <defunct>
5015 mysql 25 10 24364 23M 2216 S N 0.8 4.8 0:00 mysqld
5026 mysql 25 10 24364 23M 2216 S N 0.8 4.8 0:00 mysqld
1 root 15 0 512 512 440 S 0.0 0.1 0:05 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 15 0 0 0 0 SW 0.0 0.0 0:00 kswapd
6 root 25 0 0 0 0 SW 0.0 0.0 0:00 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
12 root 15 0 0 0 0 SW 0.0 0.0 0:01 kjournald
89 root 15 0 0 0 0 SW 0.0 0.0 0:00 khubd
212 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
541 root 15 0 0 0 0 SW 0.0 0.0 0:00 eth0
754 root 15 0 584 584 488 S 0.0 0.1 0:00 syslogd
765 root 15 0 1116 1116 452 S 0.0 0.2 0:00 klogd
829 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
831 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
833 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
834 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
835 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
852 root 15 0 1444 1444 1304 S 0.0 0.2 0:00 sshd
872 root 19 0 1244 1244 1044 S 0.0 0.2 0:00 sshd
906 root 18 0 932 932 756 S 0.0 0.1 0:00 xinetd
926 root 18 0 1016 1016 836 S 0.0 0.2 0:00 safe_mysqld
951 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
953 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
954 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
955 mysql 30 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
991 root 15 0 2552 2552 1880 S 0.0 0.5 0:00 sendmail
1059 root 15 0 8768 8768 8572 S 0.0 1.7 0:03 httpd
1119 postgres 15 0 1672 1672 1536 S 0.0 0.3 0:00 postmaster
1123 apache 15 0 8240 8240 7800 S 0.0 1.6 0:00 httpd
1125 root 17 0 300 300 240 S 0.0 0.0 0:00 cronolog
6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type
mysqladmin -u root -p variables
copy and paste output here
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /var/lib/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (December 5, 2002)
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 | concurrent_insert
| ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innodb | NO
| have_isam | YES
| have_raid | NO
| have_openssl | NO
| init_file |
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_long_queries | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_recover_options | 0
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| record_rnd_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | CDT
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 3.23.54
| wait_timeout | 28800
7. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
http://207.44.140.146/php3/admin/mysqlinfo.php (http://207.44.140.146/php3/admin/mysqlinfo.php)
8. vB is the only thing on the server.
9. how many average and max concurrent users on your vB forum ? The board regularly has around 200 people and during peak times gets around 250-350 people and that's when the connection problems start.
10. http://207.44.140.146/php3/phpinfo.php (http://207.44.140.146/php3/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 150
12. what version of vB are you running ? vB 2.2.9
I have a popular board that regularly has around 200 people and during peak times gets around 250-350 people and that's when the connection problems start and the "vBulletin (forum) Database error!" emails start pouring in.
Link-ID == false, connect failed
mysql error: Too many connections
I had this fixed in the past with a my.cnf edit, but can't find the correct one now when I lost the previous one in a transfer. I have to shut down the board during high traffic times to keep this from happening.
I recognize that I probably need better hardaware to run the board, but could use your help in optimizing this server until I can find a better solution. Thank you.
1. is this on dedicated or shared virual server: Dedicated.
2. your server specs. For example:
cpu speed/type single or dual cpus): celeron 1.3ghz
how much memory installed: 512mb ram
hard drive type/configuration: 60GB 7200 HD
linux distributor or windows version: redhat linux 7.3
apache/IIS version: apache (not sure how to look up the version - it's using Ensim Site Administrator 3.1.9-2)
PHP version: php 4.2.2
MySQL version: mysql 3.23.54
3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
4. if possible how mysql was compiled/installed. Not sure on this since it was done by the host company.
5. your top stats. Right now the board is experiencing a lot of heavy traffic and these are the numbers.
6:25pm up 1:11, 1 user, load average: 16.16, 10.08, 8.05
210 processes: 187 sleeping, 21 running, 2 zombie, 0 stopped
CPU states: 65.3% user, 10.8% system, 7.6% nice, 16.1% idle
Mem: 506024K av, 433960K used, 72064K free, 0K shrd, 3576K buff
Swap: 1020116K av, 0K used, 1020116K free 176384K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
5045 root 16 0 1156 1152 832 R 13.3 0.2 0:00 top
5025 apache 15 0 10732 10M 8700 S 9.1 2.1 0:00 httpd
5023 apache 15 0 10968 10M 8736 S 8.3 2.1 0:00 httpd
1160 apache 16 0 11512 11M 8672 S 4.9 2.2 0:47 httpd
2527 apache 15 0 10816 10M 8700 S 4.9 2.1 0:36 httpd
5007 apache 16 0 10748 10M 8708 S 4.9 2.1 0:00 httpd
4286 apache 16 0 11240 10M 8736 S 4.1 2.2 0:09 httpd
1131 apache 16 0 11308 11M 8760 S 3.3 2.2 0:38 httpd
4313 apache 16 0 11176 10M 8708 R 3.3 2.2 0:08 httpd
2523 mysql 25 10 24364 23M 2216 S N 2.4 4.8 0:06 mysqld
2526 apache 15 0 11476 11M 8708 S 2.4 2.2 0:32 httpd
4572 apache 16 0 11208 10M 8764 S 2.4 2.2 0:04 httpd
5024 apache 15 0 0 0 0 Z 2.4 0.0 0:00 httpd <defunct>
1159 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:06 mysqld
1404 apache 15 0 11160 10M 8676 S 1.6 2.2 0:44 httpd
2522 apache 15 0 11212 10M 8748 S 1.6 2.2 0:38 httpd
2541 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:04 mysqld
4637 apache 15 0 10804 10M 8748 S 1.6 2.1 0:03 httpd
5004 apache 16 0 10980 10M 8736 S 1.6 2.1 0:00 httpd
5014 mysql 25 10 24364 23M 2216 S N 1.6 4.8 0:00 mysqld
5030 apache 15 0 10828 10M 8732 S 1.6 2.1 0:00 httpd
1215 mysql 25 10 24364 23M 2216 R N 0.8 4.8 0:06 mysqld
1410 mysql 26 10 24364 23M 2216 R N 0.8 4.8 0:06 mysqld
2525 apache 15 0 10944 10M 8720 S 0.8 2.1 0:33 httpd
4312 apache 15 0 10940 10M 8764 S 0.8 2.1 0:11 httpd
4314 apache 15 0 10780 10M 8764 S 0.8 2.1 0:07 httpd
4351 mysql 25 10 24364 23M 2216 R N 0.8 4.8 0:01 mysqld
4640 apache 15 0 11216 10M 8764 S 0.8 2.2 0:04 httpd
4991 apache 16 0 10772 10M 8752 S 0.8 2.1 0:01 httpd
5005 apache 16 0 10948 10M 8736 S 0.8 2.1 0:00 httpd
5008 apache 16 0 0 0 0 Z 0.8 0.0 0:00 httpd <defunct>
5015 mysql 25 10 24364 23M 2216 S N 0.8 4.8 0:00 mysqld
5026 mysql 25 10 24364 23M 2216 S N 0.8 4.8 0:00 mysqld
1 root 15 0 512 512 440 S 0.0 0.1 0:05 init
2 root 15 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 15 0 0 0 0 SW 0.0 0.0 0:00 kapmd
4 root 34 19 0 0 0 SWN 0.0 0.0 0:00 ksoftirqd_CPU0
5 root 15 0 0 0 0 SW 0.0 0.0 0:00 kswapd
6 root 25 0 0 0 0 SW 0.0 0.0 0:00 bdflush
7 root 15 0 0 0 0 SW 0.0 0.0 0:00 kupdated
8 root 25 0 0 0 0 SW 0.0 0.0 0:00 mdrecoveryd
12 root 15 0 0 0 0 SW 0.0 0.0 0:01 kjournald
89 root 15 0 0 0 0 SW 0.0 0.0 0:00 khubd
212 root 15 0 0 0 0 SW 0.0 0.0 0:00 kjournald
541 root 15 0 0 0 0 SW 0.0 0.0 0:00 eth0
754 root 15 0 584 584 488 S 0.0 0.1 0:00 syslogd
765 root 15 0 1116 1116 452 S 0.0 0.2 0:00 klogd
829 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
831 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
833 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
834 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
835 named 15 0 2392 2392 1824 S 0.0 0.4 0:00 named
852 root 15 0 1444 1444 1304 S 0.0 0.2 0:00 sshd
872 root 19 0 1244 1244 1044 S 0.0 0.2 0:00 sshd
906 root 18 0 932 932 756 S 0.0 0.1 0:00 xinetd
926 root 18 0 1016 1016 836 S 0.0 0.2 0:00 safe_mysqld
951 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
953 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
954 mysql 25 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
955 mysql 30 10 24364 23M 2216 S N 0.0 4.8 0:00 mysqld
991 root 15 0 2552 2552 1880 S 0.0 0.5 0:00 sendmail
1059 root 15 0 8768 8768 8572 S 0.0 1.7 0:03 httpd
1119 postgres 15 0 1672 1672 1536 S 0.0 0.3 0:00 postmaster
1123 apache 15 0 8240 8240 7800 S 0.0 1.6 0:00 httpd
1125 root 17 0 300 300 240 S 0.0 0.0 0:00 cronolog
6. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type
mysqladmin -u root -p variables
copy and paste output here
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 32768
| bdb_home | /var/lib/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (December 5, 2002)
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 | concurrent_insert
| ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innodb | NO
| have_isam | YES
| have_raid | NO
| have_openssl | NO
| init_file |
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 8388600
| language | /usr/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_long_queries | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 100
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_recover_options | 0
| myisam_sort_buffer_size | 8388608
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 131072
| record_rnd_buffer | 131072
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer | 2097144
| sql_mode | 0
| table_cache | 64
| table_type | MYISAM
| thread_cache_size | 0
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | CDT
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 3.23.54
| wait_timeout | 28800
7. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
http://207.44.140.146/php3/admin/mysqlinfo.php (http://207.44.140.146/php3/admin/mysqlinfo.php)
8. vB is the only thing on the server.
9. how many average and max concurrent users on your vB forum ? The board regularly has around 200 people and during peak times gets around 250-350 people and that's when the connection problems start.
10. http://207.44.140.146/php3/phpinfo.php (http://207.44.140.146/php3/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 150
12. what version of vB are you running ? vB 2.2.9