Whilst a truncate table operation is underway a lock mutex around the table is created to ensure data consistency; if using innodb file per table the physical .ibd file must be removed which is a painfully slow operation under ext3.
In short, if your systems failed in the Amazon cloud this week, it wasn’t Amazon’s fault. You either deemed an outage of this nature an acceptable risk or you failed to design for Amazon’s cloud computing model.
The solution was to change the datatype from TINYINT to TINYINT UNSIGNED. The problem is that this is a very large dataset, ~14TB across a bunch of systems. We could use our online schema change tool (OSC) to do this change, but it would still take a week or two to run.
Basically a metadata change that needn’t affect the underlying data.
In Rdb data (re)write causes new datatype to be used. No need to update the entire table when this kind of change is made. Versioned metadata is such a fine thing.
Database archiving is becoming an important new topic for data managers. The need for this function has surfaced at most IT organizations, and the problems it addresses are only getting bigger and bigger. These problems include challenges with data retention requirements, application renovations and e-discovery. Most IT data managers recognize the problems but many do not associate database archiving as a solution. This will change as the technology matures and spreads.
SSD performance and endurance are related. Generally, the poorer the performance of a drive, the shorter the lifespan. That’s because the management overhead of an SSD is related to how many writes and erases to the drive take place. The more write/erase cycles there are, the shorter the drive’s lifespan. Consumer-grade multi-level cell (MLC) memory can sustain from 2,000 to 10,000 write cycles. Enterprise-class single-level cell (SLC) memory can last through 10 times the number of write cycles of an MLC-based drive.
Interesting discussion about SSD. Maybe we should burn-in the drive with at least one full write cycle?