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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Incomplete
    • 10.0.11
    • 10.0.11
    • None

    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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.