fandango takes 3-4 hours to summarize daily pixel data

Warehouse Summarizer takes 4 hours to summarize fandango data. we should run warehouse summarizer at debug 4 and make sure all of the queries are using indexes correctly and efficiently. we should try to find and fix the bottleneck. The goal should be that pixel data takes no longer than 2 hours to get into the warehouse for an account.

Daily data takes 4 hours, and hourly data only takes 45 minutes. There has to be something weird going on with the daily data.

It takes a long time in the "Processing Pixel Conversions by Source..." step.

2011-07-14 03:25:17.184 (3) [P1T1]: Starting daily for Account #131 Fandango - Main for Wed Jul 13 00:00:00 PDT 2011

...

2011-07-14 03:30:25.945 (3) [P1T1]: Processing Pixel Conversions by Source...

2011-07-14 05:07:18.689 (3) [main]: ThreadPool finished 0/1 tasks, 1 active tasks, 1 active threads. 1 tasks remaining.

2011-07-14 05:08:18.734 (3) [main]: ThreadPool finished 0/1 tasks, 1 active tasks, 1 active threads. 1 tasks remaining.

...

2011-07-14 07:28:20.899 (3) [main]: ThreadPool finished 0/1 tasks, 1 active tasks, 1 active threads. 1 tasks remaining.

2011-07-14 07:28:54.050 (3) [P1T1]: ...done processing Pixel Conversions by Source

2011-07-14 07:28:54.051 (3) [P1T1]: Updating perf rows that don't have a bidTargetID, but should...

2011-07-14 07:29:02.835 (3) [P1T1]: ... updated 140 perf bidTargetIDs

2011-07-14 07:29:02.844 (2) [P1T1]: ... unable to update bidTargetIDs of 23 perf records!

2011-07-14 07:29:02.844 (3) [P1T1]: Updating perf totals...

2011-07-14 07:29:20.915 (3) [main]: ThreadPool finished 0/1 tasks, 1 active tasks, 1 active threads. 1 tasks remaining.

2011-07-14 07:29:26.710 (3) [P1T1]: Updating conversion totals...

2011-07-14 07:29:27.081 (3) [P1T1]: accountID[131] has no datafeed config... do regular processing

2011-07-14 07:29:27.545 (3) [P1T1]: Finished daily for Account #131 Fandango - Main on Wed Jul 13 00:00:00 PDT 2011

== log files ==

/tmp/2011-07-14/pixelsumm.sh_d_3_T_2011_07_13_a_689\,131_02\:54\:33.log

/tmp/2011-07-13/pixelsumm.sh_d_3_T_2011_07_12_a_797\,689\,774\,131_02\:45\:04.log

== start/end times ==

therouxj@xml-06:~$ grep 'daily for Account #131' /tmp/2011-07-12/pixelsumm.sh_d_3_T_2011_07_11_a_*

/tmp/2011-07-12/pixelsumm.sh_d_3_T_2011_07_11_a_597,689,774,131_01:41:37.log:2011-07-12 02:01:52.436 (3) [P2T1]: Starting daily for Account #131 Fandango - Main for Mon Jul 11 00:00:00 PDT 2011

/tmp/2011-07-12/pixelsumm.sh_d_3_T_2011_07_11_a_597,689,774,131_01:41:37.log:2011-07-12 04:47:34.235 (3) [P2T1]: Finished daily for Account #131 Fandango - Main on Mon Jul 11 00:00:00 PDT 2011

therouxj@xml-06:~$ grep 'daily for Account #131' /tmp/2011-07-11/pixelsumm.sh_d_3_T_2011_07_??a*

/tmp/2011-07-11/pixelsumm.sh_d_3_T_2011_07_10_a_649,620,580,689,774,131_01:39:33.log:2011-07-11 01:52:25.174 (3) [P2T1]: Starting daily for Account #131 Fandango - Main for Sun Jul 10 00:00:00 PDT 2011

/tmp/2011-07-11/pixelsumm.sh_d_3_T_2011_07_10_a_649,620,580,689,774,131_01:39:33.log:2011-07-11 04:40:23.977 (3) [P2T1]: Finished daily for Account #131 Fandango - Main on Sun Jul 10 00:00:00 PDT 2011

== stack trace ==

• locked <0x00002aaae0fe6bb8> (a java.lang.Object)

at com.carldunham.jst.db.Database.execute(Database.java:274)

at com.thesearchagency.perf.V2WarehouseSummarizer$AccountWorker.updateStagingIDs(V2WarehouseSummarizer.java:4219)

at com.thesearchagency.perf.V2WarehouseSummarizer$AccountWorker.processPixelConversions(V2WarehouseSummarizer.java:2134)

at com.thesearchagency.perf.V2WarehouseSummarizer$AccountWorker.run(V2WarehouseSummarizer.java:568)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

== daily vs hourly data ==

therouxj@xml-06:~$ grep 'daily for Account #131' /tmp/2011-07-14/pixelsumm.sh_d_3_T_2011_07_13_a_689\,131_02\:54\:33.log

2011-07-14 03:25:17.184 (3) [P1T1]: Starting daily for Account #131 Fandango - Main for Wed Jul 13 00:00:00 PDT 2011

2011-07-14 07:29:27.545 (3) [P1T1]: Finished daily for Account #131 Fandango - Main on Wed Jul 13 00:00:00 PDT 2011

grep 'hourly for Account #131' /tmp/2011-07-14/pixelsumm.sh_d_3_T_2011_07_13_a_689\,131_02\:54\:33.log

2011-07-14 07:31:17.142 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 00:00:00 PDT 2011

2011-07-14 07:31:53.229 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 00:00:00 PDT 2011

2011-07-14 07:31:53.235 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 01:00:00 PDT 2011

2011-07-14 07:32:23.697 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 01:00:00 PDT 2011

2011-07-14 07:32:23.733 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 02:00:00 PDT 2011

2011-07-14 07:32:53.115 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 02:00:00 PDT 2011

2011-07-14 07:32:53.179 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 03:00:00 PDT 2011

2011-07-14 07:33:06.594 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 03:00:00 PDT 2011

2011-07-14 07:33:06.628 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 04:00:00 PDT 2011

2011-07-14 07:33:34.883 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 04:00:00 PDT 2011

2011-07-14 07:33:34.933 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 05:00:00 PDT 2011

2011-07-14 07:34:04.800 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 05:00:00 PDT 2011

2011-07-14 07:34:04.813 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 06:00:00 PDT 2011

2011-07-14 07:34:45.385 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 06:00:00 PDT 2011

2011-07-14 07:34:45.392 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 07:00:00 PDT 2011

2011-07-14 07:35:41.360 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 07:00:00 PDT 2011

2011-07-14 07:35:41.385 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 08:00:00 PDT 2011

2011-07-14 07:36:57.046 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 08:00:00 PDT 2011

2011-07-14 07:36:57.059 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 09:00:00 PDT 2011

2011-07-14 07:38:55.714 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 09:00:00 PDT 2011

2011-07-14 07:38:55.725 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 10:00:00 PDT 2011

2011-07-14 07:41:27.199 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 10:00:00 PDT 2011

2011-07-14 07:41:27.212 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 11:00:00 PDT 2011

2011-07-14 07:44:19.693 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 11:00:00 PDT 2011

2011-07-14 07:44:19.708 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 12:00:00 PDT 2011

2011-07-14 07:46:50.511 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 12:00:00 PDT 2011

2011-07-14 07:46:50.518 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 13:00:00 PDT 2011

2011-07-14 07:52:20.629 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 13:00:00 PDT 2011

2011-07-14 07:52:20.634 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 14:00:00 PDT 2011

2011-07-14 07:54:43.066 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 14:00:00 PDT 2011

2011-07-14 07:54:43.072 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 15:00:00 PDT 2011

2011-07-14 07:57:02.556 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 15:00:00 PDT 2011

2011-07-14 07:57:02.561 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 16:00:00 PDT 2011

2011-07-14 07:59:19.604 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 16:00:00 PDT 2011

2011-07-14 07:59:19.621 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 17:00:00 PDT 2011

2011-07-14 08:01:38.631 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 17:00:00 PDT 2011

2011-07-14 08:01:38.643 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 18:00:00 PDT 2011

2011-07-14 08:03:57.596 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 18:00:00 PDT 2011

2011-07-14 08:03:57.600 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 19:00:00 PDT 2011

2011-07-14 08:05:54.174 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 19:00:00 PDT 2011

2011-07-14 08:05:54.191 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 20:00:00 PDT 2011

2011-07-14 08:07:24.831 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 20:00:00 PDT 2011

2011-07-14 08:07:24.848 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 21:00:00 PDT 2011

2011-07-14 08:08:21.695 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 21:00:00 PDT 2011

2011-07-14 08:08:21.700 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 22:00:00 PDT 2011

2011-07-14 08:08:51.470 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 22:00:00 PDT 2011

2011-07-14 08:08:51.475 (3) [P1T1]: Starting hourly for Account #131 Fandango - Main for Wed Jul 13 23:00:00 PDT 2011

2011-07-14 08:09:05.956 (3) [P1T1]: Finished hourly for Account #131 Fandango - Main on Wed Jul 13 23:00:00 PDT 2011