Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E2BD89FB61E for ; Wed, 25 Jul 2007 11:43:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 25767-03 for ; Wed, 25 Jul 2007 11:43:30 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from oxford.xeocode.com (unknown [62.232.55.118]) by postgresql.org (Postfix) with ESMTP id 154DD9FB61D for ; Wed, 25 Jul 2007 11:43:41 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=oxford.xeocode.com) by oxford.xeocode.com with esmtp (Exim 4.67) (envelope-from ) id 1IDi5J-000135-Qn; Wed, 25 Jul 2007 15:43:29 +0100 From: Gregory Stark To: "Simon Riggs" Cc: "Merlin Moncure" , "Josh Berkus" , "Jim Nasby" , "postgresql performance list" Subject: Re: [pgsql-advocacy] 8.2 -> 8.3 performance numbers In-Reply-To: <1185373345.4146.48.camel@ebony.site> (Simon Riggs's message of "Wed, 25 Jul 2007 15:22:25 +0100") Organization: EnterpriseDB References: <73185727-AD4A-4C97-B243-ADD2EE1875B5@decibel.org> <46A0EAE3.7030305@agliodbs.com> <1185367933.4146.13.camel@ebony.site> <1185373345.4146.48.camel@ebony.site> X-Draft-From: ("nnimap+mail01.enterprisedb.com:INBOX.performance" 1726) Date: Wed, 25 Jul 2007 15:43:29 +0100 Message-ID: <873azczgq6.fsf@oxford.xeocode.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=Khaddafi-USCOI-Ruby-Ridge-mania-propaganda-Adriatic-Soviet-infowar-S" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/344 X-Sequence-Number: 26028 --=Khaddafi-USCOI-Ruby-Ridge-mania-propaganda-Adriatic-Soviet-infowar-S "Simon Riggs" writes: > On Wed, 2007-07-25 at 10:09 -0400, Merlin Moncure wrote: >> >> just a small 'me too' here, the RI penalty seems higher than it should >> be...especially when the foreign key table is very small, and I can >> see how this would impact benchmarks. > > Any measurements to back that up would be appreciated. "Turning it off" > isn't really a valid comparison because we do want to make the checks > and expect there to be some cost to that. We just want to quantify the > cost to allow prioritising our efforts to improve performance on that. If anyone's interested in this I would be very interested in seeing the results of your application benchmarks with various parts of the RI checking code turned off. Attached is a patch which adds three gucs for profiling purposes which cut off the RI checks at various stages. To use them you would want to benchmark your application five times in comparable conditions: all variables set to 'no' skip_ri_locks set to 'yes' skip_ri_queries set to 'yes' skip_ri_triggers set to 'yes' no RI constraints at all The last ought to be nearly identical to the fourth case. Note that it's really important to repeat your benchmarks several times to ensure that you're seeing repeatable results. Measuring CPU overhead is pretty tricky since a single checkpoint or autovacuum run can completely throw off your results. In my limited testing I found a *huge* effect for batch loads where many inserts are done in a single transaction. I only see about a 20% hit on pgbench with RI checks half of which comes from the trigger overhead and about a quarter of which comes from each of the SPI queries and the locks. I have some ideas for tackling the SPI queries which would help the batch loading case but I'm not sure how much resources it makes sense to expend to save 5% in the OLTP case. --=Khaddafi-USCOI-Ruby-Ridge-mania-propaganda-Adriatic-Soviet-infowar-S Content-Type: application/octet-stream Content-Disposition: attachment; filename=ri-profiling-v2.patch.gz Content-Transfer-Encoding: base64 H4sICMFep0YCA3JpLXByb2ZpbGluZy12Mi5wYXRjaADtWnlT48gV/9t8irdO7cRm2liHdUHNbhEw 4CyDvbZJ1SSVUgm5jRVkSaODI4TvntctyZaNDMZMKgOzLqqR+nj93q/f1erueCN6uwtRaDcvLPuK eqNmEjtu1LRGcTN0zDh0Li9pGO3YW59e/9vqHwxg7Lh0F5oTf0qbUWyFV002e7/d6w46w27/SzO4 jL66zTVYItdbIcVXeu14lxDiv8jxPRB3DGVr5IzH0LChEbJXWBRle3t7HZErCvw18UASBA0kcVcW dwUVGgL+KnyORqOxFhnRQDpuSkeUdhVpV9ZTOoyT4o9zJmoCETUFeAWbI60wAJ8bWwAIWuzYcDLc /wts40xfExrembZlTyh8grPz09O9kl62Pw2skD7qt/URmttwfH4A2018vvB9F6IrJzBxjOvbVxH2 FfaWW9icDi1vyyVPG3EGYDM0Zj98h2049W3LhXHi2TFbtCD0Yz++C2hUiomkSERStDkmkqISSZVz TCrNbVYi3YMJta8gnlgxFk4E+GfBteU6I8gYA5zYBZwzRiCwaoI9nSkFyxsBvaZevJOSYnBUnDHU lgWrs4YKql4SetDzHS+m4TGND604mdYYrnUmdgVHcGaG6bDa2Ha8sU+g2u+YR79RXDLWWiWA78N+ 53j4pdc2O2fnvXqGGhOqDAxZFomMKsQrsFuFM8sfviZ+TPvUtRioZ9aU1oKrkLoePhEIrkx8Tpmr VCIvCJH1cY3rTxSHBCLn39SfV9TJ1k+sJ9KvDtqn7YMhiHDU736G7tnpF/g5gls46vZhcLLfb0P3 CG6r8xGzeWfSVFAe6IWUqSEHO7KuKV+EwLW8uQj87RMz2R4+cQgLPAqEqy7hWiDLGpFbykwL/o9I /ByVCU9gVrdoVL9CtQq7UF0G8H+FVpketTSFtHSxoEfpQBogwer+2WF1r1DtO6PoH84/sWl8ZTJL TRsfuIBWEKAHHKCFeJcd1PLaBz7mIhmTp2RcR8TVEiL1nZEVWwTbmGnteFf0LiIwYzdVkpYuoZhG QUk2FpN5g58WFrKeru/3Kn/ZsquyStSW9OJlD77TZW/8Ur7uaqtFVEV88boHb23dywAoXXg0BFUv BFHVEIlq6G86iHq+xTMIc0TdpVh6iJ562H4ummqGTDTDKJjDSkV5WlVyZdlYK9Z0+a/3iLrQIroo FSzjVSJnUq80ku8bkDKd0BWD6Gprbim6JhF9noK/bUtJgtGSpZz3DveftxRDF4mR+48fw1IMQyWi IMjfylTemZ2IAsZYLIzCZlVQcbcqqNrb3ppZkW2N6MZBRZQEnYiSKBeAkSTUJUmW3wUwm/oQsSWI BAu1AExLYlXS285DQoobLseON1cZRZAIFvqP5F9FRUIHosjiHw52hVJoMuKj6QVzUVhMUgX5fZjL xo5E1RWEwRB/KHPRRPYpWNT+MJcVn85bOvt2Xgy7moYeRtPFN20uEY29xHU3Dy4Gi7GGVAy7RsvA KkV9F8Bs6kYwRZMJFgUHK4kqq9KEtw7MiI6txN08I5FkUSWSLIkFbGQ0MElWlPeCzaZ6I8tCi2BR 2PnIsqxjVWv+lYC5PTNBAD4Bsnke0bAzqqU8Dmbv+RkF9jjyw2nq//jxROMXLP0bj4b17KRuAZXs DK4O9xwWdLk57v9KohjGfshO1caOy05HbyiMfGD4M/Bv/BCXygcEApIA1wL4WVQEF0nMDyp4L8uO E8tNfS7QW2pnS1OJAocFcMQPJaO3AcU43v1tj7cNeh0Tp7VpFFEmuMirH1hB3YhmmMKR4yEzd4wv G2NAmKRawefaySLEwjSMLuMhiakZeVYQTfy4xgMIycMdP2ZhKoeBgjmFFUECtVcksqLNMod7RuBk f3ByMDytVOzY5WhjCJzSKSJU+4BV/CglOxXC1zquCHbApx0WmLAeWcyaUZl+Z2Kgps17oXaHK/qd WNGkzZrnvSdYhR1j69Jkj7z+0UkvazHtkFoxraFqZ+vEW1OV7px1hubv5+3+FybcoPP39uyEiiGV isWaTHQHn+E/6fPR+dnBsNM949y8CoWD9Mh5DRyynusjsXyavYxF1v4CNGANPAAeUq9QfqSNfB3R 2J5kmc+I5TTQSH2DYqDOqdLMN6zUOZ6NLa41T8fuObO8eHZB1tTLFyrmyvX45qq5nm6mCWwRswWt KKC2OWDLKvxiHX4StG+uxeupMYcu0+WPcI3pNP5jQdOlVsj4H1oXLo1qrIWF3Ps88jzSy3k4Zl6e vX6mUz+8O/AxNN/Gg9iKoxoGtqx2knhXWVNtiVQ9J8AxGLHdoX+33ImHkhU3Xj7mtrm+YXZWXX6a OpHdvEzs7+HW05yXldedWsLifSd8h5T78otOc5IVUShcTZJ3xdYuZnv5FSdGt/yOU5GCsUBB0Xax 5onLTRLu2mWh+FmUVbRyv4iqQTG5sydWCNuDwemBE0xoOEicmEbMRf4JmXDGj+8tZeMeX1/aW9GY ZU+rmvOUM0sDs9tUtu9hZpUyZ0WRc+nhJJdcXTGfYVlcrdgFU5GEztITVEU+xch3YgLHiT3wk9Cm EPF/qNzlO1rc0OrF/awuYcXyZTBOOOPISmL/2rKTZGpOrVuT5XooSA2zZfDoDfJEnuGjlCqSQtE8 yo+QXkYNIS4huLBKNd6yHsGPqZWnps0UwA/4FbJrK3S440o3IcxxYm7qhzHmoePQn6bp8dQfJS7l g5srzjR0jYiCIRXPNAyZiGLhC3VljFkmzS7X8JLVPpAsTU8z8vvqgozowHvHB+bgfNAeEjhs/619 2u21+2a3x/zygPAxUD10IiYFz9XZOGbsLJdn8SBN0pGjC3qD8u1U80H5A8JhnnWHGCbMwf7n3mmb Vz+krR8W2Enr4hA1FD6UrUkm10dIx5fJlRnRBpLlFwi/sWQZ2aJshSV6SpTc4DeQZXbh8RsLk9N9 Tpp0R9X2Rg1/3HAddD9Ti9l8to3iqWblPh0pzO+E8fIhf3ms0KVXSIyWRlRDEYqXSHSZaIIw/8CW bdAZw3vzPIPvT2lI/xyB54Pj2W4yojwuctwWrszyTwL5bpbtC33PvStYc35rtOj6WbayMovJsouC G8K31zmiYmLEutTnWrV8fzYll+6PSzisz7fIC9CxlIazzeJeDlgVw26DobZjV7f+C/1Xoq6/LQAA --=Khaddafi-USCOI-Ruby-Ridge-mania-propaganda-Adriatic-Soviet-infowar-S -- Gregory Stark EnterpriseDB http://www.enterprisedb.com --=Khaddafi-USCOI-Ruby-Ridge-mania-propaganda-Adriatic-Soviet-infowar-S--