Phoenix Kiula wrote: > On 04/09/07, Tom Lane <tgl@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx 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 // ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
