launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

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.
>
> 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.

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.

-- 
Stuart Bishop <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/