Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.7, 10.5.9, 10.6.0
-
None
-
Debian 10, Both on VM's and Baremetal installs. Tried on fast SSD storage and spinning Disks. Also tried on Machines with memory > diskspace.
Description
On our production systems we deployed MariaDB 10.5.9 after extensive testing without problems. However sometimes when the Rubber meets the road you still encounter something that forces you to revert to the previous version (10.1) where the problem did not occur.
The problem is 100% reproducible and took it some time to create a test case without our proprietary data structures. I will do some additional testing on other versions also to see which version introduced the regression also and add that to the affected version list.
Here the simplified problem case:
Table definition:
DROP DATABASE IF EXISTS `testcase`; |
CREATE DATABASE `testcase`; |
|
USE `testcase`; |
|
CREATE TABLE `contacts` ( |
`systemid` int(11) NOT NULL AUTO_INCREMENT, |
`inactive` tinyint(1) NOT NULL, |
`company` varchar(255) DEFAULT NULL, |
`account` varchar(10) DEFAULT NULL, |
`ostreet` varchar(60) DEFAULT NULL, |
`ostreet2` varchar(60) DEFAULT NULL, |
`ocountry` varchar(35) DEFAULT NULL, |
`ocity` varchar(35) DEFAULT NULL, |
`ohouseno` varchar(15) DEFAULT NULL, |
`ohousenoext` varchar(7) DEFAULT NULL, |
`mstreet` varchar(60) DEFAULT NULL, |
`mstreet2` varchar(60) DEFAULT NULL, |
`mcountry` varchar(35) DEFAULT NULL, |
`mcity` varchar(35) DEFAULT NULL, |
`mhouseno` varchar(15) DEFAULT NULL, |
`mhousenoext` varchar(7) DEFAULT NULL, |
`tel1` varchar(80) DEFAULT NULL, |
`tel2` varchar(80) DEFAULT NULL, |
`tel3` varchar(80) DEFAULT NULL, |
`tel4` varchar(80) DEFAULT NULL, |
`mobile` varchar(80) DEFAULT NULL, |
`fax` varchar(80) DEFAULT NULL, |
`notes` longtext DEFAULT NULL, |
`hstreet` varchar(60) DEFAULT NULL, |
`hstreet2` varchar(60) DEFAULT NULL, |
`hcountry` varchar(35) DEFAULT NULL, |
`hcity` varchar(35) DEFAULT NULL, |
`hhouseno` varchar(15) DEFAULT NULL, |
`hhousenoext` varchar(7) DEFAULT NULL, |
`ozipcode` varchar(15) DEFAULT NULL, |
`mzipcode` varchar(15) DEFAULT NULL, |
`hzipcode` varchar(15) DEFAULT NULL, |
`birthday` date DEFAULT NULL, |
`initials` varchar(20) DEFAULT NULL, |
`middlename` varchar(20) DEFAULT NULL, |
`title` varchar(70) DEFAULT NULL, |
`lastname` varchar(100) DEFAULT NULL, |
`relationtype` varchar(128) DEFAULT NULL, |
`relationtypefunction` varchar(128) DEFAULT NULL, |
`email` varchar(255) DEFAULT NULL, |
`nationality` varchar(50) DEFAULT NULL, |
`saluation` varchar(80) DEFAULT NULL, |
`surtitle` varchar(15) DEFAULT NULL, |
`lastupdate` datetime DEFAULT NULL, |
`datein` datetime NOT NULL, |
`firstname` varchar(100) DEFAULT NULL, |
`hcity2` varchar(35) DEFAULT NULL, |
`mailing1st` int(11) DEFAULT NULL, |
`partsameadr` int(11) DEFAULT NULL, |
`partcode` varchar(10) DEFAULT NULL, |
`christianname` varchar(60) DEFAULT NULL, |
`telintern` varchar(80) DEFAULT NULL, |
PRIMARY KEY (`systemid`), |
KEY `groupby_relationtype` (`relationtype`), |
KEY `groupby_relationtype_saluation` (`relationtype`,`saluation`), |
KEY `relationtype_lastname` (`relationtype`,`lastname`), |
KEY `partcode` (`partcode`), |
KEY `ozipcode` (`ozipcode`), |
KEY `hzipcode_hhouseno` (`hzipcode`,`hhouseno`), |
KEY `company` (`company`), |
KEY `mcity` (`mcity`), |
KEY `lastname` (`lastname`), |
KEY `firstname` (`firstname`), |
KEY `email` (`email`), |
KEY `mobile` (`mobile`), |
KEY `tel1` (`tel1`), |
KEY `tel2` (`tel2`), |
KEY `tel3` (`tel3`), |
KEY `tel4` (`tel4`), |
KEY `telintern` (`telintern`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
Generate about 80000 rows of generic test data for this table, make sure there are e-mail addresses in the rows and that more than a few of the fields where there are also indexes on have data in there.
Script to create the problematic queries
#!/bin/bash
|
|
MINVALUE=5000
|
LIMITVALUE=20000
|
#LIMITVALUE=10000
|
#LIMITVALUE=200
|
|
set -x |
# The 'select systemid from' to get a list of systemid's simulates a fulltext search as a source of the systemid list
|
SYSTEMIDLIST="
|
`echo "select systemid from testcase.contacts where systemid > ${MINVALUE} LIMIT ${LIMITVALUE}" | mysql -h localhost -N | xargs | sed 's/ /, /g'` |
"
|
|
echo " |
USE testcase;
|
|
SET profiling=1;
|
SELECT * FROM contacts WHERE systemid in ( ${SYSTEMIDLIST} ) or email = 'blaa@notexisting.nl'; |
|
SHOW PROFILE;
|
"
|
The Statistics Duration results of these queries with the different sizes:
Length of systemid list | Duration output 'Statistics' in the profiling |
---|---|
200 | 0.00038.0 |
10000 | 1.087868 |
20000 | 4.831477 |
The bigger the systemid list the exponentially worse the time it takes to do the Statistics phase.
Running the same query multiple times in a row does not make the Statistics phase faster.
On our production environment we have a much bigger table with extra complexities and the actual query there took up to a 1000 seconds. After a little while there were so many of these queries that the database server became unresponsive (even before the max amount of connections was reached) and we had to revert to the former version.
Note: I will test some more versions to see if they also have this problem (see the comments) and add that to the affects version list if that also has this. Things like show variables output I will add to the attachments.
All mariadb 10.5 version debian packages were retrieved from here:
http://mirror.i3d.net/pub/mariadb/repo/10.5/debian buster/main amd64 Packages