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
7417
March 18, 2009 02:43AM
2750
March 18, 2009 03:08AM
2619
March 18, 2009 08:29PM
2575
March 19, 2009 04:48AM
2728
March 19, 2009 10:46AM
2504
March 19, 2009 03:47AM
2653
March 19, 2009 03:55AM
2521
March 19, 2009 04:49AM
2492
March 19, 2009 10:20AM
2195
March 30, 2009 01:25AM
2270
March 30, 2009 07:13PM
2295
March 31, 2009 02:31AM
Re: InnoDB CPU Spikes
2520
March 31, 2009 08:12AM
2193
April 01, 2009 03:27AM
2243
April 01, 2009 10:01AM
2351
April 01, 2009 10:22AM
2224
April 01, 2009 03:45AM
2507
April 01, 2009 11:57PM
2402
April 06, 2009 01:23AM
2364
April 06, 2009 08:01PM
2145
April 07, 2009 12:14AM
2297
April 08, 2009 04:36PM
2315
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.