Tuesday, March 22, 2011

Mysql - checking read/write ratio

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)

Wednesday, March 9, 2011

Choosing the right scheduling algorithm for a Linux Based Load Balancer - Part II

This is a continuation of an earlier post:
Choosing the right scheduling algorithm for a Linux Based Load Balancer

This is the current scenario:
  • Two identical webservers, mirrored hardware and software specifications.
  • The main variable is physical location of the second webserver, but it is located on the same physical network but a different building.
  • Network traffic may or may not be an issue.
Based on some initial tests, (scale testing is almost irrelevant for these algorithms ), I have decided to implement the Weighted Least Connection. It seems the most appropriate at this point.

Weighted Least-Connections (default)


Distributes more requests to servers with fewer active connections relative to their capacities.
Capacity is indicated by a user-assigned weight, which is then adjusted upward or
downward by dynamic load information. The addition of weighting makes this algorithm
ideal when the real server pool contains hardware of varying capacity.

-n