Thread: Real time query analyzer
Hi everybody! I ask you for your help on a problem I have. I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have some VERY memory intense queries, that put processor up to 40%. I see all this info in unix "top" command or ps -aux Unfortunately they don't show me the query itsself. Is there a tool for postgres that can show me the queries and the time they take and the amount of processor is consuming (as Oracle has) ? I appreciate any help. Thank you, Adrian
Adrian Suciu <adrian.suciu@netopia-system.com> schrieb: > Hi everybody! > I ask you for your help on a problem I have. > I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have some > VERY memory intense queries, that put processor up to 40%. I see all this > info in unix "top" command or ps -aux > Unfortunately they don't show me the query itsself. > > Is there a tool for postgres that can show me the queries and the time they > take and the amount of processor is consuming (as Oracle has) ? You can do some things: - set log_min_duration_statement = X to log all statments with a duration more than X ms. I'm not sure, if this possible with 7.4 - select * from pg_stat_activity; you should enable stats_command_string ;-) Now you can identify your expensive querys and anaylse this with EXPLAIN. 17:53 < akretschmer> ??explain 17:53 < rtfm_please> For information about explain 17:53 < rtfm_please> see http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi 17:53 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-explain.html Read the links above to learn more about EXPLAIN. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Mon, Oct 16, 2006 at 06:10:18PM +0300, Adrian Suciu wrote: > Hi everybody! > I ask you for your help on a problem I have. > I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have > some VERY memory intense queries, that put processor up to 40%. I see Note that you're likely to see double the performance of 7.4 by going to 8.1.5. You'll likely get an even larger gain from 8.2 when it's out. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/18/06 16:08, Jim C. Nasby wrote: > On Mon, Oct 16, 2006 at 06:10:18PM +0300, Adrian Suciu wrote: >> Hi everybody! >> I ask you for your help on a problem I have. >> I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have >> some VERY memory intense queries, that put processor up to 40%. I see > > Note that you're likely to see double the performance of 7.4 by going to > 8.1.5. You'll likely get an even larger gain from 8.2 when it's out. What's the release target for 8.2? (If I get approval and the money, we'll be creating a roughly 8TB database, and in-place upgrade is the only way that we'd be able to migrate from $EXISTING_VERSION to $NEW_VERSION. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNpw5S9HxQb37XmcRAqUGAKCNMHXclyTP3Z2Bg5GIWF47xjc0IACg4YU6 KCtNVChdUo9NAJrf1aFVITw= =+uon -----END PGP SIGNATURE-----
On Wed, Oct 18, 2006 at 04:27:21PM -0500, Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 10/18/06 16:08, Jim C. Nasby wrote: > > On Mon, Oct 16, 2006 at 06:10:18PM +0300, Adrian Suciu wrote: > >> Hi everybody! > >> I ask you for your help on a problem I have. > >> I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have > >> some VERY memory intense queries, that put processor up to 40%. I see > > > > Note that you're likely to see double the performance of 7.4 by going to > > 8.1.5. You'll likely get an even larger gain from 8.2 when it's out. > > What's the release target for 8.2? > > (If I get approval and the money, we'll be creating a roughly 8TB > database, and in-place upgrade is the only way that we'd be able to > migrate from $EXISTING_VERSION to $NEW_VERSION. There's been very few (any?) bugs reported against beta1, so it could potentially be Real Soon Now. If forced to guess, I'd say mid-November, but of course the real answer is "when it's ready". There's also talk of an in-place upgrade tool for 8.1 to 8.2. In any case, you'll be much, much happier if you do this project on at least 8.1.x, as 7.4 is pretty long in the tooth. Due to Red Hat's support requirements it will probably remain supported for a few more years by Tom/the community, but you might start having issues getting commercial support. Are you planning on backing up straight to tape? -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/18/06 17:22, Jim C. Nasby wrote: > On Wed, Oct 18, 2006 at 04:27:21PM -0500, Ron Johnson wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On 10/18/06 16:08, Jim C. Nasby wrote: >>> On Mon, Oct 16, 2006 at 06:10:18PM +0300, Adrian Suciu wrote: >>>> Hi everybody! >>>> I ask you for your help on a problem I have. >>>> I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have >>>> some VERY memory intense queries, that put processor up to 40%. I see >>> Note that you're likely to see double the performance of 7.4 by going to >>> 8.1.5. You'll likely get an even larger gain from 8.2 when it's out. >> What's the release target for 8.2? >> >> (If I get approval and the money, we'll be creating a roughly 8TB >> database, and in-place upgrade is the only way that we'd be able to >> migrate from $EXISTING_VERSION to $NEW_VERSION. > > There's been very few (any?) bugs reported against beta1, so it could > potentially be Real Soon Now. If forced to guess, I'd say mid-November, > but of course the real answer is "when it's ready". > > There's also talk of an in-place upgrade tool for 8.1 to 8.2. > > In any case, you'll be much, much happier if you do this project on at > least 8.1.x, as 7.4 is pretty long in the tooth. Due to Red Hat's > support requirements it will probably remain supported for a few more > years by Tom/the community, but you might start having issues getting > commercial support. We'll be using RHES4, I guess, so if it uses 7.4, then I'll have to convince the SysAdmin to install 8.1 or 8.2. x.y.0 always scares me, so it would probably be 8.1.latest. > Are you planning on backing up straight to tape? The data CSV files will already be on OpenVMS tapes, so it's not a *primary* concern. *If* it gets purchased, and *if* it is put on the gigabit LAN, then I'd try for some sort of remote backup. Since the data will be relatively static (only loaded monthly, and no updates or deletes) and in by-quarter tables, I'm thinking that it would be adequate to "pg_dump -t" on the /current/ tables, "tar cj", ftp the tarball it to a machine with a tape drive. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNq3OS9HxQb37XmcRAsRsAKDpUIt/IbeZ59okvCEa65hltEgDNQCfanhE bCOklYbzLJROUfKnCGbIFkg= =TybQ -----END PGP SIGNATURE-----
On Wed, Oct 18, 2006 at 05:42:22PM -0500, Ron Johnson wrote: > > In any case, you'll be much, much happier if you do this project on at > > least 8.1.x, as 7.4 is pretty long in the tooth. Due to Red Hat's > > support requirements it will probably remain supported for a few more > > years by Tom/the community, but you might start having issues getting > > commercial support. > > We'll be using RHES4, I guess, so if it uses 7.4, then I'll have to > convince the SysAdmin to install 8.1 or 8.2. Typical reports are that 8.1 is 2x faster than 7.4, so it's probably well worth it to do so. If support from Red Hat becomes an issue, there's other options available. > > Are you planning on backing up straight to tape? > > The data CSV files will already be on OpenVMS tapes, so it's not a > *primary* concern. > > *If* it gets purchased, and *if* it is put on the gigabit LAN, then > I'd try for some sort of remote backup. > > Since the data will be relatively static (only loaded monthly, and > no updates or deletes) and in by-quarter tables, I'm thinking that > it would be adequate to "pg_dump -t" on the /current/ tables, "tar > cj", ftp the tarball it to a machine with a tape drive. If that's OK for backups then you could use the same technique to upgrade (assuming upgrade-in-place isn't available). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/18/06 17:56, Jim C. Nasby wrote: > On Wed, Oct 18, 2006 at 05:42:22PM -0500, Ron Johnson wrote: >>> In any case, you'll be much, much happier if you do this project on at >>> least 8.1.x, as 7.4 is pretty long in the tooth. Due to Red Hat's >>> support requirements it will probably remain supported for a few more >>> years by Tom/the community, but you might start having issues getting >>> commercial support. >> We'll be using RHES4, I guess, so if it uses 7.4, then I'll have to >> convince the SysAdmin to install 8.1 or 8.2. > > Typical reports are that 8.1 is 2x faster than 7.4, so it's probably > well worth it to do so. If support from Red Hat becomes an issue, > there's other options available. It's a definite goal! >> Since the data will be relatively static (only loaded monthly, and >> no updates or deletes) and in by-quarter tables, I'm thinking that >> it would be adequate to "pg_dump -t" on the /current/ tables, "tar >> cj", ftp the tarball it to a machine with a tape drive. > > If that's OK for backups then you could use the same technique to > upgrade (assuming upgrade-in-place isn't available). True. Reindexing that much data is not that high on my ways-to-use- resources list, though. ;) - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNrPqS9HxQb37XmcRAsNhAJ9vc9zWXL8qZOrEm9fjjMKVOOiniwCdHyM+ akxBBNBLIy4oVAa3TI9ZM6Y= =B+wy -----END PGP SIGNATURE-----
Ron Johnson <ron.l.johnson@cox.net> writes:
> We'll be using RHES4, I guess, so if it uses 7.4, then I'll have to
> convince the SysAdmin to install 8.1 or 8.2.
<plug>
Red Hat is now selling an "Application Stack" addon for RHEL4 that
provides Postgres 8.1.x, as well as more modern versions of some other
packages. So if your admin's objection is "Red Hat won't support that",
point him to the stack product.
</plug>
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/18/06 18:08, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> We'll be using RHES4, I guess, so if it uses 7.4, then I'll have to >> convince the SysAdmin to install 8.1 or 8.2. > > <plug> > Red Hat is now selling an "Application Stack" addon for RHEL4 that > provides Postgres 8.1.x, as well as more modern versions of some other > packages. So if your admin's objection is "Red Hat won't support that", > point him to the stack product. > </plug> I guess that means that ES4 comes with v7.4. Yep, ftp.redhat.com shows that it has v7.4.8. :( - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFNrrgS9HxQb37XmcRAuIQAJ9FduZ+SHrOlaQs42lWDzOGjBi3nQCfeq9h R5+O2MvvGFpzlqwtde1/9rQ= =B2TP -----END PGP SIGNATURE-----