Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6032

Slow DELETE Query (> 7000 seconds) on small table (60k rows)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Incomplete
    • Affects Version/s: 10.0.11
    • Fix Version/s: 10.0.11
    • Component/s: None
    • Labels:
    • Environment:

      Description

      Two tables:

      aggregated_data.xx_aggregated_ad_revenue (~1150 rows) and xx_tracker.ad_revenue (~60,000 rows), running the following delete query as a stored procedure on our old machine with Percona Server (GPL), Release rel29.0, Revision 315 (5.5.27-29.0-log) takes seconds up to 1-2 minutes, same query on MariaDB 10 takes more than 7000 seconds.

      -- DB: aggregated_data
      -- Command: Query
      -- Time: 7103
      -- State: updating
      DELETE FROM aggregated_data.xx_aggregated_ad_revenue
      			WHERE network = 'networkA' AND booking_date IN (
      				SELECT CONCAT(SUBSTRING(a.booking_date,-4), SUBSTRING(a.booking_date,3,2), SUBSTRING(a.booking_date,1,2)) AS booking_date
      				FROM xx_tracker.ad_revenue a
      				JOIN
      				(
      					SELECT CONCAT(SUBSTRING(booking_date,-4), SUBSTRING(booking_date,3,2), SUBSTRING(booking_date,1,2)) AS booking_date, MAX(created_ts) AS max_ts
      					FROM xx_tracker.ad_revenue b
      					WHERE network = 'networkA'
      					GROUP BY b.booking_date
      				) AS i
      				ON CONCAT(SUBSTRING(a.booking_date,-4), SUBSTRING(a.booking_date,3,2), SUBSTRING(a.booking_date,1,2)) = i.booking_date AND i.max_ts = a.created_ts
      				WHERE a.created_ts >= NOW() - INTERVAL 1 DAY
      			)

      Even though the same indices exist on Percona and MariaDB (tables have been created by dumping the definitions are exactly the same) - MariaDB is using a key (EXPLAIN of subselect: http://puu.sh/7Zjuh.png), where Percona does not (EXPLAIN of subselect: http://puu.sh/7ZjuT.png - but Percona is way faster.
      EXPLAIN DELETE for MariaDB 10: http://puu.sh/7ZjDl.png

      Indices on aggregated_data.xx_aggregated_ad_revenue:
      1: PRIMARY id, created_date
      2: created_date
      3: network

      Indices on xx_tracker.ad_revenue:
      1. PRIMARY id, created_date
      2. created_date
      3. network

      Running the Subselect of the DELETE alone is fast (< 1-2 seconds). The query is a stored procedure which spends 95% of the time in the "Sending data" state.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            james_woods Tobias Feldhaus
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration