testing HS/SR - 1 vs 2 performance
in [PgSql]
|
Prev: pg_ctl stop -m immediate on the primary server inflatessequences
Next: [HACKERS] non-reproducible failure of random test on HEAD
From: Greg Smith on 22 Apr 2010 17:41 Erik Rijkers wrote: > This is the same behaviour (i.e. extreme slow standby) that I saw earlier (and which caused the > original post, btw). In that earlier instance, the extreme slowness disappeared later, after many > hours maybe even days (without bouncing either primary or standby). > Any possibility the standby is built with assertions turned out? That's often the cause of this type of difference between pgbench results on two systems, which easy to introduce when everyone is building from source. You should try this on both systems: psql -c "show debug_assertions" just to rule that out. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg(a)2ndQuadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Mark Kirkwood on 22 Apr 2010 17:54 Greg Smith wrote: > Erik Rijkers wrote: >> This is the same behaviour (i.e. extreme slow standby) that I saw >> earlier (and which caused the >> original post, btw). In that earlier instance, the extreme slowness >> disappeared later, after many >> hours maybe even days (without bouncing either primary or standby). >> > > Any possibility the standby is built with assertions turned out? > That's often the cause of this type of difference between pgbench > results on two systems, which easy to introduce when everyone is > building from source. You should try this on both systems: > > psql -c "show debug_assertions" > > > Or even: pg_config --configure on both systems might be worth checking. regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Erik Rijkers" on 22 Apr 2010 18:32 On Thu, April 22, 2010 23:54, Mark Kirkwood wrote: > Greg Smith wrote: >> Erik Rijkers wrote: >>> This is the same behaviour (i.e. extreme slow standby) that I saw >>> earlier (and which caused the >>> original post, btw). In that earlier instance, the extreme slowness >>> disappeared later, after many >>> hours maybe even days (without bouncing either primary or standby). >>> >> >> Any possibility the standby is built with assertions turned out? >> That's often the cause of this type of difference between pgbench >> results on two systems, which easy to introduce when everyone is >> building from source. You should try this on both systems: >> >> psql -c "show debug_assertions" >> >> >> > Or even: > > pg_config --configure > > on both systems might be worth checking. (these instances are on a single server, btw) primary: $ pg_config BINDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/bin DOCDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/doc HTMLDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/doc INCLUDEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include PKGINCLUDEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include INCLUDEDIR-SERVER = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include/server LIBDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib PKGLIBDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib LOCALEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/locale MANDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/man SHAREDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share SYSCONFDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/etc PGXS = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/var/data1/pg_stuff/pg_installations/pgsql.sr_primary' '--with-pgport=6565' '--enable-depend' '--with-openssl' '--with-perl' '--with-libxml' '--with-libxslt' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,'/var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib' LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lssl -lcrypto -lz -lreadline -ltermcap -lcrypt -ldl -lm VERSION = PostgreSQL 9.0devel-sr_primary [data:port:db PGPORT=6565 PGDATA=/var/data1/pg_stuff/pg_installations/pgsql.sr_primary/data PGDATABASE=replicas] 2010.04.22 20:55:28 rijkers(a)denkraam:~/src/perl/85devel [0] $ time ./run_test_suite.sh [data:port:db PGPORT=6565 PGDATA=/var/data1/pg_stuff/pg_installations/pgsql.sr_primary/data PGDATABASE=replicas] 2010.04.22 21:00:26 rijkers(a)denkraam:~/src/perl/85devel [1] standby: $ pg_config BINDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/bin DOCDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/doc HTMLDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/doc INCLUDEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include PKGINCLUDEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include INCLUDEDIR-SERVER = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/include/server LIBDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib PKGLIBDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib LOCALEDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/locale MANDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share/man SHAREDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/share SYSCONFDIR = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/etc PGXS = /var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/var/data1/pg_stuff/pg_installations/pgsql.sr_primary' '--with-pgport=6565' '--enable-depend' '--with-openssl' '--with-perl' '--with-libxml' '--with-libxslt' CC = gcc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,'/var/data1/pg_stuff/pg_installations/pgsql.sr_primary/lib' LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lssl -lcrypto -lz -lreadline -ltermcap -lcrypt -ldl -lm VERSION = PostgreSQL 9.0devel-sr_primary $ grep -Ev '(^[[:space:]]*#)|(^$)' pgsql.sr_*ry/data/postgresql.conf pgsql.sr_primary/data/postgresql.conf:data_directory = '/var/data1/pg_stuff/pg_installations/pgsql.sr_primary/data' pgsql.sr_primary/data/postgresql.conf:port = 6565 pgsql.sr_primary/data/postgresql.conf:max_connections = 100 pgsql.sr_primary/data/postgresql.conf:shared_buffers = 256MB pgsql.sr_primary/data/postgresql.conf:checkpoint_segments = 50 pgsql.sr_primary/data/postgresql.conf:archive_mode = 'on' pgsql.sr_primary/data/postgresql.conf:archive_command= 'cp %p /var/data1/pg_stuff/dump/replication_archive/%f' pgsql.sr_primary/data/postgresql.conf:max_wal_senders = 5 pgsql.sr_primary/data/postgresql.conf:effective_cache_size= 16GB pgsql.sr_primary/data/postgresql.conf:datestyle = 'iso, mdy' pgsql.sr_primary/data/postgresql.conf:lc_messages = 'en_US.UTF-8' pgsql.sr_primary/data/postgresql.conf:lc_monetary = 'en_US.UTF-8' pgsql.sr_primary/data/postgresql.conf:lc_numeric = 'en_US.UTF-8' pgsql.sr_primary/data/postgresql.conf:lc_time = 'en_US.UTF-8' pgsql.sr_primary/data/postgresql.conf:default_text_search_config = 'pg_catalog.english' pgsql.sr_slavery/data/postgresql.conf:data_directory = '/var/data1/pg_stuff/pg_installations/pgsql.sr_slavery/data' pgsql.sr_slavery/data/postgresql.conf:port = 6566 pgsql.sr_slavery/data/postgresql.conf:max_connections = 100 pgsql.sr_slavery/data/postgresql.conf:shared_buffers = 256MB pgsql.sr_slavery/data/postgresql.conf:checkpoint_segments = 50 pgsql.sr_slavery/data/postgresql.conf:archive_mode = 'on' pgsql.sr_slavery/data/postgresql.conf:archive_command= 'cp %p /var/data1/pg_stuff/dump/replication_archive/%f' pgsql.sr_slavery/data/postgresql.conf:max_wal_senders = 5 pgsql.sr_slavery/data/postgresql.conf:effective_cache_size= 16GB pgsql.sr_slavery/data/postgresql.conf:datestyle = 'iso, mdy' pgsql.sr_slavery/data/postgresql.conf:lc_messages = 'en_US.UTF-8' pgsql.sr_slavery/data/postgresql.conf:lc_monetary = 'en_US.UTF-8' pgsql.sr_slavery/data/postgresql.conf:lc_numeric = 'en_US.UTF-8' pgsql.sr_slavery/data/postgresql.conf:lc_time = 'en_US.UTF-8' pgsql.sr_slavery/data/postgresql.conf:default_text_search_config = 'pg_catalog.english' I only now notice archive_mode = 'on' on the standby. That doesn't make sense of course - I'll remove that. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 22 Apr 2010 18:45 On Thu, 2010-04-22 at 20:39 +0200, Erik Rijkers wrote: > On Sun, April 18, 2010 13:01, Simon Riggs wrote: > any comment is welcome... Please can you re-run with -l and post me the file of times Please also rebuild using --enable-profile so we can see what's happening. Can you also try the enclosed patch which implements prefetching during replay of btree delete records. (Need to set effective_io_concurrency) Thanks for your further help. -- Simon Riggs www.2ndQuadrant.com
From: Mark Kirkwood on 22 Apr 2010 20:44
Erik Rijkers wrote: > > This is the same behaviour (i.e. extreme slow standby) that I saw earlier (and which caused the > original post, btw). In that earlier instance, the extreme slowness disappeared later, after many > hours maybe even days (without bouncing either primary or standby). > > I have no idea what could cause this; is no one else is seeing this ? > > (if I have time I'll repeat on other hardware in the weekend) > > any comment is welcome... > > > I wonder if what you are seeing is perhaps due to the tables on the primary being almost completely cached (from the initial create) and those on the standby being at best partially so. That would explain why the standby performance catches up after a while ( when its tables are equivalently cached). One way to test this is to 'pre-cache' the standby by selecting every row from its tables before running the pgbench test. regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |