Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Incomplete
-
10.0.11
-
None
-
FreeBSD 9.2-RELEASE-p3 amd64
CPU: Intel(R) Xeon(R) CPU E5-4603 0 @ 2.00GHz (2000.05-MHz K8-class CPU)
FreeBSD/SMP: Multiprocessor System Detected: 32 CPUs
512 GiB Memory
ZFS Filesystem:
8 x SSDs for tank
2 x separate SSDs for ZIL and L2ARC
zpool config:
NAME STATE READ WRITE CKSUM
tank ONLINE 0 0 0
mirror-0 ONLINE 0 0 0
gpt/disk0 ONLINE 0 0 0
gpt/disk1 ONLINE 0 0 0
mirror-1 ONLINE 0 0 0
gpt/disk2 ONLINE 0 0 0
gpt/disk3 ONLINE 0 0 0
mirror-2 ONLINE 0 0 0
gpt/disk4 ONLINE 0 0 0
gpt/disk5 ONLINE 0 0 0
mirror-3 ONLINE 0 0 0
gpt/disk6 ONLINE 0 0 0
gpt/disk7 ONLINE 0 0 0
logs
mirror-4 ONLINE 0 0 0
gpt/zil0 ONLINE 0 0 0
gpt/zil1 ONLINE 0 0 0
cache
gpt/l2arc0 ONLINE 0 0 0
gpt/l2arc1 ONLINE 0 0 0FreeBSD 9.2-RELEASE-p3 amd64 CPU: Intel(R) Xeon(R) CPU E5-4603 0 @ 2.00GHz (2000.05-MHz K8-class CPU) FreeBSD/SMP: Multiprocessor System Detected: 32 CPUs 512 GiB Memory ZFS Filesystem: 8 x SSDs for tank 2 x separate SSDs for ZIL and L2ARC zpool config: NAME STATE READ WRITE CKSUM tank ONLINE 0 0 0 mirror-0 ONLINE 0 0 0 gpt/disk0 ONLINE 0 0 0 gpt/disk1 ONLINE 0 0 0 mirror-1 ONLINE 0 0 0 gpt/disk2 ONLINE 0 0 0 gpt/disk3 ONLINE 0 0 0 mirror-2 ONLINE 0 0 0 gpt/disk4 ONLINE 0 0 0 gpt/disk5 ONLINE 0 0 0 mirror-3 ONLINE 0 0 0 gpt/disk6 ONLINE 0 0 0 gpt/disk7 ONLINE 0 0 0 logs mirror-4 ONLINE 0 0 0 gpt/zil0 ONLINE 0 0 0 gpt/zil1 ONLINE 0 0 0 cache gpt/l2arc0 ONLINE 0 0 0 gpt/l2arc1 ONLINE 0 0 0
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.