Databases Reference
In-Depth Information
There is a dark side to
innodb_file_per_table
: slow
DROP TABLE
performance. This can
be severe enough to cause a noticeable server-wide stall, for two reasons:
• Dropping the table unlinks (deletes) the file at the filesystem level, which can be
very slow on some filesystems (ext3, we're looking at you). You can shorten the
duration of this with tricks on the filesystem: link the
.ibd
file to a zero-sized file,
then delete the file manually, instead of waiting for MySQL to do it.
• When you enable this option, each table gets its own tablespace inside InnoDB. It
turns out that removing the tablespace actually requires InnoDB to lock and scan
the buffer pool while it looks for pages belonging to this tablespace, which is very
slow on a server with a large buffer pool. If you're going to be dropping a lot of
InnoDB tables (including temporary tables) and you use
innodb_file_per_table
,
you might benefit from the fix included with Percona Server, which lets the server
lazily invalidate the pages belonging to the dropped tables. You just need to set the
innodb_lazy_drop_table
option.
What's the final recommendation? We suggest that you use
innodb_file_per_table
and cap the size of your shared tablespace to make your life easier. If you run into any
circumstances that make this painful, as noted above, consider one of the fixes we
suggested.
We should also note that you don't actually have to store your InnoDB files in a tra-
ditional filesystem. Like many traditional database servers, InnoDB offers the option
of using a raw device—i.e., an unformatted partition—for its storage. However, today's
filesystems can handle sufficiently large files that you shouldn't need to use this option.
Using raw devices might improve performance by a few percentage points, but we don't
think this small increase justifies the disadvantages of not being able to manipulate the
data as files. When you store your data on a raw partition, you can't use
mv, cp
, or any
other tools on it. Ultimately, the tiny performance gains you get from using raw devices
aren't worth the extra hassle.
Old row versions and the tablespace.
InnoDB's tablespace can grow very large in a write-
heavy environment. If transactions stay open for a long time (even if they're not
doing any work) and they're using the default
REPEATABLE READ
transaction isolation
level, InnoDB won't be able to remove old row versions, because the uncommitted
transactions will still need to be able to see them. InnoDB stores the old versions in the
tablespace, so it continues to grow as more data is updated. Sometimes the problem
isn't uncommitted transactions, but just the workload: the purge process is only a single
thread until recent versions of MySQL, and it might not be able to keep up with the
number of old row versions that need to be purged.
In either case, the output of
SHOW INNODB STATUS
can help you pinpoint the problem.
Look at the history list length; it shows the size of the undo log, in units of pages.
You can corroborate this by examining the first and second lines of the
TRANSACTIONS
section, which show the current transaction number and the point to which the purge
