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?



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


  • An exceptionally well researched answer, thanks very much for your insights. Aug 14 '13 at 17:27

Innodb_data_written "is the amount of data written so far, in bytes." (quote from: http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Innodb_data_written) 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).


If you keep your DB files on a dedicated devices (as I do), you can use your OS tools to do this pretty easily. Try iostat -dm, which will print cumulative IO statistics since OS reboot. Here's the output from one of my servers

$ iostat -dNm

Device:                  tps MB_read/s MB_wrtn/s  MB_read MB_wrtn                                                                        
mysql_volgrp2-logs     80.13      0.00      1.59      313 2608762                                                                        
mysql_volgrp1-db      513.05      8.97      6.09 14726683 9988059                                                                        
mysql_volgrp3-iblogs    1.16      0.00      0.06        2   95082

For SSD longevity, you only care about the writes. Take from my example the volgrp1-db, which has seen 9.9 TB of writes since server boot. I also like taking the MB_wrtn/s, which is a mean since server boot, and do a unit conversion from MB/sec to GB/year.

If you are lazy, you can do the math really easily with a plain old Google search. Try googling "10000 * 300 GB / 8.97 MB/sec". This will calculate that a 300 GB drive rated for 10,000 write cycles at an average write rate of 8.97 MB/sec would last about 10.6 years.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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