Обсуждение: [OT] Slony Triggers pulling down performance?

Just wondering if my 'Perceived' feeling that since implementing slony
for master/slave replication of select tables, my master database
performance is getting slower.

I'm constantly seeing a very high amount of IO wait. ~40-80 according to
vmstat 1

and according to atop. (hdb/hdc = raid1 mirror)
DSK |         hdb | busy     83% | read    1052 | write     50 | avio    7 ms |
DSK |         hdc | busy     81% | read    1248 | write     49 | avio    6 ms |

Ow.Mun.Heng@wdc.com (Ow Mun Heng) writes:
> Just wondering if my 'Perceived' feeling that since implementing slony
> for master/slave replication of select tables, my master database
> performance is getting slower.
>
> I'm constantly seeing a very high amount of IO wait. ~40-80 according to
> vmstat 1
>
> and according to atop. (hdb/hdc = raid1 mirror)
> DSK |         hdb | busy     83% | read    1052 | write     50 | avio    7 ms |
> DSK |         hdc | busy     81% | read    1248 | write     49 | avio    6 ms |

The triggers generate some extra I/O, as they go off and write tuples
into sl_log_1/sl_log_2, so there's certainly a cost, there.

When you pull data from sl_log_1/sl_log_2, that will have a cost, too.

Replication does not come at zero cost...
--
(format nil "~S@~S" "cbbrowne" "linuxdatabases.info")
http://www3.sympatico.ca/cbbrowne/finances.html
"Power tends  to corrupt and absolute power  corrupts absolutely."
-- First Baron Acton, 1834 - 1902

On Fri, Jan 18, 2008 at 03:14:41PM +0800, Ow Mun Heng wrote:
> Just wondering if my 'Perceived' feeling that since implementing slony
> for master/slave replication of select tables, my master database
> performance is getting slower.

It imposes a performance penalty, yes.

A

On Fri, 2008-01-18 at 14:57 -0500, Chris Browne wrote:
> Ow.Mun.Heng@wdc.com (Ow Mun Heng) writes:
> > Just wondering if my 'Perceived' feeling that since implementing slony
> > for master/slave replication of select tables, my master database
> > performance is getting slower.
> >
> > I'm constantly seeing a very high amount of IO wait. ~40-80 according to
> > vmstat 1
> >
> > and according to atop. (hdb/hdc = raid1 mirror)
> > DSK |         hdb | busy     83% | read    1052 | write     50 | avio    7 ms |
> > DSK |         hdc | busy     81% | read    1248 | write     49 | avio    6 ms |
>
> The triggers generate some extra I/O, as they go off and write tuples
> into sl_log_1/sl_log_2, so there's certainly a cost, there.
>
> When you pull data from sl_log_1/sl_log_2, that will have a cost, too.
>
> Replication does not come at zero cost...


I've been battling with this issus for the past week and that prompted a
few changes in the manner I pull the data and in the location where i
store the data. I ended up implementing partitioning on the 2 main
largest (problematic) tables and put it intp weekly rotation and moved
the broke the 3 disk raid1(1 spare) spare disk and used that as the
slony-I sl_log_1/sl_log_2 tablespace.

Now, everything is back to normal. (until I break it again!!) IO Wait is
hovering between 0-40%