Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E6A7C9FB367 for ; Thu, 1 Mar 2007 16:41:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 43447-06 for ; Thu, 1 Mar 2007 16:41:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from beast.liquidweb.com (beast.liquidweb.com [64.91.224.2]) by postgresql.org (Postfix) with ESMTP id 108D29FB360 for ; Thu, 1 Mar 2007 16:41:26 -0400 (AST) Received: from c-24-11-214-165.hsd1.mi.comcast.net ([24.11.214.165]:60124 helo=[10.2.1.2]) by beast.liquidweb.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.52) id 1HMs5S-00069w-UU; Thu, 01 Mar 2007 15:41:15 -0500 Message-ID: <45E73A6F.50800@intellicon.biz> Date: Thu, 01 Mar 2007 15:41:19 -0500 From: Benjamin Minshall Organization: Intellicon, Inc. User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: stats collector process high CPU utilization References: <45CB5D06.7090004@intellicon.biz> <7994.1170960391@sss.pgh.pa.us> <45CB9330.3000603@intellicon.biz> <19935.1170969862@sss.pgh.pa.us> <45CB9C4D.30902@intellicon.biz> <20601.1170972476@sss.pgh.pa.us> <45CBA443.9090101@intellicon.biz> <7959.1170986672@sss.pgh.pa.us> <62089.75.14.32.120.1170994418.squirrel@intellicon.biz> <14668.1170995218@sss.pgh.pa.us> <20070209142459.GB4253@alvh.no-ip.org> <24717.1171033699@sss.pgh.pa.us> <29832.1171036557@sss.pgh.pa.us> <45CCA0DB.2010903@intellicon.biz> <1116.1171038786@sss.pgh.pa.us> <45CCBF13.2000707@intellicon.biz> <16282.1172779976@sss.pgh.pa.us> In-Reply-To: <16282.1172779976@sss.pgh.pa.us> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms010108090709020804050807" X-ClamAntiVirus-Scanner: This mail is clean X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - beast.liquidweb.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - intellicon.biz X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200703/13 X-Sequence-Number: 23418 This is a cryptographically signed message in MIME format. --------------ms010108090709020804050807 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > A further report led us to realize that 8.2.x in fact has a nasty bug > here: the stats collector is supposed to dump its stats to a file at > most every 500 milliseconds, but the code was actually waiting only > 500 microseconds :-(. The larger the stats file, the more obvious > this problem gets. > > If you want to patch this before 8.2.4, try this... > Thanks for the follow-up on this issue, Tom. I was able to link the original huge stats file problem to some long(ish) running transactions which blocked VACUUM, but this patch will really help. Thanks. -Ben --------------ms010108090709020804050807 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIQODCC BIowggNyoAMCAQICECf06hH0eobEbp27bqkXBwcwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UE BhMCU0UxFDASBgNVBAoTC0FkZFRydXN0IEFCMSYwJAYDVQQLEx1BZGRUcnVzdCBFeHRlcm5h bCBUVFAgTmV0d29yazEiMCAGA1UEAxMZQWRkVHJ1c3QgRXh0ZXJuYWwgQ0EgUm9vdDAeFw0w NTA2MDcwODA5MTBaFw0yMDA1MzAxMDQ4MzhaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMC VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5l dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVN NRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQy lbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXq vgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6 hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu 9mIwFIws6wIDAQABo4HhMIHeMB8GA1UdIwQYMBaAFK29mHo0tCb3+sQmVO8DveAky1QaMB0G A1UdDgQWBBSJgmd9xJ0mcABLtFBIfN49rgRufTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/ BAUwAwEB/zB7BgNVHR8EdDByMDigNqA0hjJodHRwOi8vY3JsLmNvbW9kb2NhLmNvbS9BZGRU cnVzdEV4dGVybmFsQ0FSb290LmNybDA2oDSgMoYwaHR0cDovL2NybC5jb21vZG8ubmV0L0Fk ZFRydXN0RXh0ZXJuYWxDQVJvb3QuY3JsMA0GCSqGSIb3DQEBBQUAA4IBAQAZ2IkRbyispgCi 54fBm5AD236hEv0e8+LwAamUVEJrmgnEoG3XkJIEA2Z5Q3H8+G+v23ZF4jcaPd3kWQR4rBz0 g0bzes9bhHIt5UbBuhgRKfPLSXmHPLptBZ2kbWhPrXIUNqi5sf2/z3/wpGqUNVCPz4FtVbHd WTBK322gnGQfSXzvNrv042n0+DmPWq1LhTq3Du3Tzw1EovsEv+QvcI4l+1pUBrPQxLxtjftz Mizpm4QkLdZ/kXpoAlAfDj9N6cz1u2fo3BwuO/xOzf4CjuOoEwqlJkRl6RDyTVKnrtw+ymsy XEFs/vVdoOr/0fqbhlhtPZZH5f4ulQTCAMyOofK7MIIF0TCCBLmgAwIBAgIRAO/VhC9jut1L VBHVMdY1Si4wDQYJKoZIhvcNAQEFBQAwga4xCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEX MBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29y azEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYwNAYDVQQDEy1VVE4tVVNF UkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwwHhcNMDcwMTAzMDAwMDAw WhcNMDgwMTAzMjM1OTU5WjCB5DE1MDMGA1UECxMsQ29tb2RvIFRydXN0IE5ldHdvcmsgLSBQ RVJTT05BIE5PVCBWQUxJREFURUQxRjBEBgNVBAsTPVRlcm1zIGFuZCBDb25kaXRpb25zIG9m IHVzZTogaHR0cDovL3d3dy5jb21vZG8ubmV0L3JlcG9zaXRvcnkxHzAdBgNVBAsTFihjKTIw MDMgQ29tb2RvIExpbWl0ZWQxGjAYBgNVBAMTEUJlbmphbWluIE1pbnNoYWxsMSYwJAYJKoZI hvcNAQkBFhdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejCBnzANBgkqhkiG9w0BAQEFAAOBjQAw gYkCgYEA4TykjizBp56X8r4qtMHghhYPSqjmfPc2khSEGsVmHcGZ4PvF3PcEzZAH7379blkA MofRC64IssMJnXhh6B4LZwPC6GxOK3TtsU0W76w1KBumVGWOGdz7qKbTCorRMlv2eZe4wQ/L DEdH3COLU14q0AaJPoKsSBp9s4QkRqGPP8kCAwEAAaOCAjQwggIwMB8GA1UdIwQYMBaAFImC Z33EnSZwAEu0UEh83j2uBG59MB0GA1UdDgQWBBTSE99IHnuw6o43lN4Ok9ErH4IjvDAOBgNV HQ8BAf8EBAMCBaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZIAYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCsw KQYIKwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0w gZowTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50 QXV0aGVudGljYXRpb25hbmRFbWFpbC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5l dC9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMIGGBggr BgEFBQcBAQR6MHgwOwYIKwYBBQUHMAKGL2h0dHA6Ly9jcnQuY29tb2RvY2EuY29tL1VUTkFk ZFRydXN0Q2xpZW50Q0EuY3J0MDkGCCsGAQUFBzAChi1odHRwOi8vY3J0LmNvbW9kby5uZXQv VVROQWRkVHJ1c3RDbGllbnRDQS5jcnQwIgYDVR0RBBswGYEXbWluc2hhbGxAaW50ZWxsaWNv bi5iaXowDQYJKoZIhvcNAQEFBQADggEBAFpPQSgEbe4qqAluhCVH5VVbS1IAAKEudK/pZB2A 4/wJD7W12XAtSzt4snOYEAecIU9YHrTAPqG592X30+7Vb3EsItTe5YQMUsHIYPM6vV1KYHRs gHTVi0qRnuQDrUsg6QjgSS/N1MaIbwBKXXBegsDQko27YuhTMe5nMr0Fk6MZn2HXIlVES4nu cpovUbNkipMeHNAh8zRC/04/Cs5n5eKarpM+xgedcOgNlsJoKrqzD8n6DRMm417xugLJvSPt ZO1ZpmGvqP4FHV2hu8/T2Xz6vklPHQfkY9ltnbmuyzhG7MfdVN2VVzZxVwpilMmdfpfO0dR/ +TiWRw3M5gl7rtcwggXRMIIEuaADAgECAhEA79WEL2O63UtUEdUx1jVKLjANBgkqhkiG9w0B AQUFADCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2Ug Q2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8v d3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xpZW50IEF1dGhl bnRpY2F0aW9uIGFuZCBFbWFpbDAeFw0wNzAxMDMwMDAwMDBaFw0wODAxMDMyMzU5NTlaMIHk MTUwMwYDVQQLEyxDb21vZG8gVHJ1c3QgTmV0d29yayAtIFBFUlNPTkEgTk9UIFZBTElEQVRF RDFGMEQGA1UECxM9VGVybXMgYW5kIENvbmRpdGlvbnMgb2YgdXNlOiBodHRwOi8vd3d3LmNv bW9kby5uZXQvcmVwb3NpdG9yeTEfMB0GA1UECxMWKGMpMjAwMyBDb21vZG8gTGltaXRlZDEa MBgGA1UEAxMRQmVuamFtaW4gTWluc2hhbGwxJjAkBgkqhkiG9w0BCQEWF21pbnNoYWxsQGlu dGVsbGljb24uYml6MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDhPKSOLMGnnpfyviq0 weCGFg9KqOZ89zaSFIQaxWYdwZng+8Xc9wTNkAfvfv1uWQAyh9ELrgiywwmdeGHoHgtnA8Lo bE4rdO2xTRbvrDUoG6ZUZY4Z3PuoptMKitEyW/Z5l7jBD8sMR0fcI4tTXirQBok+gqxIGn2z hCRGoY8/yQIDAQABo4ICNDCCAjAwHwYDVR0jBBgwFoAUiYJnfcSdJnAAS7RQSHzePa4Ebn0w HQYDVR0OBBYEFNIT30gee7DqjjeU3g6T0SsfgiO8MA4GA1UdDwEB/wQEAwIFoDAMBgNVHRMB Af8EAjAAMCAGA1UdJQQZMBcGCCsGAQUFBwMEBgsrBgEEAbIxAQMFAjARBglghkgBhvhCAQEE BAMCBSAwRgYDVR0gBD8wPTA7BgwrBgEEAbIxAQIBAQEwKzApBggrBgEFBQcCARYdaHR0cHM6 Ly9zZWN1cmUuY29tb2RvLm5ldC9DUFMwgaUGA1UdHwSBnTCBmjBMoEqgSIZGaHR0cDovL2Ny bC5jb21vZG9jYS5jb20vVVROLVVTRVJGaXJzdC1DbGllbnRBdXRoZW50aWNhdGlvbmFuZEVt YWlsLmNybDBKoEigRoZEaHR0cDovL2NybC5jb21vZG8ubmV0L1VUTi1VU0VSRmlyc3QtQ2xp ZW50QXV0aGVudGljYXRpb25hbmRFbWFpbC5jcmwwgYYGCCsGAQUFBwEBBHoweDA7BggrBgEF BQcwAoYvaHR0cDovL2NydC5jb21vZG9jYS5jb20vVVROQWRkVHJ1c3RDbGllbnRDQS5jcnQw OQYIKwYBBQUHMAKGLWh0dHA6Ly9jcnQuY29tb2RvLm5ldC9VVE5BZGRUcnVzdENsaWVudENB LmNydDAiBgNVHREEGzAZgRdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejANBgkqhkiG9w0BAQUF AAOCAQEAWk9BKARt7iqoCW6EJUflVVtLUgAAoS50r+lkHYDj/AkPtbXZcC1LO3iyc5gQB5wh T1getMA+obn3ZffT7tVvcSwi1N7lhAxSwchg8zq9XUpgdGyAdNWLSpGe5AOtSyDpCOBJL83U xohvAEpdcF6CwNCSjbti6FMx7mcyvQWToxmfYdciVURLie5ymi9Rs2SKkx4c0CHzNEL/Tj8K zmfl4pqukz7GB51w6A2WwmgqurMPyfoNEybjXvG6Asm9I+1k7VmmYa+o/gUdXaG7z9PZfPq+ SU8dB+Rj2W2dua7LOEbsx91U3ZVXNnFXCmKUyZ1+l87R1H/5OJZHDczmCXuu1zGCA9IwggPO AgEBMIHEMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFr ZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6 Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0 aGVudGljYXRpb24gYW5kIEVtYWlsAhEA79WEL2O63UtUEdUx1jVKLjAJBgUrDgMCGgUAoIIC YzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNzAzMDEyMDQx MTlaMCMGCSqGSIb3DQEJBDEWBBSFi6xapmE2nsWpEpIUrscn7rCATjBSBgkqhkiG9w0BCQ8x RTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMC BzANBggqhkiG9w0DAgIBKDCB1QYJKwYBBAGCNxAEMYHHMIHEMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhEA 79WEL2O63UtUEdUx1jVKLjCB1wYLKoZIhvcNAQkQAgsxgceggcQwga4xCzAJBgNVBAYTAlVT MQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYw NAYDVQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwC EQDv1YQvY7rdS1QR1THWNUouMA0GCSqGSIb3DQEBAQUABIGAWD5qjy+qDPaIEvQu+UjzU0vQ OcEtgE2R++w4pMeYa6NjZw58+9+DilUsMleCh8We/83A1kUHvOo3ATxKwgzQh0E43arUx+hT lcVAxnWii6AOitYYRrOhICYqL5kC1W95e7eXkkLUnR/QOC7+waEw7ZYwQRx89ZWlMSK7OKUc SwMAAAAAAAA= --------------ms010108090709020804050807--