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).
