Help with php-code for server status.

[xwishmasterx]

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

[Jamdog]

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.

[xwishmasterx]

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^^

[PFMaBiSmAd]

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?

[xwishmasterx]

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

[Jamdog]

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.

[xwishmasterx]

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