How to tune mysql to be inmemory-like

I've given MySQL 5GB of memory (I use innodb), but when I insert lots of data (dumpfile is 1GB), hard drive I/O is still a bottleneck (CPU is not busy and hard drive is).

Is it possible to force MySQL not to make the hard drive a bottleneck?

I have five(5) aspects to discuss here

ASPECT #1 : innodb_buffer_pool_size

You need to run this query

SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) BPSIZE
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the ideal sized buffer pool because InnoDB caches data and index pages.

If the DB Server has 32G RAM, the biggest buffer pool would be 75% of the Machine (which would be 24G) or BPSIZE, whichever is smaller. In other words, for 32G RAM, if BPSIZE is 10G, then use 10G. If BPSIZE is30G, use 24G.

ASPECT #2 : InnoDB Log Buffer

Setting the size of the Log Buffer can be a dream come true or your worst nightmare. How?

Dream Come True

The MySQL Documentation on innodb_log_buffer_size says the following:


The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

Your Worst Nightmare

According to the Book

Page 428 Paragraph 8 says the following:


The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction, it results in writes that, with a larger buffer, would not need to be made at all. To set the size of the log buffer, use the innodb_log_buffer_size option. The default is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

This excerpt is for MySQL 5.0.13. InnoDB has matured a lot since then, but the principle remains the same: Setting innodb_log_buffer_size too high can have two adverse affects:

• It can waste memory for low write environments or very small transactional changes

• Produce much longer commits

Lesson Learned

Look at the default innodb_log_buffer_size

• 5.0 (Default: 1M)

• 5.1 (Default: 1M)

• 5.5 (Default: 8M)

• 5.6 (Default: 8M)

You will have to experiment with larger values, perhaps 16M, 32M, 64M, or 128M.

Just remember

• the lower innodb_log_buffer_size is set, the more frequent larger transactions may flush

• the higher innodb_log_buffer_size is set, the longer commits may take

So, don't crazy with the values. Be as conservative as possible but look for reduced disk I/O you desire.

ASPECT #3 : Dirty Pages in the Buffer Pool

• 90% by default on MySQL 5.0/5.1

• 75% by default on MySQL 5.5/5.6

You should set this to 90 regardless of the version of MySQL. The higher it is, the more delay on flushing for heavy write loads. Given the increased performance of MySQL 5.5/5.6, this may not be necessary.

ASPECT #4 : Disk Issues

Run this query:

SHOW VARIABLES LIKE 'innodb_file_per_table';

If you get OFF for innodb_file_per_table, you need to Cleanup the InnoDB Infrastructure. I wrote post back on October 29, 2010 on how to do this.

With regard to having InnoDB Data sitting on a different disks, this may reduce Disk I/O but at the risk of reduced overall InnoDB Performance. Why ? Back on Feb 06, 2012, I wrote about why spreading InnoDB on different disks is not all that performant.

What is also noteworthy is this regard is the system tablespace ibdata1. Every time you access an InnoDB tables, the data dictionary is always consulted. Thus, having spread out tables onto other disk does not negate accessing ibdata1 back in datadir. Accessing two disks would always be in order.

InnoDB has many moving parts in ibdata1:

• Data Dictionary

• Double Write Buffer

• Safety Net to Prevent Data Corruption

• Helps Bypass OS for Caching

• Insert Buffer (Streamlines Changes to Secondary Indexes)

• Rollback Segments

• Undo Logs

• Click Here to see a Pictorial Representation of ibdata1

Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

ASPECTS #5 : Transaction Flushing

I am not a big fan of setting innodb_flush_log_at_trx_commit to 0 or 2 since it does the following:

• relaxes ACID compliance

• risks 1 second of data loss in the event of a crash

• puts more faith and trust in the OS and hardware for flush disk changes

EPILOGUE

Please keep this foremost in mind: Your goal here should be to get InnoDB to cache transactional changes as much as possible and flush to disk as little as possible. This will certainly curtail disk I/O without expending effort on hardware too quickly. Only after the needed due diligence fails to reduce disk I/O should you explore hardware.

StackExchange Database Administrators Q#41413, answer score: 10