0

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?

1
  • Try ROUND(((@total_writes / @total_com) * 100),2) instead. Your logic and code seem fine.
    – Vérace
    Commented Mar 7, 2016 at 11:20

1 Answer 1

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.