Bug #40634 table scan temporary table is 4x slower due to mmap instead instead of caching
Submitted: 11 Nov 2008 11:30 Modified: 23 Mar 2009 2:06
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S5 (Performance)
Version:5.1.29, 6.0.x OS:Any
Assigned to: Alexey Botchkov
Tags: mmap, read_buffer_size, regression, table scan, temporary table
Triage: Triaged: D2 (Serious)

[11 Nov 2008 11:30] Shane Bester
Description:
When performance a full table scan on a non-temporary myisam table, read_buffer_size is used to determine how much to read from the disk at once.

This doesn't happen for temporary tables, and thus performance is 5x worse (depending on disk caches).  Testcase has two selects.

First select reads non-temporary table t1 in blocks:
SUCCESS	Offset: 0 Length: 131072	
SUCCESS	Offset: 131072 Length: 131072	
SUCCESS	Offset: 262144 Length: 131072	

and takes: 1 row in set (5.77 sec)

Second select reads temporary-table t2 one row at a time:
SUCCESS	Offset: 0 Length: 20	
SUCCESS	Offset: 20 Length: 20
SUCCESS	Offset: 40 Length: 20
SUCCESS	Offset: 60 Length: 20

and takes: 1 row in set (31.16 sec)

How to repeat:
#setup two tables with a few million records:

flush tables;

drop table if exists t1;
drop temporary table if exists t2;

create table t1(a int,b int,c int,d int)engine=myisam;
create temporary table t2 (a varchar(100),
b varchar(100),c varchar(100),d varchar(100))engine=myisam;

insert into t1 values ('1','2','3','4'),('2','3','4','5'),
('3','4','5','6'),('4','5','6','7'),('5','6','7','8');

insert into t1 select t1.a,t1.b,t1.c,t1.d 
from t1,t1 a,t1 b,t1 c,t1 d,t1 e,t1 f,t1 g,t1 h,t1 i;

insert into t2 select * from t1;
flush tables;

select sql_no_cache max(length(a)),max(length(b)),
max(length(c)),max(length(d)) from t1;

select sql_no_cache max(length(a)),max(length(b)),
max(length(c)),max(length(d)) from t2;
[11 Nov 2008 11:48] Shane Bester
5.0 still worked properly, so this is a >= 5.1 bug

Sorry, ignore previous testcase.  this one is better:

flush tables;

drop table if exists t1;
drop temporary table if exists t2;

create table t1 (a varchar(100),
b varchar(100),c varchar(100),d varchar(100))engine=myisam;
create temporary table t2 (a varchar(100),
b varchar(100),c varchar(100),d varchar(100))engine=myisam;

insert into t1 values ('1','2','3','4'),('2','3','4','5'),
('3','4','5','6'),('4','5','6','7'),('5','6','7','8');

insert into t1 select t1.a,t1.b,t1.c,t1.d 
from t1,t1 a,t1 b,t1 c,t1 d,t1 e,t1 f,t1 g;

insert into t2 select * from t1;
flush tables;

select sql_no_cache max(length(a)),max(length(b)),
max(length(c)),max(length(d)) from t1;

select sql_no_cache max(length(a)),max(length(b)),
max(length(c)),max(length(d)) from t2;
[11 Nov 2008 12:23] Shane Bester
looks like "if(info->opt_flag & READ_CACHE_USED)" is false in _mi_read_rnd_dynamic_record() for tmp tables in 5.1?
[11 Nov 2008 13:02] Shane Bester
looks like 5.1 is trying mmap the temporary table because in init_read_record:

if (table->s->tmp_table == NON_TRANSACTIONAL_TMP_TABLE &&
      !table->sort.addon_field)
    VOID(table->file->extra(HA_EXTRA_MMAP));

it's doing this even though --myisam-use-mmap=OFF !  Can if it's memory mapped, then the cache will not be used.
[20 Nov 2008 16:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59416

2722 Alexey Botchkov	2008-11-20
      Bug#40634 table scan temporary table is 4x slower due to mmap instead instead of caching 
          mmap is slower that caching indeed.
          Here the problem is that mmap is used even if --myisam-use-mmap=OFF
      
          solved by checking the flag in ha_myisam::extra() as it is called in
          init_read_record()
      
      per-file comments:
        storage/myisam/ha_myisam.cc
      Bug#40634 table scan temporary table is 4x slower due to mmap instead instead of caching 
          do nothing for HA_EXTRA_MMAP if no opt_myisam_use_mmap
[21 Nov 2008 16:54] Ingo Strüwing
Approved by email.
[24 Nov 2008 17:16] Georgi Kodinov
Probably related to 37408
[23 Feb 2009 10:06] Shane Bester
Hi! what's the holdup for this bug ? been approved for months already..
[13 Mar 2009 19:04] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:patrick.crews@sun.com-20090225081629-ent6zn9d1lt6bx68) (merge vers: 5.1.33) (pib:6)
[18 Mar 2009 13:17] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:holyfoot@mysql.com-20090224115659-ju15jcofiff051zw) (merge vers: 6.0.10-alpha) (pib:6)
[23 Mar 2009 2:06] Paul Dubois
Noted in 5.1.33, 6.0.11 changelogs.

Table temporary scans were slower than necessary due to use of mmap
rather than caching, even with the myisam_use_mmap system variable
disabled.
[9 May 2009 16:41] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:39] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:36] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)