Mysql – the best storing device for DB transaction log

MySQLoptimizationperformancetransaction-log

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

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

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

Best Answer

You really need a fast HDD, but you need a properly sized innodb_log_file_size.

Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog

I wrote old posts about this

Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations?
Feb 06, 2014 : MySQL on SSD - what are the disadvantages?
Aug 12, 2014 : Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I even mentioned doing this for PostgreSQL : Postgres Write Performance on Intel S3700 SSD

BTW See my post MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined? for determining the right size for innodb_log_file_size. You don't want a small HDD for ib_logfile0 and ib_log_file1.

Give it a Try !!!

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – Data center from scratch which way to go

Currently I am running my business on 2x SQL servers which have 38 GB of ram at all

As well as the replication strategy to improve availability the though the replica taking over if the primary fails, you must factor in a god backup strategy - in what follows I'm going to assume that you already have this (if not, you need to factor it into your costings and a really good backup solution for your business critical data is not something you should try buy/create cheaply).

If you are happy with this arrangement in terms of resilience and general performance, and you are just upgrading the servers mainly to replace old hardware, then go with option 1 and upgrade the current design. Remember that if you build a different infrastructure you may run into problems you've never seen before and if you do they may turn up at the least convenient time for your business. If you don't have the knowledge/experience to fix things fast (and it isn't available in anyone else in the company you can call on) this could be very expensive in terms of business lost due to downtime and paying an expert to fly in and help.

Do I need SSDs beside plenty of RAM

That very much depends on your applications write patterns. Given you have ~80Gb of data you might find all your common working set could be held in RAM most of the time so for read heavy loads you will see little or no difference between the SSDs and spinning metal for day-to-day operations as the drives won't be touched for most reads. That data held in memory will be lost in some circumstances (such as when SQL server is shut down for what-ever reason) and it will take a while for the working set to get loaded and at this point the SSDs will help quite noticeably. For writes data will still of course need to hit the disk, so for write heavy loads the SSDs will help.

Do remember that Standard Edition will only use up to 64Gb of memory per instance (see http://msdn.microsoft.com/en-us/library/cc645993.aspx#CrossBoxScale) so you may not see all that 96Gb get used in either box.

I now have 1 main database (80GB-DATA and 20GB of SQL Server log)
I can have at TWO SQL servers 8 X 250 GB ssd SATA disc
or HP storage with 10~14 pieces of 600GB 10K SAS disc in storage

Am I reading this wrong, or are you expecting your data size to increase a fair bit over the active service life of the new hardware? In RAID10 those SSDs will give you ~1TB of space which is considerably bigger than the ~100Gb you are using now, and that large collection of spinning metal even more so (3Tb+ unless you configure a redundancy level over 2:1), so you might be over-spending here for your near-/mid-term needs.

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

Sql-server – Data center from scratch which way to go

Related Question