- developers - lists.mariadb.org

[Maria-developers] Thoughts on Global Transaction ID, parallel slave, and multi-source replication
by Kristian Nielsen 10 Aug '12

10 Aug '12

This is a writeup of some thoughts I have on getting Global Transaction ID in MariaDB, as well as some related thoughts on parallel slave and multi-source replication. MySQL 5.6 has global transaction ID now, and I looked into it in some details. Unfortunately, I _really_ did not like what I saw. There are two main problems, in my opinion: 1. The basic design is bad, leading to an excessively complicated implementation. 2. The implementation looks very incomplete/immature, to the point that I am amazed that anyone would push code like this into a main tree. Point 2 means that I really do not want to maintain this piece of **** in MariaDB, though we can hope that things will improve in subsequent releases. Point 1 leads me to suggest that instead of merging the MySQL 5.6 implementation, we could do our own implementation, using a design that is both much simpler, and at the same time more flexible that what is in MySQL 5.6. I will elaborate further on these points in the rest of the mail. ---- With respect to point 2, let me quote this little gem from the MySQL 5.6 code: /* Apparently this code is not being called. We need to investigate if this is a bug or this code is not necessary. /Alfranio */ DBUG_ASSERT(0); /NOTREACHED/ Now, certainly, there are dark corners of the MySQL replication code, and adding a comment/assert like this could be justified if a developer encounters it and finds it may be dead code. But this is _new_ code, added in a new 5.6 release! So the authors of the new feature, by their own statement, have no idea what the new code they are pushing into the main tree is actually doing. That is just insane. I have no idea what the replication team at MySQL is up to these days pulling stunts like this, I have to confess I am disappointed. I found many similar examples in the 5.6 code. I wrote up a more detailed analysis in TODO-171, where I elaborate more on the problems I see with the MySQL 5.6 Global Transaction ID code: https://mariadb.atlassian.net/browse/TODO-171 When the authors do not understand the code themselves, it really does not seem attractive for us to try to support and bugfix it. I also found a lot of what I think are left-over, unsused parts of an even more complicated design that was apparently abandoned (or postponed?), but left in the code, which does not make supporting this any easier. ---- To explain point 1, I first need to mention the MySQL 5.6 feature of parallel slave apply, as I believe that a major factor in the design of MySQL Global Transaction ID has been the need to interoperate with that feature. The MySQL parallel replication slave feature works so that transactions on tables in different databases can be run in parallel with each other (incidentally, there is some indication that the MySQL replication team considers this *the* meaning of parallel slave, which is sad, as it is just one piece of the general problem of replication slave scalability - it helps in some workloads, but it is useless for others). So with parallel slave, transactions on the master can be executed in a different order on the slave. This means that the crucial notion of a slaves current position in the replication stream becomes more complicated - it is not just a single point in the master binlog, rather every slave replication thread can be at a different point in the master binlog. MySQL 5.6 solves this by introducing "Gtid sets". A Gtid is a MySQL 5.6 global transaction ID - a unique, 24-byte identifier for every transaction. The state of a replication slave at any given moment is then the set Gtids of all transactions ever executed on that slave server This notion of having a set of all transactions ever executed really complicates the design. There is a need to represent such a set as basically intervals of "holes" in the Gtid sequence, to avoid the memory representation of such sets growing without bound over time. This in turns introduces the requirement that there can _never_ be holes in the sequence of Gtids generated on a master - if there ever is introduced a hole, slaves will wait around for ever waiting for a transaction containing the missing Gtid. I think recovering from errors with such a concept will be forever a nightmare. I think this basic fundament of the design is the main source of my dislike for the MySQL global transaction ID. It sounds nice on a first look, but once you start to think deeper on the problem it just does not seem to work well. ---- So I think I have a better idea of how to make Global Transaction ID work well with parallel slave based on executing transactions in different databases out-of-order. The MySQL Gtid is defined as a pair of server-id and sequence number. Every master server has a single server-id (despite no less than 128 bits being allocated for this - anyone planning a replication hierarchy with 2**128 servers?). The master generates a single stream of events in the binlog, though streams from other servers may be interleaved with it in case of multi-master. It is then the task of a slave to try to split up the single master stream in to multiple streams that can be executed in parallel. I think this is the root of the problem. The slave is the wrong place to do this splitting! It is on the master that we know how to interpret transactions (ie. that transactions in different databases are / are not independent), so doing this on the slave is harder. And two slaves can in general split differently, which makes it harder for a slave deeper in the replication hierarchy to switch to a new master (that master being itself a slave of a higher-up master), leading to the need for a complex set-of-all-executed-transactions concept. Suppose we instead make the decision on the _master_ on how to split up the replication stream for parallel apply. Instead of a single server-id for the master, we can have multiple - let us call them "Source ID". On the _master_, we will decide that two transactions in different databases are independent, and assign them different Source ID. The slave then receives multiple replication streams, and is free to execute each stream in parallel - or not, at the discretion of the DBA. Transactions _within_ a single stream are always executed in-order, so the slave has no need to remember any sets of all transactions ever executed - all it needs to remember is the sequence number within each stream it has seen - ie. last sequence number for each Source ID. This is a simple and obvious generalisation of the old concept of current binlog position. Downstream slaves see the exact same binlog sequences, each with transactions in the same order (though sequences may be interleaved differently) - so changing masters is simpler, all servers in the hierarchy have the same view. So to implement the same functionality as MySQL 5.6 parallel replication, all we need is on the master to generate Source ID as a hash of the database name (just as MySQL 5.6 assigns transactions to slave threads based on such hash of the database name). And implement a facility on the slave such that transactions with different Source ID can be executed in parallel by multiple slave threads. And now, since we decide on the master, we can provide much more flexibility for the application. We can have _some_ databases db1,db2 be executed in parallel on the slave - while other databases db3,db4 are considered dependent, and not modified out-of-order on the slave. Or the application can declare that two transactions are independent within the same database or even within the same table; for example log record inserts into a table can often be allowed to happen in different order on slaves. Or the DBA can assign a special Source ID to a long-runnign ALTER TABLE, and such ALTER TABLE can then execute independently on slaves to not stall the normal load. All we need is to provide a simple facility (eg. session variable) that applications/users can use to specify Source ID. And then the next step is multi-source replication, which is the much-request feature that one slave can replicate from two or more different masters at the same time. The different masters will just supply different Source IDs, so apart from the need to maintain multiple TCP connections, one to each master, from the rest of the code's point of view multi-source replication is no different, it is just more independent replication streams. So my suggestion is: rather than struggle to try and merge and support the MySQL 5.6 Global Transaction ID and parallel slave, let us instead implement something like the above idea, with multiple Source IDs on the master, and parallel execution of distinct replication streams on the slave. I believe this will be no more work, and the end result will be both simpler and more flexible. Thanks, - Kristian.

4 9

[Maria-developers] Cost analysis: checking if join optimization costs match the reality.
by Sergei Petrunia 09 Aug '12

09 Aug '12

Hi! I've ran this experiment: I took the scalar subquery out of the query and then tried to compare optimizer costs with execution times. == The query == explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') ) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ |id|select_type|table |type ||key |key_len|ref |rows|Extra | +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey|5 |nation.n_nationkey | 207| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 50|Using index | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey| 1|Using where; FirstMatch(supplier) | +--+-----------+--------+------++-------------+-------+-------------------+----+---------------------------------------------------------+ +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|Extra | +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey|5 |nation.n_nationkey| 207| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| | | 2|MATERIALIZED|part |range ||p_name |58 |NULL |2387|Using where; Using index | | 2|MATERIALIZED|partsupp |ref ||i_ps_partkey |4 |part.p_partkey | 2|Using index | +--+------------+-----------+------++-------------+-------+------------------+----+---------------------------------------------------------+ === Execution times === FirstMatch: 0.20 - 0.30 sec. Materialization: 0.20 sec - 0.30 sec run times for both fluctuate between 0.20 and 0.30 sec, FirstMatch seems to be somewhat slower (dunno if the difference is statistically meaningful) == Optimizer costs === FirstMatch: 11029.4 Materialization: 3594.5 === Read record counters === (source data is plan counters provided below) FirstMatch: 43185 Materialization: 12354 + 9K temptable writes + 400 reads === Conclusions === - optimizer thinks FirstMatch is 3x more expensive. - In reality it is *somewhat* expensive. - Record counters say FirstMatch is 3.5x .. 1.9x more expensive, depending on how we treat temptable writes/reads ==> Overall, I see no big discrepancies. ==> Also, I have also discovered that JOIN::get_prefix_cost_and_fanout() forgets to add record_count/TIME_FOR_COMPARE, which causes (slightly) different cost for SJ-Materialization. (I have noted the difference in some previous email) === Firstmatch plan counters === MariaDB [dbt3sf1]> show table_statistics; +--------------+------------+-----------+--------------+-------------------------+ | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +--------------+------------+-----------+--------------+-------------------------+ | dbt3sf1 | nation | 1 | 0 | 0 | | dbt3sf1 | part | 21386 | 0 | 0 | | dbt3sf1 | partsupp | 21386 | 0 | 0 | | dbt3sf1 | supplier | 412 | 0 | 0 | +--------------+------------+-----------+--------------+-------------------------+ 4 rows in set (0.00 sec) MariaDB [dbt3sf1]> show index_statistics; +--------------+------------+---------------+-----------+ | Table_schema | Table_name | Index_name | Rows_read | +--------------+------------+---------------+-----------+ | dbt3sf1 | nation | n_name | 1 | | dbt3sf1 | part | PRIMARY | 21386 | | dbt3sf1 | supplier | i_s_nationkey | 412 | | dbt3sf1 | partsupp | i_ps_suppkey | 21386 | +--------------+------------+---------------+-----------+ Total= 43185 rows read === Materialization plan counters === MariaDB [dbt3sf1]> show index_statistics; +--------------+------------+---------------+-----------+ | Table_schema | Table_name | Index_name | Rows_read | +--------------+------------+---------------+-----------+ | dbt3sf1 | nation | n_name | 1 | | dbt3sf1 | part | p_name | 2389 | | dbt3sf1 | supplier | i_s_nationkey | 412 | | dbt3sf1 | partsupp | i_ps_partkey | 9552 | +--------------+------------+---------------+-----------+ 4 rows in set (0.00 sec) MariaDB [dbt3sf1]> show table_statistics; +--------------+------------+-----------+--------------+-------------------------+ | Table_schema | Table_name | Rows_read | Rows_changed | Rows_changed_x_#indexes | +--------------+------------+-----------+--------------+-------------------------+ | dbt3sf1 | nation | 1 | 0 | 0 | | dbt3sf1 | part | 2389 | 0 | 0 | | dbt3sf1 | partsupp | 9552 | 0 | 0 | | dbt3sf1 | supplier | 412 | 0 | 0 | +--------------+------------+-----------+--------------+-------------------------+ 4 rows in set (0.00 sec) Total = 12354 rows read + 9K temptable writes + 400 reads from temptable. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 1

[Maria-developers] Cost analysis: Materialization plan
by Sergei Petrunia 09 Aug '12

09 Aug '12

Debugging with optimizer_switch='materialization=on', query: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'canada' order by s_name limit 10; +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ |id |select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ | 1|PRIMARY |nation |ALL ||NULL |NULL |NULL | 25| 100.00|Using where; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey |5 |nation.n_nationkey | 180| 100.00| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| 100.00| | | 2|MATERIALIZED |part |range ||p_name |58 |NULL |2387| 100.00|Using where; Using index | | 2|MATERIALIZED |partsupp |ref ||PRIMARY |4 |part.p_partkey | 1| 100.00|Using where | | 4|DEPENDENT SUBQUERY|lineitem |ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 4| 100.00|Using where | +----+------------------+-----------+------++-------------------+-------+---------------------------------------+----+--------+--------------------------------------------+ ********* SEMI-JOIN pre-optimization ************** ===== PRE-1. part === (gdb) p position->records_read $564 = 2387 (gdb) p position->read_time $565 = 497.33246183668518 (gdb) p current_record_count / TIME_FOR_COMPARE $566 = 477.39999999999998 (gdb) p current_read_time $567 = 974.73246183668516 ===== PRE-2. partsupp === (gdb) p position->records_read $570 = 1 (gdb) p position->read_time $571 = 2390.4263969377266 (gdb) p current_record_count / TIME_FOR_COMPARE $572 = 477.39999999999998 (gdb) p current_read_time $573 = 3842.5588587744119 ## (part, partsupp) is the best plan to materialize the subquery. (gdb) p join->best_read $575 = 6229.5578587744112 (gdb) p join->record_count $576 = 2387 ## Extra expenses for semi-join (gdb) print lookup_cost $582 = 0.050000000000000003 (gdb) print write_cost $584 = 0.050000000000000003 ********* MAIN join optimization ************** ===== 1. nation === (gdb) p position->records_read $587 = 25 (gdb) p position->read_time $588 = 1 (gdb) p current_record_count / TIME_FOR_COMPARE $589 = 5 (gdb) p current_read_time $590 = 6 ===== 2. supplier === (gdb) p position->records_read $593 = 180 (gdb) p position->read_time $594 = 4525 (gdb) p current_record_count / TIME_FOR_COMPARE $595 = 900 (gdb) p current_read_time $596 = 5431 ## here it considers "part, partsupp" extension, with huge cost: ## I don't skip it: === 3.A part === (gdb) p position->records_read $599 = 2387 (gdb) p position->read_time $600 = 2237996.0782650835 (gdb) p current_record_count / TIME_FOR_COMPARE $601 = 2148300 (gdb) p current_read_time $602 = 4391727.078265084 === 4.A partsupp == (gdb) p position->records_read $605 = 1 (gdb) p position->read_time $606 = 10741500 (gdb) p current_record_count / TIME_FOR_COMPARE $607 = 2148300 (gdb) p current_read_time $608 = 17281527.078265086 > advance_sj_state > Firstmatch_picker::check_qep ### Picks first match with (gdb) print read_time $615 = 12984927.078265084 (gdb) print rec_count $616 = 4500 < Firstmatch_picker::check_qep > Sj_materialization_picker::check_qep (gdb) p prefix_cost.total_cost() $622 = 5431 # OK, same as $596 (gdb) p mat_info->materialization_cost.total_cost() $623 = 3007.1088587744121 ## This one is weird. It should be $575 + $582 + $584= 6229.6578587744098 ## I will investigate. ## The difference is not millions, but a ~3K. (gdb) print prefix_rec_count $625 = 4500 (gdb) p mat_info->lookup_cost.total_cost() $626 = 0.050000000000000003 < Sj_materialization_picker::check_qep ## Materialization is chosen as the best strategy with (gdb) print current_record_count $634 = 4500 (gdb) print current_read_time $635 = 8663.1088587744125 < advance_sj_state === 5. Finally === We hit these lines: if (join->sort_by_table && join->sort_by_table != join->positions[join->const_tables].table->table) /* We have to make a temp table */ current_read_time+= current_record_count; and get the final query plan cost of (gdb) p join->record_count $643 = 4500 (gdb) p join->best_read $644 = 13163.107858774412 #records is the same for plan in this email and in the previous. The cost is very different, however, it is 13K vs 360K. Now, when we have the breakdown of the numbers, we can evaluate what did the optimizer get wrong. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 2

[Maria-developers] Cost analysis: FirstMatch plan
by Sergei Petrunia 09 Aug '12

09 Aug '12

Hi Timour, Please find below cost analysis for the plan with FirstMatch strategy. Debugging with optimizer_switch='semijoin=off', query: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ |id |select_type |table |type ||key |key_len|ef |rows|Extra | +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ | 1|PRIMARY |nation |ALL ||NULL |NULL |NULL | 25|Using where; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 180| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 39|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1|Using where; FirstMatch(supplier) | | 4|DEPENDENT SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 4|Using where | +----+------------------+--------+------++-------------------+-------+---------------------------------------+----+--------------------------------------------+ ===== 1. nation === (gdb) p position->records_read $367 = 25 (gdb) p position->read_time $368 = 1 (gdb) p current_record_count / TIME_FOR_COMPARE $369 = 5 (gdb) p current_read_time $370 = 6 ===== 2. supplier === (gdb) p position->records_read $377 = 180 (gdb) p position->read_time $378 = 4525 (gdb) p current_record_count / TIME_FOR_COMPARE $379 = 900 (gdb) p current_read_time $380 = 5431 ## here it considers "part, partsupp" extension, with cost ## 12,984,927. I skip that, because that's not the optimal plan ===== 3. partsupp === (gdb) p position->records_read $411 = 39 (gdb) p position->read_time $412 = 180000 (gdb) p current_record_count / TIME_FOR_COMPARE $413 = 35100 (gdb) p current_read_time $414 = 220531 ===== 4. part === (gdb) p position->records_read $421 = 1 (gdb) p position->read_time $422 = 175500 (gdb) p current_record_count / TIME_FOR_COMPARE $423 = 35100 (gdb) p current_read_time $424 = 431131 > advance_sj_state() > Firstmatch_picker::check_qep > optimize_wo_join_buffering ### It doesn't make any best_access_path() calls here, because no table is ### using join buffering. ### It forgets to add (current_record_count / TIME_FOR_COMPARE), and ends ### up with a slightly lower cost of (gdb) print cost $425 = 360931 > optimize_wo_join_buffering > Firstmatch_picker::check_qep ## FirstMatch is chosen as the way we will use to eliminate duplicates.. ## At the end of advance_sj_state, we have: (gdb) p *current_read_time $426 = 360931 (gdb) p *current_record_count $427 = 4500 > advance_sj_state(). And this is what is chosen as the final query plan. -- BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 2

[Maria-developers] Cost analysis: check if optimizer is able to produce a correct E(#scalar_subquery executions)
by Sergei Petrunia 09 Aug '12

09 Aug '12

Let's see if the optimizer is theoretically able to attach the subquery to the right table for the FirstMatch plan: On Thu, Aug 09, 2012 at 12:39:00PM +0400, Sergei Petrunia wrote: > == Modified query == ... > > == Benchmarking == > > Number of times scalar-subquery was executed: > Materialization: 9552 times (same as before) > First-Match: 299 times (A LOT LESS!) > > Execution times: > Materialization: 5.30 sec (same as before) > First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats > materialization) > Let's check whether the optimizer has sufficient input data to conclude that moving the scalar-subquery from table partsupp to table part is advantageous. The data about extra selectivity is basically tab->quick_condition_rows. Let's take the EXPLAIN and its analysis from the "Cost analysis: optimizer statistics vs real dataset properties" email. Relevant lines will start with 'NOTE>'. +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1| 100.00|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 251| 100.00| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 34| 100.00|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1| 100.00|Using where; FirstMatch(supplier) | | 4|DEP. SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3| 100.00|Using where | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ 5 rows in set, 3 warnings (0.01 sec) === nation: === like above, 1 row. OK. NOTE> - "using where" won't filter anything out. === supplier: === see above, 251 rows, ~ok 412 real... NOTE> - "using where" won't filter anything out. === partsupp: === - 800K rows and 10K distinct ps_suppkey, which gives rec_per_key=80 (EXPLAIN shows 34) - as for data that we will hit: select count(*) from nation, supplier, partsupp where s_suppkey=ps_suppkey and s_nationkey=n_nationkey and n_name='canada'; gives 32690 rows. 32690 / 412 = 80, matches rec_per_key. NOTE> "using where" won't filter anything out (NOT TAKING the scalar-subquery into account) === part: === - eq_ref, so exactly 1 match. We know DBT-3 dataset is such that it always has one. NOTE> "using where" is "p_name like forest"! it will filter stuff out! I haven't checked, but I suppose that condition "p_name like forest%'(*) is not correlated with the table access condition, 'p_partkey=partsupp_ps_partkey'. The email 'Cost analysis: Materialization plan' shows a range access on (*), with the estimate rows=2378. Total number of rows in table part is is 200K, InnoDB's estimate is 200999. This gives selectivity of 0.0118. === How many times scalar-subquery will be evaluated === Let's take estimate numbers: 1 * 251 * 34 * 1 * 0.0118 = 100.7012 evaluations. If the estimates were perfect: 1 * 400 * 80 * 1 * 0.118 = 377.6 evaluations As mentioned at the top of this email, in reality subquery is evaluated 299 times. === Conclusion#1 === quick_condition estimates provide information that will allow the optimizer to make a correct conclusion about # of times that scalar-subquery will be evaluated after table part. What remains to be checked: check that our cost formulas will make the plan of { FirstMatch, scalar-subquery attached to table part} cheaper than { FirstMatch, scalar-subquery attached to table partsupp} and cheaper than the SJ-Materialization plan. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Cost analysis: checking how fast cost-based predicate pushdown can be
by Sergei Petrunia 09 Aug '12

09 Aug '12

... > ********* Some benchmarking ************* > Execution times: > - FirstMatch is ~15 sec > - Materialization: ~5 sec. > > With scalar-subquery predicate replaced with TRUE: > - Materialization: ~0.20 sec. > - FirstMatch: 0.19 sec. > > Number of times the scalar subquery was executed: > - DEP. SUBQUERY: ... 24055 times > > - MATERIALIZATION: 9555 times > > If we take the cost of scalar-subquery predicate evaluation into account, > the difference should be around: > > (24055 - 9555) * 5 = 72K > > which is sufficient. Then, we've tried to imitate the effects of "cost-based predicate pushdown" (mdev-83), by making the scalar subquery predicate depend on both tables part and partsupp. In order to do that, we've also had to convert the subquery (select p_partkey from part where p_name like 'forest%') into join. This change doesn't affect the optimizer because this subquery would be converted into an inner join anyway. == Modified query == explain select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp, part where ps_partkey = p_partkey and p_name like 'forest%' and ps_availqty + p_retailprice*0 > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'CANADA' order by s_name limit 10; == Benchmarking == Number of times scalar-subquery was executed: Materialization: 9552 times (same as before) First-Match: 299 times (A LOT LESS!) Execution times: Materialization: 5.30 sec (same as before) First-Match: 1.72 sec (a lot less, used to be 15 sec! Now it beats materialization) BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] what compiler should i use when building mariadb?
by Rich Prohaska 09 Aug '12

09 Aug '12

hello, we have been using gcc 4.4 to build mysql and mariadb successfully. however, when we tried to use gcc 4.7.1 to build mariadb-5.5.25, we hit this problem: /home/tokubuild/build-tokudb-46462/mariadb-5.5.25-tokudb-46462-linux-x86_64-build/mariadb-5.5.25-tokudb-46462-src/mysys/my_context.c: Assembler messages: /home/tokubuild/build-tokudb-46462/mariadb-5.5.25-tokudb-46462-linux-x86_64-build/mariadb-5.5.25-tokudb-46462-src/mysys/my_context.c:207: Error: CFI instruction used without previous .cfi_startproc what compiler do you use to build mariadb? thanks rich prohaska

4 9

[Maria-developers] Cost analysis: optimizer statistics vs real dataset properties
by Sergei Petrunia 08 Aug '12

08 Aug '12

So, we have done: alter table name add index(n_name); and now we have: explain extended select sql_calc_found_rows s_name, s_address from nation straight_join supplier where s_suppkey in (select ps_suppkey from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%') and ps_availqty > (select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date('1994-01-01') and l_shipdate < date('1994-01-01') + interval '1' year )) and s_nationkey = n_nationkey and n_name = 'canada' order by s_name limit 10; +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|Extra | +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier |ref ||i_s_nationkey |5 |nation.n_nationkey | 251| | | 1|PRIMARY |<subquery2>|eq_ref||distinct_key |4 |func | 1| | | 2|MATERIALIZED |part |range ||p_name |58 |NULL |2387|Using where; Using index | | 2|MATERIALIZED |partsupp |ref ||PRIMARY |4 |part.p_partkey | 1|Using where | | 4|DEPENDENT SUBQUERY|lineitem |ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3|Using where | +--+------------------+-----------+------++-------------------+-------+---------------------------------------+----+---------------------------------------------------------+ === nation: === 1 row scanned, for real OK. - "using where" doesn't filter anything out. === supplier === - dataset has 412 rows for CANADA - rec_per_key: 10L suppliers / 25 countries=400 === <subquery2> == - eq_ref, we know that there's always match - "using where" won't filter anything out. === part === - dataset/where match 2378 rows for (p_name like 'forest%') OK. - "using where" won't filter anything out. === partsupp === - rec_per_key: partsupp has 800K rows and 200K distinct ps_partkey values, which gives rec_per_key=4 (while EXPLAIN shows 1!) - select count(*) from partsupp where ps_partkey in (select p_partkey from part where p_name like 'forest%'); gives 9552 rows. (rec_per_key=4) * 2378 = 9512 , close to 9552. - "using where" won't filter anything out (NOT TAKING scalar-subquery into account) +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ |id|select_type |table |type ||key |key_len|ref |rows|filtered|Extra | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ | 1|PRIMARY |nation |ref ||n_name |26 |const | 1| 100.00|Using where; Using index; Using temporary; Using filesort| | 1|PRIMARY |supplier|ref ||i_s_nationkey |5 |nation.n_nationkey | 251| 100.00| | | 1|PRIMARY |partsupp|ref ||i_ps_suppkey |4 |supplier.s_suppkey | 34| 100.00|Using where | | 1|PRIMARY |part |eq_ref||PRIMARY |4 |partsupp.ps_partkey | 1| 100.00|Using where; FirstMatch(supplier) | | 4|DEP. SUBQUERY|lineitem|ref ||i_l_suppkey_partkey|10 |partsupp.ps_partkey,partsupp.ps_suppkey| 3| 100.00|Using where | +--+-------------+--------+------++-------------------+-------+---------------------------------------+----+--------+---------------------------------------------------------+ 5 rows in set, 3 warnings (0.01 sec) === nation: === like above, 1 row. OK. - "using where" won't filter anything out. === supplier: === see above, 251 rows, ~ok 412 real... === partsupp: === - 800K rows and 10K distinct ps_suppkey, which gives rec_per_key=80 (EXPLAIN shows 34) - as for data that we will hit: select count(*) from nation, supplier, partsupp where s_suppkey=ps_suppkey and s_nationkey=n_nationkey and n_name='canada'; gives 32690 rows. 32690 / 412 = 80, matches rec_per_key. - "using where" won't filter anything out (NOT TAKING scalar-subquery into account) === part: === - eq_ref, so exactly 1 match. We know DBT-3 dataset is such that it always has one. - "using where" is "p_name like forest"! it will filter stuff out! ^^^^^^^^ but does it matter.. subquery is attached to ps? ********* Some benchmarking ************* Execution times: - FirstMatch is ~15 sec - Materialization: ~5 sec. With scalar-subquery predicate replaced with TRUE: - Materialization: ~0.20 sec. - FirstMatch: 0.19 sec. Number of times the scalar subquery was executed: - DEP. SUBQUERY: ... 24055 times - MATERIALIZATION: 9555 times If we take the cost of scalar-subquery predicate evaluation into account, the difference should be around: (24055 - 9555) * 5 = 72K which is sufficient. The problem is, Materialization executes the scalar-subquery fewer times than FirstMatch. And you expect that taking into account scalar-subquery execution cost will cause Materialization not to be used?? BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Cost analysis: comparison of the two approaches
by Sergei Petrunia 08 Aug '12

08 Aug '12

So, we're comparing two plans that were mentioned earlier. FirstMatch plan has: (gdb) p *current_read_time $426 = 360931 (gdb) p *current_record_count $427 = 4500 which is much more greater than the SJ-Materialization plan, which has: (gdb) print current_record_count $634 = 4500 (gdb) print current_read_time $635 = 8663.1088587744125 Let's check whether the difference can be overcame by adding the cost of the scalar-subquery predicate. Cost of one execution of scalar subquery predicate is 4.5 ~= 5. SJ-Materialization plan will execute the subquery predicate 2378 times: (gdb) p join->best_read $575 = 6229.5578587744112 (gdb) p join->record_count $576 = 2387 which gives added cost of 2378 * 5 = 11890. 8663 + 11890 is still much less than 360931. Hence, just taking scalar-subquery cost into account is not sufficient for query plan to be changed. ps. psergey's opinion: too much unaccounted-for WHERE clauses. to be elaborated further... BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Review request: SHOW EXPLAIN
by Sergei Petrunia 08 Aug '12

08 Aug '12

Hi Sergei, Please find attached the combined SHOW EXPLAIN patch. It is ready for review of the concurrency part. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

3 12