xwishmasterx Posted May 22, 2012 Share Posted May 22, 2012 Hello I recently got a "to many connection" error on my site, and want to know if anyone here knows a few codes that will show how many connection currently are in use (maybe even what files that creates them). I found I can use "Threads_connected" to show current open connections, but no info on how to write the code or where to pu the file. I hope you can show my what to do Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/ Share on other sites More sharing options...
Jamdog Posted May 22, 2012 Share Posted May 22, 2012 Grabbing (and outputting) all the SQL Status values whould be done like: <?php $link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); $result = mysql_query('SHOW STATUS', $link); while ($row = mysql_fetch_assoc($result)) { echo $row['Variable_name'] . ' = ' . $row['Value'] . "\n"; } ?> So, I believe the SQL you want to just get Threads_connected would be something like: $query = "SHOW GLOBAL STATUS LIKE 'Threads_connected'"; Of course, monitoring the Threads_connected value won't stop the 'Too Many Connections' error, but if it's getting close to max, you may be able to just output a 'Sorry the site is currently busy, try later' type message instead. Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347514 Share on other sites More sharing options...
xwishmasterx Posted May 22, 2012 Author Share Posted May 22, 2012 Thanks for the reply. It seems the above does work as I get some results. Do you now how to read this? Here's the output: = 140 = 11075 = 0 = 0 = 88 = 77 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 1 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = OFF = 73294 = 0 = 5279 = 1 = 0 = 0 = 90 = 1 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 132 = 511 = 0 = 3237 = 0 = 1 = 512 = 3164 = 79 = 2576265 = 60151 = 0 = 12175 = 3126 = 0 = 0 = 0 = 1432735744 = 67332 = 5055 = 110339584 = 3237 = 675 = 0 = 7825 = 1355 = 1755 = 0 = 0 = 4065792 = 16384 = 85 = 87314 = 3237 = 0 = 14 = 4 = 14 = 3 = 248 = 441 = 373495 = 665 = 0 = 6470 = 1412 = 56677841 = 1075795 = 683738 = 97035 = 0.000000 = 101 = 0 = 129 = 0 = 64 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = 5889856 = 1 = NULL = 0 = 0 = 0 = 0 = 1 = 0 = 0 = OFF = 0 = 0 = 0 = 0 = 0 = 0 = 6157524 = 1181 = 0 = 0 = 0 = 0 = 15 = 73280 = 9 = 45742 Doesn't make much sense^^ Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347598 Share on other sites More sharing options...
PFMaBiSmAd Posted May 22, 2012 Share Posted May 22, 2012 The posted code will output the mysql variable name on the left of each value. Are you sure you didn't change the $row['Variable_name'] part of the code? Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347690 Share on other sites More sharing options...
xwishmasterx Posted May 22, 2012 Author Share Posted May 22, 2012 It seems I did. I got a much more decent result, however I am not familiar with any of these phrases. Can anyone see something that looks wrong. It this point only I have 1 member online on 7 guests on site. Result: Aborted_clients = 187 Aborted_connects = 15870 Binlog_cache_disk_use = 0 Binlog_cache_use = 0 Bytes_received = 88 Bytes_sent = 77 Com_admin_commands = 0 Com_alter_db = 0 Com_alter_table = 0 Com_analyze = 0 Com_backup_table = 0 Com_begin = 0 Com_call_procedure = 0 Com_change_db = 0 Com_change_master = 0 Com_check = 0 Com_checksum = 0 Com_commit = 0 Com_create_db = 0 Com_create_function = 0 Com_create_index = 0 Com_create_table = 0 Com_create_user = 0 Com_dealloc_sql = 0 Com_delete = 0 Com_delete_multi = 0 Com_do = 0 Com_drop_db = 0 Com_drop_function = 0 Com_drop_index = 0 Com_drop_table = 0 Com_drop_user = 0 Com_execute_sql = 0 Com_flush = 0 Com_grant = 0 Com_ha_close = 0 Com_ha_open = 0 Com_ha_read = 0 Com_help = 0 Com_insert = 0 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_preload_keys = 0 Com_prepare_sql = 0 Com_purge = 0 Com_purge_before_date = 0 Com_rename_table = 0 Com_repair = 0 Com_replace = 0 Com_replace_select = 0 Com_reset = 0 Com_restore_table = 0 Com_revoke = 0 Com_revoke_all = 0 Com_rollback = 0 Com_savepoint = 0 Com_select = 0 Com_set_option = 0 Com_show_binlog_events = 0 Com_show_binlogs = 0 Com_show_charsets = 0 Com_show_collations = 0 Com_show_column_types = 0 Com_show_create_db = 0 Com_show_create_table = 0 Com_show_databases = 0 Com_show_errors = 0 Com_show_fields = 0 Com_show_grants = 0 Com_show_innodb_status = 0 Com_show_keys = 0 Com_show_logs = 0 Com_show_master_status = 0 Com_show_ndb_status = 0 Com_show_new_master = 0 Com_show_open_tables = 0 Com_show_privileges = 0 Com_show_processlist = 0 Com_show_slave_hosts = 0 Com_show_slave_status = 0 Com_show_status = 1 Com_show_storage_engines = 0 Com_show_tables = 0 Com_show_triggers = 0 Com_show_variables = 0 Com_show_warnings = 0 Com_slave_start = 0 Com_slave_stop = 0 Com_stmt_close = 0 Com_stmt_execute = 0 Com_stmt_fetch = 0 Com_stmt_prepare = 0 Com_stmt_reset = 0 Com_stmt_send_long_data = 0 Com_truncate = 0 Com_unlock_tables = 0 Com_update = 0 Com_update_multi = 0 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 = 121771 Created_tmp_disk_tables = 0 Created_tmp_files = 25279 Created_tmp_tables = 1 Delayed_errors = 0 Delayed_insert_threads = 0 Delayed_writes = 130 Flush_commands = 1 Handler_commit = 0 Handler_delete = 0 Handler_discover = 0 Handler_prepare = 0 Handler_read_first = 0 Handler_read_key = 0 Handler_read_next = 0 Handler_read_prev = 0 Handler_read_rnd = 0 Handler_read_rnd_next = 0 Handler_rollback = 0 Handler_savepoint = 0 Handler_savepoint_rollback = 0 Handler_update = 0 Handler_write = 132 Innodb_buffer_pool_pages_data = 508 Innodb_buffer_pool_pages_dirty = 30 Innodb_buffer_pool_pages_flushed = 4273 Innodb_buffer_pool_pages_free = 0 Innodb_buffer_pool_pages_misc = 4 Innodb_buffer_pool_pages_total = 512 Innodb_buffer_pool_read_ahead_rnd = 4310 Innodb_buffer_pool_read_ahead_seq = 109 Innodb_buffer_pool_read_requests = 3372955 Innodb_buffer_pool_reads = 80971 Innodb_buffer_pool_wait_free = 0 Innodb_buffer_pool_write_requests = 24089 Innodb_data_fsyncs = 4134 Innodb_data_pending_fsyncs = 0 Innodb_data_pending_reads = 0 Innodb_data_pending_writes = 0 Innodb_data_read = 1936281600 Innodb_data_reads = 90764 Innodb_data_writes = 6669 Innodb_data_written = 145515008 Innodb_dblwr_pages_written = 4273 Innodb_dblwr_writes = 851 Innodb_log_waits = 0 Innodb_log_write_requests = 9921 Innodb_log_writes = 1868 Innodb_os_log_fsyncs = 2398 Innodb_os_log_pending_fsyncs = 0 Innodb_os_log_pending_writes = 0 Innodb_os_log_written = 5227520 Innodb_page_size = 16384 Innodb_pages_created = 280 Innodb_pages_read = 118048 Innodb_pages_written = 4273 Innodb_row_lock_current_waits = 0 Innodb_row_lock_time = 14 Innodb_row_lock_time_avg = 4 Innodb_row_lock_time_max = 14 Innodb_row_lock_waits = 3 Innodb_rows_deleted = 259 Innodb_rows_inserted = 1464 Innodb_rows_read = 562494 Innodb_rows_updated = 1578 Key_blocks_not_flushed = 0 Key_blocks_unused = 6258 Key_blocks_used = 1564 Key_read_requests = 114892246 Key_reads = 1886608 Key_write_requests = 931175 Key_writes = 134836 Last_query_cost = 0.000000 Max_used_connections = 101 Not_flushed_delayed_rows = 0 Open_files = 112 Open_streams = 0 Open_tables = 64 Opened_tables = 0 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 Queries = 10138860 Questions = 1 Rpl_status = NULL Select_full_join = 0 Select_full_range_join = 0 Select_range = 0 Select_range_check = 0 Select_scan = 1 Slave_open_temp_tables = 0 Slave_retried_transactions = 0 Slave_running = OFF Slow_launch_threads = 0 Slow_queries = 0 Sort_merge_passes = 0 Sort_range = 0 Sort_rows = 0 Sort_scan = 0 Table_locks_immediate = 10385198 Table_locks_waited = 2778 Tc_log_max_pages_used = 0 Tc_log_page_size = 0 Tc_log_page_waits = 0 Threads_cached = 0 Threads_connected = 41 Threads_created = 121750 Threads_running = 36 Uptime = 69404 Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347769 Share on other sites More sharing options...
Jamdog Posted May 22, 2012 Share Posted May 22, 2012 Are you on a shared server? You said that you only have 8 people showing on your site, but you get the following, which all seem higher than they should be: Connections = 121771 Threads_connected = 41 Threads_created = 121750 Threads_running = 36 I don't know (and maybe someone else here could clarify) if SHOW STATUS gives results for the current SQL user, or for the SQL server (and therefore all users on the server). If your site host has a shared MySQL server, it could be possible that someone else has a busy website that's affecting yours. To explain the above values (as far as I understand them, but I'm not an expert): Uptime How long your server has been continually online (in seconds), so in your case, 19 hours, 16 minutes and 44 seconds. Connections How many times a connection has been made to the SQL server. Threads_connected How many threads (queries) are currently connected, usually a good indication of how busy the server is... Threads_created How many queries have been run since the server was last reset. This is usually slightly lower than 'Connections', as sometimes a connection is made without a query being done. Threads_running How many queries are currently being processed. This should be low (but at least 1, as your SHOW STATUS query will be counted) As a comparison, here's the info I just grabbed from my server: Connections 1012001 Threads_cached 52 Threads_connected 5 Threads_created 57 Threads_running 2 Uptime 478789 My uptime is 5½ days, so my 'connections' value is much higher, but as you can see, current threads (Threads_connected and Threads_running) are lower than yours. Sorry, this isn't my area of expertise, so I have no idea what's causing the problem or how to fix it. The SQL server being shared, and another site causing the problem is only a guess, but I can't think of anything else that might cause it. Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347821 Share on other sites More sharing options...
xwishmasterx Posted May 23, 2012 Author Share Posted May 23, 2012 thanks for posting your info for comparison. I got fed up with my cheap host who couldn't give me any clues, so just moved to a new host (still shared) and look at the stats now: Threads_connected = 4 Threads_created = 70 Threads_running = 1 Uptime = 4011023 Uptime_since_flush_status = 4011023 much better Quote Link to comment https://forums.phpfreaks.com/topic/262902-help-with-php-code-for-server-status/#findComment-1347836 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.