MariaDB – Calculate Reads/Writes Ratio in Percent

information-schemamariadbMySQL

I found a blog post howto calculate the reads/writes ratio in percent.

I receive NULL values in our env.

> 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;
+-----------+-------------+--------------+---------+----------+
| Total     | Total reads | Total writes | Reads % | Writes % |
+-----------+-------------+--------------+---------+----------+
| 109286055 |    85029655 |     24256400 |    NULL |     NULL |
+-----------+-------------+--------------+---------+----------+
1 row in set (0.03 sec)

MariaDB [(none)]> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.01 sec)

any idea?

Best Answer

The Reads% and Writes% does not have the @totals until too late. This should work better:

SELECT  Total,
       `Total reads`,
       `Total writes`,
       ROUND((@total_reads / @total_com * 100), 2) as `Reads %`,
       ROUND((@total_writes / @total_com * 100), 2) as `Writes %`
    FROM  
      ( 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`
            FROM  information_schema.GLOBAL_STATUS 
      ) x;
Related Question