Re: query to find gaps in sequential indexes causes CPU spikes

[Chris Nevin]

Rick -

Here's the create on the table:

CREATE TABLE "CustomerData" (
"id" int(11) NOT NULL,
"descid" int(11) NOT NULL,
"status" tinyint(3) unsigned NOT NULL DEFAULT '0',
"objid" varchar(50) DEFAULT NULL,
"pdata" longblob,
"setid" int(11) NOT NULL DEFAULT '0',
"dindex" int(11) NOT NULL DEFAULT '0',
"crc" bigint(20) NOT NULL DEFAULT '0',
"supplied" datetime DEFAULT NULL,
"resupply" datetime DEFAULT NULL,
"expired" datetime DEFAULT NULL,
"returned" datetime DEFAULT NULL,
"regid" int(11) NOT NULL DEFAULT '-1',
"devid" int(11) NOT NULL DEFAULT '0',
"istatus" tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY ("id"),
KEY "descid_regid" ("descid","regid"),
KEY "regid_descid" ("regid","descid")
)

What I referred to as Column1 is descid, and Column0 is regid. The table could be split vertically as you suggested, but would that help performance in this case? Both queries I'm running are completely covered by the data provided in the index, so I would think the data rows themselves would not be accessed.

I did try the NOT EXISTS suggestion. The cpu usage was down, but only 1 - 2%.

The table has 600,001 rows where Column1 (descid) = 10.

These queries are not done very often at all, but are concerning because of the impact on the system. There are other queries though that occur all the time accessing rows in this table, mostly a combination of selects and updates. The columns that comprise the indexes (all three) never change, so once the records are inserted, the indexes are static until other rows are inserted. The data that does change are the datetime and status columns.

It is possible to pre-calculate and save the information elsewhere which I might have to do.

Regarding the buffer pool, I had read about the 70% and that each instance should be at least a 1GB. Since my buffer pool is about 27GB, would setting the innodb_buffer_pool_instances to 27 help overall performance? Is 1GB is the optimal instance size or is there a point where performance is hurt by a large number of instances?

Thanks for sticking with me on this and your continued help.