Re: Finding bottleneck

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 - tuner(at)bdb(dot)fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""