Re: Queries sometimes take 1000 times the normal time

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(at)postgresql(dot)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