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?
migrated from stackoverflow.com May 2 '13 at 14:14
This question came from our site for professional and enthusiast programmers.
I have five(5) aspects to discuss here
ASPECT #1 : innodb_buffer_pool_size
You need to run this query
This will give you the ideal sized buffer pool because InnoDB caches data and index pages.
If the DB Server has
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
Your Worst Nightmare
According to the Book
Page 428 Paragraph 8 says the following:
This excerpt is for MySQL 5.0.13. InnoDB has matured a lot since then, but the principle remains the same: Setting
Look at the default
You will have to experiment with larger values, perhaps 16M, 32M, 64M, or 128M.
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
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:
If you get
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
What is also noteworthy is this regard is the system tablespace
InnoDB has many moving parts in ibdata1:
Expect the Highest I/O around
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:
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.
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.