MySQL Read/Write Ratio – Analyze GLOBAL_STATUS

MySQL

Could you please help me to construct a query to sum specific rows from a table, basically desired output is:

+-----------------+----------------+
| Total reads     | Total writes   |
+-----------------+----------------+
| 345436          | 345435         |
+-----------------+----------------+

Total reads = Com_select
Total writes = Com_delete, Com_insert, Com_update, Com_replace

Best Answer

Use the typical trick of sum(IF the column is the right one, use it, if not read 0:

SELECT sum(IF(variable_name = 'Com_select', variable_value, 0)) as `Total reads`,
       sum(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'),
           variable_value, 0)) as `Total writes` 
FROM information_schema.GLOBAL_STATUS;

Related Solutions

Mysql – how to write conditional where clause query

It can be done using some multiple joins from table1 into table2 and then use those joins combined with case/calculation logic, or using sub-selects. But it seems rather complex to want to do that in a single query/single join - that I'd recommend finding an alternative way to do it. It's something which is a good candidate to solve in application logic.

However, I think the route you want to go is something like this:

SELECT
  T1.*,
  CASE
    WHEN T2.value = '1' THEN 'CalculateStuffBasedOnT3OrT4'
    WHEN T2.value = '2' THEN 
        CASE         
          WHEN t3.value IS NULL THEN 'CalculateOtherStuffBasedOnT3OrT4'
          WHEN T3.value = '2' THEN 'CalculateMoreStuffBasedOnT3OrT4'
          ELSE 'SomethingElse'
        END
     ELSE 'Something'
    END
FROM tbl1 T1
LEFT JOIN tbl2 T2 ON T1.id = T2.id AND T2.key = 'm_type'
LEFT JOIN tbl2 T3 ON T1.id = T3.id AND T3.key = 'm_routine'
LEFT JOIN tbl2 T4 ON T1.id = T4.id AND T4.key = 'm_date'

Such a query can then be used as foundation in a subquery to select the posts you want.

But this will quickly become quite complex and unmaintainable. So I'd advice you to rethink the strategy and go with designing the query dynamically in you php code, or rethink the data structure, or utilize multiple queries to solve it.

MySQL – Difference Between Show Status and Show Global Status

There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS;

SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected.
SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as SHOW SESSION STATUS; (As the MySQL Documentation says, it displays the status values for the current connection).

To physically show the difference, the information_schema database has them separated as

INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS

These information_schema tables have been around since MySQL 5.1.12.

Why the difference?

To thoroughly demonstrate the difference, let me run an INNER JOIN of these tables to show which values are different. Here is the query:

SELECT a.variable_name,a.variable_value,b.variable_value
FROM information_schema.global_status A INNER join information_schema.session_status B
USING (variable_name) WHERE A.variable_value <> B.variable_value;

Please note the output:

mysql> SELECT A.variable_name,A.variable_value,B.variable_value
    -> FROM information_schema.global_status A INNER join information_schema.session_status B
    -> USING (variable_name) WHERE A.variable_value <> B.variable_value;
+---------------------------+----------------+----------------+
| variable_name             | variable_value | variable_value |
+---------------------------+----------------+----------------+
| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_ALTER_TABLE           | 111            | 0              |
| COM_BEGIN                 | 1              | 0              |
| COM_CALL_PROCEDURE        | 530            | 0              |
| COM_CHANGE_DB             | 1623           | 1              |
| COM_COMMIT                | 19220          | 0              |
| COM_CREATE_FUNCTION       | 4              | 0              |
| COM_CREATE_PROCEDURE      | 126            | 0              |
| COM_CREATE_TABLE          | 354            | 0              |
| COM_DEALLOC_SQL           | 924            | 0              |
| COM_DELETE                | 64668          | 0              |
| COM_DELETE_MULTI          | 19             | 0              |
| COM_DROP_FUNCTION         | 4              | 0              |
| COM_DROP_PROCEDURE        | 148            | 0              |
| COM_DROP_TABLE            | 238            | 0              |
| COM_EXECUTE_SQL           | 945            | 0              |
| COM_INSERT                | 1182379        | 0              |
| COM_INSERT_SELECT         | 40673          | 0              |
| COM_KILL                  | 68             | 0              |
| COM_LOAD                  | 22386          | 0              |
| COM_LOCK_TABLES           | 2              | 0              |
| COM_OPTIMIZE              | 2              | 0              |
| COM_PREPARE_SQL           | 948            | 0              |
| COM_REPAIR                | 8              | 0              |
| COM_REPLACE               | 34737          | 0              |
| COM_ROLLBACK              | 13             | 0              |
| COM_SELECT                | 1107225018     | 65             |
| COM_SET_OPTION            | 602159         | 0              |
| COM_SHOW_BINLOGS          | 8              | 0              |
| COM_SHOW_CHARSETS         | 12             | 0              |
| COM_SHOW_COLLATIONS       | 100            | 0              |
| COM_SHOW_CREATE_DB        | 6              | 0              |
| COM_SHOW_CREATE_FUNC      | 2453           | 0              |
| COM_SHOW_CREATE_PROC      | 5684           | 0              |
| COM_SHOW_CREATE_TABLE     | 1313           | 0              |
| COM_SHOW_DATABASES        | 275            | 0              |
| COM_SHOW_EVENTS           | 1              | 0              |
| COM_SHOW_FIELDS           | 13666          | 1              |
| COM_SHOW_FUNCTION_STATUS  | 362            | 0              |
| COM_SHOW_KEYS             | 494            | 0              |
| COM_SHOW_PLUGINS          | 2              | 0              |
| COM_SHOW_PROCEDURE_STATUS | 361            | 0              |
| COM_SHOW_PROCESSLIST      | 488943         | 15             |
| COM_SHOW_SLAVE_STATUS     | 4              | 0              |
| COM_SHOW_STATUS           | 12315          | 10             |
| COM_SHOW_STORAGE_ENGINES  | 30             | 0              |
| COM_SHOW_TABLE_STATUS     | 320            | 0              |
| COM_SHOW_TABLES           | 584            | 0              |
| COM_SHOW_TRIGGERS         | 2              | 0              |
| COM_SHOW_VARIABLES        | 190            | 1              |
| COM_STMT_CLOSE            | 924            | 0              |
| COM_STMT_EXECUTE          | 945            | 0              |
| COM_STMT_PREPARE          | 948            | 0              |
| COM_TRUNCATE              | 522            | 0              |
| COM_UNLOCK_TABLES         | 2              | 0              |
| COM_UPDATE                | 496041         | 0              |
| COM_UPDATE_MULTI          | 625            | 0              |
| CREATED_TMP_DISK_TABLES   | 16772          | 40             |
| CREATED_TMP_TABLES        | 34336          | 63             |
| HANDLER_COMMIT            | 1109540769     | 0              |
| HANDLER_DELETE            | 12775993       | 0              |
| HANDLER_EXTERNAL_LOCK     | 2228108102     | 24             |
| HANDLER_PREPARE           | 2155764        | 0              |
| HANDLER_READ_FIRST        | 23586          | 9              |
| HANDLER_READ_KEY          | 18285349400    | 0              |
| HANDLER_READ_LAST         | 13000          | 0              |
| HANDLER_READ_NEXT         | 72142303428    | 0              |
| HANDLER_READ_PREV         | 3000146        | 0              |
| HANDLER_READ_RND          | 1261418742     | 156            |
| HANDLER_READ_RND_NEXT     | 12320861765    | 7845           |
| HANDLER_ROLLBACK          | 269376         | 0              |
| HANDLER_UPDATE            | 2596924399     | 0              |
| HANDLER_WRITE             | 8200421074     | 8241           |
| LAST_QUERY_COST           | 0.000000       | 21.399123      |
| LAST_QUERY_PARTIAL_PLANS  | 0              | 3              |
| OPENED_TABLE_DEFINITIONS  | 2482           | 0              |
| OPENED_TABLES             | 3619           | 0              |
| QUESTIONS                 | 1110214247     | 97             |
| SELECT_FULL_JOIN          | 615            | 9              |
| SELECT_RANGE              | 243635         | 0              |
| SELECT_SCAN               | 47851          | 53             |
| SLOW_QUERIES              | 29290          | 50             |
| SORT_MERGE_PASSES         | 6              | 0              |
| SORT_RANGE                | 179956         | 0              |
| SORT_ROWS                 | 321609927      | 156            |
| SORT_SCAN                 | 1829           | 39             |
| TABLE_OPEN_CACHE_HITS     | 1109365721     | 13             |
| TABLE_OPEN_CACHE_MISSES   | 1669           | 0              |
+---------------------------+----------------+----------------+
90 rows in set (0.03 sec)

mysql>

Look at four variables:

| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_SHOW_PROCESSLIST      | 488955         | 15             |

What does this tell you?

mysqld received 123,641,576,598 bytes (115.15GB) from all DB Connections since mysqld started
The session I ran the query with received 7,757 bytes (a little over 7K) in my current session
mysqld sent 149,888,451,047 bytes (139.59GB) from all DB Connections since mysqld started
The session I ran the query with sent 300,001 bytes (a little under 297K) in my current session
There have been 121,915 administrative commands that have run since mysqld started
There have benn 0 administrative commands that have run in my current session
The command SHOW PROCESSLIST has been run 488,955 times since mysqld started
The command SHOW PROCESSLIST has been run 15 times in my current session

You can compare the other 86 varibales and interpret them the same way.

Best Answer

Related Solutions

Mysql – how to write conditional where clause query

MySQL – Difference Between Show Status and Show Global Status

Give it a Try !!!

Related Question