Reporting: Yell FTG-1 comparison report causes database crash

Hi Bill,

Thanks for your explanation regarding Yell FTG-1 account issue. It is really
helpful to know that if we try non comparison report for long running requests
it might work.

We really need the data for following parameters only,

resolution: Daily
>
> startdate: 2009-01-01 00:00:00
>
> enddate: 2009-01-31 23:59:59
>
> startCompDate: 2008-12-01 00:00:00
>
> endCompDate: 2008-12-31 23:59:59

So as per your suggestion we are going to try the manual workaround by
requesting two non comparison reports and doing comparison manually.

I will keep you posted regarding the progress of this issue.

Thanks,
-Abhiram

----Original Message----
From: Bill Duffy
Sent: Thursday, February 05, 2009 8:01 AM
To: Abhiram Bhagwat
Cc: Ranil Wiratunga; Frank Gamboa
Subject: [Fwd: CCC Account Size Limits]

Abhi,

So is the problem you need:

resolution: Daily
>
> startdate: 2009-01-01 00:00:00
>
> enddate: 2009-01-31 23:59:59
>
> startCompDate: 2008-12-01 00:00:00
>
> endCompDate: 2008-12-31 23:59:59

were any of the workarounds acceptable? The fix is largely outside my
hands, but I need to be able to provide this info in order to help
engineering set priority on this.

Thanks,

Bill

-------- Forwarded Message --------
> From: Ranil Wiratunga <Ranil@thesearchagency.com>
> To: John Stanforth <John.Stanforth@thesearchagency.com>, Carl A.
> Dunham <carl@thesearchagency.com>, Ted Ives <Ted@thesearchagency.com>
> Cc: Mike Jarvinen <mikej@thesearchagency.com>, Frank Lee
> <Frank@thesearchagency.com>, Barbara Palmer
> <Barbara@thesearchagency.com>, Bill Duffy <Bill@thesearchagency.com>,
> Jeffrey Collemer <JCollemer@thesearchagency.com>, David Costantino
> <dcostantino@thesearchagency.com>, Silvia Anghel
> <Silvia@thesearchagency.com>, Sharon Burt <Sharon@thesearchagency.com>
> Subject: CCC Account Size Limits
> Date: Tue, 3 Feb 2009 15:31:33 -0800
>
> Team,
>
>
>
> I think Bill and Dave Costantino did a great job of describing what is
> going on here. (I have included Bill’s email below.)
>
>
>
> One thing I want to do is connect this to a current business
> situation. Yell.com is a large SEM partner that we split into five
> CCC accounts. We used the size limitation of 1,000,000 keywords per
> CCC account. Reports will normally only pull keywords with traffic
> which is a much smaller number than the number of keywords in an
> account. We noticed that one Yell account had over 200,000 keywords
> with traffic in January. Per Bill’s notes below, we are having issues
> pulling these large reports in the CCC and Web Services.
>
>
>
> I want to illuminate this as an issue as Yell is expecting our
> standard reporting which requires specific data. Also, I want to make
> sure I loop in everyone before this becomes an even larger issue with
> the client. Ideally, we’d like to deliver the standard TSA reporting
> to Yell.
>
>
>
> Thanks,
>
> Ranil
>
>
>
> --------------------------------------------------------------------
>
>
>
> Forgive the long cc list but this issue has reached me from a couple
> of different channels and levels now so I thought it best I provide an
> explanation to all.
>
>
>
> There are really two issues here. The first is with the request that
> was made. It appears you are running a comparison report:
>
>
>
> resolution: Daily
>
> startdate: 2009-01-01 00:00:00
>
> enddate: 2009-01-31 00:00:00
>
> startCompDate: 2008-12-01 00:00:00
>
> endCompDate: 2008-12-31 00:00:00
>
>
>
> If you are looking to do a monthly comparison report, this should
> really
>
> be:
>
>
>
> resolution: Monthly
>
> startdate: 2009-01-01 00:00:00
>
> enddate: 2009-01-31 23:59:59
>
> startCompDate: 2008-12-01 00:00:00
>
> endCompDate: 2008-12-31 23:59:59
>
>
>
> This change will allow the queries run against the warehouse to use
> monthly tables that have the data you need pre-aggregated. Right now,
> the request is using the daily tables and doing sorts and aggregations
> on some extremely large data sets.
>
>
>
> I realize there is a chance that you really want:
>
>
>
> resolution: Daily
>
> startdate: 2009-01-01 00:00:00
>
> enddate: 2009-01-31 23:59:59
>
> startCompDate: 2008-12-01 00:00:00
>
> endCompDate: 2008-12-31 23:59:59
>
>
>
> If that is the case, the queries run will still use the daily tables
> and you will run into the same problem you are currently experiencing
> - which brings up the second issue. We have some resource limitations
> at both the database and application server level. These limits may be
> leading to a long run query which triggers the database restart due to
> a MySQL bug. Some details and recommendations are provided by Dave
> Constantino are below.
>
>
>
> 1) Out-of-memory errors on JBoss. The current server has 4 gigs of
> RAM.
>
> JBoss is configured with a 2.5 gig heap size, 512 meg "perm gen", not
> leaving us much room for growth. I recommend we upgrade the RAM (and
> possibly the server) to at least 8 gigs, preferably 16.
>
>
>
>
>
> 2) Database restarts due to MySQL bugs. During some long running
> queries, we see "long semaphore wait" errors from MySQL, followed by a
> restart. Upgrading MySQL might take care of these errors, but will
> require a lengthy validation process on our side to determine that
> new/additional issues have not been introduced. The related MySQL bugs
>
> are: http://bugs.mysql.com/bug.php?id=32149
>
> http://bugs.mysql.com/bug.php?id=20358
>
> http://bugs.mysql.com/bug.php?id=25645
>
>
>
>
>
> 3) Disk IO limitations on the warehouse database servers. The 2
> warehouse database servers are each using 2 drives in a software RAID1
> configuration for DB data. IO appears to be a major bottleneck on
> these systems. Example: at the moment, one of the systems is 74% idle,
> 24% IO wait, with 2% CPU utilized. I recommend we move to a
> configuration using an external storage array, which will dramatically
> increase IO performance and allow reports to run more quickly.
>
>
>
> So, I hope you can get by with the altered monthly request above or
> perhaps by running non-comparison reports and doing the comparison
> manually.
>
>
>
> Hope this helps you out a bit - and thanks to Dave for the help
> analyzing these issues.
>
>
>
> Bill
>
>
>
>
>
>
>
>
>
> Ranil Wiratunga | Senior Manager, Strategic Programs
> The Search Agency, Inc. – Results Driven Search Marketing and
> Optimization
> Phone: 310-582-5746 | Mobile: 818-823-3096 | Fax: 310-452-2422
> www.thesearchagency.com
>
> :: Named a Rising Star on Deloitte’s Technology Fast 500 ::
>
> Please consider the environment before printing this e-mail.
>
>
>
>