Re: Statistics collection question

Phoenix Kiula wrote:
> On 04/09/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm wondering about some transaction taking exclusive lock on the table
>> and sitting on it for a minute or so, and also about network problems
>> delaying transmission of data to the client.
>>
> How can I check what is causing the lack? When I restart pgsql it goes

You mean the lock?

You can check for active locks querying pg_locks

> away. The log is empty for a day or too (I'm only logging errors or
> slow queries) and the queries are super fast, but after a day it
> starts filling up with abysmally slow queries, even on simple queries
> with the WHERE clauses that have only one constant on the indexed
> column!

That's new information that we could have used earlier, as it means that
postgres does pick the right plan (at least initially) and things like
network and dns apparently work.

Was the explain analyze you sent from the super fast periods or from a
slow period? It'd be interesting to see a query plan of a problematic query.

I suppose if you try one of your super fast queries it is slow once
other queries slow down too? I ask, because I expect that query to not
be in the cache at that moment, so it could be a good candidate for an
explain analyze.

> Basically, what I am missing is some info on actually tweaking the
> postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and
> Exim (email server) on the same dedicated hosting server. I don't mind
> if Postgres hogs 2GB of memory, but I need to know how to tweak it. I
> have made about eight posts on this list with my entire
> postgresql.conf posted in. I have read and re-read the manual and

Yes, but you gave us conflicting information. Only now it is clear what
your problem is.

> that makes compiles all of it and presents the system's missteps and
> guidelines may be useful, ala "Tuning Primer" script from MySQL camp)
> but I am not sure where to begin!

I've seen pgadmin III doing quite a nice job at that. Haven't really
used it myself, I usually prefer the command line.

> Would appreciate any help. Why do indexed queries take so much time?
> It's a simple DB with "10 relations" including tables and indexes.
> Simple inserts and updates, about 5000 a day, but non-trivial

It looks like your indexes get bloated. Do you vacuum enough?
It'd be a good idea to at least analyze the tables involved in those
inserts regularly.

If you do those inserts in a batch, be sure to call ANALYZE after
commiting that batch. That helps quite a bit.

Besides that... How are those disks configured? You didn't put them in a
raid-5 array I hope? That wouldn't explain the above problem, but it
would slow things down (such has been mentioned on this list a few
times) and may thus be exaggerating the problem.

Good luck!

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //