MySQL Forums
Forum List  »  Performance

Re: InnoDB CPU Spikes
Posted by: Sabari girish Viswanathan
Date: April 01, 2009 03:45AM

I dont see my previous posts... Apologies if it is duplicated

I am grateful to you Rick for the support.

I used mysql profiler to my query and this is what i got from it

mysql> select hour(fldCreatedDate) RequestHour, fldshortcode as ShortCode, count(*) TotalRequests, sum(if(fldResponseType=true,1,0)) SuccessResponse, sum(if(fldResponseType=false,1,0)) AbortsAndErrors, count(distinct fldMsisdnnumber) TotalUniqueUsers from tblAuditLog where date(fldCreatedDate) = date('2009-03-04') group by RequestHour, ShortCode;
+-------------+-----------+---------------+-----------------+-----------------+------------------+
| RequestHour | ShortCode | TotalRequests | SuccessResponse | AbortsAndErrors | TotalUniqueUsers |
+-------------+-----------+---------------+-----------------+-----------------+------------------+
| 0 | #225# | 179991 | 179991 | 0 | 1 |
| 0 | #326# | 64803 | 64803 | 0 | 1 |
| 0 | #646# | 359980 | 359980 | 0 | 1 |
| 0 | #674# | 64803 | 64803 | 0 | 1 |
| 0 | #686# | 50403 | 50403 | 0 | 1 |
| 1 | #225# | 179987 | 179987 | 0 | 1 |
| 1 | #326# | 64802 | 64802 | 0 | 1 |
| 1 | #646# | 359975 | 359975 | 0 | 1 |
| 1 | #674# | 64801 | 64801 | 0 | 1 |
| 1 | #686# | 50402 | 50402 | 0 | 1 |
| 2 | #225# | 176487 | 176487 | 0 | 1 |
| 2 | #326# | 63698 | 63698 | 0 | 1 |
| 2 | #646# | 354559 | 354559 | 0 | 1 |
| 2 | #674# | 63974 | 63974 | 0 | 1 |
| 2 | #686# | 49598 | 49598 | 0 | 1 |
| 3 | #225# | 179985 | 179985 | 0 | 1 |
| 3 | #326# | 64800 | 64800 | 0 | 1 |
| 3 | #646# | 359971 | 359971 | 0 | 1 |
| 3 | #674# | 64802 | 64802 | 0 | 1 |
| 3 | #686# | 50403 | 50403 | 0 | 1 |
| 4 | #225# | 179288 | 179288 | 0 | 1 |
| 4 | #326# | 64521 | 64521 | 0 | 1 |
| 4 | #646# | 358604 | 358603 | 1 | 1 |
| 4 | #674# | 64554 | 64554 | 0 | 1 |
| 4 | #686# | 50199 | 50199 | 0 | 1 |
| 5 | #225# | 178344 | 178344 | 0 | 1 |
| 5 | #326# | 64433 | 64433 | 0 | 1 |
| 5 | #646# | 357449 | 357449 | 0 | 1 |
| 5 | #674# | 64158 | 64158 | 0 | 1 |
| 5 | #686# | 49931 | 49931 | 0 | 1 |
| 6 | #225# | 179987 | 179987 | 0 | 1 |
| 6 | #326# | 64803 | 64803 | 0 | 1 |
| 6 | #646# | 359974 | 359974 | 0 | 1 |
| 6 | #674# | 64802 | 64802 | 0 | 1 |
| 6 | #686# | 50402 | 50402 | 0 | 1 |
| 7 | #225# | 180090 | 180090 | 0 | 1 |
| 7 | #326# | 64837 | 64837 | 0 | 1 |
| 7 | #646# | 360178 | 360178 | 0 | 1 |
| 7 | #674# | 64838 | 64838 | 0 | 1 |
| 7 | #686# | 50431 | 50431 | 0 | 1 |
| 8 | #225# | 179982 | 179982 | 0 | 1 |
| 8 | #326# | 64802 | 64802 | 0 | 1 |
| 8 | #646# | 359965 | 359965 | 0 | 1 |
| 8 | #674# | 64800 | 64800 | 0 | 1 |
| 8 | #686# | 50403 | 50403 | 0 | 1 |
| 9 | #225# | 179993 | 179993 | 0 | 1 |
| 9 | #326# | 64803 | 64803 | 0 | 1 |
| 9 | #646# | 359986 | 359986 | 0 | 1 |
| 9 | #674# | 64804 | 64804 | 0 | 1 |
| 9 | #686# | 50402 | 50402 | 0 | 1 |
| 10 | #225# | 148735 | 148735 | 0 | 1 |
| 10 | #326# | 53550 | 53550 | 0 | 1 |
| 10 | #646# | 297470 | 297470 | 0 | 1 |
| 10 | #674# | 53550 | 53550 | 0 | 1 |
| 10 | #686# | 41651 | 41651 | 0 | 1 |
| 13 | #225# | 55351 | 55351 | 0 | 2 |
| 13 | #326# | 19929 | 19929 | 0 | 1 |
| 13 | #646# | 110698 | 110698 | 0 | 1 |
| 13 | #674# | 19929 | 19929 | 0 | 1 |
| 13 | #686# | 15500 | 15500 | 0 | 1 |
| 13 | #793# | 1 | 0 | 1 | 1 |
| 16 | #225# | 160 | 0 | 160 | 1 |
| 16 | #326# | 58 | 0 | 58 | 1 |
| 16 | #646# | 320 | 0 | 320 | 1 |
| 16 | #674# | 58 | 0 | 58 | 1 |
| 16 | #686# | 45 | 45 | 0 | 1 |
| 17 | #225# | 31679 | 31679 | 0 | 1 |
| 17 | #326# | 11402 | 11402 | 0 | 1 |
| 17 | #646# | 63365 | 63365 | 0 | 1 |
| 17 | #674# | 11356 | 11356 | 0 | 1 |
| 17 | #686# | 8899 | 8899 | 0 | 1 |
| 18 | #225# | 1 | 1 | 0 | 1 |
| 19 | #225# | 7 | 2 | 5 | 1 |
| 19 | #646# | 1 | 0 | 1 | 1 |
| 19 | #674# | 1 | 0 | 1 | 1 |
| 19 | #686# | 1 | 1 | 0 | 1 |
| 20 | #225# | 84800 | 84800 | 0 | 2 |
| 20 | #326# | 30530 | 30530 | 0 | 1 |
| 20 | #646# | 169594 | 169594 | 0 | 1 |
| 20 | #674# | 30530 | 30530 | 0 | 1 |
| 20 | #686# | 23746 | 23746 | 0 | 1 |
| 21 | #225# | 179992 | 179992 | 0 | 1 |
| 21 | #326# | 64804 | 64804 | 0 | 1 |
| 21 | #646# | 359983 | 359983 | 0 | 1 |
| 21 | #674# | 64804 | 64804 | 0 | 1 |
| 21 | #686# | 50404 | 50404 | 0 | 1 |
| 22 | #225# | 179987 | 179987 | 0 | 1 |
| 22 | #326# | 64802 | 64802 | 0 | 1 |
| 22 | #646# | 359975 | 359975 | 0 | 1 |
| 22 | #674# | 64801 | 64801 | 0 | 1 |
| 22 | #686# | 50403 | 50403 | 0 | 1 |
| 23 | #225# | 180085 | 180084 | 1 | 1 |
| 23 | #326# | 64836 | 64836 | 0 | 1 |
| 23 | #646# | 360169 | 360168 | 1 | 1 |
| 23 | #674# | 64837 | 64837 | 0 | 1 |
| 23 | #686# | 50429 | 50429 | 0 | 1 |
+-------------+-----------+---------------+-----------------+-----------------+------------------+
96 rows in set (3 min 26.61 sec)

mysql> select min(seq) seq,state,count(*) numb_ops, round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur, round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu from information_schema.profiling where query_id = 5 group by state order by seq;
+------+--------------------+----------+-----------+-----------+-----------+-----------+
| seq | state | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |
+------+--------------------+----------+-----------+-----------+-----------+-----------+
| 1 | starting | 1 | 0.00013 | 0.00013 | 0.00013 | 0.00013 |
| 2 | Opening tables | 1 | 0.00004 | 0.00004 | 0.00003 | 0.00003 |
| 3 | System lock | 1 | 0.00001 | 0.00001 | 0.00001 | 0.00001 |
| 4 | Table lock | 1 | 0.00001 | 0.00001 | 0.00001 | 0.00001 |
| 5 | init | 1 | 0.00006 | 0.00006 | 0.00006 | 0.00006 |
| 6 | optimizing | 1 | 0.00001 | 0.00001 | 0.00001 | 0.00001 |
| 7 | statistics | 1 | 0.00002 | 0.00002 | 0.00001 | 0.00001 |
| 8 | preparing | 1 | 0.00002 | 0.00002 | 0.00001 | 0.00001 |
| 9 | executing | 1 | 0.00005 | 0.00005 | 0.00005 | 0.00005 |
| 10 | Sorting result | 1 | 171.87326 | 171.87326 | 132.76191 | 132.76191 |
| 11 | Sending data | 1 | 34.73429 | 34.73429 | 32.65946 | 32.65946 |
| 12 | end | 1 | 0.00003 | 0.00003 | 0.00003 | 0.00003 |
| 13 | query end | 1 | 0.00001 | 0.00001 | 0.00001 | 0.00001 |
| 14 | freeing items | 2 | 0.00012 | 0.00006 | 0.00008 | 0.00004 |
| 15 | removing tmp table | 1 | 0.00002 | 0.00002 | 0.00001 | 0.00001 |
| 17 | logging slow query | 2 | 0.00001 | 0.00001 | 0.00001 | 0.00000 |
| 19 | cleaning up | 1 | 0.00001 | 0.00001 | 0.00001 | 0.00001 |
+------+--------------------+----------+-----------+-----------+-----------+-----------+
17 rows in set (0.00 sec)

mysql>



Looks like the sorting is eating the CPU, also the explain shows "Using filesort", I am unable to add an index on the date column because it slows down my inserts and ultimately my application crashes. The pk Id that I used was based on some query that I manually ran to get them, the application has no knowledge on these values.

Is there any method to optimize my sorting so that the CPU cycles reduce.

For the summary table generation : I am planning to use an event to trigger a stored procedure to populate the Hourly Summary and Daily Summary tables and use this for report generation by the application. Is the event method the right path for summary table buildings? What would happen to the inserts to summary table if the db was down at that point in time would the event be triggered after DB is back up? Ultimately can this be reliable?

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
2642
March 31, 2009 08:12AM
2268
April 01, 2009 03:27AM
2392
April 01, 2009 10:01AM
2452
April 01, 2009 10:22AM
Re: InnoDB CPU Spikes
2292
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.