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