[MDEV-9789] MariaDB 10.1 SELECT significantly slower vs MariaDB 5.5 - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Cannot Reproduce
Affects Version/s: 5.5.44, 10.0.25, 10.1.12
Fix Version/s: N/A
Component/s: OTHER
Labels:
None
Environment:
Dell PowerEdge R730 Server
Intel(R) Xeon(R) CPU E5-2660 v3 @ 2.60GHz (2x 10 core)
96GB RAM
Red Hat Enterprise Linux 7.2 (Maipo)

Sprint:
10.2.4-2

Description

Update 2: Added benchmark items from MariaDB 10.0.25 instance.

Update 1: Added benchmark items from MySQL 5.5.25a instance.

Original Post

I am looking to upgrade a MySQL 5.5.45 server to MariaDB. I have two identical machines (info above), and I have installed one version of MariaDB on each (5.5.44-MariaDB; 10.1.12-MariaDB) via the MariaDB yum repo. Both are based off of a xtrabackup of the existing MySQL server, with neither currently replicating.

The data table that I am testing on has a large amount of weather data (423M rows x 240 columns). When I run the query below, I get a much slower query time on 10.1 than I do on 5.5. Here is a sample of benchmark times. For comparison, I have also included the MySQL 5.5.25a instance running on a five year old RHEL6 box.

10.1.12-MariaDB: 13560 rows in set (2.66 sec)
10.0.25-MariaDB: 13560 rows in set (0.12 sec)
5.5.44-MariaDB: 13560 rows in set (0.41 sec)
5.5.25a-MySQL: 13560 rows in set (0.08 sec)

Any thoughts or ideas would be greatly appreciated. I would be happy to provide more info, if needed to solve this issue.

Thanks,

John

QUERY: Please note that the times below happen to be identical for every station, but this is not always the case. This is done just for a simplified example.

SELECT station,ob,obtype,temp FROM hourly WHERE (station = '31044' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '32200' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '35750' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '36580' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '38610' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '38863' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '39348' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '51370' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '52587' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '56483' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '58120' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '58163' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '61070' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '62245' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '65530' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = '70870' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'AURO' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'BALD' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'BEAR' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'BUCK' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'BURN' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'CAST' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'CLA2' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'CLAY' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'CLIN' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'DURH' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'FLET' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'FRYI' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'GOLD' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'HAML' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'JACK' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'JEFF' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'K04V' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'K04W' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'K05U' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'K06D' AND ob >= '20160310000000' AND ob <= '20160310120000') OR (station = 'K08D' AND ob >= '20160310000000' AND ob <= '20160310120000');

5.5.44-MariaDB: EXPLAIN of above query

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	hourly	range	PRIMARY,ob	PRIMARY	20	NULL	24886	Using where

10.0.25-MariaDB: EXPLAIN of above query

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	hourly	range	PRIMARY,ob	PRIMARY	20	NULL	24905	Using where

10.1.12-MariaDB: EXPLAIN of above query

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	hourly	range	PRIMARY,ob	PRIMARY	20	NULL	25197	Using where

5.5.25a-MySQL EXPLAIN of above query

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	hourly	range	PRIMARY,ob	PRIMARY	20	NULL	25029	Using where

TABLE STRUCTURE:

Create Table: CREATE TABLE `hourly` (

  `station` varchar(10) NOT NULL DEFAULT '',

  `ob` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `obtype` char(1) NOT NULL DEFAULT '',

  `inserted` datetime DEFAULT NULL,

  `temp` float DEFAULT NULL,

  `tempflag` varchar(10) DEFAULT NULL,

  `rh` float DEFAULT NULL,

  `rhflag` varchar(10) DEFAULT NULL,

  `dew` float DEFAULT NULL,

  `dewflag` varchar(10) DEFAULT NULL,

  `ws` float DEFAULT NULL,

  `wsflag` varchar(10) DEFAULT NULL,

  `wd` float DEFAULT NULL,

  `wdflag` varchar(10) DEFAULT NULL,

  `windsd` float DEFAULT NULL,

  `windsdflag` varchar(10) DEFAULT NULL,

  `gust` float DEFAULT NULL,

  `gustflag` varchar(10) DEFAULT NULL,

  `gustdir` float DEFAULT NULL,

  `gustdirflag` varchar(10) DEFAULT NULL,

  `gusttime` float DEFAULT NULL,

  `gusttimeflag` varchar(10) DEFAULT NULL,

  `precip` float DEFAULT NULL,

  `precipflag` varchar(10) DEFAULT NULL,

  `precip6` float DEFAULT NULL,

  `precip6flag` varchar(10) DEFAULT NULL,

  `precip24` float DEFAULT NULL,

  `precip24flag` varchar(10) DEFAULT NULL,

  `precipimpact1` float DEFAULT NULL,

  `precipimpact1flag` varchar(10) DEFAULT NULL,

  `precipimpact2` float DEFAULT NULL,

  `precipimpact2flag` varchar(10) DEFAULT NULL,

  `pres` float DEFAULT NULL,

  `presflag` varchar(10) DEFAULT NULL,

  `slp` float DEFAULT NULL,

  `slpflag` varchar(10) DEFAULT NULL,

  `par` float DEFAULT NULL,

  `parflag` varchar(10) DEFAULT NULL,

  `sr` float DEFAULT NULL,

  `srflag` varchar(10) DEFAULT NULL,

  `st` float DEFAULT NULL,

  `stflag` varchar(10) DEFAULT NULL,

  `sm` float DEFAULT NULL,

  `smflag` varchar(10) DEFAULT NULL,

  `wsavg` float DEFAULT NULL,

  `wsavgflag` varchar(10) DEFAULT NULL,

  `wdavg` float DEFAULT NULL,

  `wdavgflag` varchar(10) DEFAULT NULL,

  `tempavg` float DEFAULT NULL,

  `tempavgflag` varchar(10) DEFAULT NULL,

  `rhavg` float DEFAULT NULL,

  `rhavgflag` varchar(10) DEFAULT NULL,

  `stavg` float DEFAULT NULL,

  `stavgflag` varchar(10) DEFAULT NULL,

  `smavg` float DEFAULT NULL,

  `smavgflag` varchar(10) DEFAULT NULL,

  `sravg` float DEFAULT NULL,

  `sravgflag` varchar(10) DEFAULT NULL,

  `presavg` float DEFAULT NULL,

  `presavgflag` varchar(10) DEFAULT NULL,

  `paravg` float DEFAULT NULL,

  `paravgflag` varchar(10) DEFAULT NULL,

  `wsmax` float DEFAULT NULL,

  `wsmaxflag` varchar(10) DEFAULT NULL,

  `wsmaxdir` float DEFAULT NULL,

  `wsmaxdirflag` varchar(10) DEFAULT NULL,

  `wsmaxtime` float DEFAULT NULL,

  `wsmaxtimeflag` varchar(10) DEFAULT NULL,

  `temp10` float DEFAULT NULL,

  `temp10flag` varchar(10) DEFAULT NULL,

  `rh10` float DEFAULT NULL,

  `rh10flag` varchar(10) DEFAULT NULL,

  `leafwetness1` float DEFAULT NULL,

  `leafwetness1flag` varchar(10) DEFAULT NULL,

  `leafwetness2` float DEFAULT NULL,

  `leafwetness2flag` varchar(10) DEFAULT NULL,

  `dew10` float DEFAULT NULL,

  `dew10flag` varchar(10) DEFAULT NULL,

  `tempavg10` float DEFAULT NULL,

  `tempavg10flag` varchar(10) DEFAULT NULL,

  `rhavg10` float DEFAULT NULL,

  `rhavg10flag` varchar(10) DEFAULT NULL,

  `ws02` float DEFAULT NULL,

  `ws02flag` varchar(10) DEFAULT NULL,

  `wd02` float DEFAULT NULL,

  `wd02flag` varchar(10) DEFAULT NULL,

  `windsd02` float DEFAULT NULL,

  `windsd02flag` varchar(10) DEFAULT NULL,

  `wsavg02` float DEFAULT NULL,

  `wsavg02flag` varchar(10) DEFAULT NULL,

  `wdavg02` float DEFAULT NULL,

  `wdavg02flag` varchar(10) DEFAULT NULL,

  `wsmax02` float DEFAULT NULL,

  `wsmax02flag` varchar(10) DEFAULT NULL,

  `wsmaxdir02` float DEFAULT NULL,

  `wsmaxdir02flag` varchar(10) DEFAULT NULL,

  `wsmaxtime02` float DEFAULT NULL,

  `wsmaxtime02flag` varchar(10) DEFAULT NULL,

  `ws06` float DEFAULT NULL,

  `ws06flag` varchar(10) DEFAULT NULL,

  `wd06` float DEFAULT NULL,

  `wd06flag` varchar(10) DEFAULT NULL,

  `gust06` float DEFAULT NULL,

  `gust06flag` varchar(10) DEFAULT NULL,

  `gust02` float DEFAULT NULL,

  `gust02flag` varchar(10) DEFAULT NULL,

  `gustdir02` float DEFAULT NULL,

  `gustdir02flag` varchar(10) DEFAULT NULL,

  `gusttime02` float DEFAULT NULL,

  `gusttime02flag` varchar(10) DEFAULT NULL,

  `evap` float DEFAULT NULL,

  `evapflag` varchar(10) DEFAULT NULL,

  `windvarhi` float DEFAULT NULL,

  `windvarhiflag` varchar(10) DEFAULT NULL,

  `windvarlo` float DEFAULT NULL,

  `windvarloflag` varchar(10) DEFAULT NULL,

  `vis` float DEFAULT NULL,

  `visflag` varchar(10) DEFAULT NULL,

  `weather` varchar(12) DEFAULT NULL,

  `obscur` varchar(12) DEFAULT NULL,

  `altimeter` float DEFAULT NULL,

  `altimeterflag` varchar(10) DEFAULT NULL,

  `presch` float DEFAULT NULL,

  `preschflag` varchar(10) DEFAULT NULL,

  `pind` float DEFAULT NULL,

  `pindflag` varchar(10) DEFAULT NULL,

  `groundsnow` float DEFAULT NULL,

  `groundsnowflag` varchar(10) DEFAULT NULL,

  `watereq` float DEFAULT NULL,

  `watereqflag` varchar(10) DEFAULT NULL,

  `cl` int(11) DEFAULT NULL,

  `clflag` varchar(10) DEFAULT NULL,

  `cm` int(11) DEFAULT NULL,

  `cmflag` varchar(10) DEFAULT NULL,

  `ch` int(11) DEFAULT NULL,

  `chflag` varchar(10) DEFAULT NULL,

  `remarks` varchar(128) DEFAULT NULL,

  `lev1` varchar(9) DEFAULT NULL,

  `lev2` varchar(9) DEFAULT NULL,

  `lev3` varchar(9) DEFAULT NULL,

  `lev4` varchar(9) DEFAULT NULL,

  `lev5` varchar(9) DEFAULT NULL,

  `lev6` varchar(9) DEFAULT NULL,

  `waveht` float DEFAULT NULL,

  `wavehtflag` varchar(10) DEFAULT NULL,

  `domperiod` float DEFAULT NULL,

  `domperiodflag` varchar(10) DEFAULT NULL,

  `avgperiod` float DEFAULT NULL,

  `avgperiodflag` varchar(10) DEFAULT NULL,

  `wavedir` float DEFAULT NULL,

  `wavedirflag` varchar(10) DEFAULT NULL,

  `wtemp` float DEFAULT NULL,

  `wtempflag` varchar(10) DEFAULT NULL,

  `tide` float DEFAULT NULL,

  `tideflag` varchar(10) DEFAULT NULL,

  `tempmax` float DEFAULT NULL,

  `tempmaxflag` varchar(10) DEFAULT NULL,

  `tempmin` float DEFAULT NULL,

  `tempminflag` varchar(10) DEFAULT NULL,

  `tempmax6` float DEFAULT NULL,

  `tempmax6flag` varchar(10) DEFAULT NULL,

  `tempmin6` float DEFAULT NULL,

  `tempmin6flag` varchar(10) DEFAULT NULL,

  `tempmax24` float DEFAULT NULL,

  `tempmax24flag` varchar(10) DEFAULT NULL,

  `tempmin24` float DEFAULT NULL,

  `tempmin24flag` varchar(10) DEFAULT NULL,

  `rhmax` float DEFAULT NULL,

  `rhmaxflag` varchar(10) DEFAULT NULL,

  `rhmin` float DEFAULT NULL,

  `rhminflag` varchar(10) DEFAULT NULL,

  `c1sm` float DEFAULT NULL,

  `c1smflag` varchar(10) DEFAULT NULL,

  `c1tmp` float DEFAULT NULL,

  `c1tmpflag` varchar(10) DEFAULT NULL,

  `c1sal` float DEFAULT NULL,

  `c1salflag` varchar(10) DEFAULT NULL,

  `c1rdc` float DEFAULT NULL,

  `c1rdcflag` varchar(10) DEFAULT NULL,

  `c2sm` float DEFAULT NULL,

  `c2smflag` varchar(10) DEFAULT NULL,

  `c2tmp` float DEFAULT NULL,

  `c2tmpflag` varchar(10) DEFAULT NULL,

  `c2sal` float DEFAULT NULL,

  `c2salflag` varchar(10) DEFAULT NULL,

  `c2rdc` float DEFAULT NULL,

  `c2rdcflag` varchar(10) DEFAULT NULL,

  `c3sm` float DEFAULT NULL,

  `c3smflag` varchar(10) DEFAULT NULL,

  `c3tmp` float DEFAULT NULL,

  `c3tmpflag` varchar(10) DEFAULT NULL,

  `c3sal` float DEFAULT NULL,

  `c3salflag` varchar(10) DEFAULT NULL,

  `c3rdc` float DEFAULT NULL,

  `c3rdcflag` varchar(10) DEFAULT NULL,

  `c4sm` float DEFAULT NULL,

  `c4smflag` varchar(10) DEFAULT NULL,

  `c4tmp` float DEFAULT NULL,

  `c4tmpflag` varchar(10) DEFAULT NULL,

  `c4sal` float DEFAULT NULL,

  `c4salflag` varchar(10) DEFAULT NULL,

  `c4rdc` float DEFAULT NULL,

  `c4rdcflag` varchar(10) DEFAULT NULL,

  `c5sm` float DEFAULT NULL,

  `c5smflag` varchar(10) DEFAULT NULL,

  `c5tmp` float DEFAULT NULL,

  `c5tmpflag` varchar(10) DEFAULT NULL,

  `c5sal` float DEFAULT NULL,

  `c5salflag` varchar(10) DEFAULT NULL,

  `c5rdc` float DEFAULT NULL,

  `c5rdcflag` varchar(10) DEFAULT NULL,

  `tempavg24` float DEFAULT NULL,

  `tempavg24flag` varchar(10) DEFAULT NULL,

  `wsavg24` float DEFAULT NULL,

  `wsavg24flag` varchar(10) DEFAULT NULL,

  `wdavg24` float DEFAULT NULL,

  `wdavg24flag` varchar(10) DEFAULT NULL,

  `battmin` float DEFAULT NULL,

  `battminflag` varchar(10) DEFAULT NULL,

  `rhavg24` float DEFAULT NULL,

  `rhavg24flag` varchar(10) DEFAULT NULL,

  `owl` float DEFAULT NULL,

  `owlflag` varchar(10) DEFAULT NULL,

  `pwl` float DEFAULT NULL,

  `pwlflag` varchar(10) DEFAULT NULL,

  `fueltempavg` float DEFAULT NULL,

  `fueltempavgflag` varchar(10) DEFAULT NULL,

  `gsp` float DEFAULT NULL,

  `gspflag` varchar(10) DEFAULT NULL,

  `fuelmoistureavg` float DEFAULT NULL,

  `fuelmoistureavgflag` varchar(10) DEFAULT NULL,

  `wetbulb` float DEFAULT NULL,

  `wetbulbflag` varchar(10) DEFAULT NULL,

  `placeholder1` float DEFAULT NULL,

  `placeholder1flag` varchar(10) DEFAULT NULL,

  `placeholder2` float DEFAULT NULL,

  `placeholder2flag` varchar(10) DEFAULT NULL,

  `placeholder3` float DEFAULT NULL,

  `placeholder3flag` varchar(10) DEFAULT NULL,

  `comment` text,

  PRIMARY KEY (`station`,`ob`,`obtype`),

  KEY `ob` (`ob`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=1000000000

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

10.1.12-MariaDB.server.cnf
2016-03-24 20:26
2 kB
John McGuire

Issue Links

blocks

MDEV-8908 10.x.x having slower performance than 5.5.x

Closed

Activity

People

Assignee:: Axel Schwenke

Reporter:: John McGuire

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-03-24 20:26

Updated:: 2016-11-29 16:50

Resolved:: 2016-11-29 16:50

Git Integration

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