MySQL Bugs: #60087: Patch: Minimize InnoDB I/O load by adding a prefetch stage

Bug #60087	Patch: Minimize InnoDB I/O load by adding a prefetch stage
Submitted:	10 Feb 2011 13:14	Modified:	10 Feb 2011 13:25
Reporter:	Lenz Grimmer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Contribution, innodb, performance, Prefetch

Description:
Steve Hardy is CTO of Zarafa, an open-source groupware vendor using MySQL as storage platform. Steve has been working on several MySQL optimizations and he talked about the following InnoDB improvement at this year's FOSDEM 2011 conference in Brussels.

The goal of this patch is to minimize I/O load on a server during random access on traditional magnetic harddisks by implementing a prefetch stage to the InnoDB storage engine.

For more details and the actual patch please see this Wiki page:

http://www.zarafa.com/wiki/index.php/MySQL_5.5_InnoDB_prefetch_patch

How to repeat:
The InnoDB row engine basically works sequentially; when requesting multiple pages in a table, each table will be posted to the I/O subsystem separately, then InnoDB will wait for the data to come back from the disk, and then processing of the page continues.

Suggested fix:
Review and apply the patch proposed by Steve.

Please integrate the Patch!

Thank you!

Regards Nico

Implementing prefetch (as in this patch or any other form) would dramatically improve performance on sequential accesses.
Only the performance increase archived for mysqldump (see link above) already represents a huge step forward (as dumping is a very slow operation atm).

I would really love to see this implemented!

Cheers,
Sebastian

Since this patch could improve performance for very big databases (which not only Zarafa could benefit from) please include it in one of the next releases.

Regards Felix

Please implement this patch.

Ou mysqldump runs for most of ths night now, so more speed is needed as the db keeps groing.

Thanks in advance,
Jaap Huib

Hi all,

are there any updates about this issue?

Greetings,
Sebastian

I'd just like to say that this patch has a number of major performance improvements over stock innodb:

- Speedups of up to N times with N being the number of spindles you have (measured impact of 5x speed with 4 disks for example)
- Speedups of range queries and full table scans
- Speedups of multi-range queries
- Speedups of O_DIRECT setup (which don't get filesystem readahead)
- Only minor drawback of possible readahead for LIMIT clauses

It's really a shame that this hasn't been picked up yet; it helps people with large database do much, much faster mysqldumps and queries ..

Hi,

I'm looking for a way to speed up several mysql dumps on different systems at our customers servers.
Therefore i'm waiting to see the integraton of this patch.

Are there any steps taken to integrate this?
When can we see the integration happening (as in which version)?

Thanks in advance,

Kind Regards,

Van Herck Jeff