Bug #70086 | Degraded performance for index_merge updates | ||
---|---|---|---|
Submitted: | 19 Aug 2013 16:06 | Modified: | 2 Nov 2013 8:52 |
Reporter: | trevor price | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
Version: | 5.6.13-log | OS: | Linux (2.6.16.33-xenU x86_64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | index_merge update performance |
[19 Aug 2013 16:06]
trevor price
[19 Aug 2013 16:17]
trevor price
Some additional information. 1) According the show status "Handler" variables, the update and select behave the same, so its seems the optimizer is not the problem. 2) Using the performance schema, the update spends nearly all its time "fetching". *************************** 4. row *************************** event_id: 17572601 rows_examined: 4 event_name: stage/sql/updating event_name: wait/io/table/sql/handler operation: fetch sum(waitl.timer_wait): 19953568 not sure if row locking happens during this operation, but perhaps the query is slower because of row-locking overhead.
[19 Aug 2013 16:34]
trevor price
so, dropping of of the indicies being merged greatly speeds performance mysql> explain update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: power_up_value type: range possible_keys: track_id_idx key: track_id_idx key_len: 8 ref: const rows: 949 Extra: Using where 1 row in set (0.00 sec) mysql> update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 the station_id_ix index was dropped, which was part of the index_merge.
[20 Aug 2013 21:19]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior with test data. Did you try to run ANALYZE TABLE and OPTIMIZE TABLE on problematic table?
[20 Aug 2013 22:06]
trevor price
yes I ran optimize table and analyze table. This is a single table query, so I can upload the entire table, it's around 600MB.
[20 Aug 2013 23:22]
trevor price
Here is a more concise rendering of the problem. ---------------------- mysql> optimize table power_up_value ; +----------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------+----------+----------+-------------------------------------------------------------------+ | jelli.power_up_value | optimize | note | Table does not support optimize, doing recreate + analyze instead | | jelli.power_up_value | optimize | status | OK | +----------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (1 min 31.39 sec) mysql> analyze table power_up_value ; +----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+---------+----------+----------+ | jelli.power_up_value | analyze | status | OK | +----------------------+---------+----------+----------+ 1 row in set (0.01 sec) mysql> alter table power_up_value add index station_id_idx (station_id) ; Query OK, 0 rows affected (14.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: power_up_value type: index_merge possible_keys: track_id_idx,station_id_idx key: track_id_idx,station_id_idx key_len: 8,8 ref: NULL rows: 72 Extra: Using intersect(track_id_idx,station_id_idx); Using where 1 row in set (0.00 sec) mysql> update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G Query OK, 1 row affected (0.37 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G Query OK, 0 rows affected (0.35 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> alter table power_up_value drop index station_id_idx ; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: power_up_value type: range possible_keys: track_id_idx key: track_id_idx key_len: 8 ref: const rows: 961 Extra: Using where 1 row in set (0.00 sec) mysql> update jelli.power_up_value set value=0 where active<>0 and track_id=76821 and station_id=3519 \G Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ------------------------
[5 Sep 2013 9:05]
Jørgen Løland
Hi Trevor, If you can upload a repeatable test case from your data it would come very handy for us. If you're able to reduce the test case down a bit from 600MB it would be even better - most likely you don't need all the columns and rows to make a test case. I'll investigate the reason once I get a repro. Thanks, Jørgen
[6 Sep 2013 15:48]
trevor price
a data file has been ftp'd to ftp.oracle.com. This file is bug-data-70086.zip, and the instructions to replicate the issue are in the README.
[1 Oct 2013 11:02]
Jørgen Løland
Hi Trevor, Due to unexpected issues, your uploaded repro has been deleted from the ftp server. I would really like to take a look at the repro, but I'm afraid I didn't get hold of it while it was available. Can you please upload it one more time? We're terribly sorry for the inconvenience. Regards, Jørgen
[3 Nov 2013 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".