MySQL: Calculate Read and Write Ratios in Percentage

I have been assigned a task to assist a client to get some idea on his database usage in MySQL Cluster before migrating them to Galera cluster. Galera scales well for reads but not for writes. So the first thing you need to do is to calculate the reads/writes ratio:

SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) AS `Total`,
 @total_reads := SUM(IF(variable_name = 'Com_select', variable_value, 0)) AS `Total reads`,
 @total_writes := SUM(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes`,
 ROUND((@total_reads / @total_com * 100),2) as `Reads %`,
 ROUND((@total_writes / @total_com * 100),2) as `Writes %`
FROM information_schema.GLOBAL_STATUS;

The output would be as below:

+------------+-------------+--------------+--------+----------+
| Total      | Total reads | Total writes | Reads % | Writes % |
+------------+-------------+--------------+--------+----------+
| 1932344732 |  1899878513 |     32466219 |  98.32 |     1.68 |
+------------+-------------+--------------+--------+----------+
1 row in set (0.00 sec)

The output calculatedĀ above is relative since the last restart. So if you just restarted your MySQL server, you may need to wait several hours at least to get a more accurate result.

Simple initial estimation; if writes percentage is less than 50%, then the schema should be running fine in Galera cluster. However, you need to totally understand the application side as well. Hope this helps people out there!