How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by MariaDB 5.5 on a daily basis so that I can use this to estimate the possible longevity of SSDs if used in a heavy write database environment?

Current setup is that all tables are InnoDB. Can I use Innodb_data_written and Uptime to determine a rough idea of bytes written per day, or is there a better way of doing it?

MONITORING

There are so many writes to keep track of

• Data

• Innodb_data_writes : The total number of data writes.

• Innodb_data_written : The amount of data written so far, in bytes

• SQL

• Innodb_rows_deleted : The number of rows deleted from InnoDB tables

• Innodb_rows_inserted : The number of rows inserted into InnoDB tables

• Innodb_rows_updated : The number of rows updated in InnoDB tables

• Double Write Buffer

• Innodb_dblwr_writes : The number of doublewrite operations that have been performed

• Innodb_dblwr_pages_written : The number of pages written to the doublewrite buffer

• Operating System

• Innodb_os_log_written : The number of bytes written to the InnoDB redo log files

• Innodb_data_fsyncs : The number of fsync() operations so far. The frequency of fsync() calls is influenced by the setting of the innodb_flush_method configuration option.

• MyISAM-related writes

Data writes come in the form of

External sources

• DB Connections From Your Apps

• Data Restorations

• Reloading of mysqldumps

• LOAD DATA INFILE

• MySQL Replication via incoming I/O Threads (if DB Server is a Master)

Internal Sources

• Read and Write I/O Threads for Writing to Transaction Logs

• Insert Buffer from the InnoDB Buffer Pool

• Double Write Buffer

• MySQL Replication

• IO Thread (if the DB Server is a Slave)

• SQL Thread

• Log Buffer Write

• Individual Tablespace Files (.ibd)

• FSyncs (pushing changes to disk)

InnoDB Architecture

SSD LIFESPAN

If you more concerned with the SSD's longevity, try moving some of the InnoDB parts away from the SSD over to a fast HDD. Which parts go where ?

• HDD (RAID 10)

• InnoDB System Tablespace (ibdata1)

• InnoDB Transaction Logs (ib_logfile0,ib_logfile1)

• Binary Logs

• Relay Logs (if your server is a MySQL Replication Slave)

• SSD

• .frm files

• .ibd files

• See this good blog entry from a MySQL FaceBook Engineer on this suggestion

Here are some of my earlier posts on moving splitting logging mechanisms between SSD and HDD

• Jun 25, 2013 : Postgres Write Performance on Intel S3700 SSD

• Jun 18, 2013 : How big can innodb_doublewrite_file grow?

GIVE IT A TRY !!!

StackExchange Database Administrators Q#48028, answer score: 5