MySQL Connection Problems - Too Many Open Connections?

[kevjkel]

if nothing else changed, before the problems and now, a database optimizing could be helpfull or if needed a repair.
If the forum has more visitors, you can increase the XXX value in my.cnf file in the root/etc directory.
max_connections = XXX
max_user_connections = XXX

I am having problems with users telling me sometimes my site is down. Sometimes when i want to access a certain page on my site it takes ages to load or does not load at all !
I contacted my provider and this is what they told me:
"The websit appears to work fine, however, you appear to be using a database and it's likely that your scripting or traffic is leaving too many connections open.Please first check your scripting to make sure that you are not leaving connections open too long as it will preven t people from seeing the website when it cannot connect."
Can anyone tell me how to NOT let connections stay open for too long?
[/]

[青山 素子]

Make sure persistent connections are turned off (disabled/unchecked) in the Server Settings page of your Admin. This is the default.

If that is unchecked, then SMF closes the database connection after each page load.


Also, I split the topic as the one you posted on was over a year old.

[kevjkel]

Thanks for the reply Motoko
But i have allways had this setting turned off. I also sometimes get an email from SMF
"There has been a problem with the database!
This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues."

Because i have this setting turned off, is there anything else that could be causing this problem?

[青山 素子]

Any database-heavy modifications could cause extra load, but they should close the database as well. What kind of modifications are you using?

[kevjkel]

I am using
   Default Avatar      1.1.1      
   Invite    1.14    
   Admin Notepad    1.0
   Notify of posts and replys by default    1.0    
   Global Headers Footers    1.4.1        
        Registration - Show Gender mod    1.0.0    
   SMF Gallery Lite    1.8.3    
   Enhanced Calendar    1.1    
   Favicon    1.2    
   Anti-Spam Verification Questions    1.02    
   Popular icons under signature    1.0    
   SMF Links    1.7.2    
   Automatic_Karma    1.0    
   Affiliates    1.1    
   Add Domaintools to TrackIP    1.0    
   Auto Merge Double Post    1.2    
   SMF Sitemap    1.2.2    
   AddThis Social Bookmarks    1.0    
   Spam Me Not Mod    1.02    
   Hide SMF Version    1.0.3b    
   AvatarSelect    1.1.2    
   Registration redirection    0.1
   EmailValidator    1.0    
   Custom Tab    1.2    
   Ad Managment    2.3    
   Profil Moderator Managment    1.0.0

I dont know if this helps but when i look at my tables in phpMyAdmin i have some AJAX CHAT tables there even though i uninstalled it long ago.

[Something like that]

If you uninstalled AJAX Chat, you can safely remove those tables. However, it has nothing to do with the issue you're having.

[SlammedDime]

If you have a busy forum.... this mod could EASILY cause issues... hence why we NEVER recommend installing such a thing.

Notify of posts and replys by default    1.0   

[kevjkel]

Any database-heavy modifications could cause extra load, but they should close the database as well. What kind of modifications are you using?

Thanks for the replies..
Ive given you my mods that i have installed. I only have around 10 members. Here are the stats for my site, there are a couple of numbers that look a bit high.    Such as : Opened vs. Open tables:
(table_cache)    4053074.0469 (should be <= 80)
Host: http://www.streamline.net/ ( Power User account)

Code Select Expand


Basic Information
April 14, 2009, 10:10:42 PM
Operating System: Red Hat Enterprise Linux Server release 5.2 (Tikanga)
Processor: Intel® Xeon® CPU E5405 @ 2.00GHz (1995.065MHz)
Load averages: 2.15, 2.33, 2.57
Memory usage: 89.653% (3716504k / 4145424k)
Swap: 3.97% (41448k / 1044144k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 8.7682
Kilobytes received per second: 0.4984
Kilobytes sent per second: 0.9179
Queries per second: 250.2791
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 4047292.6563 (should be <= 80)
Table cache usage:
(table_cache) 1 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.0106 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.0986 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1.0001 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.3112 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0 (should be <= 0.05)

MySQL status
Aborted_clients: 54094
Aborted_connects: 198131
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 1873808083
Bytes_sent: 3450677344
Com_admin_commands: 8506750
Com_alter_db: 622
Com_alter_table: 66008
Com_analyze: 2282
Com_backup_table: 0
Com_begin: 287113
Com_call_procedure: 7247
Com_change_db: 53305607
Com_change_master: 0
Com_check: 12661
Com_checksum: 0
Com_commit: 286754
Com_create_db: 10846
Com_create_function: 0
Com_create_index: 3667
Com_create_table: 413202
Com_create_user: 7
Com_dealloc_sql: 402
Com_delete: 21968850
Com_delete_multi: 30981
Com_do: 0
Com_drop_db: 1034
Com_drop_function: 14
Com_drop_index: 1274
Com_drop_table: 84151
Com_drop_user: 1
Com_execute_sql: 509
Com_flush: 1029
Com_grant: 7000
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 52
Com_insert: 30456962
Com_insert_select: 137657
Com_kill: 164
Com_load: 5294
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 465653
Com_optimize: 56937
Com_preload_keys: 0
Com_prepare_sql: 509
Com_purge: 0
Com_purge_before_date: 0
Com_rename_table: 5399
Com_repair: 100
Com_replace: 6614726
Com_replace_select: 1493
Com_reset: 0
Com_restore_table: 0
Com_revoke: 6412
Com_revoke_all: 0
Com_rollback: 8176
Com_savepoint: 1136
Com_select: 682094927
Com_set_option: 24078463
Com_show_binlog_events: 0
Com_show_binlogs: 19724
Com_show_charsets: 163778
Com_show_collations: 221309
Com_show_column_types: 0
Com_show_create_db: 258
Com_show_create_table: 159855
Com_show_databases: 3264
Com_show_errors: 0
Com_show_fields: 1831588
Com_show_grants: 66581
Com_show_innodb_status: 181817
Com_show_keys: 96889
Com_show_logs: 0
Com_show_master_status: 36
Com_show_ndb_status: 0
Com_show_new_master: 0
Com_show_open_tables: 5
Com_show_privileges: 7
Com_show_processlist: 2872
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 183371
Com_show_storage_engines: 4306
Com_show_tables: 3409909
Com_show_triggers: 3309
Com_show_variables: 623596
Com_show_warnings: 9625
Com_slave_start: 0
Com_slave_stop: 0
Com_stmt_close: 50933
Com_stmt_execute: 51195
Com_stmt_fetch: 0
Com_stmt_prepare: 51285
Com_stmt_reset: 0
Com_stmt_send_long_data: 0
Com_truncate: 31589
Com_unlock_tables: 470219
Com_update: 56030444
Com_update_multi: 34607
Com_xa_commit: 0
Com_xa_end: 0
Com_xa_prepare: 0
Com_xa_recover: 0
Com_xa_rollback: 0
Com_xa_start: 0
Compression: OFF
Connections: 32190581
Created_tmp_disk_tables: 14707484
Created_tmp_files: 9783
Created_tmp_tables: 47259751
Delayed_errors: 0
Delayed_insert_threads: 1
Delayed_writes: 37678
Flush_commands: 1
Handler_commit: 3344243
Handler_delete: 16765504
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 135913039
Handler_read_key: 492720393
Handler_read_next: 2256866504
Handler_read_prev: 191299482
Handler_read_rnd: 3186953269
Handler_read_rnd_next: 4073567462
Handler_rollback: 29131
Handler_savepoint: 1136
Handler_savepoint_rollback: 0
Handler_update: 1543629318
Handler_write: 2522369887
Innodb_buffer_pool_pages_data: 511
Innodb_buffer_pool_pages_dirty: 4
Innodb_buffer_pool_pages_flushed: 11013755
Innodb_buffer_pool_pages_free: 0
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 1
Innodb_buffer_pool_pages_total: 512
Innodb_buffer_pool_read_ahead_rnd: 2141748
Innodb_buffer_pool_read_ahead_seq: 16485424
Innodb_buffer_pool_read_requests: 4291140875
Innodb_buffer_pool_reads: 34405701
Innodb_buffer_pool_wait_free: 235
Innodb_buffer_pool_write_requests: 1807233146
Innodb_data_fsyncs: 2966095
Innodb_data_pending_fsyncs: 1
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 726568960
Innodb_data_reads: 56692944
Innodb_data_writes: 7114546
Innodb_data_written: 2226405888
Innodb_dblwr_pages_written: 11013755
Innodb_dblwr_writes: 410410
Innodb_log_waits: 1626
Innodb_log_write_requests: 322168404
Innodb_log_writes: 2152235
Innodb_os_log_fsyncs: 2148543
Innodb_os_log_pending_fsyncs: 1
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 1949834240
Innodb_page_size: 16384
Innodb_pages_created: 6590747
Innodb_pages_read: 314092731
Innodb_pages_written: 11013755
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 303393
Innodb_row_lock_time_avg: 1280
Innodb_row_lock_time_max: 51320
Innodb_row_lock_waits: 237
Innodb_rows_deleted: 4009377
Innodb_rows_inserted: 825004626
Innodb_rows_read: 4092230410
Innodb_rows_updated: 967763
Key_blocks_not_flushed: 0
Key_blocks_unused: 7073
Key_blocks_used: 7248
Key_read_requests: 99618113205
Key_reads: 1059146339
Key_write_requests: 1412868578
Key_writes: 139321112
Last_query_cost: 0.000000
Max_used_connections: 668
Not_flushed_delayed_rows: 0
Open_files: 121
Open_streams: 0
Open_tables: 64
Opened_tables: 259026730
Prepared_stmt_count: 0
Qcache_free_blocks: 0
Qcache_free_memory: 0
Qcache_hits: 0
Qcache_inserts: 0
Qcache_lowmem_prunes: 0
Qcache_not_cached: 0
Qcache_queries_in_cache: 0
Qcache_total_blocks: 0
Questions: 918843047
Rpl_status: NULL
Select_full_join: 15511189
Select_full_range_join: 602431
Select_range: 40483841
Select_range_check: 1102752
Select_scan: 148310294
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 18341
Sort_merge_passes: 5663
Sort_range: 45780790
Sort_rows: 675165102
Sort_scan: 69963193
Ssl_accept_renegotiates: 0
Ssl_accepts: 0
Ssl_callback_cache_hits: 0
Ssl_cipher:
Ssl_cipher_list:
Ssl_client_connects: 0
Ssl_connect_renegotiates: 0
Ssl_ctx_verify_depth: 0
Ssl_ctx_verify_mode: 0
Ssl_default_timeout: 0
Ssl_finished_accepts: 0
Ssl_finished_connects: 0
Ssl_session_cache_hits: 0
Ssl_session_cache_misses: 0
Ssl_session_cache_mode: NONE
Ssl_session_cache_overflows: 0
Ssl_session_cache_size: 0
Ssl_session_cache_timeouts: 0
Ssl_sessions_reused: 0
Ssl_used_session_cache_entries: 0
Ssl_verify_depth: 0
Ssl_verify_mode: 0
Ssl_version:
Table_locks_immediate: 1037606111
Table_locks_waited: 358208
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 0
Threads_connected: 61
Threads_created: 32186947
Threads_running: 8
Uptime: 3671273
Uptime_since_flush_status: 3671273

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /usr/
bdb_cache_size: 8388600
bdb_home: /home/mysql/
bdb_log_buffer_size: 32768
bdb_logdir:
bdb_max_lock: 10000
bdb_shared_data: OFF
bdb_tmpdir: /home/tmpdir/
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set_client: latin1
character_set_connection: latin1
character_set_database: latin1
character_set_filesystem: binary
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci
completion_type: 0
concurrent_insert: 1
connect_timeout: 5
datadir: /home/mysql/
date_format: %Y-%m-%d
datetime_format: %Y-%m-%d %H:%i:%s
default_week_format: 0
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
div_precision_increment: 4
engine_condition_pushdown: OFF
expire_logs_days: 0
flush: OFF
flush_time: 0
ft_boolean_syntax: + -><()~*:""&|
ft_max_word_len: 84
ft_min_word_len: 4
ft_query_expansion_limit: 20
ft_stopword_file: (built-in)
group_concat_max_len: 1024
have_archive: NO
have_bdb: YES
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_dynamic_loading: YES
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: NO
have_openssl: DISABLED
have_ssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
hostname: mysql15.streamline.net
init_connect:
init_file:
init_slave:
innodb_additional_mem_pool_size: 1048576
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 8388608
innodb_checksums: ON
innodb_commit_concurrency: 0
innodb_concurrency_tickets: 500
innodb_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_doublewrite: ON
innodb_fast_shutdown: 1
innodb_file_io_threads: 4
innodb_file_per_table: OFF
innodb_flush_log_at_trx_commit: 1
innodb_flush_method:
innodb_force_recovery: 0
innodb_lock_wait_timeout: 50
innodb_locks_unsafe_for_binlog: OFF
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_open_files: 300
innodb_rollback_on_timeout: OFF
innodb_support_xa: ON
innodb_sync_spin_loops: 20
innodb_table_locks: ON
innodb_thread_concurrency: 8
innodb_thread_sleep_delay: 10000
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 8388600
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/share/mysql/english/
large_files_support: ON
large_page_size: 0
large_pages: OFF
lc_time_names: en_US
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error:
log_queries_not_using_indexes: OFF
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 1
long_query_time: 10
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 1048576
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 999999999
max_connections: 1400
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 16777216
max_insert_delayed_threads: 20
max_join_size: 4294967295
max_length_for_sort_data: 1024
max_prepared_stmt_count: 16382
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_sp_recursion_depth: 0
max_tmp_tables: 32
max_user_connections: 0
max_write_lock_count: 4294967295
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 2147483647
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
old_passwords: ON
open_files_limit: 7000
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /var/run/mysqld/mysqld.pid
port: 3306
preload_buffer_size: 32768
profiling: OFF
profiling_history_size: 15
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 0
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 131072
read_only: OFF
read_rnd_buffer_size: 262144
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
server_id: 0
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slave_compressed_protocol: OFF
slave_load_tmpdir: /home/tmpdir/
slave_net_timeout: 3600
slave_skip_errors: OFF
slave_transaction_retries: 10
slow_launch_time: 2
socket: /home/mysql/mysql.sock
sort_buffer_size: 2097144
sql_big_selects: ON
sql_mode:
sql_notes: ON
sql_warnings: OFF
ssl_ca:
ssl_capath:
ssl_cert:
ssl_cipher:
ssl_key:
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
system_time_zone: GMT
table_cache: 64
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 0
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: /home/tmpdir
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.45-log
version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (July 4, 2007)
version_comment: Source distribution
version_compile_machine: i686
version_compile_os: redhat-linux-gnu
wait_timeout: 28800

[Something like that]

Any database-heavy modifications could cause extra load, but they should close the database as well. What kind of modifications are you using?

Thanks for the replies..
Ive given you my mods that i have installed. I only have around 10 members. Here are the stats for my site, there are a couple of numbers that look a bit high.    Such as : Opened vs. Open tables:
(table_cache)    4053074.0469 (should be <= 80)
Host: http://www.streamline.net/ ( Power User account)

Your host never tuned MySQL (those configs are horrible), but your forum is so small it won't affect you.

[kevjkel]

Do you think that i have to change hosts...cause when my site grows surely the server will let me down?
Also what do you mean that "Your host never tuned MySQL "
Thanks for the help

[SlammedDime]

It looks like they installed mysql, and used it's default configuration, which on a shared host, is probably the worst thing you can do.

[Something like that]

It looks like they installed mysql, and used it's default configuration, which on a shared host, is probably the worst thing you can do.

Yep. I could easily double the performance of their server in five minutes... but it's not mine to administer.

[kevjkel]

Ive contacted them to see what they can do ...
Ill see what reply i get and if they cannot sort it out i might have to move to another provider.
Do you perhaps know of any good ones i could move to?

[Something like that]

There are a few providers that specialize in hosting SMF. Put a request in the Hosting Requests board

[kevjkel]

Many thanks
I changed hosts and all is well now!!