1

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?

(Apologies for the double post on https://dba.stackexchange.com/questions/1975/is-tuning-the-innodb-buffer-pool-size-important-on-solaris-zfs, but I'm not sure how big the audience is over there!)

Community
  • 1
  • 1
Mark B
  • 4,768
  • 2
  • 25
  • 30
  • http://cdn.oreillystatic.com/en/assets/1/event/21/Optimizing%20MySQL%20Performance%20with%20ZFS%20Presentation.pdf here you have several tuning tips – juanmf Mar 26 '15 at 02:14

3 Answers3

2

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, what 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.

Gordan Bobić
  • 1,748
  • 13
  • 16
1

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.

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
0

May be worth reading slow-mysql-inserts if you haven't already. Also this link to the mysql docs on the matter - especially with regards to considering a transaction if you are doing multiple inserts to a large table.

More relevant is this mysql article on performance of innodb and zfs which specifically considers the buffer pool size.

The headline conclusion is;

With InnoDB, the ZFS performance curve suggests a new strategy of "set the buffer pool size low, and let ZFS handle the data buffering."

You may wish to add some more detail such as the number / complexity of the indexes on the table - this can obviously make a big difference.

Apologies for this being rather generic advice rather than from personal experience, I haven't run zfs in anger but hope some of those links might be of use.

Community
  • 1
  • 1
Roger
  • 15,793
  • 4
  • 51
  • 73
  • 2
    The official oracle recommendation is to disable the ZFS ARC on the mysql dataset, and let InnoDB handle the caching – Allan Jude Jan 12 '14 at 21:18