Re: VERY slow queries at random

From: Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net>
To: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
Cc: Gunther Mayer <gunther(dot)mayer(at)googlemail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: VERY slow queries at random
Date: 2007-06-07 07:09:25
Message-ID: D7DB4C26-9247-43DD-A765-E81CA0D71DFC@skype.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marc Mamin 2007-06-07 09:17:40 copy from performance on large tables with indexes
Previous Message david 2007-06-07 05:22:26 Re: Thousands of tables versus on table?