Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 03F699FB1ED for ; Fri, 9 Feb 2007 14:36:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 18450-09 for ; Fri, 9 Feb 2007 14:36:05 -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 613F39F9AFC for ; Fri, 9 Feb 2007 14:36:05 -0400 (AST) Received: from c-24-11-214-165.hsd1.mi.comcast.net ([24.11.214.165]:61611 helo=[10.2.1.2]) by beast.liquidweb.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.52) id 1HFabE-0005xw-6c; Fri, 09 Feb 2007 13:35:56 -0500 Message-ID: <45CCBF13.2000707@intellicon.biz> Date: Fri, 09 Feb 2007 13:36:03 -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> In-Reply-To: <1116.1171038786@sss.pgh.pa.us> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090208010101090708050506" 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=1.602 tagged_above=0 required=5 tests=AWL, BAYES_20, BIZ_TLD X-Spam-Level: * X-Archive-Number: 200702/105 X-Sequence-Number: 23159 This is a cryptographically signed message in MIME format. --------------ms090208010101090708050506 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > Well, that's pretty interesting. What are your vacuuming arrangements > for this installation? Could the drop in file size have coincided with > VACUUM operations? Because the ultimate backstop against bloated stats > files is pgstat_vacuum_tabstat(), which is run by VACUUM and arranges to > clean out any entries that shouldn't be there anymore. VACUUM and ANALYZE are done by autovacuum only, no cron jobs. autovacuum_naptime is 30 seconds so it should make it to each database every 10 minutes or so. Do you think that more aggressive vacuuming would prevent future swelling of the stats file? > It's sounding like what you had was just transient bloat, in which case > it might be useful to inquire whether anything out-of-the-ordinary had > been done to the database right before the excessive-CPU-usage problem > started. I don't believe that there was any unusual activity on the server, but I have set up some more detailed logging to hopefully identify a pattern if the problem resurfaces. Thanks. -- Benjamin Minshall Senior Developer -- Intellicon, Inc. http://www.intellicon.biz --------------ms090208010101090708050506 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 YzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNzAyMDkxODM2 MDNaMCMGCSqGSIb3DQEJBDEWBBR6Gn0ZG4sFFlz43aDh2ocgky4W8TBSBgkqhkiG9w0BCQ8x RTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMC BzANBggqhkiG9w0DAgIBKDCB1QYJKwYBBAGCNxAEMYHHMIHEMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhEA 79WEL2O63UtUEdUx1jVKLjCB1wYLKoZIhvcNAQkQAgsxgceggcQwga4xCzAJBgNVBAYTAlVT MQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYw NAYDVQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwC EQDv1YQvY7rdS1QR1THWNUouMA0GCSqGSIb3DQEBAQUABIGAdzd+lnBdNQfHKedOOrlzHgSL OAe6i3x0HegfF0bXAa3J5K/y2/sOSmoWWpT33nCd6CESC+kpCSvqUI1o345JNlJfs5A8ediK 7xrfRHtPzXIZqYIpdFZMKZlQ19ItXCPH9sIm3fM9+bnsQwiWvDldnUmGo4Q2eqXmU8VdXD3z UeAAAAAAAAA= --------------ms090208010101090708050506--