launchpad-dev team mailing list archive

[Deryck Hodge <deryck.hodge@xxxxxxxxxxxxx>]

Hi, Robert and Stuart.

On Wed, Oct 13, 2010 at 12:07 AM, Stuart Bishop
<stuart.bishop@xxxxxxxxxxxxx> wrote:
> On Wed, Oct 13, 2010 at 6:39 AM, Robert Collins
> <robert.collins@xxxxxxxxxxxxx> wrote:
>> Hi Deryck, just wanted to call your attention to
>> https://bugs.edge.launchpad.net/malone/+bug/659582 - we're seeing
>> *huge* read counts for the hwsubmission table, and we weren't back on
>> postgresql8.3, so I am guessing a submarine query somewhere has popped
>> out and is affecting us due to planner changes in 8.4.

Thanks for calling my attention to the bug.

>>
>> It may or may not be timing out, but the load on the DB can't be good
>> - if you or anyone else has ideas about where the queries are most
>> likely coming from, that would be awesome.
>>
>> Stuart, is there a mechanism to map back to the queries causing those reads?
>
> No. This information isn't available from PG, and I can't think how we
> could link things together ourselves on a live system.
>
> This isn't a PG 8.4 issue - we have seen this table spike before. If
> you look at the daily reports, it doesn't feature. To me this
> indicates one of the cronjobs, such as the one that links submissions
> to people via their email address, or possibly the incoming submission
> processing.
>

This would be my guess, too.  I also wonder if this is API script
related, i.e. some group doing HWDB analysis around the release of
Maverick.

> High read counts can be a number of things. Insane number of queries,
> insane number of queries do to Storm refreshing invalidated objects
> after a commit, full table scans because of missing indexes, full
> table scans because large numbers of rows are being retrieved, full
> table scans due to bad or broken queries. I haven't worried about them
> much as the DB is actually coping quite fine - it wastes RAM as the
> data might be pinned unnecessarily and it wastes CPU but it doesn't
> seem to have been enough wastage to adversely affect other
> connections. It does likely indicate a performance problem we can fix
> in the affected system.
>

Since this is a spike that comes and goes, and based on my reading of
Stuart's comments here that this is not critical, I'll add a card to
our bugs backlog and ask Abel to take a look when he finishes his
current branch.  It will likely be first of next week before he gets
to it, so if this more critical than that, let me know.  If so, I'll
look into it myself or have someone else take a look.

Cheers,
deryck



-- 
Deryck Hodge
https://launchpad.net/~deryck
http://www.devurandom.org/