maria-developers team mailing list archive
Mailing list archive
Please review: 10% performance improvement for INSERT INTO t1 (blob) VALUES ('long value')
I think I found and fixed a bottleneck in how string literals
The attached patch improves INSERT performance for strings,
and especially for long blob and text values.
Insertion time for 10MByte blob 1024 times (10GByte of data total)
reduced on my laptop from 300 seconds to 274 seconds, which gives
near 10% pure time improvement.
What the patch changes:
Before the patch :
1. The function get_text() is sql_lex.cc allocated an unescaped
copy of every string literal
(unescaping backslashes and double quotes, if any).
Strangely, copying happened even if there were no really
any escapes in the string.
2, The syntax parser in sql_yacc.yy created Item_string using the
new unescaped buffer.
Furthermore, in case of a multi-byte connection character set,
the constructor for Item_string made another loop on the unescaped
buffer, to calculate length in characters, which is needed to set
After the patch:
Item is now created using directly the SQL fragment,
without making a copy. Even for escaped values!!!
Length in characters is also calculated during the very first pass
in get_text(), without any additional loops in the Item constructors.
Unescaping is now done in the very end, when the value is actually needed:
- Either in Field::store().
This happens when the string value is used in:
INSERT INTO t1 VALUES('string');
Unescaping is done directly to the Field buffer, without any
intermediary temporary storage.
- Or in val_str(), if the string value is used elsewhere
(in SELECT list, functions, operators, etc).
The unescaped value is cached, to make sure that unescaping
is done only one time, even if the value is needed for multiple rows,
SELECT * FROM t1 WHERE a='string with backslash or quote escapes';
The patch also fixes a few bugs related to string conversion/copying
(some of them were reported on JIRA earlier, and some of them
I found while working on the patch).
Please also find "a.c" attached. I used it to test the improvement.
I used a RelWithDebInfo compiled binary for tests.
Note, "perf" and "valgrind --tool=cachegrind" also demonstrate a good
improvement (reduced CPU and memory usage).
I can send their statistics in a separate letter, if anyone is interested.
Please review the patch.
Description: GNU Zip compressed data
printf("INSERT INTO t1 VALUES ('");
for (i= 0; i < 10*1024*1024; i++)
printf("DROP TABLE IF EXISTS t1;\n");
printf("CREATE TABLE t1 (a LONGBLOB) ENGINE=MyISAM;\n");
for (i= 0 ; i < 1024; i++)