2

I am using MySQL version: 5.5.38

There are following parameters that can control InnoDB log files:

innodb_log_files_in_group - amount of log files in the group, by default 2. Will create 2 files ib_logfile0 and ib_logfile1

innodb_log_group_home_dir - location of InnoDB log files.

Is there a way to place each individual log file to it's own place?

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files. Too much IO to MySQL InnoDB log files

4

Setting the two files on two different disks may not help you, because speaking generally, they are only written serially:

The redo log files are used in a circular fashion. This means that the redo logs are written from the beginning to end of first redo log file, then it is continued to be written into the next log file, and so on till it reaches the last redo log file. Once the last redo log file has been written, then redo logs are again written from the first redo log file.

(from the InnoDB blog).

Setting them both on a separate disk from the tablespaces is a good idea, as if your problem is lots of writes, as having exclusive IO will be an advantage on magnetic disks. They are also good candidates for SDDs because (again, if you have problems with transaction log IO) they are relatively small and usually written synchronously to disk, unlike the indexes and data.

Another way to load-balance IO is to use a RAID level with improved throughput. RAID 1 will provide you better reads and writes thanks to its striping, although the recommended setup is RAID 10 for redundancy.

One disadvantage of being on separate disks is that in most cases, you will lose the option of performing consistent MySQL datadir snapshots.

  • I already moved log files to dedicated HDD. But atopsar -d 30 show more than 50% load. Do you have any ideas how to decrease IO related to innodb log files? – Index Sep 16 '14 at 11:20
  • @Index I already mentioned some on my answer- better disk (SSD), RAID 10, hardware raid with cache, sharding. – jynus Sep 16 '14 at 13:05
-3

The main idea is to increase performance and place each file to it's own HDD drive. As I have to much IO related to InnoDB log files.

https://books.google.ru/books?id=BL0NNoFPuAQC&pg=PA291&lpg=PA291&dq=store+log+files+on+different+path+or+disks+in+mysql&source=bl&ots=CPUMvxsG2O&sig=eqGgsODsCZ1jjwM2bS6OB1JuExE&hl=en&sa=X&ved=0ahUKEwjJxeL9i7jKAhUK_XIKHRxTD4MQ6AEIOjAF#v=onepage&q=store%20log%20files%20on%20different%20path%20or%20disks%20in%20mysql&f=false

this can help you.

  • That link is already dead (at least for me). Please include the important points from there, so that your answer becomes and stays useful even when the link rots (which it did, at least partially). – dezso Jan 20 '16 at 11:11

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.