Command to check read/write ratio?

Question

Is there a command in MySQL that returns the read-to-write ratio of queries so that I'm able to know on what MySQL spends time, and whether the load would lower significantly by splitting data over two servers?

Answer 1

This SQL command will give you an indication as to the read/write ratio:

SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert'
OR Variable_name = 'Com_update'
OR Variable_name = 'Com_select'
OR Variable_name = 'Com_delete';

3rd party edit

On one of our servers gave this result

  Variable_name    |   Value
   Com_delete      |    6878
   Com_insert      |    5975
   Com_select      |  101061
   Com_update      |    9026
   Bytes_received  | 136301641 <-- added by 3rd party
   Bytes_sent      | 645476511 <-- added by 3rd party

I assume that update and insert have different IO implications but i combined them like this Com_insert + Com_update / Com_select to get a "write/read" idea. I also use Bytes_received and Bytes_sent - but this might lead to false conclusions since bytes received do not have to lead to a write on disk (for example a long where clause).

SELECT (136263935/1000000) AS GB_received
     , (644471797/1000000) AS GB_sent
     , (136263935/644471797) AS Ratio_Received_Sent
     , (6199+9108)/106789 AS Ins_Upd_Select_ratio;

This gave this result

GB_received |  GB_sent | Ratio_Received_Sent | Ins_Upd_Select_ratio 
    136     |    644   |    0,2114           |     0,1433