mysql-size-and-rps.sql

-- size
SELECT 
  table_schema "database_name", 
  sum( data_length + index_length )/1024/1024 "Data Base Size in MB" 
FROM information_schema.TABLES 
GROUP BY table_schema;

-- writes mb
set @start := (
  select sum(VARIABLE_VALUE/1024/1024) 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_written'
); 
do sleep(600); 
set @end := (
  select sum(VARIABLE_VALUE/1024/1024) 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_written'
); 
select round((@end - @start),2) as 'WritesMB/10min', round((@end - @start),2) * 6 as 'WritesMB/hour';

-- writes rps 
set @start := (
  select VARIABLE_VALUE 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_writes'
); 
do sleep(600); 
set @end := (
  select VARIABLE_VALUE 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_writes'
); 
select round((@end - @start),2) as 'WritesOp/10min', round((@end - @start),2) * 6 as 'WritesOp/hour';

-- reads mb per hour
set @start := (
  select sum(VARIABLE_VALUE/1024/1024) 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_read'
); 
do sleep(600); 
set @end := (
  select sum(VARIABLE_VALUE/1024/1024) 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_read'
); 
select round((@end - @start),2) as 'ReadsMB/10min', round((@end - @start),2) * 6 as 'ReadsMB/hour';

-- reads rps 
set @start := (
  select VARIABLE_VALUE 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_reads'
); 
do sleep(600); 
set @end := (
  select VARIABLE_VALUE 
  from information_schema.global_status 
  where VARIABLE_NAME like 'Innodb_data_reads'
); 
select round((@end - @start),2) as 'ReadsOp/10min', round((@end - @start),2) * 6 as 'ReadsOp/hour';