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.a...@gmail.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 >
