From: Russell Garrett <rg@tcslon.com>
To: pgsql-performance@postgresql.org
Subject: Re: Queries sometimes take 1000 times the normal time
Date: Thu, 28 Aug 2003 10:02:46 +0100
Message-ID: <MKEGJINFADFODDNOKEJCEECAEAAA.rg@tcslon.com> (raw)
In-Reply-To: <bijdiq$tor$1@harrier.cohaesio.com>
We have a somewhat similar situation - we're running a fairly constant, but
low priority, background load of about 70 selects and 40 inserts per second
(batched into fairly large transactions), and on top of that we're trying to
run time-sensitive queries for a web site (well two). I should emphasize
that this is low low priority - if a query is delayed by an hour here, it
doesn't matter.
The web site queries will jump up one or two orders of magnitude (I have
seen a normally 100ms query take in excess of 30 seconds) in duration at
seemingly random points. It's not always when the transactions are
committing, and it doesn't seem to be during checkpointing either. The same
thing happens with WAL switched off. It appears to happen the first time the
query runs after a while. If I run the same query immediately afterwards, it
will take the normal amount of time.
Any ideas?
Cheers,
Russ Garrett
pgsql-performance-owner@postgresql.org wrote:
> Subject: [PERFORM] Queries sometimes take 1000 times the normal time
>
>
> Hello,
>
> We're running a set of Half-Life based game servers that lookup user
> privileges from a central PostgreSQL 7.3.4 database server (I recently
> ported the MySQL code in Adminmod to PostgreSQL to be able to do
> this).
>
> The data needed by the game servers are combined from several
> different tables, so we have some views set up to provide the data in
> the format needed.
>
> Currently there's only a few users in the database for testing
> purposes, and most of the time the user lookup's take 2-3 ms (I have
> syslog'ing of queries and duration turned on), but several times per
> hour the duration for one of the queries is 2-3 seconds (1000 times
> larger), while the surrounding lookups take the usual 2-3 ms.
>
> This is rather critical, as the game server software isn't asynchonous
> and thus waits for a reply before continuing, so when someone
> connects, and the user lookup happens to have one of these very long
> durations, the players on this server experience a major lag spike,
> which isn't very popular :-(
>
> All the game servers and the database server are connected to the same
> switch, so I don't think, that it is a network problem.
>
> So far I've been unable to locate the problem, so any suggestions are
> very welcome.
>
> Regards,
> Anders K. Pedersen
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rg@tcslon.com
Subject: Re: Queries sometimes take 1000 times the normal time
In-Reply-To: <MKEGJINFADFODDNOKEJCEECAEAAA.rg@tcslon.com>
References: <bijdiq$tor$1@harrier.cohaesio.com> <MKEGJINFADFODDNOKEJCEECAEAAA.rg@tcslon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox