- developers - lists.mariadb.org

Re: [Maria-developers] Restarting cooperation of Percona and Monty Programming AB
by Kristian Nielsen 06 Jan '11

06 Jan '11

Michael Widenius <monty(a)askmonty.org> writes: >>>>>> "Kristian" == Kristian Nielsen <knielsen(a)knielsen-hq.org> writes: > > <cut> > > Kristian> Anyway, I fixed the patch, added test cases, and merged into MariaDB. It is > Kristian> pushed here: > > Kristian> lp:~knielsen/maria/5.2-percona-row_based_replication_without_primary_key > > Kristian> Here is the commit message: > > Kristian> Merge Percona patch row_based_replication_without_primary_key.patch into MariaDB. > > Kristian> This patch improves the selection of index to use to apply row-based > Kristian> DELETE and UPDATE events on tables with no primary key (original code > Kristian> picks the first index unconditionally). > Kristian> If ANALYZE TABLE is done, the index cardinalities will be compared and > Kristian> the best index will be used. > > Kristian> Fixes some problems in the original patch: > > Kristian> - Without ANALYZE TABLE, rec_per_key statistics is not available; in this > Kristian> case the original patch could choose a really bad index, even ignoring > Kristian> a primary key. > > Kristian> - The original patch did not consider multi-column keys correctly, and > Kristian> could thus pick a less desirable single-column key over a good > Kristian> multi-column index. > > Kristian> Also fixes Bug#58997, and adds test cases. > > Kristian> Monty, how do I include it in our percona tree?, can I just push here: > > Kristian> lp:~maria-captains/maria/5.2-percona > > Yes, feel free to push if no one from Percona has any problems with > your patch. So, any objections or comments from Percona on this patch? I've included the patch below for convenience. - Kristian.

2 2

[Maria-developers] WL#172 New (by Monty): Add support for prepared statements to HANDLER READ
by worklog-noreply＠askmonty.org 03 Jan '11

03 Jan '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add support for prepared statements to HANDLER READ CREATION DATE..: Mon, 03 Jan 2011, 19:19 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 172 (http://askmonty.org/worklog/?tid=172) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: DESCRIPTION: Add support for prepared statements to HANDLER READ. As part of this work, move some of the word done during handler_read to handler_open to speed up HANDLER READ calls. Example: handler t1 open; prepare stmt from 'handler t1 read a=(?) limit ?,?'; set @a=16,@b=1,@c=100; execute stmt using @a,@b,@c; deallocate prepare stmt; ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 2999: Speed up `mtr --parallel=<lots>` by scheduling some slow tests earlier. in http://bazaar.launchpad.net/~maria-captains/maria/5.1
by Sergei Golubchik 03 Jan '11

03 Jan '11

Hi, knielsen! On Jan 03, knielsen(a)knielsen-hq.org wrote: > At http://bazaar.launchpad.net/~maria-captains/maria/5.1 Hm. Why do you prefer to add a dummy include file to every "long" test, instead of using a separate file, like disabled.def ? I think it'd be easier to maintain in a separate file, and easier to extend later on. Regards, Sergei

2 2

[Maria-developers] Logoff trigger for MariaDB
by Oli Sennhauser 31 Dec '10

31 Dec '10

Hi all, As suggested by Sergei we should discuss this here. Copying the login trigger stuff to a place in the code where I thought it makes sense I have created a logout trigger. This gives the capability to track a session from its beginning to its end and collects all the data from each session. With this capability we can start thinking about auditing of MySQL users like it is needed/wanted from big companies. I am NOT a developer and I was just lucky that it worked out how I did it. So I am not of much help for an other approach as Sergei hinted in his comment. Now: free to discuss... http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing Regards, Oli -- FromDual - Vendor independent and neutral MySQL consulting. Website http://www.fromdual.com/ Skype fromdual Jabber fromdual(a)swissjabber.ch Yahoo IM fromdual Xing https://www.xing.com/profile/Oliver_Sennhauser LinkedIn http://ch.linkedin.com/in/shinguz Twitter http://twitter.com/shinguz

2 2

Re: [Maria-developers] [Fwd: [Commits] Rev 2998: Fixed LP bug #639935 (bug #58727). in file:///home/igor/maria/maria-5.1-bug693935/]
by Oleksandr Byelkin 24 Dec '10

24 Dec '10

Hi! 24 Dec 2010, в 06:45, Igor Babaev написал(а): > Sanja, > > Please review this fix ASAP. > We need it to move on with 5.3 The patch is OK, but I would prefer to have comment before Item::quick_fix_field() that this method is only for optimizer needs, it can be used only for certain Items controlled by optimizer and maybe add that only Item_func_* need recursion for this. But you could write comments much better then I so it is up to you how write the comment. > > Regards. > Igor. > > -------- Original Message -------- > Subject: [Commits] Rev 2998: Fixed LP bug #639935 (bug #58727). in > file:///home/igor/maria/maria-5.1-bug693935/ > Date: Fri, 24 Dec 2010 06:42:05 +0200 (EET) > From: <igor(a)askmonty.org> > Reply-To: maria-developers(a)lists.launchpad.net > To: <commits(a)mariadb.org> > > At file:///home/igor/maria/maria-5.1-bug693935/ > > ------------------------------------------------------------ > revno: 2998 > revision-id: igor(a)askmonty.org-20101224044150-p9yja1e87riwjh7g > parent: monty(a)askmonty.org-20101221172350-wqtf0t6evpc0un4b > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.1-bug693935 > timestamp: Thu 2010-12-23 20:41:50 -0800 > message: > Fixed LP bug #639935 (bug #58727). > When the optimizer creates items out of other items it does > not have to call the fix_fields method. Usually in these > cases it calls quick_fix_field() that just marks the > created item as fixed. If the created item is an Item_func > object then calling quick_fix_field() works fine if the > arguments of the created functional item are already fixed. > Otherwise some unfixed nodes remain in the item tree and > it triggers an assertion failure whenever the item is > evaluated. > > Fixed the problem by making the method quick_fix_field > virtual and providing an implementation for the class > Item_func objects that recursively calls the method > for unfixed arguments of any functional item. [skip]

1 0

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

22 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