Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Alvaro Herrera wrote: Excerpts from Tom Lane's message of jue jun 10 11:46:25 -0400 2010: Yes, the folks at commandprompt need to be told about this. Loudly. It's a serious packaging error. Just notified Lacey, the packager (not so loudly, though); she's working on new packages, and apologizes for the inconvenience. Hello Everyone, New packages for 8.4.4 on CentOS 5.5 and RHEL 5.5 (all arches), have been built, and are available in the PGDG repo. http://yum.pgsqlrpms.org/8.4/redhat/rhel-5-i386/ http://yum.pgsqlrpms.org/8.4/redhat/rhel-5-x86_64/ Output from pg_config --configure --version is below. x86_64: '--build=x86_64-redhat-linux-gnu' '--host=x86_64-redhat-linux-gnu' '--target=x86_64-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib64' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--disable-rpath' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-krb5' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--enable-thread-safety' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql' '--with-docdir=/usr/share/doc' 'build_alias=x86_64-redhat-linux-gnu' 'host_alias=x86_64-redhat-linux-gnu' 'target_alias=x86_64-redhat-linux-gnu' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et' 'CPPFLAGS= -I/usr/include/et' PostgreSQL 8.4.4 i386: '--build=i686-redhat-linux-gnu' '--host=i686-redhat-linux-gnu' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--disable-rpath' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib' '--with-openssl' '--with-pam' '--with-krb5' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib' '--enable-nls' '--enable-thread-safety' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--datadir=/usr/share/pgsql' '--with-docdir=/usr/share/doc' 'build_alias=i686-redhat-linux-gnu' 'host_alias=i686-redhat-linux-gnu' 'target_alias=i386-redhat-linux-gnu' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -I/usr/include/et' 'CPPFLAGS= -I/usr/include/et' PostgreSQL 8.4.4 Again, I extend deep apologies for the inconvenience. If there is anything further we can help with, please let us know. Regards, Lacey -- Lacey Powers The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 ext 104 PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Greg Smith greg at 2ndquadrant.com writes: Max Williams wrote: Can I just turn this off on 8.4.4 or is it a compile time option You can update your postgresql.conf to include: debug_assertions = false And restart the server. This will buy you back *some* of the performance loss but not all of it. Will have to wait for corrected packaged to make the issue completely go away. Ah! I am so thankful I found this thread. We've been having the same issues described here. And when I do a SHOW debug_assertions I get: postgres=# show debug_assertions; debug_assertions -- on (1 row) Can you let us know when the corrected packages have become available? Regards, John -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Well the packages are from the pgdg repo which I would have thought are pretty common? https://public.commandprompt.com/projects/pgcore/wiki -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: 10 June 2010 02:52 To: Max Williams Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com wrote: Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote: On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com wrote: Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... Given that there are 2 recent reports on the same issue, I wonder if the new packages were built with debugging options or not. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
How do I tell if it was built with debugging options? -Original Message- From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org] Sent: 10 June 2010 09:30 To: Robert Haas Cc: Max Williams; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? On Wed, 2010-06-09 at 21:51 -0400, Robert Haas wrote: On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com wrote: Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... Given that there are 2 recent reports on the same issue, I wonder if the new packages were built with debugging options or not. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com writes: How do I tell if it was built with debugging options? Run pg_config --configure and see if --enable-cassert is mentioned. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
I'm afraid pg_config is not part of the pgdg packages. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 10 June 2010 15:11 To: Max Williams Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? Max Williams max.willi...@mflow.com writes: How do I tell if it was built with debugging options? Run pg_config --configure and see if --enable-cassert is mentioned. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com wrote: I'm afraid pg_config is not part of the pgdg packages. Connect (using psql or your favorite client) and run: show debug_assertions; -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com writes: I'm afraid pg_config is not part of the pgdg packages. Sure it is. They might've put it in the -devel subpackage, though. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Ah, yes its OFF for 8.4.3 and ON for 8.4.4! Can I just turn this off on 8.4.4 or is it a compile time option? Also is this a mistake or intended? Perhaps I should tell the person who builds the pgdg packages?? Cheers, Max -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: 10 June 2010 16:16 To: Max Williams; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4? Max Williams max.willi...@mflow.com wrote: I'm afraid pg_config is not part of the pgdg packages. Connect (using psql or your favorite client) and run: show debug_assertions; -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams max.willi...@mflow.com writes: Ah, yes its OFF for 8.4.3 and ON for 8.4.4! Hah. Can I just turn this off on 8.4.4 or is it a compile time option? Well, you can turn it off, but that will only buy back part of the cost (and not even the bigger part, I believe). Also is this a mistake or intended? Perhaps I should tell the person who builds the pgdg packages?? Yes, the folks at commandprompt need to be told about this. Loudly. It's a serious packaging error. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Excerpts from Tom Lane's message of jue jun 10 11:46:25 -0400 2010: Yes, the folks at commandprompt need to be told about this. Loudly. It's a serious packaging error. Just notified Lacey, the packager (not so loudly, though); she's working on new packages, and apologizes for the inconvenience. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Max Williams wrote: Can I just turn this off on 8.4.4 or is it a compile time option You can update your postgresql.conf to include: debug_assertions = false And restart the server. This will buy you back *some* of the performance loss but not all of it. Will have to wait for corrected packaged to make the issue completely go away. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Hi, I was doing some benchmarking while changing configuration options to try to get more performance out of our postgresql servers and noticed that when running pgbench against 8.4.3 vs 8.4.4 on identical hardware and configuration there is a large difference in performance. I know tuning is a very deep topic and benchmarking is hardly an accurate indication of real world performance but I was still surprised by these results and wanted to know what I am doing wrong. OS is CentOS 5.5 and the postgresql packages are from the pgdg repo. Hardware specs are: 2x Quad core Xeons 2.4Ghz 16GB RAM 2x RAID1 7.2k RPM disks (slow I know, but we are upgrading them soon..) Relevant Postgresql Configuration: max_connections = 1000 shared_buffers = 4096MB temp_buffers = 8MB max_prepared_transactions = 1000 work_mem = 8MB maintenance_work_mem = 512MB wal_buffers = 8MB checkpoint_segments = 192 checkpoint_timeout = 30min effective_cache_size = 12288MB Results for the 8.4.3 (8.4.3-2PGDG.el5) host: [r...@some-host ~]# pgbench -h dbs3 -U postgres -i -s 100 pgbench1 /dev/null 21 pgbench -h dbs3 -U postgres -c 100 -t 10 pgbench1 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 query mode: simple number of clients: 100 number of transactions per client: 10 number of transactions actually processed: 1000/1000 tps = 5139.554921 (including connections establishing) tps = 5140.325850 (excluding connections establishing) opreport: CPU: Intel Core/i7, speed 2394.07 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 10 CPU_CLK_UNHALT...| samples| %| -- 37705832 61.3683 postgres 18472598 30.0652 no-vmlinux 4982274 8.1089 libc-2.5.so 138517 0.2254 oprofiled 134628 0.2191 libm-2.5.so 1465 0.0024 libc-2.5.so 1454 0.0024 libperl.so 793 0.0013 libdcsupt.so.5.9.2 444 7.2e-04 dsm_sa_datamgrd CPU_CLK_UNHALT...| samples| %| -- 401 90.3153 dsm_sa_datamgrd 43 9.6847 anon (tgid:8013 range:0xe000-0xf000) 410 6.7e-04 libxml2.so.2.6.26 356 5.8e-04 ld-2.5.so 332 5.4e-04 libnetsnmp.so.10.0.3 327 5.3e-04 dsm_sa_snmpd CPU_CLK_UNHALT...| samples| %| -- 255 77.9817 dsm_sa_snmpd 72 22.0183 anon (tgid:8146 range:0xe000-0xf000) 304 4.9e-04 libcrypto.so.0.9.8e 290 4.7e-04 libpthread-2.5.so 199 3.2e-04 libdcsmil.so.5.9.2 139 2.3e-04 modclusterd snip Results for the 8.4.4 (8.4.4-1PGDG.el5) host: [root@ some-host ~]# pgbench -h dbs4 -U postgres -i -s 100 pgbench1 /dev/null 21 pgbench -h dbs4 -U postgres -c 100 -t 10 pgbench1 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 query mode: simple number of clients: 100 number of transactions per client: 10 number of transactions actually processed: 1000/1000 tps = 2765.643549 (including connections establishing) tps = 2765.931203 (excluding connections establishing) opreport: CPU: Intel Core/i7, speed 2394.07 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 10 CPU_CLK_UNHALT...| samples| %| -- 312481395 84.5038 postgres 41861164 11.3204 no-vmlinux 14290652 3.8646 libc-2.5.so 812148 0.2196 oprofiled 305909 0.0827 libm-2.5.so 7647 0.0021 libc-2.5.so 3809 0.0010 libdcsupt.so.5.9.2 3077 8.3e-04 libperl.so 2302 6.2e-04 dsm_sa_datamgrd CPU_CLK_UNHALT...| samples| %| -- 2113 91.7897 dsm_sa_datamgrd 189 8.2103 anon (tgid:8075 range:0xe000-0xf000) 2175 5.9e-04 libxml2.so.2.6.26 1455 3.9e-04 dsm_sa_snmpd CPU_CLK_UNHALT...| samples| %| -- 1226 84.2612 dsm_sa_snmpd 229 15.7388 anon (tgid:8208 range:0xe000-0xf000) 1227 3.3e-04 libdchipm.so.5.9.2 1192 3.2e-04 libpthread-2.5.so 804 2.2e-04 libnetsnmp.so.10.0.3 745 2.0e-04 modclusterd snip Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Cheers, Max
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
Can you give the config params for those : fsync = synchronous_commit = wal_sync_method = Also, some vmstat 1 output during the runs would be interesting. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Large (almost 50%!) performance drop after upgrading to 8.4.4?
On Wed, Jun 9, 2010 at 6:56 AM, Max Williams max.willi...@mflow.com wrote: Any input? I can reproduce these numbers consistently. If you need more information then just let me know. By the way, I am a new postgresql user so my experience is limited. Maybe different compile options? If we'd really slowed things down by 50% between 8.4.3 and 8.4.4, there'd be an awful lot of people screaming about it... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
