pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
database profiling 2+ messages / 2 participants [nested] [flat]
* database profiling @ 2004-01-20 19:47 Orion Henry <orion@trustcommerce.com> 2004-01-21 00:24 ` Re: database profiling Christopher Kings-Lynne <chriskl@familyhealth.com.au> 0 siblings, 1 reply; 2+ messages in thread From: Orion Henry @ 2004-01-20 19:47 UTC (permalink / raw) To: pgsql-performance 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 Attachments: signature.asc (application/pgp-signature, 188B) download ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: database profiling 2004-01-20 19:47 database profiling Orion Henry <orion@trustcommerce.com> @ 2004-01-21 00:24 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au> 0 siblings, 0 replies; 2+ messages in thread From: Christopher Kings-Lynne @ 2004-01-21 00:24 UTC (permalink / raw) To: Orion Henry <orion@trustcommerce.com>; +Cc: pgsql-performance > 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. Enable the stats collector in your postgresql.conf, and then look at the pg_stat_* views. They are your friend. > 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. Lots of that is in the pg_statio_* tables (if you have the collector running) > 3) The ability to view the current running time, plan and SQL text of > all currently running queries. That's in the pg_stat_activity table. There's also a new postgresql.conf setting in 7.4 that lets you log any query that runs more than a certain number of milliseconds - which is very useful for tracking down your slow queries. Chris ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-21 00:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-01-20 19:47 database profiling Orion Henry <orion@trustcommerce.com> 2004-01-21 00:24 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox