| 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: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S5 (Performance) |
| Version: | 5.1.29, 6.0.x | OS: | Any |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
| Tags: | mmap, read_buffer_size, regression, table scan, temporary table | ||
[11 Nov 2008 11:48]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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)
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;