We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), and are getting some pretty slow inserts (e.g. simple insert of a single row taking 90 seconds!).
Our innodb_buffer_pool_size is set to 400MB, which would normally be way too low for this kind of setup. However, our hosting provider advises that this is irrelevant when running on ZFS. Is he right?
No, he's not right. If a block is not in the InnoDB cache, then it has to be fetched, which means it will come either from disk or from the ZFS cache, at which point two copies of it exist in main memory. If you use that block, it will come out of the InnoDB cache. If you write that block, it will go from the InnoDB cache to the disk. The ZFS cache is just a helpless spectator in this scenario.
However, if your issue is INSERT performance, it's unlikely to be related to this unless your system is under a very heavy load... is it? From the docs:
If an index record should be inserted into a nonunique secondary index, InnoDB checks whether the secondary index page is in the buffer pool. If that is the case, InnoDB does the insertion directly to the index page. If the index page is not found in the buffer pool, InnoDB inserts the record to a special insert buffer structure. The insert buffer is kept so small that it fits entirely in the buffer pool, and insertions can be done very fast.
Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions into the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.
The insert buffer merging may continue to happen after the inserting transaction has been committed. In fact, it may continue to happen after a server shutdown and restart (see Section 13.2.6.2, “Forcing InnoDB Recovery”).
Insert buffer merging may take many hours when many secondary indexes must be updated and many rows have been inserted
SHOW ENGINE INNODB STATUS should show you exactly what the DB is waiting on. Also see if strace can help.
He's not correct.
In a nutshell, what he is arguing is that you do not need such a large InnoDB buffer pool because ZFS has some fancy caches like a L2 ARC.
While cache misses might be less expensive, each page will still needed to be loaded into InnoDB for modifications to be made, and a smaller buffer pool will mean more churning of what pages are loaded in memory.
The internal lock (mutex) held during page replacement (buf0buf) in versions of MySQL prior to 5.5 is a real hot spot, and I'm sure it would be very trivial to write a benchmark to prove him wrong.
(Other steps have to happen during page replacement too, such as verifying/updating page checksums).
I would still set the innodb_buffer_pool_size much higher that 400M. The reason? InnoDB Buffer Pool will still cache the data and index pages you need for tables accessed frequently.
Run this query to get the recommended innodb_buffer_pool_size in MB:
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine='InnoDB') A,(SELECT 2 pw) B;
Simply use either the result of this query or 80% of installed RAM (in your case 19660M) whichever is smaller.
I would also set the innodb_log_file_size to 25% of the InnoDB Buffer Pool size. Unfortunately, the maximum value of innodb_log_file_size is 2047M. (1M short of 2G) Thus, set innodb_log_file_size to 2047M since 25% of innodb_buffer_pool_size of my recommendated setting is 4915M.
Yet another recommedation is to disable ACID compliance. Use either 0 or 2 for innodb_flush_log_at_trx_commit (default is 1 which support ACID compliance) This will produce faster InnoDB writes AT THE RISK of losing up to 1 second's worth of transactions in the event of a crash.
zfs will use the available memory to cache. Your database will be better because you can dedicate that to exactly the purpose where you want to spend your memory on. zfs cache can be a good second. 90 seconds for a single insert looks like ages. Is it a video that is inserted? Is something like a physical standby database in place in synchronous mode? Does the table happen to be a remote table? Do you have indexes or triggers on that table that cause a lot of extra work? How many disk are in the raid 10 set? Do you have a transaction log active? Is that log location full? I don't know how to tune innodb so I can not give you any guarantee but if this was oracle, for sure, this is no memory problem, there is something more going on in the database that slows the insert to an amazing 'speed' unless you happen to be inserting something like a video with 'Rambo' or something with a similar size. ZFS is in development; how up to date is your Solaris and zfs installation?
Your hosting provider is incorrect. There are various things you should tune differently when running MySQL on ZFS, but reducing the innodb_buffer_pool_size is not one of them. I wrote an article on the subject of running MySQL on ZFS and gave a lecture on it a while back. Specifically regarding innodb_buffer_pool_size, you should do is set it to whatever would be reasonable on any other file system, and because O_DIRECT doesn't mean "don't cache" on ZFS, you should set primarycache=metadata on your ZFS file system containing your datadir. There are other optimisations to be made, which you can find in the article and the lecture slides.
