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)

[26 Jun 2007 6:50] Yoshinori Matsunobu
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)
[26 Jun 2007 9:27] Hakan Küçükyılmaz
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
[26 Oct 2007 9:24] Kevin Lewis
Jim, Can you address this performance issue?
[30 Jan 2008 21:47] Kevin Lewis
Theses triage values assume that the solution is a read-ahead capability
[31 Jan 2008 15:44] Hakan Küçükyılmaz
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
[29 Aug 2008 20:08] Kevin Lewis
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.