mariadbquery-optimizationmariadb-10.3

Slow queries for specific table after upgrading MariaDB 10.3 to 10.11

I recently upgraded our db servers from MariaDB 10.3 to 10.11 and one of our tables takes significantly longer to query than it did on 10.3. The following simple query takes about 10 seconds to query on 10.11, and it runs instantly on 10.3. The table has about 67,000 records and it runs slow on any query that isn't using an index in the filter. I'm confused why the upgrade would only cause one table to query slowly. We have other tables with over 100,000 records that can do table scans instantly. This table can be searched by many columns, so I'd rather not have to put indexes on all the columns especially since this wasn't a problem pre-upgrade.

SELECT * FROM student_repository WHERE LAST_NAME = "DOE"

CREATE TABLE `student_repository` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`FIRST_NAME` varchar(255) DEFAULT NULL,
`LAST_NAME` varchar(255) DEFAULT NULL,
`AID_YEAR` int(11) DEFAULT NULL,
`TRANSACTION_NUMBER` int(11) DEFAULT NULL,
`ISIR_LINE` varchar(15000) DEFAULT NULL,
`ISIR_HEADER_ID` bigint(20) DEFAULT NULL,
`SESSION_ID` bigint(20) NOT NULL,
`SSN_LAST_FOUR` varchar(4) DEFAULT NULL,
`ED_HASH_TRANSFER` varchar(255) DEFAULT NULL,
`ED_HASH_CURRENT` varchar(255) DEFAULT NULL,
`ED_HASH_ORIGINAL` varchar(255) DEFAULT NULL,
`EXPORTED_BY_USER_ID` varchar(255) DEFAULT NULL,
`EXPORTED_DATE` datetime DEFAULT NULL,
`SID` varchar(255) DEFAULT NULL,
`SCHOOL_CODE` varchar(255) DEFAULT NULL,
`STATUS` varchar(255) DEFAULT NULL,
`STUDENT_ID_FK` bigint(20) DEFAULT NULL,
`EOP` bit(1) DEFAULT b'0',
`ED_HASH` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `idx_student_repository_ED_HASH_ORIGINAL` (`ED_HASH_ORIGINAL`),
KEY `idx_student_repository_STUDENT_ID_FK` (`STUDENT_ID_FK`),
KEY `idx_student_repository_ED_HASH_CURRENT` (`ED_HASH_CURRENT`),
KEY `idx_student_repository_ED_HASH_TRANSFER` (`ED_HASH_TRANSFER`)
) ENGINE=InnoDB AUTO_INCREMENT=134634 DEFAULT CHARSET=latin1 
COLLATE=latin1_swedish_ci;

I've tried to run ANALYZE on the table to get the statistics updated and that didn't help.

I'm struggling to find any server options that changed between versions that would cause this kind of slow down.

There are some differences between the execution plan, but I'm not sure what is causing it.

10.3 Execution Plan10.3 Execution Plan

10.11 Execution Plan10.11 Execution Plan

10.3 Explain10.3 Explain

10.11 Explain10.11 Explain

The only other differences I've found is that our 10.3 processlist has InnoDB purge threads and they are missing in 10.11. I'm wondering if those missing are causing a performance issue, but I can't figure out how to start them.

10.3 Processlist10.3 Processlist

Solution

  • It turns out the issue was the large column on the table ISIR_LINE varchar(15000). In 10.3 queries were slow if you returned that column in a select, but if you removed it from the select it would run quick. Seems like in 10.11 even if you leave that column off of the select it will still allocate that memory to the process, causing it to be slower still. This seems to only be the case if it is a full table scan, so I moved the column out into its own table with a FK back to the original and optimized the table. Now a full table scan runs quickly on the main table.

    We saw a large decrease in performance if a table had a column with a max character length of 3,000 vs 2,500. So, if a table has a column of length over 2,500 you should try putting an index on the columns you are filtering by, lowering the max length of the column if you can, or moving it to it's own table with a FK connecting it to the main table.