Re: InnoDB CPU Spikes

[Rick James]

The number of unique users for a given day is not equal to the sum of the uniques for each of the 24 hours, right? So, yes, it requires a different algorithm. But it can still use a summary table, though perhaps one just for such. How about this:

The table has just two fields:
* hour (timestamp truncated to the hour)
* user
KEY (hour, user) -- note: not PRIMARY OR UNIQUE (see below)

The insertion is
INSERT INTO summary2
SELECT DISTINCT ... as hour, user FROM main_table
WHERE id BETWEEN $leftoff and ...;

The uniques for one hour or day or month:
SELECT count(DISTINCT user) FROM summary2
WHERE hour >= ? AND hour < DATE_ADD(?, INTERVAL 1 xxx);
with xxx = HOUR or DAY or MONTH

The devil's in the details. Since you can't trust to do the summarization exactly on the hour boundary, bit the bullet and allow some dups in the table. (Well, I guess INSERT IGNORE would work, too.)