patternsqlMinor
Is tuning the innodb_buffer_pool_size important on Solaris ZFS?
Viewed 0 times
theinnodb_buffer_pool_sizetuningsolarisimportantzfs
Problem
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?
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?
Solution
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
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.
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.
Context
StackExchange Database Administrators Q#1975, answer score: 5
Revisions (0)
No revisions yet.