Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 1 down vote accepted

MONITORING

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

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

GIVE IT A TRY !!!

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

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).

share|improve this answer
add comment

Your Answer

 
discard

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.