maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

  Hi,

I think I found and fixed a bottleneck in how string literals
are processed.


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
  max_length properly.



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,
  like in:
    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.

Thanks.

Attachment: mdev-6218v136.diff.gz
Description: GNU Zip compressed data

#include <stdio.h>

static void
one_row()
{
  int i;
  printf("INSERT INTO t1 VALUES ('");
  for (i= 0; i < 10*1024*1024; i++)
    printf("a");
  printf("');\n");
}

int main()
{
  int i;
  printf("DROP TABLE IF EXISTS t1;\n");
  printf("CREATE TABLE t1 (a LONGBLOB) ENGINE=MyISAM;\n");
  for (i= 0 ; i < 1024; i++)
    one_row();
}