GIGJ.COM
welcome to my space |
HOME  Optimization request, mysql_connect(): Too many connections |
Optimization request, mysql_connect(): Too many connections | | Published by: wktd 2010-03-17 |
| | My vB installation uses about 2 - 10 MySQL connections during peak times, but sometimes the forum freaks out and hits the limit (400 connections) resulting in huge server-load, and mysql_connect(): Too many connections error.
Yes I've $config['MasterServer']['usepconnect'] = 0;
What could be the cause of this problem? You can find the server settings below.
1. Is this on dedicated or shared virual server. If shared, how many sites share this server.
Dedicated, just one site
2. your server specs.
cpu speed/type single or dual cpus): dual p4 2.8GHz
how much memory installed: 512mb ram
hard drive type/configuration: 80GB IDE non raid
linux distributor or windows version: linux
apache/IIS version: apache 1.3.37 (cgi-fcgi)
PHP version: php 5.1.6
MySQL version: mysql 4.0.27-standard
3. what version of vB are you running?
vB 3.6.4
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server?
No idea.
5. if possible how mysql was compiled/installed
No idea.
6. your top stats
1:32pm up 78 days, 20:22, 1 user, load average: 2.21, 2.12, 1.79
28 processes: 26 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 22.2% user, 13.0% system, 6.0% nice, 63.2% idle
CPU1 states: 24.0% user, 0.1% system, 0.1% nice, 75.0% idle
Mem: 2067712K av, 2021820K used, 45892K free, 0K shrd, 64740K buff
Swap: 1999984K av, 108436K used, 1891548K free 917856K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
32235 byggahus 10 0 21436 20M 10584 S 19.2 1.0 1:51 php-fcgi5
4585 byggahus 9 0 17840 17M 10364 S 9.4 0.8 0:06 php-fcgi5
22843 byggahus 11 0 18300 17M 10332 S 3.4 0.8 0:08 php-fcgi5
23391 byggahus 9 0 18304 17M 10352 S 3.1 0.8 0:04 php-fcgi5
26189 byggahus 10 0 18264 17M 10340 S 1.7 0.8 0:06 php-fcgi5
539 byggahus 9 0 18120 17M 10400 S 1.3 0.8 0:04 php-fcgi5
23430 byggahus 10 0 1088 1088 888 R 0.3 0.0 0:00 top
1065 byggahus 9 0 10208 9M 9004 S 0.0 0.4 0:00 php-fcgi5
7492 byggahus 9 0 8996 8996 8260 S 0.0 0.4 0:00 php-fcgi4
5810 byggahus 9 0 13772 13M 9008 S 0.0 0.6 0:00 php-fcgi4
14461 byggahus 9 0 13772 13M 9008 S 0.0 0.6 0:00 php-fcgi4
19042 byggahus 9 0 13768 13M 9008 S 0.0 0.6 0:00 php-fcgi4
244 byggahus 9 0 13508 13M 8964 S 0.0 0.6 0:00 php-fcgi4
32690 byggahus 9 0 13508 13M 8964 S 0.0 0.6 0:00 php-fcgi4
3880 byggahus 9 0 20736 20M 10408 S 0.0 1.0 2:05 php-fcgi5
10620 byggahus 9 0 19716 19M 10260 S 0.0 0.9 1:59 php-fcgi5
7801 byggahus 9 0 21464 20M 10440 S 0.0 1.0 1:21 php-fcgi5
31167 byggahus 9 0 18244 17M 10260 S 0.0 0.8 0:48 php-fcgi5
24277 byggahus 9 0 1544 1540 1248 S 0.0 0.0 0:00 ipop3d
6950 byggahus 9 0 10200 9M 9236 S 0.0 0.4 0:00 php-fcgi5
12384 byggahus 9 0 10208 9M 9204 S 0.0 0.4 0:00 php-fcgi5
24288 byggahus 9 0 18252 17M 10360 S 0.0 0.8 0:07 php-fcgi5
26695 byggahus 9 0 18324 17M 10380 S 0.0 0.8 0:05 php-fcgi5
1212 byggahus 9 0 18348 17M 10352 S 0.0 0.8 0:06 php-fcgi5
27932 byggahus 9 0 18308 17M 10348 S 0.0 0.8 0:05 php-fcgi5
7336 byggahus 9 0 18548 18M 10384 S 0.0 0.8 0:05 php-fcgi5
2632 byggahus 9 0 1892 1892 1724 R 0.0 0.0 0:00 sshd
18781 byggahus 9 0 1284 1284 1028 S 0.0 0.0 0:00 bash
7. your mysql configuration variables located at /etc/my.cnf or c:my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
back log 100
basedir /usr/local/
binlog cache size 32 768
bulk insert buffer size 8 388 608
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 /usr/local/mysql/data/
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1 000
flush OFF
flush time 0
ft boolean syntax + -><()~*:""&
ft max word len 254
ft max word len for sort 20
ft min word len 4
ft stopword file (built-in)
have bdb NO
have crypt YES
have innodb DISABLED
have isam YES
have openssl NO
have query cache YES
have raid YES
have symlink YES
init file
innodb additional mem pool size 1 048 576
innodb autoextend increment 8
innodb buffer pool size 8 388 608
innodb data file path
innodb data home dir
innodb fast shutdown ON
innodb file io threads 4
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb log arch dir
innodb log archive OFF
innodb log buffer size 1 048 576
innodb log file size 5 242 880
innodb log files in group 2
innodb log group home dir
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb table locks ON
innodb thread concurrency 8
interactive timeout 28 800
join buffer size 520 192
key buffer size 67 108 864
language /usr/local/share/mysql/english/
large files support ON
license GPL
local infile ON
locked in memory OFF
log OFF
log bin OFF
log error
log slave updates OFF
log slow queries OFF
log update OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 16 776 192
max binlog cache size 4 294 967 295
max binlog size 1 073 741 824
max connect errors 10 000
max connections 800
max delayed threads 20
max heap table size 16 777 216
max insert delayed threads 20
max join size 4 294 967 295
max relay log size 0
max seeks for key 4 294 967 295
max sort length 1 024
max tmp tables 32
max user connections 400
max write lock count 4 294 967 295
myisam max extra sort file size 268 435 456
myisam max sort file size 2 147 483 647
myisam recover options BACKUP,FORCE
myisam repair threads 1
myisam sort buffer size 67 108 864
net buffer length 16 384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
open files limit 65 535
pid file /usr/local/mysql/data/dedi6.pid
port 3 306
protocol version 10
query alloc block size 8 192
query cache limit 1 048 576
query cache size 33 554 432
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8 192
range alloc block size 2 048
read buffer size 2 093 056
read only OFF
read rnd buffer size 262 144
rpl recovery rank 0
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave net timeout 3 600
slow launch time 2
socket /tmp/mysql.sock
sort buffer size 8 388 600
sql mode 0
table cache 256
table type MYISAM
thread cache size 8
thread stack 126 976
timezone CET
tmp table size 33 554 432
tmpdir /tmp/
transaction alloc block size 8 192
transaction prealloc size 4 096
tx isolation REPEATABLE-READ
version 4.0.27-standard
version comment Official MySQL-standard binary
version compile os pc-linux-gnu
wait timeout 28 800
8. MySQL stats from ssh telnet as root user type:
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Variable_name Value
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
back_log 100
basedir /usr/local/
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 /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_max_word_len 254
ft_max_word_len_for_sort 20
ft_min_word_len 4
ft_stopword_file (built-in)
have_bdb NO
have_crypt YES
have_innodb DISABLED
have_isam YES
have_openssl NO
have_query_cache YES
have_raid YES
have_symlink 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_fast_shutdown ON
innodb_file_io_threads 4
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
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_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_table_locks ON
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 520192
key_buffer_size 67108864
language /usr/local/share/mysql/english/
large_files_support ON
license GPL
local_infile ON
locked_in_memory OFF
log OFF
log_bin OFF
log_error
log_slave_updates OFF
log_slow_queries OFF
log_update OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 16776192
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10000
max_connections 800
max_delayed_threads 20
max_heap_table_size 16777216
max_insert_delayed_threads 20
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 400
max_write_lock_count 4294967295
myisam_max_extra_sort_file_size 268435456
myisam_max_sort_file_size 2147483647
myisam_recover_options BACKUP,FORCE
myisam_repair_threads 1
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 65535
pid_file /usr/local/mysql/data/dedi6.pid
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 33554432
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 2093056
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 0
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_net_timeout 3600
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 8388600
sql_mode 0
table_cache 256
table_type MYISAM
thread_cache_size 8
thread_stack 126976
timezone CET
tmp_table_size 33554432
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
version 4.0.27-standard
version_comment Official MySQL-standard binary
version_compile_os pc-linux-gnu
wait_timeout 28800
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+--------------------------+------------+
Variable_name Value
+--------------------------+------------+
Aborted_clients 216
Aborted_connects 411
Bytes_received 1982576919
Bytes_sent 4084785017
Com_admin_commands 0
Com_alter_table 1
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 735713
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 0
Com_delete 17303
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 2382
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 223462
Com_insert_select 0
Com_kill 6
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 2382
Com_optimize 1
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 63040
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 2643610
Com_set_option 539
Com_show_binlog_events 0
Com_show_binlogs 9
Com_show_create 0
Com_show_databases 5
Com_show_fields 5
Com_show_grants 8
Com_show_innodb_status 4939
Com_show_keys 2
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 8
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 4943
Com_show_tables 563
Com_show_variables 593
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 2921
Com_update 578784
Com_update_multi 0
Connections 722913
Created_tmp_disk_tables 1201
Created_tmp_files 0
Created_tmp_tables 448326
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 88182
Handler_read_first 764735
Handler_read_key 52474246
Handler_read_next 141541864
Handler_read_prev 5093379
Handler_read_rnd 9416571
Handler_read_rnd_next 929402345
Handler_rollback 0
Handler_update 687888
Handler_write 4015785
Key_blocks_used 62345
Key_read_requests 279503290
Key_reads 882400
Key_write_requests 571426
Key_writes 513650
Max_used_connections 392
Not_flushed_delayed_rows 0
Not_flushed_key_blocks 0
Open_files 314
Open_streams 0
Open_tables 185
Opened_tables 19881
Qcache_free_blocks 155
Qcache_free_memory 24666296
Qcache_hits 1966233
Qcache_inserts 2436120
Qcache_lowmem_prunes 114157
Qcache_not_cached 207443
Qcache_queries_in_cache 1738
Qcache_total_blocks 3824
Questions 6969954
Rpl_status NULL
Select_full_join 21
Select_full_range_join 0
Select_range 894384
Select_range_check 0
Select_scan 348248
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 157
Slow_queries 10690
Sort_merge_passes 0
Sort_range 910009
Sort_rows 10691443
Sort_scan 39118
Table_locks_immediate 6845104
Table_locks_waited 30090
Threads_cached 7
Threads_connected 3
Threads_created 14016
Threads_running 1
Uptime 274450
+--------------------------+------------+
Uptime: 274450 Threads: 3 Questions: 6969955 Slow queries: 10690 Opens: 19881 Flush tables: 1 Open tables: 185 Queries per second avg: 25.396
mysqladmin Ver 8.40 Distrib 4.0.27, for pc-linux-gnu on i686
(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.27-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 3 days 4 hours 14 min 10 sec
Threads: 3 Questions: 6969955 Slow queries: 10690 Opens: 19881 Flush tables: 1 Open tables: 185 Queries per second avg: 25.396
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?
WordPress-blogg.
10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?
400 users. 900 seconds.
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://byggahus.se/forum/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 :
12. 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
Nope
13. Post output from these 3 commands
Linux dedi6 2.4.33.3-grsec #8 SMP Wed Sep 6 08:28:58 CEST 2006 i686 unknown
core file size (blocks) unlimited
data seg size (kbytes) unlimited
file size (blocks) unlimited
max locked memory (kbytes) unlimited
max memory size (kbytes) unlimited
open files 32768
pipe size (512 bytes) 8
stack size (kbytes) unlimited
cpu time (seconds) unlimited
max user processes unlimited
virtual memory (kbytes) unlimited
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 1
cpu MHz : 2800.200
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe pni monitor ds_cpl cid
bogomips : 5583.66
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 2.80GHz
stepping : 1
cpu MHz : 2800.200
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe pni monitor ds_cpl cid
bogomips : 5596.77
Yeah vBSEO isn't exactly light on cpu loads.
Sorry to bump in (not an expert by any means) but the vBSeo Plugin also plays a big role for sure, it drives the server crazy with mod rewrites. Unplug the vBSeo thingie and I can guarantee you will see the server load going down immediately. Also, the 400 users average with 900 seconds doesn't sound right with an Alexa ranking of 77K.
Quite simple you're using up to 392 mysql concurrent connections on a single P4 2.8ghz cpu server which has HT enabled so 2nd virtual cpu not real dual cpu server. Even if it was a dual cpu server, for that kind of traffic you should be already planning to split to 2x dual cpu server setups one for web and one server as a dedicated mysql server both with minimum of 2GB memory and SCSI disks on db server.
 Nortel Unveils Vision, Strategy for Israeli High-Performance Net
 Busy Friday Leads to Strong Close for Net Stocks
|
You are looking at:gigj.com's Optimization request, mysql_connect(): Too many connections, click gigj.com to home
|
#If you have any other info about this subject , Please add it free.# | |
|
