I'm using MySQL with InnoDB storing engine. Transactional log is placed to dedicated HDD. Still as it is HDD
atopsar shows big load (>50%) to this HDD because of big amount of small IOs (about 50 writes per second 6Kb each).
innodb_flush_log_at_trx_commit to 2 solved problem and now load in average 5%. However, MySQL documentation says:
When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2.
With a value of 2, only an operating system crash or a power outage can erase the last second of transactions.
Therefore, it is possible to lose transactional log for last second.
I'm not experienced in configuring hard for DB servers and therefore want to know what modern solutions exists and are most suitable for DB's transactional log giving reliability and high IO capable.
the best I mean different variants, but personally would prefer optimal combination of performance/price. Any way I think it will be great to collect here different variants.
I know that usual answer will be "use SSD", but as I'm aware of its storing principles it's not reliable solution for transactional log and it should relatively fast fail. From first look, it may seem that rewrite count of modern SSD is enough to operate for years. However, as I know SSD rewrite data by blocks and for example if you want to write 1b of data SSD will rewrite entire block, which may be 4Kb. So for 1 transaction log rewrite circuit SSD might rewrite its blocks many times.
From my perspective, ideal storing device will be a combination of Battary+RAM+HDD with relatively small size (around 5Gb or less). Which in normal operation uses only RAM and on electricity black out flushes all data to HDD. It should be all in one device, not a software solution. However, I do not know if they exist.