MySQL Forums
Forum List  »  Performance

Re: InnoDB CPU Spikes
Posted by: Rick James
Date: April 01, 2009 11:57PM

Minor items:

Any two of these can be used to calc the third:
        count(*) TotalRequests,
        sum(if(fldResponseType=true,1,0)) SuccessResponse,
        sum(if(fldResponseType=false,1,0)) AbortsAndErrors,
Suggest keeping only two of them.

I suspect true is 1 and false is 0:
sum(if(fldResponseType=true,1,0)) -->
sum(fldResponseType)

date('2009-03-04')  -->
'2009-03-04'

OK, back to discussing the summary table. You are almost there!

* Don't build the summary table all at once (except to get started).
* You have an AUTO_INCREMENT PK on the table tblAuditLog, right?
* You can remember "where you left off" using that PK.
* To the SELECT ... GROUP BY ... add a WHERE clause restricting the PK to pick up where you left off. It becomes "INSERT INTO summary SELECT ..."
* That will make each hourly SELECT run in a fraction of the time you quote. Sure, it will be a filesort, but it won't be that long.
* The report will have to SUM the sums, SUM the COUNTs, do SUM(sum...)/SUM(count...) to get an average, etc.
* The PK is all you need, no extra indexes.
* Don't worry about getting exactly one hour's worth in each pass; the report will take care of the dups of (RequestHour, ShortCode); instead, concentrate on "left off". (This deals with your concern about the db being down.)
* Events? If you are on Unix, just use cron. Maybe set it up to run every 10 minutes (*/10 * * * *).

More discussion...
http://forums.mysql.com/read.php?125,252723,253118
http://forums.mysql.com/read.php?10,252593,252740
http://forums.mysql.com/read.php?125,245133,245357

Options: ReplyQuote


Subject
Views
Written By
Posted
7829
March 18, 2009 02:43AM
2874
March 18, 2009 03:08AM
2732
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
2642
March 31, 2009 08:12AM
2268
April 01, 2009 03:27AM
2392
April 01, 2009 10:01AM
2452
April 01, 2009 10:22AM
2292
April 01, 2009 03:45AM
Re: InnoDB CPU Spikes
2603
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.