The Ultimate Guide to High Availability with MariaDB

Comments - Slow insert queries after upgrade

3 years, 2 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.