At some point during database tuning, it becomes necessary to check the amount of reads and write transactions to determine the best way forward.
In order to check these stats, you need to login as the Mysql admin. Then execute the following query:
show global status like 'Com%';
* These stats are only gathered for the duration the mysql service is running. All data will be reset if the service is restarted.
Expected output:
In this case, the read:write ratio is quite high, selects are 28 million; other write based operations add up to approx 11 million.
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 164 |
| Com_analyze | 4 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 6903942 |
| 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 | 246 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 68561 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 246 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 26 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1859347 |
| Com_insert_select | 3288 |
| Com_kill | 0 |
| Com_load | 328 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 26 |
| Com_optimize | 4 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 6804 |
| 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 | 29848728 |
| Com_set_option | 6755821 |
| 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 | 760708 |
| 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 | 3 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 65 |
| Com_show_triggers | 0 |
| Com_show_variables | 26 |
| 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 | 3048 |
| Com_unlock_tables | 26 |
| Com_update | 2871820 |
| Com_update_multi | 2540 |
| 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 |
+--------------------------+----------+
104 rows in set (0.00 sec)
