- developers - lists.mariadb.org

Re: [Maria-developers] [Commits] Rev 3145: MDEV-80 Memory engine table full at much less than max_heap_table_size with btree index. in file:///home/hf/wmar/mdev80/
by Sergei Golubchik 06 Apr '12

06 Apr '12

Hi, Holyfoot! So, does that mean you've managed to reproduce the bug? Or you just found a possible cause for such a behavior and fixed it? On Apr 05, holyfoot(a)askmonty.org wrote: > revno: 3145 > revision-id: holyfoot(a)askmonty.org-20120405082116-w4xqjil2adoq12r3 > parent: sergii(a)pisem.net-20120404134150-6bonhsdqyrxjhku5 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev80 > timestamp: Thu 2012-04-05 13:21:16 +0500 > message: > MDEV-80 Memory engine table full at much less than max_heap_table_size with btree index. > RB-tree index in the MEMORY table fails if it grews over 4G. > That happened because the old_allocated variable in hp_rb_write_key() > had the uint type. Changed with the 'size_t' type to be same as the > 'rb_tree.allocated'. > > per-file comments: > storage/heap/hp_write.c > MDEV-80 Memory engine table full at much less than max_heap_table_size with btree index. > uint->size_t for the 'old_allocated'. Regards, Sergei

2 3

[Maria-developers] MariaDB 5.1.62 and 5.2.12 releases
by Daniel Bartholomew 06 Apr '12

06 Apr '12

All, I've uploaded the packages for MariaDB 5.1.62 and 5.2.12 to the primary mirror. I've also created initial Release Notes and Changelog pages for the releases. These releases are happening now because they include an important security fix. MariaDB 5.1.62: * http://kb.askmonty.org/en/mariadb-5162-release-notes * http://kb.askmonty.org/en/mariadb-5162-changelog MariaDB 5.2.12: * http://kb.askmonty.org/en/mariadb-5212-release-notes * http://kb.askmonty.org/en/mariadb-5212-changelog Please let me know ASAP about anything which should be mentioned in the release notes. My current plan is to give the mirrors some time to pull from the primary mirror and then activate the releases on Friday morning (6 April, US Eastern time). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

Re: [Maria-developers] [Commits] Rev 3488: Fix of LP bug#968720. in file:///home/bell/maria/bzr/work-maria-5.3-lpb968720/
by Igor Babaev 05 Apr '12

05 Apr '12

Sanja, Please correct the comment (see my variant below) and fix the problem in cut() I discovered. Sorry, I basically don't understand your code. Contact me on IRC please. Regards, Igor. On 04/04/2012 04:01 AM, sanja(a)montyprogram.com wrote: > At file:///home/bell/maria/bzr/work-maria-5.3-lpb968720/ > > ------------------------------------------------------------ > revno: 3488 > revision-id: sanja(a)montyprogram.com-20120404110135-7eca0tt18auum3xo > parent: monty(a)askmonty.org-20120402092715-4cwn2rrcdeafir0x > committer: sanja(a)montyprogram.com > branch nick: work-maria-5.3-lpb968720 > timestamp: Wed 2012-04-04 14:01:35 +0300 > message: > Fix of LP bug#968720. > > Problem: > During converting from merged to materialized all items listed in used_item list changed from real tables to When a view/derived table is converted from merged to materialized the items from the used_item lists are substituted for items referring to the fields of the result of the materialization. The problem appeared with queries employing natural joins. Since the resolution of a natural join was performed only once the used_item list formed at the second execution of the query lacked the references to the fields that were used only in the equality predicates generated for the natural join. the temporary table of materialized join. The problem was that natural join matching made only once and on second prepare phase (first is prepare opf PREPARE statement, second is prepare phase of executing the statement) the list lack fields of natural join because it is not made second time. > > Solution: > Save the used_items list after natural join matching and restore it during re-initialization. > > > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits + /* + Save the lists made during natural join matching (because + the matching done only once but we need the list in case + of prepared statements). + */ ^ remove this comment altogether. + table_ref_1->save_used_items= table_ref_1->used_items; + table_ref_2->save_used_items= table_ref_2->used_items; You save here the head of list only. Why? + ... + /** + Cut the list with leaving not more then n elements ^to leave + */ + inline uint cut(uint n) + { + list_node *element= first; + uint i= 0; + for (; + i < n && element != &end_of_list; + element= element->next); + if (element != &end_of_list) + { + elements= i + 1; + last= &element->next; + element->next= &end_of_list; + } + return i + 1; + } ^^^ i is initialized to 0. i is never changed. in this context the comparison i<n does not make any sense. + /* + Following is a bit hack but pointer length is equal everywhere + and we need revert pointers after prepared statement execution. + */ ^We need to restore the pointers after the execution of the prepared statement + thd->change_item_tree((Item **)&ref->ref, (Item*)materialized_items + idx);

1 0

Re: [Maria-developers] [Commits] Rev 3126: FIX for LP bug#956079 in file:///home/bell/maria/bzr/work-maria-5.2-lpb956079/
by Michael Widenius 03 Apr '12

03 Apr '12

Hi! >>>>> "sanja" == sanja <sanja(a)montyprogram.com> writes: sanja> At file:///home/bell/maria/bzr/work-maria-5.2-lpb956079/ sanja> ------------------------------------------------------------ sanja> revno: 3126 sanja> revision-id: sanja(a)montyprogram.com-20120319122824-7tnmdh8bq7cbnvni sanja> parent: sanja(a)montyprogram.com-20120314100903-a6txqk4l73smh189 sanja> committer: sanja(a)montyprogram.com sanja> branch nick: work-maria-5.2-lpb956079 sanja> timestamp: Mon 2012-03-19 14:28:24 +0200 sanja> message: sanja> FIX for LP bug#956079 sanja> Always set all callbacks for page cache. <cut> > --- a/storage/maria/ma_pagecache.c 2011-08-10 19:44:39 +0000 > +++ b/storage/maria/ma_pagecache.c 2012-03-19 12:28:24 +0000 > @@ -633,7 +633,6 @@ static my_bool pagecache_fwrite(PAGECACH > { > DBUG_ENTER("pagecache_fwrite"); > DBUG_ASSERT(type != PAGECACHE_READ_UNKNOWN_PAGE); > - > #ifdef EXTRA_DEBUG_BITMAP > /* > This code is very good when debugging changes in bitmaps or dirty lists > @@ -654,6 +653,12 @@ static my_bool pagecache_fwrite(PAGECACH > } > #endif > > + DBUG_ASSERT(filedesc->read_callback); > + DBUG_ASSERT(filedesc->write_callback); > + DBUG_ASSERT(filedesc->write_fail); > + DBUG_ASSERT(filedesc->flush_log_callback); Aren't the above callbacks guranteed to be set by the init code? If thats the case, there is no reason to test for these. There is no reason to test for read_callback or write_callback as we will always get a crash if these are not set (a few lines later). As we already test this in ma_pagecache.h, I think we can remove the DBUG_ASSERT's from ma_pagecache.c > === modified file 'storage/maria/ma_pagecache.h' > --- a/storage/maria/ma_pagecache.h 2011-02-28 17:39:30 +0000 > +++ b/storage/maria/ma_pagecache.h 2012-03-19 12:28:24 +0000 > @@ -271,6 +271,10 @@ extern void pagecache_set_write_on_delet > (F).read_callback= (RC); (F).write_callback= (WC); \ > (F).write_fail= (WF); \ > (F).flush_log_callback= (GLC); (F).callback_data= (uchar*)(D); \ > + DBUG_ASSERT((F).read_callback); \ > + DBUG_ASSERT((F).write_callback); \ > + DBUG_ASSERT((F).write_fail); \ > + DBUG_ASSERT((F).flush_log_callback); \ > } while(0) The above is good! It would of course be good to always use the function pagecache_file_init() to set all callbacks instead of setting these directly. To do this, you could change ma_open.c:: _ma_set_data_pagecache_callbacks() to use the above function instead of setting these directly. This way we would know for sure that either none or all are set and we don't need to do any DBUG_ASSERT() of these elements in the code. Regards, Monty

1 0

[Maria-developers] Please test: fix for BUG#913030
by Sergei Petrunia 02 Apr '12

02 Apr '12

Hi Elena, I need a separate test run of semi-join subquery tests on this branch: bzr+ssh://bazaar.launchpad.net/~maria-captains/maria/5.3-bug913030 I need it because the bugfix in this branch changes the order in which join optimizer considers query plans for semi-join subqueries. For synthetic data that RQG works on, this can have an effect that the query plans that were not choosen before will not be used. BUG#913030 is a high-priority 5.3 bug, so this is fairly urgent. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB 5.1.61 and 5.2.11 releases
by Daniel Bartholomew 31 Mar '12

31 Mar '12

All, I've uploaded the packages for MariaDB 5.1.61 and 5.2.11 to the primary mirror. I've also created initial Release Notes and Changelog pages for the releases. MariaDB 5.1.61: * http://kb.askmonty.org/en/mariadb-5161-release-notes * http://kb.askmonty.org/en/mariadb-5161-changelog MariaDB 5.2.11: * http://kb.askmonty.org/en/mariadb-5211-release-notes * http://kb.askmonty.org/en/mariadb-5211-changelog Please let me know ASAP about anything which should be mentioned in the release notes. My current plan is to activate the releases on Monday morning (US Eastern time). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] Analysing MariaDB 5.5 sysbench performance regression
by Kristian Nielsen 31 Mar '12

31 Mar '12

I have been analysing the performance difference between MariaDB 5.5.21 and MySQL 5.5.21 using oltp.lua in sysbench 0.5. It turns out that MySQL has about 8% higher tps in this benchmark when running CPU bound. (See below for links to blog posts that started this). I took the base sysbench run and tweaked it to get stable numbers - basically put the datadir in ramdisk and adjusted my.cnf a bit (more details below). Then I used OProfile to analyse where the difference comes from. Oprofile is a really great tool for this kind of analysis, where CPU time spent is the measurement. It is very easy to use, and it gives detailed and accurate measurements on how much time is spent in each function. It is then easy to compare this against MySQL and find where we have performance regressions. Then further details is available by adding call graph to the reports, which shows from where the expensive parts are called; and by using the annotation facility which annotates every function and puts execution times on the individual statement and assembler functions. Here is an overview of where the extra time is spent. Time is in units of samples, which is 1/100000 of a clock cycle. Total was around 4.15M samples. Here are the top functions I found used more time in MariaDB, and the amount of extra samples spent in MariaDB: 39k mysql_select 20k alloc_root 16k JOIN::choose_subquery_plan 10k thd_ha_data 10k make_sortkey() 9k get_best_combination 9k handler::read_range_next 7k handler::ha_thd 7k update_ref_and_keys 7k Item::cmp_type 6k Item::operator delete 5k ha_maria::implicit_commit 4k end_write 4k sort_and_filter_keyuse 4k my_interval_timer 4k st_select_lex::master_unit 4k st_select_lex::save_leaf_tables 4k sel_arg_range_seq_next 4k thd_test_options 4k Item_func::walk 4k TABLE::init 4k sub_select 3k strnmov 3k ut_free 3k vio_write 3k ut_malloc_low 2k setup_tables 2k create_ref_for_key Some care is needed to interpret these data. GCC -O3 does heavy inlining. Oprofile is a statistical profiler, so numbers are somewhat uncertain, more so for smaller numbers. And modern CPUs are superscalar, out-of-order, and hyper-threaded, which makes interpreting individual instruction costs higly challenging. But the list can be used to drill down into the actual code; opannotate --source --assembler is very enlightening, in my experience (few examples below). >From this list, it seems that - not surprising - the 5.3 optimiser features seem to have added a bit of overhead. Much of this is spread out over many places in the code, though it could be that the root cause comes from few inlined functions. I checked mysql_select(), and most of the overhead is from lp:941889, expensive constructors in JOIN, which was inlined by GCC. I believe Sergey plans to fix this. More analysis of the individual functions is needed to find the cause there. It seems we are also using alloc_root() somewhat more. thd_ha_data and maybe handler_thd are likely due to using dynamically loaded innodb_plugin rather than built-in. I would like to do more analysis (and would probably need help from optimiser-knowledgable people for most of it) - I think it is feasible to remove most of the overhead in a modest time frame. But my main point is to present these results, to show that we have a very useful tool to pin-point these performance issues, and to start discussion in more detail (and I have plenty more of those). ----------------------------------------------------------------------- More background: These benchmarks started from these blog posts: http://www.mysqlperformanceblog.com/2012/02/18/mariadb-5-3-4-benchmarks/ http://blog.montyprogram.com/benchmarking-mariadb-5-3-4/ The main difference in these tests seem to be caused by different XtraDB buffer pool flush algorithm. So Axel tried using innodb_pluing in MariaDB instead, and got much smaller differences - but a difference was still there. So for 5.5 I took Axel's benchmark, adjusted until I got stable, fully cpu-bound numbers, and analysed the results, as above. I have the actual scripts and my.cnf available if anyone is interested. Main point is datadir in ramdisk, reducing redo log size to 300MB total, and running for 30 seconds. As examples, I analysed a handful of cases, and came up with two simple patches for specific issues. One is update_virtual_fields(), which originally accounted for 0.5% of total time in sysbench. This seems to be called for every row, so just the push/pop of registers in function prologue became significant. I fixed this with below patch; it puts a NULL pointer into the table instead of an empty list of virtual fields, and guards a few frequent calls with an if (NULL) check. Another is my_hash_sort_simple(). This is not a regression, but Oprofile shows we spend 10% of total time in this function. opannotate of the assembler showed the problem. Below simple patch speeds it up by 33%, by reducing 4 memory loads per loop to 2. Still, it would be nice to understand why this is called that much - here is the call stack: my_hash_sort_simple() ha_heap::write_row(). evaluate_join_record() sub_select() do_select() JOIN::exec() mysql_select() It would be nice to understand why this is needed - this is an expensive function, it needs to lookup every character in the collation tables. If this could be eliminated, that alone would probably be enough to win over MySQL, even without fixing any of the regressions. Some other top contenders in the profile are innodb functions. For example buf_calc_page_new_checksum(). This is expensive, as it needs to loop over every byte in each buffer pool page (16K), computing a 7-long dependency chain for each byte. This could be much faster if the checksum was done on 8-byte longwords, but AFAIK, it's not trivial to change the checksum algorithm due to the migration issues. Facebook may have done some work on this. Another top contender is row_sel_store_mysql_rec() and similar - they show high costs in the opannotate on loads from the records, this is probably cache misses from accessing pages in the buffer pool - not too much to do about that I think. Hope this helps, - Kristian. ----------------------------------------------------------------------- Patch for my_hash_sort_simple(): === modified file 'strings/ctype-simple.c' --- strings/ctype-simple.c 2012-01-13 14:50:02 +0000 +++ strings/ctype-simple.c 2012-03-16 16:34:31 +0000 @@ -306,19 +306,24 @@ void my_hash_sort_simple(CHARSET_INFO *c { register const uchar *sort_order=cs->sort_order; const uchar *end; + ulong n1, n2; /* Remove end space. We have to do this to be able to compare 'A ' and 'A' as identical */ end= skip_trailing_space(key, len); - + + n1= *nr1; + n2= *nr2; for (; key < (uchar*) end ; key++) { - nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * - ((uint) sort_order[(uint) *key])) + (nr1[0] << 8); - nr2[0]+=3; + n1^=(ulong) ((((uint) n1 & 63)+n2) * + ((uint) sort_order[(uint) *key])) + (n1 << 8); + n2+=3; } + *nr1= n1; + *nr2= n2; } ----------------------------------------------------------------------- Patch for update_virtual_fields(): === modified file 'sql/filesort.cc' --- sql/filesort.cc 2012-03-11 22:45:18 +0000 +++ sql/filesort.cc 2012-03-16 19:17:58 +0000 @@ -589,7 +589,7 @@ static ha_rows find_all_keys(SORTPARAM * { if ((error= select->quick->get_next())) break; - if (!error) + if (!error && sort_form->vfield) update_virtual_fields(thd, sort_form); file->position(sort_form->record[0]); DBUG_EXECUTE_IF("debug_filesort", dbug_print_record(sort_form, TRUE);); @@ -608,7 +608,7 @@ static ha_rows find_all_keys(SORTPARAM * else { error=file->ha_rnd_next(sort_form->record[0]); - if (!error) + if (!error && sort_form->vfield) update_virtual_fields(thd, sort_form); if (!flag) { === modified file 'sql/records.cc' --- sql/records.cc 2012-01-13 14:50:02 +0000 +++ sql/records.cc 2012-03-16 19:15:51 +0000 @@ -344,7 +344,8 @@ static int rr_quick(READ_RECORD *info) break; } } - update_virtual_fields(info->thd, info->table); + if (info->table->vfield) + update_virtual_fields(info->thd, info->table); return tmp; } === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-03-11 22:45:18 +0000 +++ sql/sql_select.cc 2012-03-16 19:15:05 +0000 @@ -15824,7 +15824,8 @@ evaluate_join_record(JOIN *join, JOIN_TA DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ } - update_virtual_fields(join->thd, join_tab->table); + if (join_tab->table->vfield) + update_virtual_fields(join->thd, join_tab->table); if (select_cond) { === modified file 'sql/table.cc' --- sql/table.cc 2012-03-02 23:03:20 +0000 +++ sql/table.cc 2012-03-18 16:10:18 +0000 @@ -2466,30 +2466,35 @@ int open_table_from_share(THD *thd, TABL /* Process virtual columns, if any. */ - if (!(vfield_ptr = (Field **) alloc_root(&outparam->mem_root, - (uint) ((share->vfields+1)* - sizeof(Field*))))) - goto err; - - outparam->vfield= vfield_ptr; - - for (field_ptr= outparam->field; *field_ptr; field_ptr++) + if (!share->vfields) + outparam->vfield= NULL; + else { - if ((*field_ptr)->vcol_info) + if (!(vfield_ptr = (Field **) alloc_root(&outparam->mem_root, + (uint) ((share->vfields+1)* + sizeof(Field*))))) + goto err; + + outparam->vfield= vfield_ptr; + + for (field_ptr= outparam->field; *field_ptr; field_ptr++) { - if (unpack_vcol_info_from_frm(thd, - outparam, - *field_ptr, - &(*field_ptr)->vcol_info->expr_str, - &error_reported)) + if ((*field_ptr)->vcol_info) { - error= 4; // in case no error is reported - goto err; + if (unpack_vcol_info_from_frm(thd, + outparam, + *field_ptr, + &(*field_ptr)->vcol_info->expr_str, + &error_reported)) + { + error= 4; // in case no error is reported + goto err; + } + *(vfield_ptr++)= *field_ptr; } - *(vfield_ptr++)= *field_ptr; } + *vfield_ptr= 0; // End marker } - *vfield_ptr= 0; // End marker #ifdef WITH_PARTITION_STORAGE_ENGINE if (share->partition_info_str_len && outparam->file) -----------------------------------------------------------------------

4 5

[Maria-developers] MariaDB 5.5.22-rc release
by Daniel Bartholomew 29 Mar '12

29 Mar '12

All, The MariaDB 5.5.22-rc release has been uploaded to the primary mirror and will be replicated to the other mirrors over the next few hours. The release notes and changelog pages for this release are at: https://kb.askmonty.org/en/mariadb-5522-release-notes https://kb.askmonty.org/en/mariadb-5522-changelog As soon as the release files are mirrored I will activate the downloads page, update various banners and links, and then I or Colin will send out an announce email and promote the release in the usual manner. In the mean time, please let me know if there is anything which should be mentioned in the release notes that currently is not. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-developers] WL#250 New (by Igor): Use the statistics from persistent statistical tables instead of the statistics provided by engine
by worklog-noreply＠askmonty.org 25 Mar '12

25 Mar '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Use the statistics from persistent statistical tables instead of the statistics provided by engine CREATION DATE..: Sun, 25 Mar 2012, 17:36 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Monty Psergey Sergei Timour CATEGORY.......: Server-Sprint TASK ID........: 250 (http://askmonty.org/worklog/?tid=250) VERSION........: Server-5.6 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: This task adds the functionality that allows optionally to use use the statistics from persistent statistical tables (see MWL #248) instead the statistics provided by engine. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Improve filesort performance for small sorts
by Sergei Golubchik 24 Mar '12

24 Mar '12

Hi, Kristian! FYI: http://bazaar.launchpad.net/~mysql/mysql-server/trunk/revision/tor.didrikse… On Mar 24, knielsen(a)knielsen-hq.org wrote: > ------------------------------------------------------------ > revno: 3471 > revision-id: knielsen(a)knielsen-hq.org-20120324160859-6pzy6tcxvd6j4fbx > parent: monty(a)askmonty.org-20120323162239-h18wbqpbjz0p00jd > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-5.3 > timestamp: Sat 2012-03-24 17:08:59 +0100 > message: > Improve filesort performance for small sorts: Don't write pointers to records that we will never use. Regards, Sergei

1 0