[MDEV-30167] Huge slow down in query performance. ENGINE=SPHINX Created: 2022-12-06  Updated: 2023-05-15

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - SphinxSE
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Dave Minogue Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu Jammy

Issue Links:
PartOf
is part of MDEV-30218 Incorrect optimization for rowid_filt... Closed

 Description   

Query using SphinxSE.
When using 10.10.1 or 10.9.2 and earlier.
The queries are super quick.
30 rows in set (1 min 32.022 sec)

After upgrading 10.10.2, and 10.9.3 the same query
30 rows in set (0.125 sec)

When running explain on the query type=eq_ref on the quick version.
and eq|filter on the slow one.

Disabling eq|filter with
set @@optimizer_switch="rowid_filter=off";
Resolved the issue.

 Comments   
Comment by Dave Minogue [ 2022-12-06 ] 

CREATE TABLE `sphinxmedia` (
 
  `id` bigint(10) unsigned NOT NULL,
 
  `weight` int(11) NOT NULL,
 
  `query` text NOT NULL,
 
  `_sph_geodist` float DEFAULT NULL,
 
  KEY `query` (`query`(768))
 
) ENGINE=SPHINX DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='sphinx://172.30.3.35:9312'
 
 
 
CREATE TABLE `data_mediagallery` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `status` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `contenttype` char(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `filename` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `injector` char(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `hits` int(11) DEFAULT 0,
 
  `message` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
 
  `date` datetime DEFAULT NULL,
 
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
 
  `tags` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
 
  `metadata` mediumblob DEFAULT NULL,
 
  `location` char(8) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `uid` int(11) unsigned NOT NULL DEFAULT 0,
 
  `filesize` bigint(20) NOT NULL DEFAULT 0,
 
  `upload` datetime DEFAULT NULL,
 
  `privacy` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `width` int(10) unsigned NOT NULL DEFAULT 0,
 
  `height` int(10) unsigned NOT NULL DEFAULT 0,
 
  `offensive` int(10) unsigned NOT NULL DEFAULT 0,
 
  `sourcelocation` varchar(40) DEFAULT NULL,
 
  `miscsort` int(11) DEFAULT 0,
 
  `extension` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `filetype` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `conversiontime` float NOT NULL DEFAULT 0,
 
  `converttime` datetime DEFAULT NULL,
 
  `sender` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `vhost` int(10) unsigned NOT NULL DEFAULT 0,
 
  `channel` int(10) unsigned NOT NULL DEFAULT 0,
 
  `rotation` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `votecount` int(10) unsigned NOT NULL DEFAULT 0,
 
  `url` varchar(150) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `geo_latitude` double DEFAULT NULL,
 
  `geo_longitude` double DEFAULT NULL,
 
  `length` decimal(8,2) DEFAULT 0.00,
 
  `parentid` int(11) NOT NULL DEFAULT 0,
 
  `language` char(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `author` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `context` tinyint(3) unsigned NOT NULL DEFAULT 0,
 
  `externalid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
 
  `originalsaved` tinyint(1) DEFAULT 1,
 
  `hidden` tinyint(4) NOT NULL DEFAULT 0,
 
  `commentcount` int(11) NOT NULL DEFAULT 0,
 
  `approvedcomments` int(11) NOT NULL DEFAULT 0,
 
  `notdeniedcomments` int(11) NOT NULL DEFAULT 0,
 
  `lastupdatetime` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `channelleft` int(10) unsigned NOT NULL DEFAULT 0,
 
  `originalLocation` char(8) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '',
 
  `shares` int(11) DEFAULT 0,
 
  `startdate` datetime DEFAULT NULL,
 
  `enddate` datetime DEFAULT NULL,
 
  `children` int(11) NOT NULL DEFAULT 0,
 
  `position` tinyint(4) DEFAULT 0,
 
  `trendrank` int(11) DEFAULT 0,
 
  PRIMARY KEY (`id`),
 
  KEY `upload` (`upload`),
 
  KEY `vhostupload` (`vhost`,`upload`),
 
  KEY `vhostmodstatus` (`vhost`,`status`,`moderationstatus`,`uid`),
 
  KEY `complexfiletype` (`vhost`,`status`,`moderationstatus`,`filetype`,`channel`),
 
  KEY `vhostcontext` (`vhost`,`moderationstatus`,`context`,`parentid`,`status`,`filetype`),
 
  KEY `externalid` (`externalid`),
 
  KEY `externalcomments` (`vhost`,`moderationstatus`,`filetype`,`status`,`context`,`hidden`,`notdeniedcomments`),
 
  KEY `vhostchannel` (`vhost`,`status`,`moderationstatus`,`context`,`channelleft`),
 
  KEY `vhostparent` (`vhost`,`parentid`),
 
  KEY `uploadfiletype` (`vhost`,`uid`,`status`,`filetype`),
 
  KEY `twnusermedia` (`context`,`filetype`,`hidden`,`moderationstatus`,`uid`,`vhost`,`status`),
 
  KEY `mfdefault` (`vhost`,`status`,`moderationstatus`,`parentid`),
 
  KEY `twncomments` (`parentid`,`vhost`,`upload`,`hidden`,`status`),
 
  KEY `twnstatusfiletypeuid` (`uid`,`hidden`,`context`,`filetype`,`moderationstatus`,`status`,`vhost`,`upload`),
 
  KEY `uidhcmsvu` (`uid`,`hidden`,`context`,`moderationstatus`,`status`,`vhost`,`upload`),
 
  KEY `vhoststartdate` (`vhost`,`startdate`),
 
  KEY `vhostmodupload` (`vhost`,`moderationstatus`,`upload`),
 
  KEY `vhostlastupdate` (`vhost`,`lastupdatetime`)
 
) ENGINE=InnoDB AUTO_INCREMENT=36421631 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT 
 
 
 
 
SELECT  `data_mediagallery`.`channel` AS `channel`, `data_mediagallery`.`commentcount` AS commentcount, `data_mediagallery`.`filetype` AS `filetype`, `data_mediagallery`.`hits` AS `hits`, `data_mediagallery`.`id` AS `id`, `data_mediagallery`.`metadata` AS `metadata`, `data_mediagallery`.`title` AS `title`, `data_mediagallery`.`vhost` AS `vhost`, `data_mediagallery`.`votecount` AS `votecount` FROM data_mediagallery  INNER JOIN `usergroup_media` ON `data_mediagallery`.`id` = `usergroup_media`.`mid` AND `usergroup_media`.`vhost` = `data_mediagallery`.`vhost` AND `usergroup_media`.`gid` = '422841' INNER
 
JOIN channels ON channels.id = data_mediagallery.channel and channels.treeleft>=301 AND channels.treeright<=388 INNER JOIN sphinxmedia ON sphinxmedia.id = data_mediagallery.id  WHERE ( data_mediagallery.hidden = 0 )
 
AND (1=1) AND (data_mediagallery.context = 1) AND (data_mediagallery.filetype IN (1)) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.upload > "2022-11-22 00:00:00") AND (data_mediagallery.vhost = 123) AND (sphinxmedia.query='mode=extended2;index=realtime_media_123;;filter=moderationstatus,1;filter=vhost,123;filter=gid,422841;range=channelleft,301,388;filter=status,3;filter=context,1;filter=hidden,0;filter=filetype,1;range=upload,1669093200,2145934800;floatrange=latituderadians,0.742523007306,0.758230970574;floatrange=longituderadians,-1.42732013012,-1.41161216685;sort=extended:upload DESC,upload DESC;limit=50;offset=0;maxmatches=10000;')  ORDER BY `data_mediagallery`.`upload` DESC  LIMIT 0, 50 ;

Comment by Igor Babaev [ 2022-12-13 ]

Dave,
You have this problem after upgrading from what version to what version? From 10.9.2 to 10.9.3?

Comment by Igor Babaev [ 2022-12-13 ]

Dave,
Could you please provide the output of EXPLAIN EXTENDED for your query with:
set optimizer_switch='rowid_filter=on'
and
set optimizer_switch='rowid_filter=off'?

Comment by Igor Babaev [ 2022-12-13 ]

Dave,
Where are the definitions of the tables
`usergroup_media`,
channels
?

Comment by Dave Minogue [ 2022-12-14 ] 

 CREATE TABLE `usergroup_media` (
 
  `gid` int(11) unsigned NOT NULL,
 
  `mid` int(11) unsigned NOT NULL,
 
  `position` int(4) DEFAULT 0,
 
  `vhost` int(10) unsigned NOT NULL,
 
  `treeleft` int(10) unsigned NOT NULL DEFAULT 0,
 
  PRIMARY KEY (`gid`,`mid`),
 
  KEY `media_group` (`mid`,`gid`),
 
  KEY `vhosttree` (`vhost`,`treeleft`,`mid`),
 
  KEY `gidvhost` (`gid`,`vhost`),
 
  KEY `vmid` (`vhost`,`mid`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Comment by Dave Minogue [ 2022-12-14 ]

Igor, yes 10.9.2 to 10.9.3.
So then downgraded to 10.8 to resolve the isssue.
Then
And then again when I went to 10.10 from 10.8

Comment by Dave Minogue [ 2022-12-14 ] 

 explain extended SELECT  `data_mediagallery`.`channel` AS `channel`, `data_mediagallery`.`commentcount` AS commentcount, `data_mediagallery`.`filetype` AS `filetype`, `data_mediagallery`.`hits` AS `hits`, `data_mediagallery`.`id` AS `id`, `data_mediagallery`.`metadata` AS `metadata`, `data_mediagallery`.`title` AS `title`, `data_mediagallery`.`vhost` AS `vhost`, `data_mediagallery`.`votecount` AS `votecount` FROM data_mediagallery  INNER JOIN `usergroup_media` ON `data_mediagallery`.`id` = `usergroup_media`.`mid` AND `usergroup_media`.`vhost` = `data_mediagallery`.`vhost` AND `usergroup_media`.`gid` = '422841' INNER  JOIN channels ON channels.id = data_mediagallery.channel and channels.treeleft>=301 AND channels.treeright<=388 INNER JOIN sphinxmedia ON sphinxmedia.id = data_mediagallery.id  WHERE ( data_mediagallery.hidden = 0 )  AND (1=1) AND (data_mediagallery.context = 1) AND (data_mediagallery.filetype IN (1)) AND (data_mediagallery.hidden = 0) AND (data_mediagallery.moderationstatus = 1) AND (data_mediagallery.status IN (3)) AND (data_mediagallery.upload > "2022-11-22 00:00:00") AND (data_mediagallery.vhost = 123) AND (sphinxmedia.query='mode=extended2;index=realtime_media_123;;filter=moderationstatus,1;filter=vhost,123;filter=gid,422841;range=channelleft,301,388;filter=status,3;filter=context,1;filter=hidden,0;filter=filetype,1;range=upload,1669093200,2145934800;floatrange=latituderadians,0.742523007306,0.758230970574;floatrange=longituderadians,-1.42732013012,-1.41161216685;sort=extended:upload DESC,upload DESC;limit=50;offset=0;maxmatches=10000;')  ORDER BY `data_mediagallery`.`upload` DESC  LIMIT 0, 50;
 
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+
 
| id   | select_type | table             | type   | possible_keys                                                                                                                                                                                                          | key     | key_len | ref                                    | rows | filtered | Extra                                                              |
 
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+
 
|    1 | SIMPLE      | sphinxmedia       | ref    | query                                                                                                                                                                                                                  | query   | 3074    | const                                  | 3    |   100.00 | Using where with pushed condition; Using temporary; Using filesort |
 
|    1 | SIMPLE      | data_mediagallery | eq_ref | PRIMARY,upload,vhostupload,vhostmodstatus,complexfiletype,vhostcontext,hockeynews_custom,externalcomments,vhostchannel,vhostparent,uploadfiletype,twnusermedia,mfdefault,vhoststartdate,vhostmodupload,vhostlastupdate | PRIMARY | 4       | sabretooth2.sphinxmedia.id             | 1    |     0.00 | Using where                                                        |
 
|    1 | SIMPLE      | channels          | eq_ref | PRIMARY,idtltr                                                                                                                                                                                                         | PRIMARY | 4       | sabretooth2.data_mediagallery.channel  | 1    |   100.00 | Using where                                                        |
 
|    1 | SIMPLE      | usergroup_media   | eq_ref | PRIMARY,media_group,vhosttree,gidvhost,vmid                                                                                                                                                                            | PRIMARY | 8       | const,sabretooth2.data_mediagallery.id | 1    |    99.95 | Using where                                                        |
 
+------+-------------+-------------------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+----------------------------------------+------+----------+--------------------------------------------------------------------+

On/off are both giving the same results now on 10.10.2.

Comment by Igor Babaev [ 2022-12-14 ]

Dave,
What about the definition for table channels?
Re the slow plan with |filter : how did it look like?

Generated at Thu Feb 08 10:14:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.