Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 15F639FA4ED for ; Fri, 9 Feb 2007 12:27:26 -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 91378-09 for ; Fri, 9 Feb 2007 12:27:12 -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 2FD8D9F9AFC for ; Fri, 9 Feb 2007 12:27:12 -0400 (AST) Received: from c-24-11-214-165.hsd1.mi.comcast.net ([24.11.214.165]:63107 helo=[10.2.1.2]) by beast.liquidweb.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.52) id 1HFYaU-00066A-EZ; Fri, 09 Feb 2007 11:27:02 -0500 Message-ID: <45CCA0DB.2010903@intellicon.biz> Date: Fri, 09 Feb 2007 11:27:07 -0500 From: Benjamin Minshall Organization: Intellicon, Inc. User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) MIME-Version: 1.0 To: Tom Lane CC: Alvaro Herrera , 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> In-Reply-To: <29832.1171036557@sss.pgh.pa.us> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms010802000301080709050609" 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.662 tagged_above=0 required=5 tests=AWL, BAYES_20, BIZ_TLD X-Spam-Level: * X-Archive-Number: 200702/102 X-Sequence-Number: 23156 This is a cryptographically signed message in MIME format. --------------ms010802000301080709050609 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > I wrote: >> Alvaro Herrera writes: >>> Regarding temp tables, I'd think that the pgstat entries should be >>> getting dropped at some point in both releases. Maybe there's a bug >>> preventing that in 8.2? > >> Hmmm ... I did rewrite the backend-side code for that just recently for >> performance reasons ... could I have broken it? > > I did some testing with HEAD and verified that pgstat_vacuum_tabstat() > still seems to do what it's supposed to, so that theory falls down. > > Alvaro, could you send Benjamin your stat-file-dumper tool so we can > get some more info? > Alternatively, if Benjamin wants to send me a copy > of his stats file (off-list), I'd be happy to take a look. > > regards, tom lane When I checked on the server this morning, the huge stats file has returned to a normal size. I set up a script to track CPU usage and stats file size, and it appears to have decreased from 90MB down to about 2MB over roughly 6 hours last night. The CPU usage of the stats collector also decreased accordingly. The application logs indicate that there was no variation in the workload over this time period, however the file size started to decrease soon after the nightly pg_dump backups completed. Coincidence perhaps? Nonetheless, I would appreciate a copy of Alvaro's stat file tool just to see if anything stands out in the collected stats. Thanks for your help, Tom. -- Benjamin Minshall Senior Developer -- Intellicon, Inc. http://www.intellicon.biz --------------ms010802000301080709050609 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 YzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNzAyMDkxNjI3 MDdaMCMGCSqGSIb3DQEJBDEWBBSrMmb6TapBLkZT9ikoWJexRlQNTDBSBgkqhkiG9w0BCQ8x RTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMC BzANBggqhkiG9w0DAgIBKDCB1QYJKwYBBAGCNxAEMYHHMIHEMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhEA 79WEL2O63UtUEdUx1jVKLjCB1wYLKoZIhvcNAQkQAgsxgceggcQwga4xCzAJBgNVBAYTAlVT MQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYw NAYDVQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwC EQDv1YQvY7rdS1QR1THWNUouMA0GCSqGSIb3DQEBAQUABIGAsu6PyYxla679YhOZ+KOxnZzf 6J3St1SRb74YixRGP5cQEC6uMJ8Eukews/piTf/UlwOvS4fNf6Lir6BFdVMnBllFmVpIFAc2 tZd7Lc5J06rPXAAUSBqLrVb2OkYuJb3xUN4IQNCrOPqy9DmoR1K5tQQWeOTJTPnrhxLvZ8cW sy4AAAAAAAA= --------------ms010802000301080709050609--