December 2010 - developers - lists.mariadb.org

[Maria-developers] [PATCH] Make innodb_bug57255.test 10-20 times faster
by Stewart Smith 14 Dec '10

14 Dec '10

Real simple patch, makes the test run much faster if using a real storage device without libeatmydata. patch should be appliable (just change the path) ------------------------------------------------------------ revno: 2477 committer: Stewart Smith <stewart(a)flamingspork.com> branch nick: update-innobase timestamp: Tue 2010-12-07 13:40:36 +1100 message: make innodb_bug57255 test run 10-20 times faster (use a txn when loading data) modified: plugin/innobase/tests/t/innodb_bug57255.test diff: === modified file 'plugin/innobase/tests/t/innodb_bug57255.test' --- plugin/innobase/tests/t/innodb_bug57255.test 2010-12-07 02:40:01 +0000 +++ plugin/innobase/tests/t/innodb_bug57255.test 2010-12-07 02:40:36 +0000 @@ -12,6 +12,7 @@ insert into A values(1), (2); --disable_query_log +begin; let $i=257; while ($i) { @@ -24,6 +25,7 @@ insert into C(f1) values(2); dec $i; } +commit; --enable_query_log # Following Deletes should not report error -- Stewart Smith

3 2

[Maria-developers] DBT-3/TPC-H RQG tests are now available
by Philip Stoev 14 Dec '10

14 Dec '10

Hello, In response to popular demand, the DBT-3 dataset will be used for testing along with a new set of grammars that generate queries against that dataset. While I personally doubt that the realism of the DBT-3, it being 99% random, here is what we have so far: 1. DBT-3 datasets for scales 0.1 0.01 and 0.001 2. RQG grammars that implement the following: - a grammar on the range optimizer via single-table queries against the lineitem database. The WHERE clause consists of nested AND and OR expressions of varying depth, where each individual expression involves an indexed column and is generated to be realistic. E.g. for a date column, we generate expressions that filter records for a specific month or a specific year. - a grammar for general join tests - realistic multi-table joins are generated by observing the star structure of the dataset. The WHERE and HAVING conditions are generated to be realistic with respect to the columns being queried or filtered out. GROUP BY is used so that it matches the ONLY_FULL_GROUP_BY mode. - (forthcoming) - subquery tests where subqueries that return a currency value are used in various locations and expressions within the query where a currency value would generally be expected; In addition, I have reviewed the actual queries from the benchmark and I think the RQG grammars more or less cover the scenarios described in the specification. >From the test runs that have been performed so far, no new bugs are being discovered as compared to the existing RQG grammars that use purely random queries against purely random data. Philip Stoev

1 0

[Maria-developers] WL#170 New (by Knielsen): Promotion of new master in " full independence" parallel replication.
by worklog-noreply＠askmonty.org 13 Dec '10

13 Dec '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Promotion of new master in "full independence" parallel replication. CREATION DATE..: Mon, 13 Dec 2010, 13:55 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 170 (http://askmonty.org/worklog/?tid=170) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: MWL#169: "Parallel replication of transactions in distinct databases" allows replication events and transactions to be applied on the slave in parallel, and potentially out-of-order (in "full independence"). This makes it more complex to promote a new master in case the old master dies or needs to be taken offline. The issue is that the states of any two slaves may not be commensurable, meaning that either slave may have a transaction applied that is missing on the other. This is more complicated that with old single-threaded replication, where there is a single ordered replication stream; then between any two slaves one will be ahead (or equal) to the other, and the one ahead can serve immediately as a master for the other. So with single-threaded replication, the problem is merely to find a slave that is maximally progressed. This worklog proposes an algorithm that allows to keep track of the states on slaves and a way to use this information to promote a new master reliably and potentially automated. This assumes that some kind of *global transaction ID* is implemented, along the lines of the Google patch for this [1]. A global transaction ID uniquely identifies the event(s) of a transaction (or event group in the case of non-transactional statements) across the entire replication topology. Global transaction IDs are monotonic, so E1 occurs before E2 if and only if the ID of E1 is less that the ID of E2. Note that we do not require all IDs to be comparable; in the case of two independent masters, events from different masters will be incomparable. (A common implementation is to use (server_id, incrementing_64bit_int) as the ID). Also note that we do not require ID sequences to be without holes, just that they are increasing. So it is permissible that IDs (server1,10) and (server1,12) exist, yet that there never existed any event with ID (server1,11). [1] http://code.google.com/p/google-mysql-tools/wiki/GlobalTransactionIds ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#169 New (by Knielsen): Parallel replication of transactions in distinct databases
by worklog-noreply＠askmonty.org 13 Dec '10

13 Dec '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Parallel replication of transactions in distinct databases CREATION DATE..: Mon, 13 Dec 2010, 13:54 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 169 (http://askmonty.org/worklog/?tid=169) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: The main challenge in parallel replication is to determine which events are safe and valid to run in parallel on the slave. This worklog is about parallel replication of transactions that each access only tables in a single database. Two such transactions can be safely applied in parallel on a slave if the two databases they each access are distinct. The term "database" is used here in the usual MySQL sense which is similar to "schema" in other RDBMS. There will be a server option "--fail-cross-database" to disallow multi-database statements and cross-database transactions. Ie. cause such queries to fail on the master so that the assumption remains valid on the slave that transactions do not span databases. This option defaults to FALSE. There will be a fixed number of apply threads on the slave, configured by the user with the option "--slave-apply-threads". This will default to 1, which will give the old non-parallel slave apply behaviour. Setting this to >1 will enable parallel slave apply. For this first version of parallel slave apply, it should probably be an error to set --slave-apply-threads >1 without also setting --fail-across-database to TRUE. If other options for discovering opportunities for parallel apply are implemented later, this restriction can be lifted. For this worklog, it is permissible, but not required, to use a simple scheduling of parallel events to apply threads: take a numeric hash of the database name and queue it for execution by thread (HASH mod N). This might be simpler to implement than a more clever scheduling, though it might reduce the opportunity for parallelism in case of hash collisions between distinct database names. When two transactions are applied in parallel on the slave, there is the question whether they are allowed to commit in different order than they did on the master. If commit in different order is allowed, this is called "full independence". If not, this is called "binlog order". With "binlog order", it is necessary to implement some mechanism to delay the commit of one thread until other threads have applied and committed all prior transactions. This waiting also has the potential to reduce the opportunities for parallelism. For "full independence", as a consequence of committing in different order, there will be database states seen on the slave that never existed on the master; the application will have to be tolerant of this. Also, the binlogs on the slaves will have events in different order than on the master; this complicates the promotion of a new master, as different servers will in general have incommensurable states, neither of which is a subset of the other. It still needs to be determined which of "binlog order" and "full independence" should be implemented. Probably a good idea is to start with just "full independence" due to its simplicity. Then "binlog order" and/or automated master promotion can be added as follow-up projects later. Related worklogs: MWL#XXX: Promotion of new master with full independence parallel replication. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[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