Bug #41885 Falcon: slowdown in 6.0.9 in update_of_key_big test from mysql-bench suite

• Submitted: 5 Jan 2009 20:15
• Modified: 26 May 2010 17:50
• Reporter: Alexey Stroganov
• Status: Unsupported
• Category: MySQL Server: Falcon storage engine
• Severity: S3 (Non-critical)
• Version: 6.0.9-release
• OS: Any
• Assigned to: Olav Sandstå
• CPU Architecture: Any
• Tags: F_PERFORMANCE, regression

[5 Jan 2009 20:15] Alexey Stroganov

Description:
MySQL 6.0.9/Falcon almost two times slower than MySQL 6.0.8/Falcon in update_of_key_big  test from mysql-bench suite.

I've simplified test case and run it for 6.0.8 and 6.0.9:

perl bug-update_of_key_big.pl --db-socket=/tmp/608.sock
MySQL 6.0.8 alpha, ENGINE: Falcon
Creating table bench1
Inserting 50000 rows in order
Time for insert_in_order (50000):  7 wallclock secs 
Time for update_of_key_big (50000): 23 wallclock secs

perl bug-update_of_key_big.pl --db-socket=/tmp/609.sock
MySQL 6.0.9 alpha, ENGINE: Falcon
Creating table bench1
Inserting 50000 rows in order
Time for insert_in_order (50000):  7 wallclock secs 
Time for update_of_key_big (50000): 45 wallclock secs 

oprofile output:
6.0.8
Profiling through timer interrupt
samples  %        symbol name
947      18.7711  SyncObject::lock(Sync*, LockType, int)
641      12.7056  SyncObject::unlock(Sync*, LockType)
323       6.4024  Bitmap::setSafe(int)
321       6.3627  Record::release()
299       5.9267  Record::addRef()
198       3.9247  Table::fetch(int)
189       3.7463  RecordGroup::store(Record*, Record*, int, RecordSection**)
145       2.8741  Sync::lock(LockType)
143       2.8345  RecordLeaf::store(Record*, Record*, int, RecordSection**)
123       2.4381  Sync::~Sync()
111       2.2002  Table::insert(Record*, Record*, int)
86        1.7047  RecordLeaf::fetch(int)
72        1.4272  Sync::Sync(SynchronizationObject*, char const*)
64        1.2686  Record::poke()
60        1.1893  RecordVersion::getTransaction()
59        1.1695  Bitmap::set(int)
56        1.1100  Thread::getThread(char const*)

6.0.9
samples  %        symbol name
985      12.7706  SyncObject::lock(Sync*, LockType, int)
664       8.6088  SyncObject::unlock(Sync*, LockType)
570       7.3901  Record::release()
404       5.2379  Record::addRef()
382       4.9527  MemFreeBlock::insert(MemFreeBlock*)
339       4.3952  Bitmap::setSafe(int)
326       4.2266  RecordVersion::~RecordVersion()
261       3.3839  Sync::~Sync()
243       3.1505  Table::fetch(int)
218       2.8264  RecordGroup::store(Record*, Record*, int, RecordSection**)
212       2.7486  Sync::lock(LockType)
179       2.3208  MemMgr::releaseBlock(MemBlock*)
157       2.0355  RecordLeaf::store(Record*, Record*, int, RecordSection**)
155       2.0096  MemFreeBlock::findNextLargest(int)
140       1.8151  SRLUpdateRecords::append(Transaction*, RecordVersion*, bool)
127       1.6466  Table::insert(Record*, Record*, int)
114       1.4780  RecordLeaf::fetch(int)

How to repeat:
1. start server with default settings:

./bin/mysqld --no-defaults --basedir=. --datadir=data/

2. run attached test case:

perl bug-update_of_key_big.pl --db-socket=<path to db socket>

[5 Jan 2009 20:19] Alexey Stroganov

Test case for the issue

Attachment: bug-update_of_key_big.pl (application/octet-stream, text), 2.35 KiB.

[7 Jan 2009 6:25] Kevin Lewis

Ranger,  Does this slowdown occur when you turn off MRR?

[8 Jan 2009 16:38] Alexey Stroganov

Kevin,

Disabling of MRR doesn't affect results:

MySQL 6.0.8 alpha, ENGINE: Falcon
@optimizer_use_mrr=force
Time for update_of_key_big (496): 22 wallclock secs 

MySQL 6.0.8 alpha, ENGINE: Falcon
@optimizer_use_mrr=disable
Time for update_of_key_big (496): 22 wallclock secs 

MySQL 6.0.9 alpha, ENGINE: Falcon
@optimizer_use_mrr=force
Time for update_of_key_big (496): 46 wallclock secs

MySQL 6.0.9 alpha, ENGINE: Falcon
@optimizer_use_mrr=disable
Time for update_of_key_big (496): 44 wallclock secs