0

Aside from using standard count queries with a date, is there a global way to get the number of writes (or reads for that matter) in a 24hr period on a table, or an entire schema?

  • Is your database all InnoDB ??? – RolandoMySQLDBA Aug 3 '15 at 15:31
  • Have you looked at the performance schema? – Vérace Aug 3 '15 at 15:37
  • Are you looking for disk reads/writes? logical reads/writes (including cached)? rows read/written? SQL statements that read/write? – Rick James Aug 3 '15 at 21:05
0

NOTE : This is all reads and writes for all databases in the MySQL Instance

You should create a temp database for collecting stats

SET sql_log_bin = 0;
DROP DATABASE IF EXISTS mystats;
CREATE DATABASE mystats;

Then, every hour run this

SET sql_log_bin = 0;
USE mystats
DROP TABLE IF EXISTS rwstats;
CREATE TABLE rwstats
(
    id INT NOT NULL AUTO_INCREMENT,
    stat_name  VARCHAR(64) NOT NULL,
    stat_value BIGINT NOT NULL,
    PRIMARY KEY (stat_name,id)
) ENGINE=MyISAM;
#
# Collect Stats
#
INSERT INTO rwstats (stat_name,stat_value)
SELECT variable_name,0 - variable_value
FROM information_schema.global_status
WHERE variable_name IN
('Innodb_rows_inserted','Innodb_rows_updated','Innodb_rows_deleted',
'Innodb_rows_read','Innodb_data_reads','Innodb_data_read',
'Innodb_data_writes','Innodb_data_written');
#
# Sleep For 1 Hour
#
DO SLEEP(3600);
#
# Collect Stats
#
INSERT INTO rwstats (stat_name,stat_value)
SELECT variable_name,variable_value
FROM information_schema.global_status
WHERE variable_name IN
('Innodb_rows_inserted','Innodb_rows_updated','Innodb_rows_deleted',
'Innodb_rows_read','Innodb_data_reads','Innodb_data_read',
'Innodb_data_writes','Innodb_data_written');
#
# Display Results
#
SELECT stat_name,SUM(stat_value) stat_value FROM rwstats GROUP BY stat_name;

GIVE IT A TRY !!!

0

If you have binary logging enabled, you can analyse your binlogs to identify types and number of statements executed.

mysqlbinlog your_binlog | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

You might need --base64-output=DECODE_ROWS for row based binlog format. You can analyze your day worth of binlogs or use --start/--end options to select time.

Check out this and this blog posts.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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