Bug #34087 Truncate is extremely slow for Falcon tables

• Submitted: 27 Jan 2008 4:27
• Modified: 27 Jan 2008 22:53
• Reporter: Mark Callaghan
• Status: Can't repeat
• Category: MySQL Server: Falcon storage engine
• Severity: S5 (Performance)
• Version: 6.0.3-alpha
• OS: Any
• Assigned to: Hakan Küçükyılmaz
• CPU Architecture: Any
• Tags: falcon, slow, truncate

[27 Jan 2008 4:27] Mark Callaghan

Description:
Truncate for falcon tables is either very slow or hangs.

How to repeat:
create table fl2(d double, i int not null auto_increment, primary key (i) ) engine=falcon;

create table fl(d double primary key) engine=falcon;
-- insert 3M rows into fl

-- this takes about 10 seconds
insert into fl2 select * from fl limit 0, 300000;

-- wait 10 seconds

-- each of these takes a few seconds
select count(*) from fl2;
select count(*) from fl2;
select count(*) from fl2;

-- this has been running for minutes, given that the insert was so fast
-- I don't know why this is so slow. The CPU is pinned at 100%.
truncate table fl2;

oprofile from the truncate:
CPU: AMD64 processors, speed 2200 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Cycles outside of halt state) with a unit mask of 0x00 (No unit mask) count 100000
samples  %        image name               app name                 symbol name
690111   32.9586  mysqld                   mysqld                   RecordVersion::scavenge(RecordScavenge*)
587972   28.0806  mysqld                   mysqld                   RecordScavenge::inventoryRecord(Record*)
375693   17.9425  mysqld                   mysqld                   Record::getPriorVersion()
167621    8.0053  mysqld                   mysqld                   RecordVersion::getPriorVersion()
61281     2.9267  mysqld                   mysqld                   RecordLeaf::retireRecords(Table*, int, RecordScavenge*)
39056     1.8653  mysqld                   mysqld                   RecordLeaf::inventoryRecords(RecordScavenge*)
26114     1.2472  mysqld                   mysqld                   RecordVersion::isVersion()
25326     1.2095  mysqld                   mysqld                   SyncObject::lock(Sync*, LockType)
24244     1.1579  bash                     bash                     (no symbols)
22369     1.0683  mysqld                   mysqld                   Sync::lock(LockType)
18871     0.9012  python2.4                python2.4                (no symbols)
6337      0.3026  mysqld                   mysqld                   RecordGroup::inventoryRecords(RecordScavenge*)
5837      0.2788  mysqld                   mysqld                   RecordGroup::retireRecords(Table*, int, RecordScavenge*)
5668      0.2707  oprofiled                oprofiled                (no symbols)
5123      0.2447  opreport                 opreport                 (no symbols)
2965      0.1416  mysqld                   mysqld                   SyncObject::unlock(Sync*, LockType)
2643      0.1262  libstdc++.so.6.0.7       libstdc++.so.6.0.7       (no symbols)

[27 Jan 2008 22:53] Hakan Küçükyılmaz

TRUNCATE works a lot faster now in latest Falcon:

[23:48] root@test>create table fl2(d double, i int not null auto_increment, primary key (i) )
    -> engine=falcon;

[23:50] root@test>insert into fl2 (d) select * from ml limit 0, 300000;
Query OK, 300000 rows affected (2.88 sec)
Records: 300000  Duplicates: 0  Warnings: 0

[23:52] root@test>truncate table fl2;
Query OK, 300000 rows affected (0.36 sec)