MySQL Forums
Forum List  »  Performance

Re: InnoDB CPU Spikes
Posted by: Rick James
Date: March 31, 2009 08:12AM

"Using index" is somewhat good -- it means that it could satisfy the query (for this one table) by looking only in the index, not in the data.

"Using where" does not say much.

"Using filesort" is somewhat bad -- it means that it could not gather the desired rows in the desired order, but had to do a sort. "filesort" is a misnomer; there are times when it actually does it all in RAM. Still, it is an extra pass over the data.

Using MEMORY engine -- Sure you could use it. But don't (in this case). You would, as you point out, have to do something to persist the data. Note: regardless of the engine used, you have to scan 1.8M rows -- that takes a lot of work. You will still have a CPU spike.

Summary table...
You could collect the summary every minute (or 10 minutes, or hour, or whatever):
CREATE TABLE summary1 (
    slot DATETIME NOT NULL   COMMENT 'to the minute',
    ct SMALLINT UNSIGNED NOT NULL  COMMENT 'how many for that minute'
  PRIMARY KEY (slot)
);

INSERT INTO summary1
  select $starttime,     # The 'minute' for this count
         count(*)
  from tblAuditLog
  where pkAuditLogId > $start  # Looks like you know the beginning/end
    and pkAuditLogId < $end;
That would be a tiny cpu spike every minute.
Then your 'report' for one day would be
SELECT SUM(ct)
  FROM summary1
  WHERE slot >= $start_time
    AND slot <  DATE_ADD($start_time, INTERVAL 1 DAY)
This would also be a tiny cpu spike.

Uniques... Your original query did not do anything visible to comput "uniqueness"?? Or is each row a "unique transaction"? In Internet usage, "unique users" is a count of how many different (DISTINCT) users visited a site. Summary tables get messy -- a unique user for one minute or hour may be the same (not-unique) user the next minute or hour. Hence the SUM(ct) is not quite right.

Options: ReplyQuote


Subject
Views
Written By
Posted
7830
March 18, 2009 02:43AM
2874
March 18, 2009 03:08AM
2733
March 18, 2009 08:29PM
2689
March 19, 2009 04:48AM
2848
March 19, 2009 10:46AM
2602
March 19, 2009 03:47AM
2746
March 19, 2009 03:55AM
2621
March 19, 2009 04:49AM
2590
March 19, 2009 10:20AM
2281
March 30, 2009 01:25AM
2346
March 30, 2009 07:13PM
2420
March 31, 2009 02:31AM
Re: InnoDB CPU Spikes
2642
March 31, 2009 08:12AM
2268
April 01, 2009 03:27AM
2392
April 01, 2009 10:01AM
2452
April 01, 2009 10:22AM
2293
April 01, 2009 03:45AM
2604
April 01, 2009 11:57PM
2511
April 06, 2009 01:23AM
2470
April 06, 2009 08:01PM
2262
April 07, 2009 12:14AM
2393
April 08, 2009 04:36PM
2391
April 16, 2009 07:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.