myisam_use_mmap does not use mmap after a massive insert

The task that I am trying to solve is very simple: I want to be able to have fast reads from tables with thousands of entries using index (fast random read in case of MyISAM).

In newer versions of MySQL (e.g. 5.1 that I am using) there is an option called myisam_use_mmap, that greatly (more than 2 times) improves speed of random read in MyISAM.

The problem is that if I create an empty table and then perform massive insert (700k rows) into this table (either using INSERT INTO ... SELECT * FROM ... or LOAD DATA LOCAL INFILE), it does mmap the inserted rows as it should (it should remmap every 1000 insertions)

It results in quite poor performance: even InnoDB performs better if this index is set as primary key

Any alteration of table, e.g. OPTIMIZE TABLE makes MySQL / MariaDB remmap the table properly

Answer Answered by Sergei Golubchik in this comment.

You're right.

I couldn't find this in the manual, but I see it in the source code.

It looks like a bug then. I'd suggest you to submit a bug report at http://bugs.mysql.com (and mention that this behavior is not documented - which is a second, documentation, bug).

I'm sure this bug applies to MariaDB as well, still we really prefer bugs to be fixed upstream. But if you'll get no reaction from Oracle in a reasonable time frame, feel free to resubmit the bug at https://bugs.launchpad.net/maria/ (and quote MySQL's bug number there please).

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.