Re: pgbench - tps for Postgresql-9.0.2 is more than tps for Postgresql-8.4.1

Pg 9.0.2 is performing better than pg8.4.1

There are more transactions per second in pg9.0.2 than in pg8.4.1, which is
a better thing.

also below are kernel parameters that i used.

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 15099492
max total shared memory (kbytes) = 15099492
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 8192
max semaphores per array = 250
max semaphores system wide = 2048000
max ops per semop call = 32
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

Is there anything that i can do to still improve 9.0.2 performance. the
performance (tps) that i got is only 10% is it ideal, or should i need to
get more?

Thanks
Deepak

On Wed, Jan 26, 2011 at 7:12 PM, DM <dm(dot)aeqa(at)gmail(dot)com> wrote:

> Hello All,
>
> I did a pgbench marking test and by comparing the tps of both versions, it
> looks like 8.4.1 performing better than 9.0.2.
>
> Let me know if I need to make any changes to Postgresql.conf of 9.0.2 file
> to improve its performance
>
>
> =========================================================================================
> Server Information:
> OS - CentOS - version 4.1.2
> CPU - Intel(R) Xeon(R) CPU X5550 @ 2.67GHz
> 16 CPUS total
> RAM - 16GB
> ===============================
>
> Postgresql 8.4.1
> shared_buffers = 4GB
> checkpoint_segments = 3
> checkpoint_completion_target = 0.5
> wal_buffers = 64kB
> max_connections = 4096
>
> Postgresql 9.0.2
> shared_buffers = 4GB
> checkpoint_segments = 3
> checkpoint_completion_target = 0.5
> wal_buffers = 64KB
> max_connections = 4096
>
> (rest parameters are default)
> =====================================
> 8.4.1 Analysis
>
> Iterations, Trans_type, Scale, Query_Mode, Clients, no.trans/client, no.
> trans processed, tps (wih connections estab), tps (without connections
> estab), DB Size
> 1/1, SELECT, 1, simple, 32, 2000, 64000/64000, 66501.728525, 70463.861398,
> 21 MB
> 1/2, SELECT, 1, simple, 32, 2000, 64000/64000, 66743.003977, 70702.841481,
> 21 MB
> 1/3, SELECT, 1, simple, 32, 2000, 64000/64000, 67547.172201, 71925.063075,
> 21 MB
> 5/1, SELECT, 5, simple, 32, 2000, 64000/64000, 56964.639200, 60009.939146,
> 80 MB
> 5/2, SELECT, 5, simple, 32, 2000, 64000/64000, 62999.318820, 67349.775799,
> 80 MB
> 5/3, SELECT, 5, simple, 32, 2000, 64000/64000, 64178.222925, 68242.135894,
> 80 MB
> 10/1, SELECT, 10, simple, 32, 2000, 64000/64000, 63754.926064,
> 67760.289506, 155 MB
> 10/2, SELECT, 10, simple, 32, 2000, 64000/64000, 62776.794166,
> 66902.637846, 155 MB
> 10/3, SELECT, 10, simple, 32, 2000, 64000/64000, 63354.794770,
> 67239.957345, 155 MB
> 20/1, SELECT, 20, simple, 32, 2000, 64000/64000, 63525.843107,
> 66996.134114, 305 MB
> 20/2, SELECT, 20, simple, 32, 2000, 64000/64000, 62432.263433,
> 66401.613559, 305 MB
> 20/3, SELECT, 20, simple, 32, 2000, 64000/64000, 63381.083717,
> 67308.339503, 305 MB
> 30/1, SELECT, 30, simple, 32, 2000, 64000/64000, 61896.090005,
> 65923.244742, 454 MB
> 30/2, SELECT, 30, simple, 32, 2000, 64000/64000, 62743.314161,
> 66192.699359, 454 MB
> 30/3, SELECT, 30, simple, 32, 2000, 64000/64000, 62526.378316,
> 66496.546336, 454 MB
> 40/1, SELECT, 40, simple, 32, 2000, 64000/64000, 61668.201948,
> 65381.511334, 604 MB
> 40/2, SELECT, 40, simple, 32, 2000, 64000/64000, 60185.106819,
> 64128.449284, 604 MB
> 40/3, SELECT, 40, simple, 32, 2000, 64000/64000, 60613.292874,
> 64453.754431, 604 MB
> 50/1, SELECT, 50, simple, 32, 2000, 64000/64000, 60863.172930,
> 64428.319468, 753 MB
> 50/2, SELECT, 50, simple, 32, 2000, 64000/64000, 61051.691704,
> 64447.977894, 753 MB
> 50/3, SELECT, 50, simple, 32, 2000, 64000/64000, 61442.988587,
> 65375.166630, 753 MB
> 75/1, SELECT, 75, simple, 32, 2000, 64000/64000, 59635.904169,
> 62949.189185, 1127 MB
> 75/2, SELECT, 75, simple, 32, 2000, 64000/64000, 60065.133129,
> 63538.645892, 1127 MB
> 75/3, SELECT, 75, simple, 32, 2000, 64000/64000, 61838.497170,
> 65818.634695, 1127 MB
> 100/1, SELECT, 100, simple, 32, 2000, 64000/64000, 57373.940935,
> 60575.027377, 1501 MB
> 100/2, SELECT, 100, simple, 32, 2000, 64000/64000, 58197.108149,
> 61314.721760, 1501 MB
> 100/3, SELECT, 100, simple, 32, 2000, 64000/64000, 57523.281200,
> 60991.938581, 1501 MB
> 200/1, SELECT, 200, simple, 32, 2000, 64000/64000, 52143.250545,
> 54823.997834, 2996 MB
> 200/2, SELECT, 200, simple, 32, 2000, 64000/64000, 51014.063940,
> 53368.779097, 2996 MB
> 200/3, SELECT, 200, simple, 32, 2000, 64000/64000, 56898.700754,
> 59677.499065, 2996 MB
> 500/1, SELECT, 500, simple, 32, 2000, 64000/64000, 53167.009206,
> 55809.410862, 7482 MB
> 500/2, SELECT, 500, simple, 32, 2000, 64000/64000, 53141.669047,
> 55865.580430, 7482 MB
> 500/3, SELECT, 500, simple, 32, 2000, 64000/64000, 53038.703336,
> 55914.388083, 7482 MB
>
> =====================================
> 9.0.2 Analysis
>
> Iterations, Trans_type, Scale, Query_Mode, Clients, no.trans/client, no.
> trans processed, tps (wih connections estab), tps (without connections
> estab), DB Size
> 1/1, SELECT, 1, simple, 32, 2000, 64000/64000, 70763.426807, 76119.159787,
> 21 MB
> 1/2, SELECT, 1, simple, 32, 2000, 64000/64000, 70139.061649, 75282.249622,
> 21 MB
> 1/3, SELECT, 1, simple, 32, 2000, 64000/64000, 69998.140674, 75508.027447,
> 21 MB
> 5/1, SELECT, 5, simple, 32, 2000, 64000/64000, 71248.938224, 76835.989978,
> 80 MB
> 5/2, SELECT, 5, simple, 32, 2000, 64000/64000, 68324.678874, 73664.740257,
> 80 MB
> 5/3, SELECT, 5, simple, 32, 2000, 64000/64000, 67986.887029, 73594.855720,
> 80 MB
> 10/1, SELECT, 10, simple, 32, 2000, 64000/64000, 67766.818613,
> 73131.991818, 155 MB
> 10/2, SELECT, 10, simple, 32, 2000, 64000/64000, 69045.201952,
> 74669.616117, 155 MB
> 10/3, SELECT, 10, simple, 32, 2000, 64000/64000, 62094.807128,
> 66287.996487, 155 MB
> 20/1, SELECT, 20, simple, 32, 2000, 64000/64000, 66972.157372,
> 72221.720682, 305 MB
> 20/2, SELECT, 20, simple, 32, 2000, 64000/64000, 67587.975254,
> 72683.167260, 305 MB
> 20/3, SELECT, 20, simple, 32, 2000, 64000/64000, 67113.601305,
> 71948.430962, 305 MB
> 30/1, SELECT, 30, simple, 32, 2000, 64000/64000, 65509.670353,
> 70293.133349, 454 MB
> 30/2, SELECT, 30, simple, 32, 2000, 64000/64000, 67489.902878,
> 72454.333958, 454 MB
> 30/3, SELECT, 30, simple, 32, 2000, 64000/64000, 65234.497633,
> 70089.363939, 454 MB
> 40/1, SELECT, 40, simple, 32, 2000, 64000/64000, 65681.175365,
> 70457.733066, 604 MB
> 40/2, SELECT, 40, simple, 32, 2000, 64000/64000, 64592.963404,
> 69444.519797, 604 MB
> 40/3, SELECT, 40, simple, 32, 2000, 64000/64000, 66772.250287,
> 71749.602855, 604 MB
> 50/1, SELECT, 50, simple, 32, 2000, 64000/64000, 57715.060745,
> 61701.317420, 753 MB
> 50/2, SELECT, 50, simple, 32, 2000, 64000/64000, 64812.489367,
> 69917.311854, 753 MB
> 50/3, SELECT, 50, simple, 32, 2000, 64000/64000, 65786.903883,
> 70713.309460, 753 MB
> 75/1, SELECT, 75, simple, 32, 2000, 64000/64000, 65105.491241,
> 70354.023646, 1127 MB
> 75/2, SELECT, 75, simple, 32, 2000, 64000/64000, 64134.747104,
> 68658.772338, 1127 MB
> 75/3, SELECT, 75, simple, 32, 2000, 64000/64000, 63974.154442,
> 68779.264771, 1127 MB
> 100/1, SELECT, 100, simple, 32, 2000, 64000/64000, 62137.309862,
> 66605.264938, 1501 MB
> 100/2, SELECT, 100, simple, 32, 2000, 64000/64000, 62003.667904,
> 66372.002630, 1501 MB
> 100/3, SELECT, 100, simple, 32, 2000, 64000/64000, 61511.372876,
> 65768.109866, 1501 MB
> 200/1, SELECT, 200, simple, 32, 2000, 64000/64000, 59470.544890,
> 63584.980830, 2996 MB
> 200/2, SELECT, 200, simple, 32, 2000, 64000/64000, 60463.204833,
> 64584.359283, 2996 MB
> 200/3, SELECT, 200, simple, 32, 2000, 64000/64000, 59025.725071,
> 63048.783011, 2996 MB
> 500/1, SELECT, 500, simple, 32, 2000, 64000/64000, 56162.668148,
> 59781.963968, 7482 MB
> 500/2, SELECT, 500, simple, 32, 2000, 64000/64000, 55649.899526,
> 59268.808123, 7482 MB
> 500/3, SELECT, 500, simple, 32, 2000, 64000/64000, 57373.632334,
> 60672.421067, 7482 MB
>
>
> I have also attached postgresql.conf file for both versions for refrence
>
> Thanks
> Deepak
>