Re: VERY slow queries at random

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.