|
Planet MySQL
|
Planet MySQL - http://www.planetmysql.org/
|
-
MariaDB optimization for string related operations
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|
=================================================================================
-
MySQL 5.5 is GA and 5.5.8 missing from launchpad…
While it’s great that MySQL 5.5 is GA with the 5.5.8 release (you can download it here), I’m rather disappointed that the bzr repositories on launchpad aren’t being kept up to date. At time of writing, it looked like this:
Yep – nothing for five weeks in the 5.5 repo – nothing since the 5.5.7 release :(
There hasn’t been zero changes either – the changelog has a decent number of fixes.
-
Percona and HandlerSocket
Percona just announced that they’re going to include HandlerSocket in the latest Percona build.
This is huge news. In case you aren’t aware, HandlerSocket is a raw protocol interface that is similar to a dictionary lookup (kind of like memcached, or a hashtable) and does NOT go through the SQL parser.
The performance implications are huge as this thing flies.
Most web applications access data in terms of key/values. In fact, databases like Redis take the concept a bit further and implement functions you can use to manipulate keys directly.
What’s interesting here is that I think HandlerSocket is bigger than Percona and probably deserves a whole project and open source community behind it directly.
-
MySQL 5.5 may be GA but not the docs.....
-
MySQL 5.5 GA announcement
This morning, Oracle gave a MySQL technology update that included the announcement that MySQL 5.5 is GA. The short list of exciting new and improved features is -
InnoDB performance improvements
Better instrumentation and diagnostics
InnoDB recovery performance improvements
Scalability improvements
Semi-synchronous replication
The InnoDB and MySQL database improvements in 5.5 are impressive. Many of these improvments are gained through increased i/o subsystem improvements and removal of lock contention that has long been an issue when scaling on hosts above 4 cores.
The benchmark graphs that Oracle had in this morning’s call show improvements on multicore machines, at high levels of transactions and across read, write and mixed workloads.
With 5.5, InnoDB has become the default storage engine and thus out of the box, MySQL has ACID compliance, transactions, foreign keys and crash recovery.
The work on InnoDB additionally addresses some specific issues during crash recovery that many people have run into. The team was able to isolate an issue of memory allocation during crash recovery that drove the CPU utilization by doing an intensive check frequently to avoid over allocating memory. The second big win in crash recovery is in the handling of the flush_list (the list of dirty pages at the time of the crash). In regular database operations, the flush list is only inserted to at the head. However, during an InnoDB recovery, records are inserted after a linear search to determine the proper placement in the list. The InnoDB engineers added a dataconstruct during recovery that improves the efficiency of the inserts. This morning’s graphs show a better than fourfold increase in recovery speeds most of which happens during the log application phase of the recovery.
Much of this morning’s call was spent on performance improvements, but I’m most interested in the performance_schema and the replication enhancements that made it into this release. I’ll be spending more time investigating semi-synchronous replication, the enhanced SHOW ENGINE INNODB STATUS, and whether or not the performance_schema meets it’s design goal of causing no changes in server behavior.
After the announcement webinar this morning, I had an opportunity to record the first of the revived OurSQL podcasts with Sheeri Cabral. There are lots of tidbits in there about what we heard and saw in the release promotion and documentation. I’ll update this post with a link to that audio when it is posted.
MySQL 5.5 is the first GA release of MySQL Server under Oracle’s stewardship and the first in almost 2 years. I’m looking forward to digging further into 5.5 and applying these new features to existing client issues. Those of us who prefer to run GA for customers have been waiting for some of these features for a long time.
Related posts:DBD::mysql 4.018 released
Semi-Synchronous Replication in MySQL 5.5
MySQL 5.1GA
|
