MySQL InnoDB log files on different location

innodbMySQLoptimizationtransaction-log

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

Best Answer

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.

Related Solutions

Mysql – the best way to reduce the size of ibdata in thesql

Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1

This file normally houses many classes of information (when innodb_file_per_table is 0)

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollbacks Segments
- Undo Tablespace
Table Metadata
See Pictorial Representation

Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:

Makes the table's data and indexes contiguous inside ibdata1
It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. To shrink ibdata1 once and for all you must do the following

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql) (More details here)

Step 02) Drop all databases (except mysql, performance_schema, and information_schema)

Step 03) Shutdown mysql

Step 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Mysql – Does having Default Database Engine MyISAM and keeping InnoDB tags in the.ini create any problem

Binary Logs

As for your binary logs, the bad magic number is making reference to an encoding done at the beginning of the a binary log. It is a base64 constant that looks something like : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number. If this encoding is not in this format, it would be reported as corrupt.

You should erase all binary logs and start fresh.

In order to clear them, run this in the mysql client

mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;

This will erase all binary logs and show you the first binary log.

I have written posts on this issue before

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

InnoDB Options

It is OK for the InnoDB options to be around. Keep in mind that every time mysql is restarted, InnoDB's infrastructure is validated

mobiledoc_InnoDb_data1 exist in the innodb_data_file_path (D:/eClinicalWorks/mysql/data)
mobiledoc_InnoDb_data1 must be at least 300M
innodb_data_home_dir has ib_logfile0/ib_logfile1
innodb_log_file_size=256M (checks if ib_logfile0/ib_logfile1 are 256M)
innodb_buffer_pool_size=256M (allocates the 256M in RAM)
innodb_log_buffer_size=8M (allocates the 8M in RAM)

If none of your tables are InnoDB but you want to keep the present InnoDB settings and corresponding files, I have a great suggestion:

Add this under the [mysqld] section of my.ini

[mysqld]
skip-innodb

then run

C:\> net stop mysql
C:\> net start mysql

This will make mysqld startup faster because all the steps for InnoDB initialization/validation are bypassed. The memory InnoDB is configured to use (264M [256 + 8]) will not be allocated.

Give it a Try !!!