Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted


There are so many writes to keep track of

Data writes come in the form of

External sources

  • DB Connections From Your Apps
  • Data Restorations
    • Reloading of mysqldumps
  • 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

InnoDB Architecture


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


share|improve this answer
An exceptionally well researched answer, thanks very much for your insights. – Craig Sefton Aug 14 '13 at 17:27

Innodb_data_written "is the amount of data written so far, in bytes." (quote from: The storage engine actually writes more to disk than that.

There are two major write pattern in Innodb (beside of binlogs if there's any):

  • Innodb log files (actual data change)
  • Flushing to tablespace (per pages, even a change of 1 byte will cause a page to be marked dirty)

If you want to have accurate metrics you need to take into consideration both.

Log sequence number from show engine innodb status and its changing over time which means written bytes to innodb_log_files.

Innodb_buffer_pool_pages_flushed from show global status; gives you the number of flushed pages since start. Page size is 16k by default (can be changed though by recompiling).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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