Total Number of Read/Writes in a Day in MySQL

MySQL

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?

Best Answer

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 !!!

Related Question