Thread: [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%