Re: Statistics collection question - Mailing list pgsql-general

On 04/09/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> > On 04/09/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >> Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select
> >> statement times?
>
> > Because the statement has been executed and is in the cache.
>
> That answer is way too flippant.  In particular it doesn't explain your
> repeated 80sec queries --- you should have enough memory in that thing
> to be caching a fair amount of your data.
>
> 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
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!

As for "network problems delaying transmission of data" -- not sure
what this means. MySQL is super fast on the very same system. Does
pgsql require anything different?

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
devoured as many google-groups archives of this list as I possibly
can. I am looking at plenty of catalogue and stats tables (a utility
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!

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
concurrent selects (about 45 million a day). Works fine when I
restart, but a day later all goes cattywumpus.

TIA!