could be that the checkpoints are done too seldom.
what is your wal checkpoint config?
Kristo
On 07.06.2007, at 0:27, Scott Marlowe wrote:
Gunther Mayer wrote:
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?
Maybe your hard drive is set to spin down after a certain period of
idle, and since most all your data is coming from memory, then it
might be that on the rare occasion when it needs to hit the drive
it's not spun up anymore.
Maybe some other process is cranking up (cron jobs???) that are
chewing up all your I/O bandwidth?
Hard to say. Anything in the system logs that would give you a
hint? Try correlating them by the time of the slow pgsql queries.
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
