Bug #33648 Falcon selects on blobs above certain size are starved with default settings
Submitted: 3 Jan 2008 12:18 Modified: 26 May 2010 17:49
Reporter: Philip Stoev Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server: Falcon storage engine Severity:S3 (Non-critical)
Version:6.0.4-p2 OS:Any
Assigned to: CPU Architecture:Any
Tags: F_BLOB

[3 Jan 2008 12:18] Philip Stoev
When one runs an insert/update/delete workload against Falcon blobs, selects against the same blobs are starved and queries examining 30 blobs will take 10 to 30 seconds to complete, during which time hundreds of insert/update/delete operations proceed.

The problem is observed on tables with blobs with an average size of 128Kb. Tables with blobs with an average size of 12Kb do not appear to be affected. Queries not involving the blob column are not affected. Queries returning just a few rows are fast enough.

Only one unsert/update/delete and one select thread are involved. char() columns are not affected. Innodb blobs are not affected.

Running with --falcon_record_memory_max=200Mb and --falcon_page_cache_size=200Mb makes the problem appear only on queries which examine a thousand rows or more.

How to repeat:
1. Edit the header of the attached script and run it. The script will start its own mysqld and proceed to insert/delete/update blobs. Observe that those operations proceed at decent speed.

2. Connect to the mysqld using a mysql client and issue:

mysql> select length(bl) from blobtest.blobtest where id BETWEEN 9 AND 90;
This query will take 35 seconds to return about 26 rows.

mysql> select high_priority length(bl) from blobtest.blobtest where id BETWEEN 9 AND 90;
This query will take about 12 seconds.

3. Kill the perl script -- the server will continue to run. Observe that select queries are now more or less complete as if they were only I/O bound.

Suggested fix:
In my humble opinion, writes should not starve reads regardless of the memory situation. If I am wrong or this can not be achieved for theoretical reasons, can we consider adjusting the Falcon memory defaults upwards or provide guidance as to selecting proper values in the manual in the manual.
[3 Jan 2008 12:20] Philip Stoev
Perl script to reproduce bug #33648

Attachment: bug33648.pl (application/octet-stream, text), 1.89 KiB.

[4 Jan 2008 10:48] MySQL Verification Team
Thank you for the bug report.
[12 May 2009 12:07] Vladislav Vaintroub
Unassigning myself. Kevin, please find a new owner.