2

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

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Nate L
  • 21
  • 3
  • Welcome to Stack Overflow! To help you with your [tag:query-optimization] question, we need to see your table definitions and your index definitions as well as the output of EXPLAIN. In MariaDB you can say `SHOW CREATE TABLE student_repository;` to get this information. Do it on both versions of MariaDB, please, and show us the output. Please [read this](https://stackoverflow.com/tags/query-optimization/info), then [edit] your question. And, I too have seen weird performance anomalies on later MariaDB versions. (@danblack ? ) – O. Jones May 31 '23 at 19:03
  • and please show the create table (and the other things) as text, not images – ysth Jun 01 '23 at 03:07
  • I added the create table, but it won't let me convert the links into images since I don't have 10 reputation points. – Nate L Jun 01 '23 at 13:54
  • We ran in a similar issue - we switched from 10.3 to 10.11 as well and suddenly we have an extreme performance loss with a stored procedure call. Any ideas why? Were there any major changes with stored procedures? – Scorpia Jun 21 '23 at 09:51
  • 1
    @Scorpia I've found the solution to my problem and put it under this post, hopefully it helps your situation as well. – Nate L Jun 23 '23 at 17:11

2 Answers2

0

Table scans for large tables aren't instantaneous. They take CPU and IO operations on your database server. They may be fast, especially for cached rows, but instantaneous they are not.

The query you showed us needs this index to be efficient.

CREATE KEY idx_student_repository_LAST_NAME  
        ON student_repository(LAST_NAME );

It's hard to say why this was efficient on your old server without this index. That's surprising. It's possible the old server had the query_cache_type variable set to ON (1) and your fast query was somehow satisfied from the cache (because it already ran slowly). Newer versions have it set to OFF (0), for good reasons.

Pro tip Decide which indexes you need based on which queries are slow in your application. Don't "put indexes on all the columns". As apps grow their tables often need new indexes, and it's really hard to predict what indexes they'll need before app growth. It's standard operating procedure to revisit your app's SQL performance every month or so, and determine whether new indexes are needed as tables grow.

Check this out https://use-the-index-luke.com/

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

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.

Nate L
  • 21
  • 3