DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=beta;
	h=received:message-id:date:user-agent:mime-version:to:subject:content-type:content-transfer-encoding:from;
	b=qflmi0n1MHyjR5OhIPtxk9Fy08K/YJ5ige9OCbb+sCMsy/p34NWMPIlPqE7hQUFUCrvtoz6BENtMHthf7wK5GRjD+Ke6osIc8ixDUxVHp9WvWXE2Gwz/z92blkZMTBtdThmVikboick8taE15R7C5+PBRFkCQZH3hsYWSQek1xk=
Message-ID: <46670916.9090505@gmail.com>
Date: Wed, 06 Jun 2007 21:20:54 +0200
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: VERY slow queries at random
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From: Gunther Mayer <gunther.mayer@googlemail.com>

Hi there,

We run a small ISP with a FreeBSD/freeradius/postgresql 8.2.4 backend
and 200+ users. Authentication happens via UAM/hotspot and I see a lot
of authorisation and accounting packets that are handled via PL/PGSQL
functions directly in the database.

Everything seems to work 100% except that a few times a day I see

Jun  6 10:41:31 caligula postgres[57347]: [4-1] radiususer: LOG:
duration: 19929.291 ms  statement: SELECT fn_accounting_start(...)

in my logs. I'm logging slow queries with log_min_duration_statement =
500 in my postgresql.conf. Sometimes another query runs equally slow or
even slower (I've seen 139 seconds!!!) a few minutes before or after as
well, but then everything is back to normal.

Even though I haven't yet indexed my data I know that the system is
performant because my largest table (the accounting one) only has 5000+
rows, the entire database is only a few MB's and I have plenty of memory
(2GB), shared_buffers = 100MB and max_fsm_pages = 179200.  Also from
briefly enabling

log_parser_stats = on
log_planner_stats = on
log_executor_stats = on

I saw that most queries are 100% satisfied from cache so the disk
doesn't even get hit. Finally, the problem seems unrelated to load
because it happens at 4am just as likely as at peak traffic time.

What the heck could cause such erratic behaviour? I suspect some type of
resource problem but what and how could I dig deeper?

Gunther