InnoDB optimization
deekayen - July 7, 2009 - 17:46
| Project: | Project Issue File Review |
| Version: | 6.x-2.x-dev |
| Component: | Documentation |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed |
Description
Working with nnewton, he suggested the following file for /etc/mysql/conf.d/innodb.cnf to help optimize InnoDB testing for the type of environment we're testing in. With this configuration alone, there is still enough table creation and dropping that the performance even on a nice server is poor. This config could help, but needs continued revision.
[mysqld]
innodb_buffer_pool_size=1G
innodb_flush_method=O_DIRECT
innodb_additional_mem_pool_size=10M
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=0To apply this configuration to a server that had previously running InnoDB instances, you must stop MySQL and remove all the ib_logfile* files from /var/lib/mysql.
#1
We appear to have wrapped up for now with the following conclusions:
As long as InnoDB is available, even if MyISAM is default, I'm pretty sure #301362: Default to InnoDB in MySQL will cause Drupal to be installed as InnoDB, so I don't think we can just ignore testing on InnoDB. Making significant optimizing probably means going back to the testing process to find out how the queries to or use of the database can be optimized in a way that doesn't cause so much disk writing.
#2
I'm running MyISAM right now. So far, it doesn't seem noticeably faster, but I don't have exact times to report (yet). What is noticeable is there appears to be a lot less disk writes going on from MySQL. kjournald is taking up 90%+ of the iotop write time.
#3
Note the times I provide in this update are just for running simpletests on the final self check stage. It doesn't include all the previous self tests, checking out, installing, etc.
The final round of tests with InnoDB took 39 minutes from review.runTests() to review.resetStatics(). The same server with the same setup, with turning InnoDB support off, took 45 minutes using MyISAM from review.runTests() to review.resetStatics(). This means the previously mentioned theory that MyISAM is faster than InnoDB for testing might be false. Attached are the configuration files for MySQL. Watching top output never spiked CPU over 21%. More than half of the i/o was kjournald, so XFS might be the next variable to try changing.
Server specs: Dual Xeon 5420, SATA II 250GB 3ware RAID 0, 8gb RAM, Debian 5.0 Lenny 64bit, eaccelerator 0.9.5.3, MySQL 5.0.51a, noatime added on /etc/fstab
Client config: Testing timeout 120 minutes, concurrency 8
I did the same over at a 512MB ram VPS on http://www.slicehost.com/ with concurrency 1. The INNO result with my.cnf defaults came back in 58 minutes. Note, that's a shared server and resources are capped, but could burst to be effectively RAID-10 with 4 cores instead of the 8 above. I never saw the CPU on the VPS go over 12% or so through top.
Then I tried on a single core AMD 2.133 Ghz machine running Ubuntu 9.04 Jaunty, 768 mb ram, and software raid 0 between two pata drives. The MySQL 5.1.31 result on ISAM with my.cnf defaults came back in 56 minutes. The CPU did hover in the 80-90% range a lot of the time, so that might be a sign of being CPU-bound at 2.133 Ghz.
I'm tempted to conclude the concurrency on the first server doesn't help a lot. I did see it firing off multiple php processes, but there are other bottlenecks that are preventing that from being an effective solution to add speed. I can keep trying things, but I'd prefer to get some input from others before following a dead end.
#4
I applied noatime to /etc/fstab and the nnewton's suggested InnoDB configuration on the slicehost VPS. Instead of taking 58 minutes, it took only 37. That's somewhere in line with the standalone dual quad core intel that was not shared with other Xen instances. Remember, it's a shared Xen instance, so that could affect the numbers here.
Then I reformatted the AMD 2.133 Ghz Jaunty machine and put XFS on the software raid 0 partition with noatime in /etc/fstab and the mysql tmpdir pointed at a tmpfs. It is standalone, but since the current version of PIFR only offers ISAM for 5.1 (and since I'm comparing to last time), the INNO optimization doesn't apply because 5.1 apparently has to run as ISAM. However, by doing all those optimizations, it was CPU-limited once again. Disk I/O isn't a factor - iotop showed only mysql making any measurable disk writes and it only burst to 1400 K/s. Because of the following hdparm output (and watching the HD activity light), I'm sure it's not drive-bound with this setup on a single processor machine:
sudo hdparm -tT /dev/md0
/dev/md0:
Timing cached reads: 184 MB in 2.00 seconds = 91.96 MB/sec
Timing buffered disk reads: 120 MB in 3.04 seconds = 39.48 MB/sec
The top output on the Jaunty machine showed an average of about 10% system CPU consumption, which I suspect was managing the RAID 0 array (software). I am making an educated guess that a hardware raid array would have freed a measurable amount of CPU cycles.
By switching to XFS (from Ext3), adding noatime, and moving MySQL's tmpdir to a tmpfs on Jaunty ISAM, the test remained on par with before at 56 minutes confirming it was CPU-bound at 2.133 Ghz. Moving MySQL's tmpdir to tmpfs was a waste of time because it never got used as far as I can tell (according to
df -h), though I'm not sure if MySQL does garbage collection and and used it in a way I wouldn't have caught it ondf -h.In summary...
On a machine with lots of CPU, adding noatime to /etc/fstab can improve performance for testing. Moreover, for machines with enough CPU that run InnoDB tests, nnewton's aforementioned configuration changes can help as well.
#5
I removed the software RAID 0 from the Jaunty machine and mounted / and /home on separate physical drives. MySQL worked off / and the testing was in a user directory on /home, both on XFS. Instead of the 56 minutes I got twice previously, it takes 76-93 minutes. iotop never made the i/o look stressed out when I glanced at it, but since that was the only difference between the 56 and 76/93 minute runs, it does appear that even a slower CPU can be i/o bound with concurrency 1. The system % of CPU consumption didn't change (still around 10%), so my theory about software RAID 0 consuming CPU was apparently wrong. This result would also explain why concurrency 8 on the dual quad core machine was still slow because it only has two SATA II drives hardware RAID 0'ed together.
Based on this new information, I learned that to sway towards becoming CPU bound rather than I/O bound, you need a two drives RAID 0'ed per CPU. Very roughly extrapolated, to stress the CPU of a dual quad core machine, you'd need a RAID 0 of 16 drives. I know that sounds insane, but only continued testing will prove its accuracy I guess.
Reminder: this setup is with MySQL 5.1.31 ISAM.
#6
I rebuilt the Jaunty machine again on software RAID 0, but did 5.1.31 InnoDB instead of MyISAM. It was again CPU limited, and took 62 minutes on InnoDB instead of the 56 it took repeatedly MyISAM. I'm betting that's a statistically insignificant difference given the variation of commits to HEAD. Since I'm counting them the same, I'm going to move on to switching back to ISAM and up the concurrency to 2. Unfortunately on this machine, it might mean swapping, which is an extra undesired variable, but I'll find out.
#7
Continuing tests on the Jaunty machine, configured to be CPU limited... increasing concurrency from 1 to 2 made no change other than to consume more RAM for multiple PHP instances. It took as little as 56 minutes (on par with concurrency 1) up to 75 minutes. I'm thinking in the case of a CPU limited machine, configuring MySQL to run entirely from tmpfs won't bring a significant benefit.
I've done all I know that's reasonable to do to get my Jaunty machine optimized from a server config perspective by:
Which leaves it limited by the amount of available CPU, rather than RAM or I/O limits.
Ways I made it worse or wasted my time:
Once again, a rundown on the specs for it:
I also forgot to note previously that nnewton finds mysqlreport from hackmysql.com to be a helpful MySQL diagnosis tool.
wget hackmysql.com/scripts/mysqlreportchmod +x mysqlreport
./mysqlreport --user=root --password=password
#8
Caveat:
The following tuning was performed on a 500MHz Pentium III just running the Node* class of tests. I should get a chance this weekend to these tweaks out on a faster machine.
I found that changing innodb_buffer_pool_size to 32M (the default is 8M if not specified) did not result in any time benefit, but it did reduce the number of Innodb_buffer_pool_reads.
I found that changing max_heap_table_size to match tmp_table_size at 32M (the default for the former is 16M) did not result in any time benefit and no other performance metric appeared to improve.
N.B. MySQL will use the lower of the two values when deciding to create temporary tables in memory or on disk.
I found that changing table_cache to 256 (the default is 64) did not result in any time benefit, but it did reduce the number of opened tables.
#9
@phillipnet: to clarify, the alternate configurations you tried to InnoDB resulted in no improvement in test completion time?
#10
mysqlreport I mentioned in #7 is GPL, so I'm attaching it here for future reference in case the official download ever disappears
#11
Same as: #514016: MySQL on tmpfs
We need to figure out where to document this type of stuff...as not really issue queue.
#12
@deekayen: yes the changes I made didn't improve the time taken for the Node* tests, which surprised me.
Maybe making the same/better changes on a faster machine and running the full suite of tests will show an improvement. I expect to try this at the weekend
#13
I just moved MySQL to tmpfs with InnoDB on the same machine as #7 and it took the same 75-85 minutes as it did when MySQL was on the pata raid. It was definitely limited by CPU at 2.133 Ghz, not the I/O.
#14
I was just using this as a notepad. I'm done.