Re: [Maria-developers] Please look at and advise about MDEV-3801
On 10/22/2012 12:53 AM, Timour Katchaounov wrote:
Igor,
Great, the patch indeed fixes the bug both on 5.3 and 5.5, however the reduced test case I produced still takes 21 MB, and I was not successful in reducing it to a size that can be added to the regression test suite. I already spent too much in reducing the test case. Would you agree that I push your patch without a test case?
I think you still have to add the test case case (in a separate file) that is run with the --big option. Regards, Igor.
Timour
On 20.10.2012 08:56, Igor Babaev wrote:
On 10/19/2012 12:43 PM, Timour Katchaounov wrote:
Hi Igor,
Could you please look at this bug https://mariadb.atlassian.net/browse/MDEV-3801, and read my last comment. The bug may be (just a guess ATM) related to index creation on the temporary table created for the derived table. Since this is your code, I'd like you to tell me if you have seen anything similar before, or if you have any hints.
If not, I will continue with the bug and will trace the cause for the wrong key information.
Thanks, Timour
Timour,
The following patch fixes this problem
Regards, Igor.
=== modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() JOIN_TAB *tab; for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) {
TABLE *table=tab->table;
MariaDB [test]> set @@tmp_table_size=16384; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test
Query OK, 0 rows affected (5.48 sec)
MariaDB [test]> set @@tmp_table_size=16384; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8488 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 1 | PRIMARY | t10 | ref | PRIMARY,full_id | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 8488 | | | 3 | DERIVED | t4 | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary | | 3 | DERIVED | t3 | ref | PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const | 8488 | | | 3 | DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.deal_id | 1 | Using index | | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | 1 | Using index; Distinct | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
8 rows in set (4.22 sec)
MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +---------+ | deal_id | +---------+ | 30 | | 82 | | 83 | | 108 | | 155 | | 156 | | 162 | | 184 | | 186 | | 195 | +---------+ 10 rows in set (10.62 sec)
On 10/22/2012 07:54 AM, Igor Babaev wrote:
On 10/22/2012 12:53 AM, Timour Katchaounov wrote:
Igor,
Great, the patch indeed fixes the bug both on 5.3 and 5.5, however the reduced test case I produced still takes 21 MB, and I was not successful in reducing it to a size that can be added to the regression test suite. I already spent too much in reducing the test case. Would you agree that I push your patch without a test case?
I think you still have to add the test case case (in a separate file) that is run with the --big option.
I've changed my mind: probably it's not a good idea. You can't reproduce it it with a much smaller test case because the min value for tmp_table_size=16384 is too big. I would recommend to add a long dummy field that would make the size of the row in the derived field long enough (~1L long) Regards, Igor.
Regards, Igor.
Timour
On 20.10.2012 08:56, Igor Babaev wrote:
On 10/19/2012 12:43 PM, Timour Katchaounov wrote:
Hi Igor,
Could you please look at this bug https://mariadb.atlassian.net/browse/MDEV-3801, and read my last comment. The bug may be (just a guess ATM) related to index creation on the temporary table created for the derived table. Since this is your code, I'd like you to tell me if you have seen anything similar before, or if you have any hints.
If not, I will continue with the bug and will trace the cause for the wrong key information.
Thanks, Timour
Timour,
The following patch fixes this problem
Regards, Igor.
=== modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2012-10-10 05:21:22 +0000 +++ sql/sql_select.cc 2012-10-20 05:49:13 +0000 @@ -8981,7 +8981,7 @@ void JOIN::drop_unused_derived_keys() JOIN_TAB *tab; for (tab= first_linear_tab(this, WITHOUT_CONST_TABLES); tab; - tab= next_linear_tab(this, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(this, tab, WITH_BUSH_ROOTS)) {
TABLE *table=tab->table;
MariaDB [test]> set @@tmp_table_size=16384; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test
Query OK, 0 rows affected (5.48 sec)
MariaDB [test]> set @@tmp_table_size=16384; Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> EXPLAIN SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 8488 | | | 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 1 | PRIMARY | t10 | ref | PRIMARY,full_id | PRIMARY | 4 | tU.deal_id | 1 | Using index | | 2 | MATERIALIZED | <derived3> | ALL | NULL | NULL | NULL | NULL | 8488 | | | 3 | DERIVED | t4 | system | PRIMARY | NULL | NULL | NULL | 1 | Using temporary | | 3 | DERIVED | t3 | ref | PRIMARY,feid,exchange,deal_feid,feid_date | exchange | 3 | const | 8488 | | | 3 | DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t3.deal_id | 1 | Using index | | 3 | DERIVED | t2 | eq_ref | PRIMARY,full_id | PRIMARY | 8 | test.t3.deal_id,test.t3.feid | 1 | Using index; Distinct | +----+--------------+-------------+--------+-------------------------------------------+----------+---------+------------------------------+------+-----------------------+
8 rows in set (4.22 sec)
MariaDB [test]> SELECT t1.deal_id FROM table_e AS t1 JOIN table_d AS t10 ON (t1.deal_id = t10.deal_id) WHERE t1.deal_id IN (SELECT * FROM (SELECT DISTINCT t1.deal_id FROM table_e AS t1 JOIN table_d AS t2 ON (t1.deal_id = t2.deal_id) JOIN table_c_s AS t3 ON (t2.deal_id = t3.deal_id) AND (t2.feid = t3.feid) JOIN table_f AS t4 ON t3.exchange = t4.exchange) tU) limit 10; +---------+ | deal_id | +---------+ | 30 | | 82 | | 83 | | 108 | | 155 | | 156 | | 162 | | 184 | | 186 | | 195 | +---------+ 10 rows in set (10.62 sec)
participants (1)
-
Igor Babaev