Mysql 5.0.16 - show status returns 0 for many variables
Posted by: Ravenous Bugblatter Beast
Date: December 16, 2005 02:20PM
Date: December 16, 2005 02:20PM
I have installed Mysql 5.0.16 on a server supporting a php web site.
-the site is written in php 4.3.8
-runs on apache 1.3
-does not use persistent connections
-database is on the same server
-php pages connect using a UNIX socket, not TCP
-mysql 3.23 is running on the same machine to support other sites.
-this single php site/database is the only user of mysql
-OS is FreeBSD 4.10
-I installed the FreeBSD binary version of mysql 5
-the site is designed so each page hit uses one database connection.
-there are 18 MYISAM tables and 6 HEAP tables.
-no table has more than 100,000 rows.
I've been trying to monitor activity on the server in order to optimise the site but I'm confused by the results of a show status command shown below. I was logged in as root to run the command but have also tried it using the account the PHP connects under.
These status variables all look correct for the level of site testing done so far:
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Compression | OFF |
| Connections | 12230 |
| Flush_commands | 1 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 18223 |
| Key_blocks_used | 4897 |
| Key_read_requests | 16754771 |
| Key_reads | 10599 |
| Key_write_requests | 223587 |
| Key_writes | 6046 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 93 |
| Open_streams | 0 |
| Open_tables | 62 |
| Opened_tables | 0 |
| Qcache_free_blocks | 101 |
| Qcache_free_memory | 7550576 |
| Qcache_hits | 16699 |
| Qcache_inserts | 16601 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 11278 |
| Qcache_queries_in_cache | 358 |
| Qcache_total_blocks | 848 |
| Questions | 99557 |
| Rpl_status | NULL |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Table_locks_immediate | 100307 |
| Table_locks_waited | 28 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 4 |
| Threads_running | 1 |
| Uptime | 158617 |
+-----------------------------------+----------+
However, the remainder of the status variables, shown below only seem to show activity for my diagnostic session using the mysql client - there are no stats from the connections from the php application. The mysql 3 server on the same server which is running an older version of the same PHP web application
shows believable values for all status variables.
Does anyone know why half of my status variables are not counting correctly?
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Bytes_received | 107 |
| Bytes_sent | 5603 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 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_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 | 2 |
| 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 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 18 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
+-----------------------------------+----------+
-the site is written in php 4.3.8
-runs on apache 1.3
-does not use persistent connections
-database is on the same server
-php pages connect using a UNIX socket, not TCP
-mysql 3.23 is running on the same machine to support other sites.
-this single php site/database is the only user of mysql
-OS is FreeBSD 4.10
-I installed the FreeBSD binary version of mysql 5
-the site is designed so each page hit uses one database connection.
-there are 18 MYISAM tables and 6 HEAP tables.
-no table has more than 100,000 rows.
I've been trying to monitor activity on the server in order to optimise the site but I'm confused by the results of a show status command shown below. I was logged in as root to run the command but have also tried it using the account the PHP connects under.
These status variables all look correct for the level of site testing done so far:
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Compression | OFF |
| Connections | 12230 |
| Flush_commands | 1 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 18223 |
| Key_blocks_used | 4897 |
| Key_read_requests | 16754771 |
| Key_reads | 10599 |
| Key_write_requests | 223587 |
| Key_writes | 6046 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 93 |
| Open_streams | 0 |
| Open_tables | 62 |
| Opened_tables | 0 |
| Qcache_free_blocks | 101 |
| Qcache_free_memory | 7550576 |
| Qcache_hits | 16699 |
| Qcache_inserts | 16601 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 11278 |
| Qcache_queries_in_cache | 358 |
| Qcache_total_blocks | 848 |
| Questions | 99557 |
| Rpl_status | NULL |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Table_locks_immediate | 100307 |
| Table_locks_waited | 28 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 4 |
| Threads_running | 1 |
| Uptime | 158617 |
+-----------------------------------+----------+
However, the remainder of the status variables, shown below only seem to show activity for my diagnostic session using the mysql client - there are no stats from the connections from the php application. The mysql 3 server on the same server which is running an older version of the same PHP web application
shows believable values for all status variables.
Does anyone know why half of my status variables are not counting correctly?
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Bytes_received | 107 |
| Bytes_sent | 5603 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 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_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 | 2 |
| 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 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 18 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
+-----------------------------------+----------+
Subject
Views
Written By
Posted
Mysql 5.0.16 - show status returns 0 for many variables
3125
December 16, 2005 02:20PM
2212
December 16, 2005 04:50PM
1528
December 16, 2005 06:42PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.