The Ultimate Guide to High Availability with MariaDB

Comments - Slow insert queries after upgrade

3 years, 9 months ago Oli Sennhauser
Re: Slow insert queries after upgrade

It would be interesting to know from which version you were upgrading to 10.5.8? If you upgrade from 10.5.7 to 10.5.8 I would NOT expect any issues. If you upgrade from 10.3.x to 10.5.8 I am not surprised...

Why are you sure, that it is NOT the SELECT? Because I would bet on the SELECT... Can you prove this?

3rd it would be interesting to know what is the PK of both tables? So possibly you are running into the InnoDB locality problem in time series.

Have you tested the query several times? If it is horribly slow just the first time and if this only/always happens after a MariaDB restart (caching issues) then you run indeed into the InnoDB locality problem in time series.

 
3 years, 9 months ago Stavros Antoniou
Re: Slow insert queries after upgrade

Thanks for your reply.

It was a rather massive upgrade version wise... We went from 10.1 to 10.5.8.

When I run the select on it's own it takes a few seconds to run. Thats why I assumed that it was not the select that was slowing things down. Please see the explain of the select below:

 "id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
"1"	"PRIMARY"	"m"	"index"	\N	"PRIMARY"	"3"	\N	"269"	"Using where"
"1"	"PRIMARY"	"md"	"ref"	"id timestamp,meter_id"	"meter_id"	"3"	"stackTest.m.meter_id"	"7854"	"Using index"
"2"	"DEPENDENT SUBQUERY"	"md"	"ref"	"id timestamp,timeStamp,meter_id"	"id timestamp"	"3"	"func"	"7854"	"Using where; Using index"

Both tables have an autoincrement primary key.

I have tested the query several times and whilst the select improves after the first iteration the insert does not.

Can you please expand on the "InnoDB locality problem in time series" comment you have mentioned? Just for context I am a developer so I am not well versed in the setup of the database itself.

Thanks, S.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.