Partitioning in MySQL 5.1

Sheeri Cabral of the Pythian Group ( presented a session on the new partitioning feature in MySQL 5.1 at the January 2009 Boston User Group.

From her description:
I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

The slides are available at

The partitioning part of the MySQL Manual is at:

The functions that are not allowed in partitioning expressions are listed at:

We also had an interesting development — according to the manual, an INSERT to a partitioned table that includes values that do not have a partition should insert all values up to the failure point. The example I used (and that you can see in the video) is:

insert into nums_list_mod (id) VALUES (4001, 4004, 4002);

Where 4001 and 4002 fit into partitions but there is no partition defined that will accept 4004. According to the manual, the value 4001 should be inserted, but 4004 and 4002 should not. Instead, we got the behavior that nothing was inserted (sql_mode= and this was not within a transaction).

Enjoy the presentation!

See video


Unfortunately the key buffer

Unfortunately the key buffer iteslf has a big fat mutex around it (one per key buffer, that is, just one unless you use multiple key buffers), which is taken any time anyone modifies the key buffer (which includes bringing pages in AND out of it, which is done by selects as well as updates).This means that operations which do a large amount of key buffer work effectively block up the entire server even if they're operating on an entirely different table.The example we see, is doing a lot of inserts with DELAY_KEY_WRITE on, then doing a flush table on that table. It effectively stops the world during the flush no index on any table at all may be touched whatsoever.

Post new comment

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options