How to tune mysql to be inmemory-like

Question

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?

Answer 1

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:

1. It can waste memory for low write environments or very small transactional changes
2. 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.

Answer 2

The short answer is that you can't eliminate writes to disk, because InnoDB is doing its very best to make sure that it can recover if the server crashes.

I'd strongly recommend against migrating to MyISAM - a MyISAM table that's heavily written to is extremely likely to be corrupted if your server crashes, and you'll likely lose data (or need to restore from backup).

However, there are several points during a transaction at which you can make tradeoffs between performance and data safety. You may also be able to tune the disk configuration on the server.

Tuning your import

You may find that changing the way you load data is going to be the most effective way to solve your problem.

Is the dumpfile the result of running mysqldump on another MySQL database? If so, and the source database is MyISAM, you might find that exporting the MyISAM data in primary key order will make the import go a lot more smoothly (though it will cause the export to take longer on the source system).

If the data is being loaded via some other mechanism, there are several behaviour changes that might help - smaller or larger transaction sizes, ordering the data before it's loaded in, deliberately adding delays between writes, etc.

General disk tuning

If you don't already, you will probably want to have your InnoDB data on a different disk to everything else, and preferably a fast one.

Different parts of InnoDB/MySQL also write to disk differently, and it's beneficial to try and keep them on separate disks if you can. For example, binary logs and innodb logfiles are generally written sequentially (position 1, position 2, position 3...), while data files are read/written to at random (position 200, position 38937, position 2...).

If you're using local spinning disks (and not a SAN or SSDs), keeping random and sequential IO on different disks can provide a significant performance improvement.

Tweaking your hard disk configuration can also help - if you're using Linux, consider modern file systems like XFS or ext4, or using options like noatime when mounting your filesystem, so that it doesn't have to write an update to disk each time the database reads a file.

Finally, if you're using a Unix-type OS, the disk scheduler will also play an important role. Databases will generally perform better if the disk is configured to use a scheduler like noop or deadline instead of the default cfq. There's some detailed discussion about this on SO.

The book High Performance MySQL is an excellent read, and goes into a lot of detail on most of these topics.

The binary log

By default, binary logging is turned off in MySQL. It is controlled by the log_bin server setting.

If it's on, this offers some significant benefits. Replication isn't possible without it, and you'll be unable to do a point-in-time restore if your database gets corrupted and all you have is a backup from 12 hours ago.

However, writing to the binary log and the master.info file is yet another thing for your disk to do.

If you don't need the benefits that the binary log offers, you can turn it off.

The innodb logfile

This is generally where most write activity occurs.

The default behaviour is this: when you commit a change to the database, the database will "write" what's happened to the innodb logfile (usually called ib_logfile*), and immediately "flush" that change to disk.

There's a difference between "write" and "flush" - writing a file to disk means that MySQL has told the Operating System to make a change, but the Operating System might choose to wait before it actually writes the file to disk. "Flushing" the file means MySQL will force the OS to physically write it to disk.

However, this behaviour can be modified through the MySQL variable innodb_flush_log_at_trx_commit.

If possible I recommend leaving this at the default of 1 - this is by far the safest setting for MySQL.

If the value is 2, the database will "write" to disk when a transaction is committed, but "flush" once per second - giving the OS more time to batch up its writes to the innodb logfiles, and generally improving performance slightly.

If the value is 0, the database will only "write" and "flush" to disk once per second.

The innodb data file

Surprisingly, MySQL will generally do what it can to delay writing data to an InnoDB data file. Depending on the value of innodb_max_dirty_pages_pct, and your version of MySQL, it might wait until more than 75% or even 90% of your database has changed before it worries about pushing data from memory to the data files on disk.

InnoDB will also forcibly write changes to disk if the log files are rotated out (the innodb log file size affects how big the log files are supposed to be). Getting this value right for your server will be somewhat trial and error - you may need the value low, so that you don't slow the server down when a file expires, or you may need it large, so that you can handle your data import without needing MySQL to flush the data files to disk.