Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6FEA5475CE7 for ; Tue, 21 Jan 2003 16:21:00 -0500 (EST) Received: from martin.sysdetect.com (martin.sysdetect.com [65.209.102.1]) by postgresql.org (Postfix) with ESMTP id F09DF475425 for ; Tue, 21 Jan 2003 16:20:53 -0500 (EST) Received: (from mail@localhost) by martin.sysdetect.com (8.11.3/8.11.3) id h0LLKtH20808 for ; Tue, 21 Jan 2003 21:20:55 GMT Received: from winwood.sysdetect.com(172.16.1.1) via SMTP by mail.sysdetect.com, id smtpdm26056; Tue Jan 21 21:20:49 2003 Received: from winwood.sysdetect.com (seth@localhost) by winwood.sysdetect.com (8.11.6/8.11.6) with ESMTP id h0LLKn820463 for ; Tue, 21 Jan 2003 16:20:49 -0500 Message-Id: <200301212120.h0LLKn820463@winwood.sysdetect.com> To: pgsql-performance@postgresql.org From: "Seth Robertson" Subject: Postgres 7.3.1 poor insert/update/search performance esp WRT Oracle MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <20460.1043184020.0@winwood.sysdetect.com> Date: Tue, 21 Jan 2003 16:20:49 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/214 X-Sequence-Number: 861 ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <20460.1043184020.1@winwood.sysdetect.com> Content-Description: text I'm trying to get converted over to Postgres from Oracle (Postgres is billions of times more straightforward and pragmatically clean than Oracle), but I'm having some severe performance problems on what naively appears to be a very straightforward dead-simple test. The test is comprised of two parts: a write part which attempts to accumulate (sum) numbers by distinct keys, and a read part which searches for keys in the database (some of which will be present, some of which will not). In a more realistic scenario, both will be happening all of the time, but we can start off easy. However, performance is terrible: around 39 write transactions/second and 69 searches/second. Oracle, by comparison, writes at 314 and reads at 395--practically an order of magnitude better performance. Both are using the same hardware (obviously not at the same time) which is a dual-processor AMD 2000+ with 3GB memory and both oracle and postgres loaded on a 105GB ``MD'' striped (no redundancy) 2 SCSI disks running ext3 fs (no special flags) with Linux 2.4.18-10smp. I actually have seven different schemes for performing the writes using Postgres: ---------------------------------------------------------------------- "normal" C libpq 39 t/s "normal" Perl DBI 39 t/s "DBI Prepared Statement" Perl DBI 39 t/s "Batching" Perl DBI 45 t/s "arrays" Perl DBI 26 t/s "server-side function" Perl DBI 39 t/s "server-side trigger" Perl DBI 39 t/s "normal" Perl DBI read 69 t/s "normal" Perl DBI for Oracle 314 t/s "normal" Perl DBI read for Oracle 395 t/s ---------------------------------------------------------------------- Only batching had a statistically significant improvement, and it wasn't that major. I couldn't use true Postgres prepared statements since you cannot determine the success/failure of the statements yet. I was planning on using arrays as well, but the additional 33% performance impact is not amusing (though I suppose it is only an additional 3% if you consider the 87% performance drop of Postgres from Oracle). I'll include all methods in the attached file, but since there was no significant difference, I'll concentrate on the basic one: Example table: ---------------------------------------------------------------------- CREATE TABLE test ( val BIGINT PRIMARY KEY, # "vals" may be between 0 and 2^32-1 accum INTEGER ); ---------------------------------------------------------------------- Basic algorithm for writes ---------------------------------------------------------------------- while (<>) { chomp; @A = split; if (dosql($dbh, "UPDATE test SET accum = accum + $A[1] WHERE val = '$A[0]';",0) eq "0E0") { dosql($dbh, "INSERT INTO test VALUES ( $A[0], $A[1] );"); } } ---------------------------------------------------------------------- Basic algorithm for reads ---------------------------------------------------------------------- while (<>) { chomp; @A = split; $sth = querysql($dbh,"SELECT accum FROM test WHERE val = $A[0];"); $hit++ if ($sth && ($row = $sth->fetchrow_arrayref)); $tot++; } ---------------------------------------------------------------------- What could be simpler. In my randomly generated write data, I usually have about 18K inserts and 82K updates. In my randomly generated read data, I have 100K keys which will be found and 100K keys which will not be found. The postgresql.conf file is default (my sysadmin nuked all of my changes when he upgraded to 7.3.1--grr) and there are some shared memory configs: kernel.sem = 250 32000 100 128, kernel.shmmax = 2147483648, kernel.shmmni = 100, kernel.shmmax = 134217728 The WAL is not seperated (but see below). A "vacuum analyze" is performed between the write phase and the read phase. However, for your analysis pleasure, here are the results of a full verbose analyze and some explain results (both before and after). /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ seth=> explain update test set accum = accum + 53 where val = '5'; QUERY PLAN ----------------------------------------------------- Seq Scan on test (cost=0.00..0.00 rows=1 width=18) Filter: (val = 5::bigint) (2 rows) seth=> explain insert into test values (5, 53); QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row) seth=> vacuum full verbose analyze test; INFO: --Relation public.test-- INFO: Pages 541: Changed 2, reaped 540, Empty 0, New 0; Tup 18153: Vac 81847, Keep/VTL 0/0, UnUsed 0, MinLen 40, MaxLen 40; Re-using: Free/Avail. Space 3294932/3294932; EndEmpty/Avail. Pages 0/541. CPU 0.00s/0.03u sec elapsed 0.02 sec. INFO: Index test_pkey: Pages 355; Tuples 18153: Deleted 81847. CPU 0.03s/0.34u sec elapsed 0.65 sec. INFO: Rel test: Pages: 541 --> 99; Tuple(s) moved: 18123. CPU 1.01s/0.31u sec elapsed 9.65 sec. INFO: Index test_pkey: Pages 355; Tuples 18153: Deleted 18123. CPU 0.02s/0.06u sec elapsed 0.19 sec. INFO: Analyzing public.test VACUUM seth=> explain select accum from test where val = 5; QUERY PLAN ---------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=4) Index Cond: (val = 5) (2 rows) seth=> explain update test set accum = accum + 53 where val = '5'; QUERY PLAN ----------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=18) Index Cond: (val = 5::bigint) (2 rows) seth=> explain insert into test values (5, 53); QUERY PLAN ------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (1 row) /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ I certainly understand that using an index scan might well speed things up WRT the update policy, but considering the search performance is post-analyze (pre-analyze it is even more deadly slow), I am dubious that doing it during the updates will get me within striking distance of Oracle since read performance has got to be better than write performance, right?. This is also why I am dubious that moving the WAL to another filesystem or futzing with the fsync policy will do anything. I will include below a compressed tarball of the programs I used (and the corresponding RUNME script) in case you wish to play along at home. I don't claim they are pretty, BTW :-) -Seth Robertson ------- =_aaaaaaaaaa0 Content-Type: application/tar; name="perftest.tar.gz" Content-ID: <20460.1043184020.2@winwood.sysdetect.com> Content-Transfer-Encoding: base64 H4sIALO4LT4AA+09a3vaxtL9Wv2KCSYFbDAX2/H7GNsNwYrjpwQ7gJv0TV1X oAXUgkR08eWkPr/9zOyuQOJinNR2knYnMUir3ZnZmdXszGhXjJjb9Znn5797 OCgUNgvbW1v4XShsbz+j72Jxs8TPJXxX2H5W3NjY2Cxt4fVisbSx+R1sPSBP Ywg833ABvvOY37+tnskuHoOdx4ZRqP/Gab2lN1sPQaNQLNymfyyK6L9A+i9t bBS+g8JDMDMN/3L9rzzJty077/W1FW0F9CtjOBowGLlOzzWG4DvgBjYYgwHQ IPGwkoaCghzTsPa7d++gqtebxw3tuFGp1vTzV8ev9b08tv4j77hGZ8A0djVy XB8i17WTSuvV3vpOkr7DE9Fm5Hh+z2UesbQTRcMLRIP66euf9WrruNHcK/Lh o50039TOq8f1OhY3W42j+uFeIteHrhtYZmLR1UvHNpmLlw9nLubMdh8ZGSNY XCNEgr2bV8c2hmyPYwE6DTzm7nUdh5+MDM/baxtLGgsCk9bGZZv0MMEgCxIa 9vKkdtqcxoTk8kjluezLgloSS15+Pw/7FVOxxjp9Bw6ZzVzDZ5CcKAJcwzad IVj2KPDhgnV8x/U0wpTrYfULZvvro0GsxT7k/eEof7Vu2VDal8dO4Gva1d7v OGbcWO3UagpKvwsGEpIDy+5B8ooPSzqUVCEdbWcGIy+T0NIdw5/QK8OtnCE3 eKvn7WAwyMBfvGsDy2aQMyZMe8xwO30hkckHJJpoSjg3nsVvI8tGpfn5YGSS xIZoYRwTCwGN7iAh2wBUB4iOWmEto214LKGNvA/I1ezQhVxXcI/Xx+1P0IQ7 7pAwCIIJzbeGTFRsXxMx3snpgTwRyTxUxAxK1bW6ForPcuyE0KgZDEfz0Y3F Y3T8wBhoptXtwkS18aso2j4zzHmUXSznzEObYRkDwzYG1/9h0W71mD/R3qKO STXdSsLA2c+dUFgs+A4kLoxOEAzDyuXP5edvDRsIPKp58OIIjTQbGS4zAWcw nw2R+mRMpX9hXhYsH0yHeXaKvpHva78vRgn2Wd43KBEIzS5cMz/z6OMylws7 0ra+0kF6HxprG36HpP8F5MtJ/4NFa7iuce19AcFywvct2MVo71nAd5AsVkBO c55lMugGdkew+yhy9vvB1z1z/S3BorxwbPuu1euRo/XYEs3lJOlvTrZLrEI1 P7Daow/3J9GqMxxZA0IgY6KE1ulA7rgEuaN84Ll5y+4MApPlRz1CCzknNBOd 8cE6NhiMPuBHx70e+Xe1Mx3YjXjJK9B0XPc6C33DNTuOifM+xSDfopKOeUx3 q45QlqNB4EFybsQCz0M9rWN8SGIKXfa7yRYbcTnMhF6fN/gR3Vi0syjv3S2e 6knM9VzYo9D3nI7/x/mfMNB5gBzDkvxPcevZ9nT+Z3trW+V/HgNk/ocmhi1K AeG/wGMY5vvOyN/ZafpmWdOG1+nkqYcjLQN7kOBHO5As7MD7nNUzzmC3aw3Y /vr6+q92oqz1eFsvnepZRgpj6L/AtBgIBGXN6mKMvlJpHP6M920howF8xD+A UeD108+pPJvIJTJlLLzRRG3Edm5lNKpHnHiMmfz6CI2GD4kTNB/IMqOoBugi UEzTd7GQ8wPAmyDru83WwVF9n4rw/hqOIrjCKj1m9w3kRFwBTp9fevJfyP/2 q7mWzEdpN+mS5YnmkppHt5IoKWs3Ght4jPMuy5NUqFFga5BXjgR4r0X/OL0u sA9AQuCCEeIBWH25t+r5pmWXecENfnLU0TrU/IkzYnb6ZTbZzWS07z9q3wNc Gq4Niaph244v7Eiyiwp8whnG6za78vHgZoz4OVkCjyT2Ukhr4Hgs/ZL3HP9f 9lHfpEZRbX9PKHLCxxDdV5412QNvNLA6LM1rZin0THMxhI3XiplMFoqZsmwp 5Mobh0UDA+P0wB4wz5ODwciEMkBJal7QnqgtMkx8l4br8/NyWBAMs8mO7WeT A2b3/D5HgnpIy/M98SValqnmrrzCT9bW4n0kfIiff63Cxjas4bgz08gNtudI BDXs4ERjVJv0/6Vv+zGM7f/EWbl3GmT/n21uLrL/m4WN4pT93yiWVP7/UWBF +q+wS7bFWe/va7EidKeny8hVi5UluM+d67L1fkLTVkzWpVv/1XGzVa+81iER ppPRnOO9bbJ20MMbp1CW5w76cemTw45j27BqtvtZNM6oklUsxjtnaRV+w58c uswLBj6s4jfdbtTMpntNGlVOVdzAXW5jumnsHXPdbOKph3YwK6jJ2k/SLjd/ J2/YFeukOUWqwGezkzeCFrq7fuBRzQw8wbqHDZ381NevK/WD8+OfJlPbNMGq EwxMIFtM2AP0jX9/6qV24KmXfmpmJDNZQUcSmUMzk51XKEyN0XZcPx1OokCS wN4YvmMhps7QbAXon0daYCH54LyAC8FlfuDaaWyX4dMViXNoWHaaDgy31wk1 gMcX78+EEnhJO+i+LxU2/++sHJZgZyYlESUiR/XTWm1cb5WejdAM7DP7Ip04 0F+cHiYiOsHLUoGOs5coJPiVcDTxvlENUcqxn7zBm9ps15weMh4Oxywnevtn hChiysRFSjP5peV3+ihKT0ieKoXq7pAvIl3wo+M6DoSduYPg5A2I1vDUhOOf SOtxfEKTbZyu/yzPwfuicnAHxFjrkzE3W5VGS78Ldlnzkym8rhzod0BP1T4Z d+Vt5aiFgc853o0nx/XmXQiFVT+d2Gnr1d00TDU/Q8tNvaXXf76LKnjFpfjR NhtoMe6A8EB/WTmttZZi5PZFOoRdvHe9NFoAtJbWf5jTpWN077jbmpkYxOAc 6W6UztGUmKaLd2p6XJLBG3kw4O0EobEhpxNhJ0Y+b4MzUafvcnIpwEBjjbuR vF7E3o3ImROlnh12GM2rZJCsejZxenJQaeniiRBKEoxOR2Dg32skj7ev9IYO Fxii78HToJzIEpEs70DIKU0zfKai+YIQY4RTjDuNi1k4wvHXaMFRvXUs+Pi5 UjvVm5B+GmSRfoYoEjFOd+wu30aTqH4fmQlCH1R83nCzjzO1hW4zKZaqsCvL TxcyX5WHquAhYdr/lxnie6WxbP3PZnHa/98sbBaV//8YsPKEZ7HDHNBU+qfm 2L0yLzl4cYRWVGSCmpVDfTYTZJ7hR7hwJczkyjK+eIU+6KIs4+tX6OMSw2ZZ RhntPfqI1PM6fTY09sTXuJw/0+RHk6fH0VN+zB+kncEuN+M0wMc5KuzH0+MT mmKbaO2i3d3ZqTp21+oFLksn2gFPivbQ9iZs59zq2Y7LzmmSliVG4ONHG2fD C1kisl/nHWc4Mnwqc9mHwMJW2EsMggS14xGlj730ryETWUhxT/Yvcy1Fx0KK njjm0pPHXGrymEtLHodSojMuGzqYiCZyRodcMNHcHKmUJ/og/dz0bJoxRUIO T7IJyU5SMvtR8pe6ScgM2cwFRBBpzxmNVqKCua3FhXhrWkAWa00Fc1uLC/HW jhB1DIEsm4tjfC2OxvevYyjwfG5zXo5NVyJtw4EfQ0CFczGIC1MowvskLgcs nC8HfiEjFJoUwRDewLl9ir1Yx0+jRq2dk95OYv0PB2OjBDoXXO1ZmOUkXiZQ Z+EjVHDoV53h0PJhbx+KWWgYlsd013VcKihgAEuuTrTgLeUh6RBuYDL4kLOd HXRA0Z8jExPvjBjLNyLApK7k9tsM47lzlMaf5KfEq0dG/E2YCoSkH4woU8db yxrpD78tc/p+jPl8P5Z/y5RDfJY9B98CDy79YxZ+zMjW0lHe3Z84wzwTLfyz 5xWZMPWlx8o7h9zn9mWmIJ2svC+eoUoq7wtnGZ4nLuiFqUwx8RdrURAtimeh 0MsRN5BnqOdKMRQhaSlx4DCP5yxI8LRSKJc7CdcNdcLMMiUNQGZinG6Y3uBZ ceGlJnkq5cNvJw39pNIQwhfPB9Mvjg5RdFmSn36oNzJQacIyFSVLMR0li1LM i4mJh4FziS3SXxLHdrL0NzQY5Sahv9Orp9inpV0jfcV6l+KaTOG9Wlik+Ril hREFRIcEYGSRWTIexHQhR8PfFUCk42Khy7j370tnoQDo8CFkIChOCyL1sZgV N9ZGdvMmNV8k7L57/7Wpfanh7XBrT0ZXnJuWF04oY2ueOJCeHzBu+BProu7E vmsr9wLaChwNR3wl8AWD5psaEHuGbdIidXokJIWSLI+fCYEQUZJi72R3YPS8 8AGRFj54arYO9EYDEohvn+qhszg9u5KbxidXMRe4F+Vx0hhPxvOC6aSTPGUc ecQ5TeLJLrbY343LaD0RK8HT25kIk7WAvirryieI1Dn4YfxcjWtGPv8Lc868 f7s7ME1dPsW8mSsUYvg2dmiskERu7lXRDWZ0Fqn5Q8Dc60fRtOf3I6rGsxkf 4A4KRynPKvx+FSy5uZOCF4xg7NzYgfiMAfyFBuxnjFeQav3X5rvi+R+5jOme aSzJ/2wVNp5NP//dKpZU/ucx4FvN/4jUzj8/l6MyNSpT85VmasY+0S3BonST Jk4auWWJpl7Tqy2Kb7LjaO9l4/h1JEQ7buAcDi9+oUo89IktpbsV7eoE13w0 3OlxLstauI4NT8ZeT5dh3IMF55wPl3VFR+R6P6qa28cwCuRRUdgg8ndVUKSC IhUUqaBIBUUKvhmIx3+RTfH3SGPZ/o/iTPy3uYHVVfz3CDAb/61M3vHAnbNg wPc6AR8YHro5Q+MKzaF4+UY5PE3SHgbyi7hRCU/QgaT5jy+yv9orlJNXu1Qf v2k1PdnPFfQ9WZe5YLNLSQIMH7oWmrEs9GhnlTEYXIPXt7p8b5nvkC3HFmLH WEDbC4hXyZ6w2XyLwf4e7TKo5eljrYTMRmehpFgiNt6PUFpd3ShlQpMpN1bE qiZr76O7F2p8vVnmLGyyAgcOzRzOpU1ycwJ7vL9eOH0Y1HX+xCKsa5hmOhmu rR3vBCEyaY/elvIRkgbsovudbJfRAX9ei/qgoSHn9ZOvPtL3zdgJ5dM/xz/Z CUGUJjM9dwTQHc5Eu7+F0lkrCk+AxzK1rGjGZ8CQipgb5AmsoUzIrZ64D5In LJT8fOnRrWAZTNt///Ht/1ZxY3r/3+bGttr/8Sgwx/5r30gKcP4SsH94SlBl AlUm8KvLBGqRVRFiI5BcETG1HkImDBZm7sQiiEn2Lra2hxYvlOV+oHEGTy6R IqQ//LA0kyfcv5VJ1J7gWGGc1ZNRMzo5fctfW5PHviOPY67hLJrcpHmkyd9Z LRGiR24gz7GSzV1PU0GeTjMy0lcJRJVA/FRFqwSiSiCqBOK/HOLxn3gxzn3T WBr/Fbam139slDZU/PcY8K2u/1D7fxavGVH7f1QseYdYUryGbWeOVr+GkPIL LrW/75X28YX2apmIivJUlKeiPBXlKfjyMBP/PcAWgCXx3+azjen4b7Ok3v/2 OPAtxn//8AhOhWUqLPtaw7KxL/dZK/DFAny1/F7FVSquUnGViqu+grhqxv9/ gCWAS5//bE77/1uFbfX+t0cBtf5PBQcqOFDBwTe1DFCtAlSrAFUccz+KVnGM imP+AXGMgs+DePyHg+ABaCz5/Z+N7e3p+K+0vaF+/+1R4KBxfAKtyouaXChT rTSrlQOMK6oNnVbPRC6lNe6DiVenwknj6HWl8Qv8pP+S1aTLJt+mquHNP4VX vNRlMXJx/c4U3p/NoXEe/qbpYjLjKjFKC/l/eVrnP8Mh2vJf+Jx5c+yYyl0q Q0NvnTbqzbCAXjubOtCrNXo9rUUcVWpHWPbyuEEvswVL8BUpLJXhhY5Il74/ VjaN+s5EoAxHLxHTaf0AWq/0umQIkJZea+qL3oELaWqblUgzWBnbH70sR5vX D1JQq9QPTzEshdRowCWQQq9gkSi5MtALnlbXLVVD8bUaR4eh+O4mjrr+dn1W IlS6WCj814mWyEViyE4IzEpH4lkooHCYyk5NdRtH6wsddT9mIpSMHMt8Tqex oVeqr6Bx/BbCVwyfNI6r+sGpfPNxTJJI9edK9fT0NVTqldov/68DRQ/5VW1l WgnJj83qK/115Wbdu/bM8y4z0L9gaC0tM93S37UmSqGflSduSSkykpvUPrdM EdEtxCcVE2lyNRycD80t8WLniOzQ9yhTomqaVTYcnaMpGY5gwVAR0RofMOKQ IJeDKm3NB79v+ISDAmqgTfGeMTDcayB/r4cesj1pg8OF9B1WPmqSivnYmdQh aFSOcPDo76o6d48glQqbyFdmtxkfHTQcA5bCwTBuLsfQDE3J1CeQfBoS6xsX kpxAkkplo92YRzwmprd9h7/92+NvNQg8uOwzG7w+gyHOojBCC04XLP/HRVzv QuEzWDbAZj0Ri34e4w02ZMM2Ds1L7EGnb9g9ZqK6GbHsOoMB78ikBaGmH508 579bvLOHDNnOZUw74yqUaKEqncB1mS3OI/VCC6C/lYXIIB7NNwSTIR0O2smI njIBjdDgiWEvcC8xAmNkdPuvaKt58stn/D/6ReV79jGW+H/o7s38/uPmtnr/ y6PAlA91i98XuixLHL8v3SEFnwTx+9/vBw/wA1BL3/9bnHn/U6n0TN3/jwHf 4vo/KpfO779vc5fsuHoQ+O9+EPhtvwgk3udwSN9MsuG37rMaZ3Fu/2WTmRe5 kTz/COw/Zx48QvjkMZK6if9sEkaPlXrzrd4Y41YbudSDOvWgTj2oUw/qFChQ oECBAgUKFChQoECBAgUKFChQoECBAgUKFChQoECBAgUKFChQoECBAgUKFChQ 8EjwP+/zYX4AyAAA ------- =_aaaaaaaaaa0--