Best way to find out read/write distribution in MySQL?

Question

People often say things like "application X is write heavy", 20/80 write vs reads, etc.

What metric(s) is most relevant for MySQL when checking if an application is write/read heavy?

Is checking Com_insert, etc enough, not taking into account number of rows read/modified, or is it checking iostat metrics?

Answer 1

An application being read/write heavy is subject to interpretation based on the following:

1. How many SELECTs are launched : Com_select
2. How many UPDATEs, DELETEs, INSERTs are launched
   • Com_insert
   • Com_delete
   • Com_delete_multi
   • Com_update
   • Com_update_multi
3. Prepared Statements (could be a bottleneck if prepared statements are not parameterized)
   • Com_stmt_prepare
   • Com_stmt_execute
   • Com_stmt_fetch
   • Com_stmt_send_long_data
   • Com_stmt_reset
   • Com_stmt_close
4. How much client byte traffic mysqld sees
   • Bytes_received
   • Bytes_sent
5. How many bytes have been read
   • Innodb_data_read
   • Innodb_data_reads
6. How many bytes have been written
   • Innodb_data_writes
   • Innodb_data_written
7. Perhaps a combination of these

These things are meant to measure SQL command counts and the amount of data passing here and there. There are other metrics within the InnoDB storage engine that should be monitored and tuned so that any write heavy behavior is improved.

Rather than name those features of InnoDB, please go to the Server Status Variables page and look for any status variable mentioning the Redo Log Writes and Buffer Pool read/writes.