1

Can anyone let me know how to calculate mysql server writes,reads,deletes,updates per second.

2 Answers 2

0

From a different perspective...

SHOW GLOBAL STATUS;, then

For SQL counts, divide Com_delete by Uptime to get the DELETE statements per second since MySQL was started. Etc.

For actual I/O, divide these by Uptime to get "per second since MySQL started":

  • Key_reads and Key_writes track I/O for MyISAM index operations.
  • Innodb_buffer_pool_reads and Innodb_buffer_pool_pages_flushed track all data and index activity for InnoDB.
  • Innodb_dblwr_writes for double-buffer writes.

Binlog, iblog, MyISAM data, and a few other things are not included above.

2
  • Can i calculate like this. mysql> show global status like '%com_select%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | Com_select | 11476882 | +---------------+-------------+ mysql> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ mysql> show global status like '%com_select%'; +---------------+-------------+ | Variable_name | Value | +---------------+-------------+ | Com_select | 11476998 | +---------------+-------------+ so for 2 seconds 116 (11476998-11476882) for 1 second 58 selects
    – kasi
    May 14, 2015 at 11:45
  • Yes. But that was probably a little more than 2 seconds (to allow for entering the commands). Do them in a single "line" to get closer: mysql> SHOW...; SELECT...; SHOW...;
    – Rick James
    May 14, 2015 at 15:49
1

There are two ways of determining this.

RECENT VERSIONS OF MYSQL

One is to use the performance_schema (assuming 5.6 or higher).

There are many tables that you can query to get at this information, particularly if you have innodb_file_per_table = 1 in your my.cnf.

If you do USE performance_schema there are several tables that can help you to find this information, notably (from DESC TABLES;)

| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |

and

| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |

Then queries such as

mysql> SELECT SUM(sum_number_of_bytes_read) AS Total_Read 
FROM file_summary_by_instance;

+----------+
|Total_Read|
+----------+
| 10470860 |
+----------+
1 row in set (0.00 sec)
mysql> 

and

mysql> SELECT SUM(sum_number_of_bytes_read) 
+ SUM(SUM_NUMBER_OF_BYTES_WRITE) 
AS Total_IO 
FROM file_summary_by_instance;

+----------+
| Total_IO |
+----------+
| 15161292 |
+----------+
1 row in set (0.00 sec)
mysql>

can help you get the information you want.

OLDER VERSIONS OF MYSQL

If you don't have a recent version of MySQL, then you can use the Unix utility iostat to help you obtain a (reasonably good) estimate of your overall usage.

[pol@localhost inst]$ iostat
Linux 3.11.10-301.fc20.x86_64 (localhost.localdomain)   11/05/15    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.33    0.03    2.97    6.12    0.00   84.55

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
scd0              0.02         0.09         0.00        116          0
sda              13.34       544.46        55.79     742770      76109
dm-0              0.20         0.82         0.00       1116          0
dm-1             16.14       497.16        23.26     678245      31736
dm-2              5.69        42.97        32.52      58628      44364
[pol@localhost inst]$ 

Some of the readings are on a per/second basis, but others represent data from machine bootup. You should obtain a baseline figure (with no MySQL running) and then take your measurements with the RDBMS running and subtract the baseline from the MySQL numbers - that way you will have a reasonable estimate of how much I/O is going on in your server.

iostat is available through the sysstat package which may or may not be installed on your distro - use man iostat (and man sar &c.). The package is available from here. Check out the documentation for your particular system.

For completeness, I should also add that this information is available on an ad-hoc basis through the (excellent) program MySQL Workbench, as shown by the screenshot below:

enter image description here

1
  • thanks verace. very nice information. Really thanks for your time.
    – kasi
    May 18, 2015 at 10:02

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.