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.aeqa@xxxxxxxxx> 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
