On Mon, 8 Aug 2005, Tom Lane wrote:
What that sounds like to me is a machine with inadequate disk I/O bandwidth.
Your earlier comment that checkpoint drives the machine into the ground
fits right into that theory, too. You said there is "almost no IO-wait"
but are you sure you are measuring that correctly?
Currently there's some iowait caused by "fragmentation" of the comment
table. Periodic clustering helps a lot.
Disk configurations looks something like this:
sda: data (10 spindles, raid10)
sdb: xlog & clog (2 spindles, raid1)
sdc: os and other stuff
Usually iostat (2 second interval) says:
avg-cpu: %user %nice %sys %iowait %idle
32.38 0.00 12.88 11.62 43.12
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 202.00 1720.00 0.00 3440 0
sdb 152.50 4.00 2724.00 8 5448
sdc 0.00 0.00 0.00 0 0
And during checkpoint:
avg-cpu: %user %nice %sys %iowait %idle
31.25 0.00 14.75 54.00 0.00
Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 3225.50 1562.00 35144.00 3124 70288
sdb 104.50 10.00 2348.00 20 4696
sdc 0.00 0.00 0.00 0 0
I think (insufficiency of) disk IO shouldn't cause those lingering queries
because dataset is rather small and it's continuously accessed. It should
fit into cache and stay there(?)
400 queries? Are you launching 400 separate backends to do that?
Well yes. That's the common problem with php and persistent connections.
Some sort of connection pooling seems like a good idea, if you don't
have it in place already.
pg_pool for example? I'm planning to give it a try.
regards, tom lane
|\__/|
( oo ) Kari Lavikka - [EMAIL PROTECTED] - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
