MySQL Bugs: #29352: Falcon full table scan is slow

Bug #29352	Falcon full table scan is slow
Submitted:	26 Jun 2007 6:50	Modified:	26 May 2010 17:49
Reporter:	Yoshinori Matsunobu	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S5 (Performance)
Version:	6.0.1-alpha	OS:	Any
Assigned to:	Kelly Long	CPU Architecture:	Any
Tags:	F_PERFORMANCE
Triage:	Triaged: D3 (Medium) / R4 (High) / E5 (Major)

Description:
I found that Falcon takes much more time for full table scan than InnoDB.

How to repeat:
1. Load 10M records as desribed in http://bugs.mysql.com/bug.php?id=29300
2. Run full table scan for each table:

# innodb
mysql> select * from t1 where c2 = 1000; 
+------+------+----------------------+
| c1   | c2   | c3                   |
+------+------+----------------------+
| 1000 | 1000 | ABCDEFGHIJabcdefghij |
+------+------+----------------------+
1 row in set (5.46 sec)

# falcon
mysql> select * from t2 where c2 = 1000; 
+------+------+----------------------+
| c1   | c2   | c3                   |
+------+------+----------------------+
| 1000 | 1000 | ABCDEFGHIJabcdefghij |
+------+------+----------------------+
1 row in set (33.12 sec)

Verified as described.

InnoDB
mysql> select * from t1 where c2 = 1000;
+-----+------+----------------------+
| c1  | c2   | c3                   |
+-----+------+----------------------+
| 999 | 1000 | ABCDEFGHIJabcdefghij |
+-----+------+----------------------+
1 row in set (6.31 sec)

Falcon
mysql> select * from t2 where c2 = 1000;
+-----+------+----------------------+
| c1  | c2   | c3                   |
+-----+------+----------------------+
| 999 | 1000 | ABCDEFGHIJabcdefghij |
+-----+------+----------------------+
1 row in set (52.32 sec)

Best regards,

Hakan

Jim, Can you address this performance issue?

Theses triage values assume that the solution is a read-ahead capability

Latest numbers with mysql-6.0-falcon-team tree on a 8-CPU/8GB machine

InnoDB:
- load 10 million rows
     02:04 min, 01:39 min, 01:38 min
- select count(*)
     04.57 sec, 04.57 sec, 04.57 sec
- select * from t1 where c2 = 1000
     05.84 sec, 05.85 sec, 05.84 sec

Falcon, page size 4k:
- load 10 million rows
     04:22 min, 04:21 min, 04:22 min
- select count(*)
     49.57 sec, 49.90 sec, 49.44 sec
- select * from t1 where c2 = 1000
     51.36 sec, 50.41 sec, 46.19 sec

Falcon, page size 32k:
- load 10 million rows
     09:42 min, 09:41 min, 09:42 min
- select count(*)
     01:13 min, 45.33 sec, 51.87 sec
- select * from t1 where c2 = 1000
     46.90 sec, 51.40 sec, 50.67 sec

Result:
  - page size has no effect on table scan
  - table scan is a magnitude slower on Falcon than on InnoDB

Kelly, Here is a performance bug we have had in the list for a long time.  We need to understand this difference that Hakan have verified twice over the last year.