Thread: Tool to compare db schemas?

Tool to compare db schemas?

From
"Chris Hoover"
Date:
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


Re: Tool to compare db schemas?

From
CoL
Date:
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.

Re: Tool to compare db schemas?

From
Robert Treat
Date:
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


Postmaster hogs CPU

From
Chris Gamache
Date:
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

Re: Postmaster hogs CPU

From
"scott.marlowe"
Date:
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.


Re: Postmaster hogs CPU

From
Gaetano Mendola
Date:
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

Re: Postmaster hogs CPU

From
Tom Lane
Date:
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

Re: Postmaster hogs CPU

From
Gaetano Mendola
Date:
-----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-----


Re: Postmaster hogs CPU

From
"scott.marlowe"
Date:
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.


Re: Postmaster hogs CPU

From
Chris Gamache
Date:
--- "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