- developers - lists.mariadb.org

Re: [Maria-developers] pre-allocating binlog to speed up sync_binlog=1
by Sergei Golubchik 23 Dec '10

23 Dec '10

Hi, Arjen! [moving to the public list] On Dec 14, Arjen Lentz wrote: > Hi all > > Can we adopt/implement http://forge.mysql.com/worklog/task.php?id=4925 in > MariaDB? > The benchmark info is in the item, and looks quite interesting. > The author tested it using a separate tool to preallocate the binlog, but > it should be straightforward implementing this inside the server, that'd > also be much easier to manage without extra fuss for a DBA. You mean - before it gets into 5.6 and we merge it the normal way? Or you mean - with a different implementation (w/o mysqlbinlogalloc) ? Regards, Sergei P.S. These are the changesets: final version: http://lists.mysql.com/commits/113309 http://lists.mysql.com/commits/113306 http://lists.mysql.com/commits/113307 review comments: http://lists.mysql.com/commits/116965 http://lists.mysql.com/commits/121478

4 6

Re: [Maria-developers] [Commits] Rev 2959: New table creation/renaming block added if old encoded table present. in file:///home/bell/maria/bzr/work-maria-5.1-CREATE/
by Sergei Golubchik 20 Dec '10

20 Dec '10

Hi, sanja! On Oct 22, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.1-CREATE/ > > ------------------------------------------------------------ > revno: 2959 > revision-id: sanja(a)askmonty.org-20101021220232-eqso4ey46985wfzf > parent: sergii(a)pisem.net-20101020105843-7ybuqyc9n5328tie > committer: sanja(a)askmonty.org > branch nick: work-maria-5.1-CREATE > timestamp: Fri 2010-10-22 01:02:32 +0300 > message: > New table creation/renaming block added if old encoded table present. ok to push Regards, Sergei

1 0

[Maria-developers] Proposals for improving BKA/MRR visibility in EXPLAIN and diagnostics
by Sergey Petrunya 17 Dec '10

17 Dec '10

Hello, Below are some ideas on how to make DS-MRR/BKA easier to work with for the users (= those who don't run mysqld under debugger). Questions are marked with 'Q:' but any comments are welcome. Better EXPLAIN -------------- Philip has complained numerous times that it is not shown in EXPLAIN whether DS-MRR will use key sorting. Currently, MRR alone is show like this: MariaDB [test]> explain select * from t1 where key1<30; +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+ | 1 | SIMPLE | t1 | range | key1 | key1 | 5 | NULL | 1 | Using index condition; Using MRR | +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------+ MRR with BKA are shown like this: MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | test.t2.a | 1 | Using join buffer (flat, BKA join) | +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------+ The suggestion is to - get rid of "Using MRR" word, - instead, show "Sort rowids" and/or "Sort keys". - Show the above two whenever rowid-ordered and/or key-ordered retrieval is performed. The above examples will look as follows: MariaDB [test]> explain select * from t1 where key1<30; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ | 1 | SIMPLE | t1 | range | key1 | key1 | 5 | NULL | 1 | Using index condition; Sort keys; Sort rowids | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+ MRR with BKA are shown like this: MariaDB [test]> explain select * from t2,t1 where t2.a=t1.key1; +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | test.t2.a | 1 | Using join buffer (flat, BKA join); Sort keys; Sort rowids | +----+-------------+-------+------+---------------+------+---------+-----------+------+------------------------------------------------------------+ Q: any comments? Counters -------- Like it is done with other kinds of table accesses, we want to have counter-based way of analyzing of what has been happening around MRR. The most obvious are: 1. Handler_multi_range_read_init_count status variable This will tell how many multi_range_read_init() calls have been made that used non-default MRR implementation, i.e. one will be able to see how many times real MRR scans were performed. Q: this counter doesn't show how many times key sorting/rowid sorting/both strategies were used. Is that ok? One could argue that information about strategy choice is not in high demand as MRR strategy choice is based mostly on system settings and DDLs. 2. Handler_multi_range_read_next_count status variable This will tell how many records were returned by MRR to the upper layer. Q: MRR does index and rnd_pos scans under the hood, and these scans do increase counters. This means that, for a single row returned by MRR, multiple counters will be incremented. Less obvious suggestions: 3. It would be useful to have an idea about whether DS-MRR had sufficient buffer space to operate. One can get a rough picture by adding counters of key/rowid sort operations: - Handler_mrr_rowid_sort_count - Handler_mrr_key_sort_count If Handler_mrr_key_sort_count == Handler_multi_range_read_init_count then we've had enough buffer space. If key_sort_count is two times greater than the number of init operations, then one could conclude that on average two buffer refills were needed and increasing buffer size up to 2x would be beneficial. 4. Other possible things - Index condition pushdown check count, true/false ratio - Some statistics to globally check if the optimizer's guesses about required sizes for key/rowid parts of the buffer were any good. Q: do we see the need for any of the above? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 3

[Maria-developers] WL#171 New (by Monty): new free MariaDB client library
by worklog-noreply＠askmonty.org 14 Dec '10

14 Dec '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: new free MariaDB client library CREATION DATE..: Tue, 14 Dec 2010, 19:51 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-Sprint TASK ID........: 171 (http://askmonty.org/worklog/?tid=171) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Create a new MariaDB/MySQL/Drizzle client library that is Apache, BSD or LGPL. This task is to correct the limitations of the old lgpl client library, MWL#134, as outlined in http://kb.askmonty.org/v/lgpl-mysql-client-library-32358. Minimum requirements are: * Should be binary link level compatible with the current MySQL 5.1 client library. To use it, one would just have to relink the application with the new library. No re-compilation of the source should be necessary. ** It should be able to connect to all current versions of MariaDB, MySQL and Drizzle. * It should support all the new functionality in MariaDB's client library: ** New functions ** Pluggable authentication ** The license should be one of BSD/LGPL/Apache The different development options are: * Use the old LGPL MySQL library from 3.23 as a base. * Use Drizzles client library as base. * Use one of the free MySQL client libraries used by some of the languages as a base. * Writing a new one from scratch. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 2867: Fix LP BUG#685411 in file:///home/tsk/mprog/src/5.3-lpb-685411/
by Oleksandr Byelkin 14 Dec '10

14 Dec '10

Hi! On 14.12.2010 14:08, timour(a)askmonty.org wrote: > At file:///home/tsk/mprog/src/5.3-lpb-685411/ > > ------------------------------------------------------------ > revno: 2867 > revision-id: timour(a)askmonty.org-20101214120805-vafrxb6i59u9gyef > parent: igor(a)askmonty.org-20101211205039-b1alcmyethjpddzu > committer: timour(a)askmonty.org > branch nick: 5.3-lpb-685411 > timestamp: Tue 2010-12-14 14:08:05 +0200 > message: > Fix LP BUG#685411 Ok, to push. [skip]

1 0

[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