Re: InnoDB CPU Spikes
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?