In our upcoming MariaDB 5.3 release Monty optimized the internal string append code for performance. I tested his patch with a plain MariaDB 5.2 vs. a patched MariaDB 5.2 with sql-bench, which showed an overall performance gain around 3%.
The details of the patch Monty describes like this
Patch to optimize string append:
While examining a trace output from a mysql-test-run case, I noticed a
lot of reallocation calls. This was strange as MariaDB/MySQL was
designed to do so few malloc/realloc as possible. The system uses was
to first calculate how big buffer you would need and then allocate a
buffer big enough for most usage. Then there was some safety calls to
extend the buffer if needed.What had happened in the MySQL code over time was that new coders was
using a buffer that was not preallocated and it was filled with a lot
of append calls(), each which caused a new realloc().I fixed a couple of the worst cases to prealloc the buffer properly,
and for the other cases I made a general fix by making the
reallocation addaptive:The essence was to replace the realloc() call with the following function:
bool realloc_with_extra(uint32 arg_length) { if (extra_alloc < 4096) extra_alloc= extra_alloc*2+128; return realloc(arg_length + extra_alloc); }In MySQL 5.3, this gave a speedup for some test cases (in debug mode)
with 25 %. For an optimized version of MySQL we got a general
performance boost of 3 %. The difference comes from the fact that
we do a lot of extra checks for the malloc() call in a debug build and
fewer malloc() calls gives a notable speed improvement.The full patch can be seen by doing this in the MariaDB 5.3 tree:
bzr log -p -r monty@askmonty.org-20101108114354-jl61qx8e36gvbzm7
Here are the detailed results of the sql-bench runs. I ran sql-bench with MariaDB 5.2.2 with default settings and used MyISAM as storage engine.
Column 1 is in seconds. All other columns are presented relative
to this. 1.00 is the same, bigger numbers indicates slower.
1) MariaDB 5.2.2.
2) MariaDB 5.2.2 patched with Monty’s string append optimization.
3) MariaDB 5.2.2 patched with Monty’s string append optimization.
4) MariaDB 5.2.2 patched with Monty’s string append optimization.
The number in () after each tests shows how many SQL commands the particular
test did. As one test may have many different parameters this gives only
a rough picture of what was done.
I ran the test on a quite fast machine and therefore you can ignore the results with run times around 0 – 3 seconds. I am adjusting the tests to run at least for 5 seconds to have more reliable results.
As machine I used our new benchmark system “pitbull”, which I also used for benchmarking the MyISAM segmented key cache performance.
# OS: Ubuntu 10.10 # Platform: x86_64 # CPU: Two-socket x hexa-core Intel Xeon X5660 @ 2.80GHz. With hyperthreading: 24CPUs # RAM: 28GB # Disk(s): 1 x ST3500320NS S-ATA
=================================================================================
Operation | 1| 2| 3| 4|
|mysql-Linu|mysql-Linu|mysql-Linu|mysql-Linu|
---------------------------------------------------------------------------------
Relative results per test (First column is in seconds): |
---------------------------------------------------------------------------------
ATIS | 407.00| 0.980| 0.983| 0.978|
alter-table | 112.00| 0.991| 0.955| 0.991|
big-tables | 425.00| 0.965| 0.953| 0.958|
connect | 403.00| 0.893| 0.963| 0.846|
create | 918.00| 0.997| 1.001| 0.971|
insert | 536.00| 1.007| 1.034| 1.011|
select | 417.00| 0.959| 0.868| 0.986|
wisconsin | 408.00| 1.007| 0.983| 0.985|
---------------------------------------------------------------------------------
The results per operation: |
---------------------------------------------------------------------------------
alter_table_add (992) | 73.00| 0.973| 0.945| 0.973|
alter_table_drop (496) | 38.00| 1.026| 0.947| 1.000|
connect (50000) | 41.00| 1.024| 1.024| 1.000|
connect+select_1_row (50000) | 48.00| 1.042| 1.042| 1.021|
connect+select_simple (50000) | 45.00| 1.022| 1.022| 1.022|
count (100) | 3.00| 0.667| 0.667| 0.667|
count_distinct (7000) | 22.00| 1.000| 1.000| 1.000|
count_distinct_2 (7000) | 38.00| 1.000| 1.000| 1.000|
count_distinct_big (720) | 19.00| 1.105| 1.000| 1.053|
count_distinct_group (7000) | 36.00| 0.667| 0.667| 0.972|
count_distinct_group_on_key (7000) | 29.00| 1.034| 1.000| 1.034|
count_distinct_group_on_key_parts (7| 33.00| 1.000| 0.697| 0.818|
count_distinct_key_prefix (7000) | 15.00| 1.000| 0.933| 1.000|
count_group (7000) | 33.00| 1.000| 0.606| 1.030|
count_group_on_key_parts (7000) | 27.00| 1.074| 1.000| 1.037|
count_group_with_order (7000) | 34.00| 1.000| 0.618| 1.029|
count_on_key (50100) | 66.00| 0.848| 0.955| 0.955|
create+drop (10000) | 450.00| 1.002| 1.007| 0.973|
create_MANY_tables (400) | 17.00| 1.059| 1.059| 1.059|
create_index (8) | 0.00| 1.000| 0.000| 1.000|
create_key+drop (10000) | 450.00| 0.991| 0.996| 0.967|
create_table (31) | 2.00| 0.500| 0.500| 0.500|
delete_all_many_keys (1) | 5.00| 1.200| 1.200| 1.000|
delete_big (1) | 0.00| 0.000| 0.000| 0.000|
delete_big_many_keys (128) | 5.00| 1.200| 1.200| 1.000|
delete_key (10000) | 2.00| 0.500| 0.500| 0.500|
delete_range (12) | 1.00| 2.000| 1.000| 1.000|
drop_index (8) | 1.00| 0.000| 1.000| 1.000|
drop_table (28) | 0.00| 0.000| 0.000| 0.000|
drop_table_when_MANY_tables (400) | 1.00| 0.000| 0.000| 0.000|
insert (350768) | 39.00| 1.103| 1.077| 1.051|
insert_duplicates (100000) | 9.00| 1.000| 1.444| 1.333|
insert_key (100000) | 25.00| 0.960| 1.040| 0.960|
insert_many_fields (140000) | 112.00| 0.982| 0.982| 0.982|
insert_select_1_key (1) | 1.00| 1.000| 1.000| 1.000|
insert_select_2_keys (1) | 0.00| 0.000| 1.000| 1.000|
min_max (60) | 1.00| 2.000| 1.000| 2.000|
min_max_on_key (85000) | 16.00| 1.000| 1.000| 1.000|
multiple_value_insert (100000) | 1.00| 0.000| 1.000| 1.000|
once_prepared_select (100000) | 13.00| 1.000| 1.308| 1.231|
order_by_big (10) | 5.00| 1.000| 1.200| 1.200|
order_by_big_key (10) | 6.00| 0.833| 1.000| 0.833|
order_by_big_key2 (10) | 5.00| 1.000| 1.000| 1.200|
order_by_big_key_desc (10) | 5.00| 1.200| 1.000| 1.200|
order_by_big_key_diff (10) | 6.00| 1.000| 0.833| 0.833|
order_by_big_key_prefix (10) | 5.00| 1.000| 1.200| 1.000|
order_by_key2_diff (500) | 1.00| 1.000| 1.000| 1.000|
order_by_key_prefix (500) | 1.00| 1.000| 1.000| 1.000|
order_by_range (500) | 1.00| 1.000| 0.000| 0.000|
outer_join (10) | 5.00| 1.000| 1.200| 1.000|
outer_join_found (10) | 5.00| 1.000| 1.000| 1.000|
outer_join_not_found (500) | 4.00| 1.250| 1.000| 1.250|
outer_join_on_key (10) | 5.00| 1.000| 0.800| 1.000|
prepared_select (100000) | 22.00| 0.955| 0.955| 0.955|
select_1_row (500000) | 52.00| 0.750| 0.788| 0.673|
select_1_row_cache (500000) | 50.00| 0.660| 0.700| 0.660|
select_2_rows (500000) | 57.00| 0.772| 0.947| 0.684|
select_big (80) | 5.00| 1.200| 1.000| 1.200|
select_big_str (50000) | 14.00| 1.071| 1.071| 1.071|
select_cache (10000) | 18.00| 1.000| 0.944| 0.944|
select_cache2 (10000) | 18.00| 0.944| 0.944| 0.944|
select_column+column (500000) | 53.00| 0.925| 0.830| 0.792|
select_diff_key (500) | 0.00| 0.000| 0.000| 0.000|
select_distinct (40000) | 113.00| 1.018| 1.009| 1.009|
select_group (140111) | 121.00| 1.008| 1.041| 1.000|
select_group_when_MANY_tables (400) | 0.00| 0.000| 0.000| 0.000|
select_join (5000) | 22.00| 1.000| 1.000| 1.000|
select_key (200000) | 43.00| 1.000| 0.977| 0.953|
select_key2 (200000) | 46.00| 0.978| 0.957| 0.957|
select_key2_return_key (200000) | 44.00| 1.000| 0.977| 0.955|
select_key2_return_prim (200000) | 46.00| 0.978| 0.935| 0.935|
select_key_prefix (200000) | 46.00| 1.022| 0.957| 0.957|
select_key_prefix_join (5000) | 131.00| 0.908| 0.893| 0.916|
select_key_return_key (200000) | 43.00| 0.953| 0.953| 0.953|
select_many_fields (140000) | 313.00| 0.958| 0.942| 0.949|
select_range (410) | 22.00| 0.955| 0.955| 1.000|
select_range_key2 (25010) | 5.00| 1.000| 1.200| 1.000|
select_range_prefix (25010) | 6.00| 1.000| 1.000| 1.000|
select_simple (500000) | 22.00| 1.000| 1.318| 0.955|
select_simple_cache (500000) | 21.00| 0.952| 1.524| 0.952|
select_simple_join (25000) | 22.00| 1.045| 1.045| 1.000|
update_big (10) | 4.00| 1.000| 1.000| 1.000|
update_of_key (50000) | 6.00| 1.167| 1.333| 1.333|
update_of_key_big (501) | 3.00| 1.000| 1.000| 0.667|
update_of_primary_key_many_keys (256| 3.00| 0.667| 1.000| 0.667|
update_with_key (300000) | 30.00| 1.133| 1.333| 1.333|
update_with_key_prefix (100000) | 10.00| 1.100| 1.400| 1.400|
wisc_benchmark (50014) | 403.00| 1.010| 0.988| 0.988|
---------------------------------------------------------------------------------
TOTALS | 3619.00| 0.978| 0.975| 0.966|
=================================================================================