- developers - lists.mariadb.org

[Maria-developers] Implementing new "group commit" API in PBXT?
by Kristian Nielsen 13 Dec '10

13 Dec '10

Hi Paul! I want to ask your opinion about implementing in PBXT an extension to the storage engine API that I am working on. There are lots of details in http://askmonty.org/worklog/Server-BackLog/?tid=116 (and even more details in other places), but I thought you would appreciate the short version :-) The idea is to get a well-defined ordering of commits in the server in an efficient way (eg. not break group commit like InnoDB does currently, Bug#13669). For this, two new (optional) storage engine methods are introduced: void (*prepare_ordered)(handlerton *hton, THD *thd, bool all); void (*commit_ordered)(handlerton *hton, THD *thd, bool all); The prepare_ordered() method is called after prepare(), as soon as commit order is decided. The commit_ordered() method is called before commit(), just after the transaction coordinator has made the final decision that the transaction will be durably committed (and not rolled back). The calls into commit_ordered() among different transactions will happen in the order that these transactions are committed, consistently across all engines and the binary log. Same for prepare_ordered(). The idea is that the storage engine should do the minimal amount of work in commit_ordered() necessary to make the commit visible to other threads. And to make sure commits appear to be done in the order of calls to these methods. Do you think either (or both) of these methods could be implemented in PBXT with reasonable effort (and if so, how)? ---- In InnoDB, this was trivial to do, as the InnoDB commit() method already had a "fast" part (which fixed the transaction log order (= "commit order") and made the transaction visible) and a "slow" part (which did the fsync() to make the transaction durable, and handled group commit). (It is necessary that commit_ordered() is fast, as it runs under a global lock. Ideally, it will just allocate an LSN in the transaction log to fix commit order, and perhaps whatever else already needs to happen serialised during engine commit). I hope my explanation was sufficiently clear for you to make a qualified answer. Maybe you can point me to where in the PBXT code a commit becomes visible and the commit order is fixed? In case you were wondering, here are some of the motivations for this feature: 1. For taking a hot backup, it is useful to have consistent commit order between binlog and storage engines. Without it, it can happen that the backed up state of the server has transaction A (but not B) committed in the storage engine, and transaction B (but not A) written to the binlog. Using such backup to provision a new master or slave would leave replication in an inconsistent state. 2. This feature implements working group commit for the binlog while still preserving consistent order as per (1). 3. This will allow to implement START TRANSACTION WITH CONSISTENT SNAPSHOT for multi-engine transactions that is truly consistent (currently is is possible for a transaction to be visible in one engine but not another in such "consistent" snapshot). 4. Galera relies on a consistent commit order, and I believe this feature will allow it to get this in a more engine-independent way. 5. We are planning to use consistent commit order to allow MySQL to recover after a crash transactions that were synced to disk in the binlog but not in the engine. This will allow to reduce the number of fsyncs() during prepare() / commit() from 3 to 1; it only needs to be done in the binlog (with group commit); the engine does not need to fsync(), as any lost transactions will be recovered from the binlog after crash. 6. The prepare_ordered() method is inspired by the Facebook patch to release InnoDB row-level read locks early (before syncing the binlog to disk) to improve performance in the presence of hot spots (probably does not apply to PBXT). - Kristian.

2 9

Re: [Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
by Sergey Petrunya 13 Dec '10

13 Dec '10

On Thu, Dec 09, 2010 at 11:34:05AM +0200, Philip Stoev wrote: > I have a few questions with respect to the feature so that I can test it > better (we may have discussed those previously but I can not seem to find > the email): > > - apart from mrr_sort_keys ON and OFF and engine_condition_pushdown = ON > | OFF and all join_cache_levels , is there any other optimizer switch > that I should fiddle with? Is there any switch that I can set to ON or > OFF in order to drive more queries to use your code? @@engine_condition_pushdown variable does not have any effect with the current code. It controls table condition pushdown, which is only supported by NDB. XtraDB/MyISAM/Maria have *index* condition pushdown, which can be turned on and off by using SET @@optimizer_switch='index_condition_pushdown=on|off'; Overall, DS-MRR behavior can be modified by: - @@join_cache_level setting (need to be such that BKA is enabled in order for new code to wor) - @@join_buffer_size (sets size of the buffer that will be shared between BKA module and DS-MRR module) - mrr_sort_keys=on|off @@optimizer_switch flag - index_condition_pushdown=on|off @@optimizer_switch flag. > - apart from mrr_buffer_size , is there any other mysqld option I should > be conserned with. What values of mrr_buffer_size should be tested? > - what table sizes should I use and also how many tables should I join? > Is up to 1000 rows per table and 2-3-table joins sufficient? Or should I > go for the DBT-2 data set? I think that we should first test with up to 1K rows per table and 2-4 way joins. I was quite impressed by the issue you've found in BUG#671340 (it was a specific interaction of key sorting and rowid sorting, which could occur only when we're scanning a particular kind of data and exhaust the buffer at a certain point). The testcase for that bug was rather small, I take the original testcase found by RQG wasn't big either? > - how about multipart and prefix indexes? Yes. I don't expect bugs to be there, but the code does work with index tuples, so it would be nice to check how scanning on multipart indexes (and their prefixes) work. Prefix indexes can be checked, too. > - should I test enum/set/bit and other marginal data types? I can't think of realistic examples with those types, so we better focus on something else. > - are there any engine specific code or considerations? XtraDB is different in the regard that it has clustered primary keys, and MWL#121-125 adds support for MRR/BKA to work for clustered primary keys. Maria/MyISAM should be checked too, they both have very similar DS-MRR handling. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

[Maria-developers] DS-MRR improvements patch R5 ready for review.
by Sergey Petrunya 10 Dec '10

10 Dec '10

Hi Monty, I've finished addressing the review feedback and I'm submitting the patches for another review. The attached are: - cumulative all-included diff againist 5.3-main (should just apply) - diff with post-review changes ( I needed to tweak that file a bit becasue I've done a merge from -mwl128 tree, so I needed to remove those changes. The tree is at lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk. I'm not aware of any buildbot failures except the solaris32 crashes. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] Volunteer my machine for buildbot and database with test results
by Haidong Ji 10 Dec '10

10 Dec '10

To whom it may concern, Per information shown on URL below, with relevant paragraph pasted, I'd love to volunteer to run a build slave and other tests. Let me know how to participate, such as: Will an old laptop work? If so, what do you need? What do I need to do on my side? Does it mean that I need to leave my machine on all the time? What if a reboot happens while it is being used? I am sure more questions as we proceed. Thanks, Haidong "Alex" Ji http://kb.askmonty.org/v/about-buildbot Volunteering to run a build slave Many of our build hosts are run by community members, and we are always looking for additional volunteers to help us cover additional platforms or build options in BuildBot. If you are able to provide a spare machine for this purpose, your help is greatly appreciated! This is a good way to get involved without having to spend a lot of time on it. Get started by writing an email to maria-developers(a)lists.launchpad.net with an offer to run a BuildBot slave. Database with test results Buildbot saves the results of test runs in a database, to be used for enhanced reporting on web pages without need to change the Buildbot code, and for data mining when investigating test failures. The database schema is documented under Buildbot Database Schema. The schema is likely to evolve as we gradually add more kinds of information. For now, the data is not externally available. But the plan is to set up a slave database to replicate the data, and provide access (eg. remote database accounts) to members of the community with interesting ideas about how to present or mine this data, or who are just curious to play with it. If anyone has an interest in this, or wants to volunteer a slave host for this purpose, please send a mail to maria-developers(a)lists.launchpad.net. The more people show interest in this, the faster it is likely to happen!

2 5

[Maria-developers] problem with partitioning and our storage engine in 5.2
by Zardosht Kasheff 10 Dec '10

10 Dec '10

Hello all, We have been working on testing our storage engine, TokuDB, against MariaDB 5.2.3, and we have encountered a problem with partitioning that does not exist on MySQL 5.1, MySQL 5.5, and MariaDB 5.1.50. This problem also does not exist with any other storage engine that we have tried. It ONLY exists with TokuDB and MariaDB 5.2. On MariaDB 5.2, the attached test produces the attached result file, that has incorrect results. Here is what the test is doing: 1. Create a table with some partitions 2. update the table 3. run select max(f_int1) 4. run select * 5. run select max(f_int1) again. The problem is that the query results for 3 are incorrect, even though the query results for 4 and 5 are correct. MySQL 5.1, MySQL 5.5, and MariaDB 5.1 produce the correct results for 3. What makes this even stranger is that the query results for 4 and 5 are always correct. Any ideas on how we can go about figuring out what is wrong? Thanks -Zardosht

6 11

[Maria-developers] MWL#90: need a list of "obvious things to improve"
by Sergey Petrunya 09 Dec '10

09 Dec '10

Hi Timour, In Helsinki, we've noted the following: > MWL#90: Subqueries: Inside-out execution for non-semijoin materialized subqueries in WHERE (4K diff); Critical task > = Get the code reviewed/pushed > TODO: > - Timour will tell the "obvious things to improve"; Timour 1 day I'm going to need those "obvious things" fairly soon. Do you need to have anything as a prerequisite to produce the list? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Philip please test maria-5.3-mwl128-dsmrr-cpk
by Sergey Petrunya 09 Dec '10

09 Dec '10

Hi Philip, The new version of MWL#121-125, DS-MRR impovements, is ready for your testing. I believe all previously reported crashes and wrong query result bugs are no longer there (they've been either targeted and fixed, or were gone after code re-working I've made when addressing review feedback). The slowdown bugs (https://bugs.launchpad.net/maria/+bug/637160, https://bugs.launchpad.net/maria/+bug/631504) are most likely still there (I didn't check if they are, but I haven't made any changes that could impact them). The code to be tested is at lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] sql-bench results for MariaDB string optimization
by Hakan Kuecuekyilmaz 09 Dec '10

09 Dec '10

Hi Monty, here are the results comparing your string optimization patch with MariaDB 5.2 and sql-bench. I added the configuration used, a short summary, and a full summary of the results. I ran the test on our new machine "pitbull", a two socket hexa-core Intel Xeon X5660 @ 2.80GHz with 23GB RAM with a single S-ATA disk. Todo: the tests show a couple of results with 0 - 1 wallclock seconds, which is due the quite faster machine. I will adjust the tests to run for at least 5 seconds (again). I attached the result file for the that the results are not good readable in this email. Best, Hakan hakan@pitbull:~/work/monty_program/mariadb-tools/sql-bench$ more conf/sql-bench-only_myisam.cnf # Base configuration for running sql-bench tests for MariaDB > 5.2.2. # # This is the minimal base test version # # Hakan Kuecuekyilmaz, <hakan at askmonty dot org>, 2010-11-18. # Configure environment variables. # Note: ccache is checked in the host specific config file. $ENV{'CC'} = "gcc"; $ENV{'CFLAGS'} = "-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparenthese s -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wunused-paramete r -O3 -fno-omit-frame-pointer"; $ENV{'CXX'} = "gcc"; $ENV{'CXXFLAGS'} = "-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparenthe ses -Wsign-compare -Wwrite-strings -Wunused-function -Wunused-label -Wunused-value -Wunused-variable -Wreorder -Wcto r-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer"; $ENV{'CXXLDFLAGS'} = ""; $config = { # Configure invocation line. # Do not use --prefix here. configure_line => '--enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-plugin-aria --with-aria-tmp-tables --without-plugin-innodb_plugin --with-mysqld-ldflags=-static --with-client -ldflags=-static --with-readline --with-ssl --with-plugins=max-no-ndb --with-embedded-server --with-libevent --enabl e-local-infile', }; # # Different test to be run with the above configuration. # The sql-bench-test will automaticly get --suffix=_test_name as an argument # The different options that can be used are: # # mysqld_start_options Options given to the mysqld that is started. # This is prefixed with --skip-grant. # mysqld_init_command SQL commands given to mysqld before tests starts. # sql_bench_options Options given to sql-bench. # $sql_bench_test->{'base'} = { mysqld_start_options => '', mysqld_init_command => '', sql_bench_options => '--comment="base test (with MyISAM)"', }; # This has to be the last line. See "perldoc -f require" for details. 1; RESULTS: Comparing 5.2 plain against patched 5.2 (three runs opt1/opt2/opt3) Overall the string optimization patch shows better results. Highlights are * Biggest gain we see in "connect": ~ 4 - 15% * Biggest loss we see in "insert": ~ 1 - 3% Biggest loss in detail: 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_distinct_big (720) | 19.00| 1.105| 1.000| 1.053| count_distinct_group_on_key (7000) | 29.00| 1.034| 1.000| 1.034| count_group_on_key_parts (7000) | 27.00| 1.074| 1.000| 1.037| create_MANY_tables (400) | 17.00| 1.059| 1.059| 1.059| delete_all_many_keys (1) | 5.00| 1.200| 1.200| 1.000| delete_big_many_keys (128) | 5.00| 1.200| 1.200| 1.000| insert (350768) | 39.00| 1.103| 1.077| 1.051| insert_duplicates (100000) | 9.00| 1.000| 1.444| 1.333| insert_duplicates (100000) | 9.00| 1.000| 1.444| 1.333| min_max (60) | 1.00| 2.000| 1.000| 2.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_desc (10) | 5.00| 1.200| 1.000| 1.200| outer_join_not_found (500) | 4.00| 1.250| 1.000| 1.250| select_big (80) | 5.00| 1.200| 1.000| 1.200| select_big_str (50000) | 14.00| 1.071| 1.071| 1.071| update_of_key (50000) | 6.00| 1.167| 1.333| 1.333| 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| Full results: hakan@pitbull:~/sql-bench-results/pitbull$ /tmp/tmp.jAANb0DlLs/install/sql-bench/compare-results 2010-12-08-5.2-base/base/RUN-mysql-Linux_2.6.35_23_server_x86_64 2010-12-08-5.2-string-opt1/base/RUN-mysql-Linux_2.6.35_23_server_x86_64 2010-12-08-5.2-string-opt2/base/RUN-mysql-Linux_2.6.35_23_server_x86_64 2010-12-09-5.2-string-opt3/base/RUN-mysql-Linux_2.6.35_23_server_x86_64 --relative This is the result file of the different benchmark tests. 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. Check the source for more information :) Keep in mind that one can't compare benchmarks run with different --cmp options. The --cmp options sets the all limits according to the worst limit for all server in the benchmark. Numbers marked with '+' are estimated according to previous runs because the query took longer than a given time-limit to finish. The estimation shouldn't be far from the real result thought. Numbers marked with '?' contains results that gave wrong result. This can only be used as an indication of how long it took for the server to produce a wrong result :) Numbers marked with '*' are tests that was run a different number times than the test in the first column. The reason for this is normally that the marked test was run with different options that affects the number of tests or that the test was from another version of the MySQL benchmarks. Hope this will give you some idea how each db is performing at what thing .... Hope you like it .... Luuk & Monty (1997) Column 1 is in seconds. All other columns are presented relative to this. 1.00 is the same, bigger numbers indicates slower The result logs which where found and the options: 1 mysql-Linux_2.6.35_23_server_x86_64 : MySQL 5.2.4 MariaDB --socket=/tmp/tmp.bn05ikyeM5/mysql.sock base test (with MyISAM) 2 mysql-Linux_2.6.35_23_server_x86_64 : MySQL 5.2.4 MariaDB --socket=/tmp/tmp.eNBvB5BqYb/mysql.sock base test (with MyISAM) 3 mysql-Linux_2.6.35_23_server_x86_64 : MySQL 5.2.4 MariaDB --socket=/tmp/tmp.AG0msBbvC2/mysql.sock base test (with MyISAM) 4 mysql-Linux_2.6.35_23_server_x86_64 : MySQL 5.2.4 MariaDB --socket=/tmp/tmp.jAANb0DlLs/mysql.sock base test (with MyISAM) ================================================================================= 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| ================================================================================= -- Hakan Küçükyılmaz, QA/Benchmark Engineer, Stuttgart/Germany Monty Program Ab, http://askmonty.org/ Skype: hakank_ Phone: +49 171 1919839

1 0

[Maria-developers] bringing mysql-5.5 cmake goodies into MariaDB?
by Vladislav Vaintroub 08 Dec '10

08 Dec '10

Hi, I spend about some time (took about a day) trying to improve the current state of windows build and packaging. Partly, I it was to improve my own experience working on Windows (spoiled by MySQL5.5 :) with out-of-source build, partly because I think the fact that the packaging (even zip) requires specially prepared source on Unix or Cygwin (known as bootstrapping in the old MySQL, gone in MySQL-5.5) , is not the way to go forward. It is hard to explain it to our Windows users that Cygwin might is required or recommended, die-hard windows people try to avoid it, and it is known to be broken in recent Windows (Vista +) too... So, the proposed patch would eliminate every Cygwin dependency. Those cmake goodies are not the full 5.5 cmake stuff (nothing Unix related, no improvements for plugins, no improvements to avoid useless recompilation in embedded or in libmysql etc.), but mainly Windows-related packaging plus out-of-source build and test. I have created a repo with my changes on Launchpad https://code.launchpad.net/~maria-captains/maria/5.1-windows-packaging to configure the build (for releases) one would execute % cmake . -DWITH_EMBEDDED_SERVER=1 (all engines that should be compiled in are set to compile in by default, I do not use embedded by default since the build is taking too long with it) To build the ZIP package % devenv mysql.sln /build relwithdebinfo /project package This creates ./mariadb-<major>.<minor>.<patch>-win32.zip To build MSI (assuming Wix 3.0 is installed from http://wix.sourceforge.net/downloadv3.html ) % devenv mysql.sln /build relwithdebinfo % devenv mysql.sln /build relwithdebinfo /project MSI the resulting msi is in win\cmake\ mariadb-<major>.<minor>.<patch>-win32.msi This procedure resembles very closely the official MySQL5.5 release-type builds on Windows ( Jonathan blogged about it some time ago http://www.perkin.org.uk/blog/2010/11/how-to-build-mysql-releases/ ) NSIS is probably broken. Frankly speaking I do not know it at all, neither I believe it would be a good idea to learn, as it will disappear in 5.5 at least. I appreciate comments on the idea. Especially from Philip as QA, and from Kristian as build automator ( should we decide to use this patch, some things will have to change on Buildbot). On the patch itself: - Preventively (to avoid 5.5 merge errors), I created new files in different places from where they live in MySQL 5.5 (e.g. not in cmake/* but in win/cmake/* , not packaging/Wix/* but win/packaging/*). I have done it as bazaar cannot handle situations, where files are "retrospectively" added in old releases. - I also had to fix Maria's mtr for out-of-source build. - When merging with 5.5 most of this stuff (except MTR changes) should disappear, it is already there I can create worklog about this ,and do other procedurally required stuff. I'm not sure in which MariaDB version it should be applied , if we decide to apply it at all. Thanks, Vladislav

1 0

Re: [Maria-developers] a bug that affects group commit implementations
by Kristian Nielsen 08 Dec '10

08 Dec '10

[Cc:ing maria-developers@ as I want us to get into the habbit of discussing more openly, hope that is ok] MARK CALLAGHAN <mdcallag(a)gmail.com> writes: > I think that Mats identified the problem and then Vamsi did a > reproduction of it. > http://bugs.mysql.com/58787 Thanks for pointing me to this issue Mark! I'm wondering what the real bug is here? Can one of you explain? I suppose it is that we can get in binary log eg. INSERT, ALTER; while in InnoDB transaction log we get ALTER, INSERT ? I believe the reason to avoid such inconsistency normally is to allow something like XtraBackup to get a consistent binlog position that can be used to provision a slave. However, for DDL this is not enough, as .frm files are not handled. So ensuring consistent order for DDL between binlog and innodb transaction log does not really solve the problem. The bug also suggests that DDL should use 2-phase commit. But 2-phase commit implies the ability to rollback. I do not know if InnoDB is able to roll back DDL, but MySQL .frm handling certainly is not. So great care would be needed to not just introduce different bugs, if this approach was taken. It is also a non-trivial change of the storage engine API. There has been talk of making DLL in MySQL (/MariaDB) transactional, or at least crash-safe. This is something that I would really like to see. I was told that partitioning already has code for this, by logging .frm changes and recovering / rolling back after crash. Something similar could work for general DDL. This would allow a proper solution to the binlog order problem for DDL. It does not mean that a partial solution now cannot be an improvement, however I do not understand from the bug discussion what such improvement would be. Can you elaborate? BTW, the purpose of 2-phase commit is to ensure consistency between different engines/binlog in case of crash, not to ensure consistent ordering of commits. The fact that it currently _does_ ensure ordering for InnoDB is just a gross hack with the prepare_commit_mutex. This is so expensive (3 x fsync() per commit) that I believe most users don't use it anyway (eg. setting sync_binlog != 1, which defeats the whole purpose of prepare_commit_mutex). I would really recommend looking at MWL#116 (http://askmonty.org/worklog/Server-Sprint/?tid=116) which solves the ordering issue in a proper way. - Kristian.

1 0