CODEWITHSUNDEEP
mysql
Brandon
Brandon

Reputation: 545

MySQL - How to count the number of inserts/updates to a table

I'm looking into some MySQL performance issues and would like to generate a report of how many inserts/updates are made to each table in my database over some period of time.

I have lots of data available regarding server performance in general such RDS's disk I/O metrics. MySQL's SHOW STATUS command show Queries and even Innodb_data_writes, but these are all at the entire server level, not granular at all. I'd like to get detail down to the table level.

Perhaps there is something buried in the information_schema or performance_schema databases that I can turn on or use?

Upvotes: 1

Views: 5455

Answers (2)

Marc Alff
Marc Alff

Reputation: 8395

The performance schema keeps statistics of table io, on a table (and even index) granularity.

See table performance_schema.table_io_waits_summary_by_table

https://dev.mysql.com/doc/refman/5.7/en/table-waits-summary-tables.html#table-io-waits-summary-by-table-table

Upvotes: 3

Brandon
Brandon

Reputation: 545

Thanks to barat for pointing out this link which suggests going through the binary log for the relevant information. I coded up a quick script that uses a combination of mysqlbinlog to retrieve the binary log from a remote server and regex parsing of the log contents into something that outputs the desired information in the format:

Parsed 1,096,063 lines spanning 300 seconds between 2016-09-13 03:05:00 and 2016-09-13 03:10:00
master                         metrics                        update          = 43570
master                         metrics                        insert into     = 9
DEFAULT                        accounts                       update          = 501
DEFAULT                        users                          update          = 5
DEFAULT                        logins                         insert into     = 1
mysql                          rds_heartbeat2                 insert into     = 1

Code shared on Github at https://github.com/sellerlabs/mysql-writes-per-table

Upvotes: 1

Related Questions