July 2011 - developers - lists.mariadb.org

[Maria-developers] MySQL Bug 59449 - Accepted by MariaDB?
by Jeremy Thomerson 21 Sep '11

21 Sep '11

I just submitted a patch to a MySQL bug that I am also experiencing. I would like to see it accepted into the wider community (MySQL, MariaDB, Percona, etc). Please let me know what I need to do so that this patch could also be accepted into your project. The bug is here: http://bugs.mysql.com/bug.php?id=59449 The "[11 Jun 3:16] Jeremy Thomerson" comment will be the most useful. Please CC me on any replies to this thread because I am not subscribed to this mailing list. Best Regards, Jeremy Thomerson

3 4

[Maria-developers] Changes to table_statistics for mariadb 5.2
by Eric Bergen 26 Aug '11

26 Aug '11

Hi, I've been working on some improvements to the table and index statistics in mariadb that allow tracking on the session and query level. I've been pushing the changes to lp:~provenscaling-eric/maria/tivo The long form version of where I'm at in my hacking is at http://ebergen.net/wordpress/2011/07/28/second-update-of-modifying-table-st… I want to poll this list for ides on a few places I'm stuck and get the review process started to hopefully get some of these changes merged back into mainline mariadb. My first issue is around allocation of query ids for both show profiles and show table_statistics; Table statistics are enabled by userstat and profiles are controlled by profiling. Enabling these options at different times makes some confusing results: mysql> use test; Database changed mysql> set query_statistics_history_size=5; Query OK, 0 rows affected (0.00 sec) mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> select * from t limit 5; +-------+--------+ | t | u | +-------+--------+ | 14515 | 282874 | | 14521 | 258653 | | 14573 | 113276 | | 14577 | 826475 | | 14585 | 444645 | +-------+--------+ 5 rows in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 7 | 0.00052300 | select * from t limit 5 | +----------+------------+-------------------------+ 1 row in set (0.00 sec) mysql> set profiling=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from t limit 10; +-------+--------+ | t | u | +-------+--------+ | 14515 | 282874 | | 14521 | 258653 | | 14573 | 113276 | | 14577 | 826475 | | 14585 | 444645 | | 14612 | 792545 | | 14626 | 483300 | | 14842 | 447267 | | 15325 | 38865 | | 15340 | 744424 | +-------+--------+ 10 rows in set (0.00 sec) mysql> show query table_statistics; +----------+--------------+------------+-----------+--------------+-------------------------+ | Query_id | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +----------+--------------+------------+-----------+--------------+-------------------------+ | 2 | #temp# | #temp# | 15 | 15 | 15 | | 7 | test | t | 5 | 0 | 0 | | 10 | test | t | 10 | 0 | 0 | +----------+--------------+------------+-----------+--------------+-------------------------+ 3 rows in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------+ | 7 | 0.00052300 | select * from t limit 5 | +----------+------------+-------------------------+ 1 row in set (0.00 sec) My current thought is to create a show query history command that unifies both show profiles and user stats but only table stats or profiles will be available for any given query depending on the flags set when it ran. I can also create a separate show queries command only for table statistics. Any ideas on how to unify the two different profiling methods or split up the syntax? -- Eric Bergen eric.bergen(a)gmail.com http://www.ebergen.net

3 4

[Maria-developers] Review of mysql-test-run speedup patch
by Kristian Nielsen 31 Jul '11

31 Jul '11

Hi Serg, Can you review below patch? (sorry for no commit mail, I forgot to configure outgoing mail on my new laptop before committing). I was for a long time annoyed that mtr takes 20 seconds starting up before it starts running the first test. So being bored one evening I ran a Perl profiler on the code and fixed the culprit. (BTW, I haven't forgotten your suggestion to change include/long_test.inc information to be in a separate file instead; I just haven't gotten around to it yet.) - Kristian.

2 2

Re: [Maria-developers] [Commits] Rev 3135: Subquery cache going on disk management fix: Do not go on disk if hit rate is not good. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hi Sanja, Ok to push. On Thu, Jul 28, 2011 at 05:10:29PM +0300, sanja(a)askmonty.org wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/ > > ------------------------------------------------------------ > revno: 3135 > revision-id: sanja(a)askmonty.org-20110728141029-adkbj1vedh86m3uc > parent: igor(a)askmonty.org-20110723064728-j2sgq39rp528eaol > committer: sanja(a)askmonty.org > branch nick: work-maria-5.3-subquerycachedisk > timestamp: Thu 2011-07-28 17:10:29 +0300 > message: > Subquery cache going on disk management fix: Do not go on disk if hit rate is not good. <skip> BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Is it time to change 5.3 bugs status from Fix Committed to Fix Released?
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hello, The bug database has 197 bugs with status="Fix committed" and target_milestone=5.3. Since we've released 5.3, is it time to change status on these bugs to "Fix released"? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3135: Subquery cache going on disk management fix: Do not go on disk if hit/miss ration less than 2. in file:///home/bell/maria/bzr/work-maria-5.3-subquerycachedisk/
by Sergey Petrunya 28 Jul '11

28 Jul '11

Hi Sanja A summary of our discussion on irc/skype: Please change EXPCACHE_DISK_HITMISS_RATIO to be a "hit ratio", i.e "fraction of accesses that result in cache hits", as that's this is the most common number people use when they think about cache performance. Please change one limit into two: 1. A very poor hit ratio that will cause cache to stop working altogether (like the below patch does) 2. A moderately poor ratio that will cause the in-memory table to be emptied (instead of being converted to on-disk table). Suggested names: EXPCACHE_MIN_HIT_RATIO_FOR_DISK_TABLE, EXPCACHE_MIN_HIT_RATIO_FOR_MEM_TABLE. Please supply both definitions with comments about their meaning. On Thu, Jul 28, 2011 at 01:18:56PM +0300, sanja(a)askmonty.org wrote: > === modified file 'sql/sql_expression_cache.cc' > --- a/sql/sql_expression_cache.cc 2011-07-19 20:19:10 +0000 > +++ b/sql/sql_expression_cache.cc 2011-07-28 10:18:55 +0000 > @@ -16,6 +16,8 @@ > #include "mysql_priv.h" > #include "sql_select.h" > > +#define EXPCACHE_DISK_HITMISS_RATIO 2 > /* > Expression cache is used only for caching subqueries now, so its statistic > variables we call subquery_cache*. > @@ -26,7 +28,7 @@ Expression_cache_tmptable::Expression_ca > List<Item> &dependants, > Item *value) > :cache_table(NULL), table_thd(thd), items(dependants), val(value), > - inited (0) > + hit(0), miss(0), inited (0) > { > DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable"); > DBUG_VOID_RETURN; > @@ -180,10 +182,12 @@ Expression_cache::result Expression_cach > if (res) > { > subquery_cache_miss++; > + miss++; > DBUG_RETURN(MISS); > } > > subquery_cache_hit++; > + hit++; > *value= cached_result; > DBUG_RETURN(Expression_cache::HIT); > } > @@ -224,12 +228,26 @@ my_bool Expression_cache_tmptable::put_v > if ((error= cache_table->file->ha_write_tmp_row(cache_table->record[0]))) > { > /* create_myisam_from_heap will generate error if needed */ > - if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && > - create_internal_tmp_table_from_heap(table_thd, cache_table, > - cache_table_param.start_recinfo, > - &cache_table_param.recinfo, > - error, 1)) > + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP)) > goto err; > + else > + { > + if ((hit/miss) < EXPCACHE_DISK_HITMISS_RATIO) > + { > + DBUG_PRINT("info", ("hit/miss ratio is not so good to go to disk")); > + free_tmp_table(table_thd, cache_table); > + cache_table= NULL; > + DBUG_RETURN(FALSE); > + } > + else > + { > + if (create_internal_tmp_table_from_heap(table_thd, cache_table, > + cache_table_param.start_recinfo, > + &cache_table_param.recinfo, > + error, 1)) > + goto err; > + } > + } > } > cache_table->status= 0; /* cache_table->record contains an existed record */ > ref.has_record= TRUE; /* the same as above */ > > === modified file 'sql/sql_expression_cache.h' > --- a/sql/sql_expression_cache.h 2011-07-19 20:19:10 +0000 > +++ b/sql/sql_expression_cache.h 2011-07-28 10:18:55 +0000 > @@ -85,6 +85,8 @@ private: > List<Item> &items; > /* Value Item example */ > Item *val; > + /* hit/miss counters */ > + uint hit, miss; Please change to be ulong (same type as subquery_cache_hit/miss) > /* Set on if the object has been succesfully initialized with init() */ > bool inited; > }; > -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] WL#233 New (by Aag): Partial Range Indx
by worklog-noreply＠askmonty.org 28 Jul '11

28 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Partial Range Indx CREATION DATE..: Thu, 28 Jul 2011, 10:44 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Maria-RawIdeaBin TASK ID........: 233 (http://askmonty.org/worklog/?tid=233) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: A way to make Indices significantly useful in some usage scenarios: Scoping the index to a specific range of values. This can be useful if some queries are only performed on a specific subset of the rows. Example: A process that implements a state machine for each row. All finished tasks have the state -1. To lookup the next task to perform only states > -1 have to be considered. Creating an index for the criteria to find the next task quickly would also index all the finished tasks. Which can make index creation expensive and waste several Gigabytes of diskspace (assuming millions of entries in the DB). While partitioning promises similar effects (a second axis for query optimizations) it is rendered utterly useless by its restriction that the partioning index must occur in *all* primary and unique keys which means one cannot define a large natural key via unique and a small artificial ID as primary key and then partition by an unrelated aspect (such as the state in my example). Range-restricted indices would allow similar optimizations: specify a range-restricted column as first column and then add the remaining criteria as other columns. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#232 New (by ColinCharles): Add missing SHUTDOWN statement
by worklog-noreply＠askmonty.org 28 Jul '11

28 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add missing SHUTDOWN statement CREATION DATE..: Thu, 28 Jul 2011, 03:26 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: Monty CATEGORY.......: Server-RawIdeaBin TASK ID........: 232 (http://askmonty.org/worklog/?tid=232) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: >From http://www.jebriggs.com/blog/2011/07/mysqls-missing-shutdown-statement/ Databases are more easily managed if all operations and logging are available to DBAs and scripts at the SQL client command line. So an additional improvement would be to add a SQL command like Oracles SHUTDOWN. See the blog post for a comparison table Reference from Oracle: http://www.dba-oracle.com/concepts/shutdown_commands.htm http://dev.mysql.com/doc/refman/5.5/en/mysql-shutdown.html http://dev.mysql.com/doc/refman/5.5/en/server-shutdown.html http://dev.mysql.com/doc/refman/5.5/en/kill.html http://dev.mysql.com/doc/refman/5.5/en/stop-slave.html USD$100 committed from James Briggs at the MariaDB BoF at OSCON 2011 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#231 New (by Monty): Faster UNIQUE key generation with ALTER TABLE
by worklog-noreply＠askmonty.org 27 Jul '11

27 Jul '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Faster UNIQUE key generation with ALTER TABLE CREATION DATE..: Wed, 27 Jul 2011, 16:49 SUPERVISOR.....: Monty IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 231 (http://askmonty.org/worklog/?tid=231) VERSION........: Server-5.5 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 30 (hours remain) ORIG. ESTIMATE.: 30 PROGRESS NOTES: DESCRIPTION: Faster UNIQUE key generation with ALTER TABLE Generate also unique keys by sorting (as we do with non unique keys) to speed up alter tables with unique keys significantly. This worklog is for doing this for MyISAM and Aria tables. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] Semi-join processing with Duplicate Elimination
by Sergey Petrunya 21 Jul '11

21 Jul '11

Hello, The below really should be in documentation, but for now, I'm posting it here: Semi-join processing with Duplicate Elimination =============================================== There are three cases: Semi-join next to outer join ---------------------------- ot1 left join (ot10, ot11) semi-join (it1, it2) interesting possible join orders: ot1 {ot10 ot11} it1 it2 <-- here we have just a "tail" so no problem. it1 ot1 {ot10, ot11} it2 Here the range fully covers the inner join with all its NULL-complemented record generation, etc. We need to take care about - NULL rowids are provided for NULL-complemented rows - being able to jump over all this when doing weedout (can we have a problem with some OJ's match flag not being reset?) it1 ot1 it2 {ot10 ot11} | | +------------------+ dups-w range This is a special case of the previous case. Need to - check dups-w for NULL-complemented record. (in case of nested outer joins: check for all NULL-complemented records generated at this point? well they all call evaluate_null_complemented_record, do they?) - check if this works with Not-exists optimization. it1 ot1 it2 {ot10 ot11} | | +------------+ dups-w range This case is the most interesting. The question is: should we first check dups-w, or care about outer joins? - dups-w check can be moved to the right. if we take a record combination of {ot1.row it2.row ot10.row} we must not discard it, because we'll need to know if this record combination extends to {ot1.row, it2.row ot10.row ot11.row } If it does, outer join will not have NULL-complemented record, otherwise it will. this means: no dups-w checks inside outer joins. Move them to the right until we've got the table that's last for all inner joins we're in. Outer join inside semi-join --------------------------- ot1 semi join (it1 left join (iit2, iit3)) Possible join orders: 1) ot1 it1 {iit2, iit3} 2) it1 ot1 {iit2, iit3} 3) it1 {iit2, iit3} ot1 4) ot1 {iit2, iit3} it1 #1 and #2 have an "outside" prefix followed by "tail" of SJ-inner OJ-inner tables. For iit3, we must first form a record for which both parts of WHERE and ON have been checked (or the record could be NULL-complemented) for #3 and #3, dups-w check is done after OJ processing. Semi-join inside outer join --------------------------- - Not possible in current scheme. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0