X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2F759D1EC0F for ; Tue, 20 Jan 2004 20:16:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26738-02 for ; Tue, 20 Jan 2004 16:16:07 -0400 (AST) Received: from front1.mail.megapathdsl.net (front1.mail.megapathdsl.net [66.80.60.31]) by svr1.postgresql.org (Postfix) with ESMTP id 77026D1EC1A for ; Tue, 20 Jan 2004 15:47:16 -0400 (AST) Received: from [64.32.131.193] (HELO [192.168.1.11]) by front1.mail.megapathdsl.net (CommuniGate Pro SMTP 4.1.3) with ESMTP id 140876181 for pgsql-performance@postgresql.org; Tue, 20 Jan 2004 11:47:10 -0800 Subject: database profiling From: Orion Henry To: pgsql-performance@postgresql.org Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-YceqiCXq26Nibl4mLjl4" Organization: Message-Id: <1074628029.1563.521.camel@orthanc> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 20 Jan 2004 11:47:09 -0800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/202 X-Sequence-Number: 5376 --=-YceqiCXq26Nibl4mLjl4 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable I'm running several postgreSQL databases containing tens of millions of rows. While postgreSQL has proven itself amazingly scalable, I am finding it difficult to properly tune/profile these databases as they grow. I am not aware of any tools to profile the database outside of heavy use of the "EXPLAIN" command and application space logging of query times. I wanted to lay down some ideas I have for some powerful tools for dealing with this. Please let me know if these are redundant, not needed or too complex to implement. 1) Statistics kept on all the tables and indexes showing the number of times and the number of milliseconds spent sequential scanning / index scanning them. You could easily identify an index that's not being used or a table that's being sequential scanned too often. Also supply a database command to reset the statistic gather to zero. 2) Statics that show count/total times to insert into/updating/deleting from a table with time times spent in table and index separated so you could see how much the indexes are effecting the write performance on the table. 3) The ability to view the current running time, plan and SQL text of all currently running queries. Thank you for your time, ___________________________________________________ O R I O N H E N R Y Chief Technical Officer, TrustCommerce 959 East Colorado Blvd #1, Pasadena, CA 91106 (626) 744-7700 x815, fax (626) 628-3431 orion@trustcommerce.com www.trustcommerce.com --=-YceqiCXq26Nibl4mLjl4 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQBADYW99IoQcq23xtARAmA4AJ9tA8AIjrizfEKyxULR/bU8AcYkYwCfZNz5 N27D/FHhuTrvRwTIWLSKhfs= =HZtY -----END PGP SIGNATURE----- --=-YceqiCXq26Nibl4mLjl4--