Large (almost 50%!) performance drop after upgrading to 8.4.4?

От: Max Williams
Тема: Large (almost 50%!) performance drop after upgrading to 8.4.4?
Дата: ,
Msg-id: 0357196CB603794BB76F4F6B373F27330506EACE45@SERVER.ddnglobal.local
(см: обсуждение, исходный текст)
Ответы: Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  ("Pierre C")
Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Robert Haas)
Список: pgsql-performance

Скрыть дерево обсуждения

Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Max Williams, )
 Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  ("Pierre C", )
 Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Robert Haas, )
  Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Max Williams, )
  Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Devrim GÜNDÜZ, )
   Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Max Williams, )
    Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Tom Lane, )
     Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Max Williams, )
      Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  ("Kevin Grittner", )
       Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Max Williams, )
        Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Tom Lane, )
         Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Alvaro Herrera, )
          Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Lacey Powers, )
        Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Greg Smith, )
      Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (Tom Lane, )
 Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?  (John Reeve, )

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:

[root@some-host ~]# pgbench -h dbs3 -U postgres -i -s 100 pgbench1 > /dev/null 2>&1 && pgbench -h dbs3 -U postgres -c 100 -t 100000 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: 100000

number of transactions actually processed: 10000000/10000000

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 100000

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:0xffffe000-0xfffff000)

      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:0xffffe000-0xfffff000)

      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 2>&1 && pgbench -h dbs4 -U postgres -c 100 -t 100000 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: 100000

number of transactions actually processed: 10000000/10000000

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 100000

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:0xffffe000-0xfffff000)

     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:0xffffe000-0xfffff000)

     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


В списке pgsql-performance по дате сообщения:

От: "Pierre C"
Дата:
Сообщение: Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?
От: Robert Haas
Дата:
Сообщение: Re: No hash join across partitioned tables?