Thread: Tool to compare db schemas?
Does anyone know of a good tool to compare db schemas? I have over 200 db's that should all have identical schemas and have been asked to report on the differences. Thanks, Chris
hi, Chris Hoover wrote: > Does anyone know of a good tool to compare db schemas? I have over 200 db's > that should all have identical schemas and have been asked to report on the > differences. if you looking for a program, check http://ems-hitech.com/pgsqlutils/index.phtml#pgcomparer but you can write your own scripts to compare schemas, even in plpgsql too with different servers (using dblink) C.
On Tue, 2004-05-04 at 13:37, Chris Hoover wrote: > Does anyone know of a good tool to compare db schemas? I have over 200 db's > that should all have identical schemas and have been asked to report on the > differences. > http://pgdiff.sourceforge.net/ http://gborg.postgresql.org/project/pgdiff/projdisplay.php If you run across any others please lmk, I think I'm going to need this for a project in the next few days. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
When I run an query (that I have identified as expensive, and non-critical), I would like to be able to take its priority down a few notches to allow the standard every-day tasks of the DB to run unhindered. As it stands, postmasters elbow each other out for processor time, and it seems like one expensive query can bring other DB functions to a crawl. Hardware : Dual P4 Xeon 2.8GHz; 1 GB RAM; ULTRA320 RAID 10 with an ext3 filesystem PostgreSQL 7.4.2 What can be done to allow for smarter preemption? Could I do anything at the OS level to throttle that particular postmaster's process? I'm running (IMO) a balanced config, but there's always room for improvement. Its that oddball query that comes around once every so often that causes the problem. CG __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover
On Wed, 5 May 2004, Chris Gamache wrote: > When I run an query (that I have identified as expensive, and non-critical), I > would like to be able to take its priority down a few notches to allow the > standard every-day tasks of the DB to run unhindered. As it stands, postmasters > elbow each other out for processor time, and it seems like one expensive query > can bring other DB functions to a crawl. > > Hardware : Dual P4 Xeon 2.8GHz; 1 GB RAM; ULTRA320 RAID 10 with an ext3 > filesystem > PostgreSQL 7.4.2 > > What can be done to allow for smarter preemption? Could I do anything at the OS > level to throttle that particular postmaster's process? I'm running (IMO) a > balanced config, but there's always room for improvement. Its that oddball > query that comes around once every so often that causes the problem. It is inadvisable to change priority of backends as that could lead to deadlocks in certain situations I believe. Have you read: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html yet? It may just be that you don't have enough shared_buffers or whatnot.
Chris Gamache wrote: > What can be done to allow for smarter preemption? Could I do anything at the OS> level to throttle that particular postmaster's process? I'm running (IMO) a> balanced config, but there's always room for improvement. Its that oddball> query that comes around once every so often that causes the problem. You can basically renice the process that is performing the query. See 'man nice' for details. Regards Gaetano Mendola
Gaetano Mendola <mendola@bigfoot.com> writes:
> You can basically renice the process that is performing the query.
However, that's unlikely to do anything very pleasant, since you'll have
priority-inversion problems. "nice" has no idea when the process is
holding a lock that someone else wants ...
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola <mendola@bigfoot.com> writes: | |>You can basically renice the process that is performing the query. | | | However, that's unlikely to do anything very pleasant, since you'll have | priority-inversion problems. "nice" has no idea when the process is | holding a lock that someone else wants ... That can be true, however in order to have a priority-inversion problem I think are necessary 3 different level of priority, you have carefully choose the postmaster and good value of nice in order to have it happen. I was wandering about do the same work done with vacuum ( the sleep trick each n records) in order to slow some expensive but not crucial queries: test> set query_delay = 10; <-- 10 ms test> select * from <very expensive query >; Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAmZuP7UpzwH2SGd4RAvVxAKCfvQDk2CkdcC2dCFtgg7nLzf7qTwCgt8/w F0zVE0HYoI9lb9l7u9qwZIo= =/mFq -----END PGP SIGNATURE-----
On Thu, 6 May 2004, Gaetano Mendola wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Tom Lane wrote: > > | Gaetano Mendola <mendola@bigfoot.com> writes: > | > |>You can basically renice the process that is performing the query. > | > | > | However, that's unlikely to do anything very pleasant, since you'll have > | priority-inversion problems. "nice" has no idea when the process is > | holding a lock that someone else wants ... > > That can be true, however in order to have a priority-inversion problem > I think are necessary 3 different level of priority, you have carefully > choose the postmaster and good value of nice in order to have it happen. > > I was wandering about do the same work done with vacuum ( the sleep > trick each n records) in order to slow some expensive but not crucial > queries: > > test> set query_delay = 10; <-- 10 ms > test> select * from <very expensive query >; I like that idea. Make it more like a query_priority and let the system figure out delays though.
--- "scott.marlowe" <scott.marlowe@ihs.com> wrote: > On Wed, 5 May 2004, Chris Gamache wrote: > Have you read: > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > yet? It may just be that you don't have enough shared_buffers or whatnot. Absolutely. I have a copy on my desk, and one under my pillow at night. :) PostgreSQL profiling is a bit of an art. I've been a settings-tweaker since I can remember. I feel like my config is pretty well balanced. Its that giant, oddball-query that throws the proverbial wrench in the gears. The guide is quick to warn that "too much" in any particular setting can be as bad as "not enough" ... That's not to say that someone else, with more expertise than I, couldn't look at what I have as far as settings and choose some better ones. The query_priority idea sounds like a promising idea. For queries that you /know/ will be expensive (or that you know you want to have run lickety-split), it'd do the trick. The next logical step would be an "intelligent" query priority engine. However, that might require some ESP (the X-Files kind) ... How do the other big RDBMS's do it? CG __________________________________ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover