List:General Discussion« Previous MessageNext Message »
From:Stephen Tu Date:April 10 2012 7:58pm
Subject:forcing mysql to use batched key access (BKA) optimization for joins
View as plain text  
Hi all,

I am playing around w/ the BKA optimization found in mysql 5.6. I'm having
an issue getting the expected increase in performance with this
optimization.

Specifically, I'm trying to optimize the following query (TPC-H Q10):
select
  c_custkey,
  c_name,
  sum(l_extendedprice * (100 - l_discount)) as revenue,
  c_acctbal,
  n_name,
  c_address,
  c_phone,
  c_comment
from   CUSTOMER_INT,   ORDERS_INT,   LINEITEM_INT,   NATION_INT
where
  c_custkey = o_custkey
  and l_orderkey = o_orderkey
  and o_orderdate >= date '1994-08-01'
  and o_orderdate < date '1994-08-01' + interval '3' month
  and l_returnflag = 'R'   and c_nationkey = n_nationkey
group by   c_custkey,   c_name,   c_acctbal,   c_phone,   n_name,
c_address,   c_comment
order by   revenue desc limit 20;

When running the query, iostat reports the disk reading at about 4MB/s,
which suggests lots of random seeking.

Looking at explain, I get:

mysql> explain select   c_custkey,   c_name,   sum(l_extendedprice * (100 -
l_discount)) as revenue,   c_acctbal,   n_name,   c_address,   c_phone,
c_comment from   CUSTOMER_INT,   ORDERS_INT,   LINEITEM_INT,   NATION_INT
where   c_custkey = o_custkey   and l_orderkey = o_orderkey   and
o_orderdate >= date '1994-08-01'   and o_orderdate < date '1994-08-01' +
interval '3' month   and l_returnflag = 'R'   and c_nationkey = n_nationkey
group by   c_custkey,   c_name,   c_acctbal,   c_phone,   n_name,
c_address,   c_comment order by   revenue desc limit 20;
+----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+
| id | select_type | table        | type   | possible_keys     | key
| key_len | ref                                 | rows    |
Extra                                               |
+----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+
|  1 | SIMPLE      | CUSTOMER_INT | ALL    | PRIMARY           | NULL
| NULL    | NULL                                | 1501528 | Using
temporary; Using filesort                     |
|  1 | SIMPLE      | NATION_INT   | eq_ref | PRIMARY           | PRIMARY
| 4       | tpch-10.00.CUSTOMER_INT.C_NATIONKEY |       1 |
NULL                                                |
|  1 | SIMPLE      | ORDERS_INT   | ref    | PRIMARY,O_CUSTKEY | O_CUSTKEY
| 4       | tpch-10.00.CUSTOMER_INT.C_CUSTKEY   |       7 | Using where;
Using join buffer (Batched Key Access) |
|  1 | SIMPLE      | LINEITEM_INT | ref    | PRIMARY           | PRIMARY
| 4       | tpch-10.00.ORDERS_INT.O_ORDERKEY    |       1 | Using
where                                         |
+----+-------------+--------------+--------+-------------------+-----------+---------+-------------------------------------+---------+-----------------------------------------------------+
4 rows in set (0.00 sec)

I'm wondering why, in this particular query, mysql doesn't use the BKA to
join the LINEITEM_INT table, but uses it for the ORDERS_INT table? It seems
like it should also use BKA to batch key fetches from the LINEITEM_INT
table (this I believe is the reason for the slow performance).

Relative mysql paramters:
       --innodb-buffer-pool-size=2G
       --optimizer_switch=mrr=on
       --optimizer_switch=mrr_cost_based=off
       --optimizer_switch=batched_key_access=on
       --join_buffer_size=12M
       --read_rnd_buffer_size=12M

Hopefully, I'm not missing anything too obvious here. Thanks!

Thread
forcing mysql to use batched key access (BKA) optimization for joinsStephen Tu10 Apr
  • Re: forcing mysql to use batched key access (BKA) optimizationfor joinshsv11 Apr
    • RE: forcing mysql to use batched key access (BKA) optimization forjoinsRick James17 Apr