pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase conversion woes...
9+ messages / 8 participants
[nested] [flat]
* Database conversion woes...
@ 2004-02-03 16:42 Kevin Carpenter <kevin@valenfor.com>
2004-02-03 17:00 ` Re: Database conversion woes... Jeff <threshar@torgo.978.org>
2004-02-03 17:30 ` Re: Database conversion woes... scott.marlowe <scott.marlowe@ihs.com>
2004-02-03 19:01 ` Re: Database conversion woes... Josh Berkus <josh@agliodbs.com>
2004-02-03 19:29 ` Re: Database conversion woes... Richard Huxton <dev@archonet.com>
2004-02-03 19:59 ` Re: Database conversion woes... Christopher Browne <cbbrowne@acm.org>
0 siblings, 5 replies; 9+ messages in thread
From: Kevin Carpenter @ 2004-02-03 16:42 UTC (permalink / raw)
To: pgsql-performance
Hello everyone,
I am doing a massive database conversion from MySQL to Postgresql for a
company I am working for. This has a few quirks to it that I haven't
been able to nail down the answers I need from reading and searching
through previous list info.
For starters, I am moving roughly 50 seperate databases which each one
represents one of our clients and is roughly 500 megs to 3 gigs in size.
Currently we are using the MySQL replication, and so I am looking at
Mammoths replicator for this one. However I have seen it only allows on
DB to be replicated at a time. With the size of each single db, I don't
know how I could put them all together under one roof, and if I was
going to, what are the maximums that Postgres can handle for tables in
one db? We track over 2 million new points of data (records) a day, and
are moving to 5 million in the next year.
Second what about the physical database size, what are the limits there?
I have seen that it was 4 gig on Linux from a 2000 message, but what
about now? Have we found way's past that?
Thanks in advance, will give more detail - just looking for some open
directions and maybe some kicks to fuel my thought in other areas.
Thanks,
--
kevin@valenfor.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
@ 2004-02-03 17:00 ` Jeff <threshar@torgo.978.org>
4 siblings, 0 replies; 9+ messages in thread
From: Jeff @ 2004-02-03 17:00 UTC (permalink / raw)
To: kevin@valenfor.com; +Cc: pgsql-performance
On Tue, 03 Feb 2004 11:42:59 -0500
"Kevin Carpenter" <kevin@valenfor.com> wrote:
> For starters, I am moving roughly 50 seperate databases which each one
> represents one of our clients and is roughly 500 megs to 3 gigs in
> size.
> Currently we are using the MySQL replication, and so I am looking at
> Mammoths replicator for this one. However I have seen it only allows
> on DB to be replicated at a time. With the size of each single db, I
Not knowing too much about mammoths, but how the others work, you should
be able to run a replicator for each db. (Or hack a shell script up to
make it run the replicator for each db.. either way each db will be
replicated independant of the others)
> don't know how I could put them all together under one roof, and if I
> was going to, what are the maximums that Postgres can handle for
> tables in one db? We track over 2 million new points of data
> (records) a day, and are moving to 5 million in the next year.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
@ 2004-02-03 17:30 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-03 22:27 ` Re: Database conversion woes... Kevin Carpenter <kevin.carpenter@icrossing.com>
4 siblings, 1 reply; 9+ messages in thread
From: scott.marlowe @ 2004-02-03 17:30 UTC (permalink / raw)
To: Kevin Carpenter <kevin@valenfor.com>; +Cc: pgsql-performance
On Tue, 3 Feb 2004, Kevin Carpenter wrote:
> Hello everyone,
>
> I am doing a massive database conversion from MySQL to Postgresql for a
> company I am working for. This has a few quirks to it that I haven't
> been able to nail down the answers I need from reading and searching
> through previous list info.
>
> For starters, I am moving roughly 50 seperate databases which each one
> represents one of our clients and is roughly 500 megs to 3 gigs in size.
> Currently we are using the MySQL replication, and so I am looking at
> Mammoths replicator for this one. However I have seen it only allows on
> DB to be replicated at a time.
Look into importing all those seperate databases into seperate schemas in
one postgresql database.
> With the size of each single db, I don't
> know how I could put them all together under one roof,
There's no functional difference to postgresql if you have 1 huge database
or 50 smaller ones that add up to the same size.
> and if I was
> going to, what are the maximums that Postgres can handle for tables in
> one db?
None. also see:
http://www.postgresql.org/docs/faqs/FAQ.html#4.5
> We track over 2 million new points of data (records) a day, and
> are moving to 5 million in the next year.
That's quite a bit. Postgresql can handle it.
> Second what about the physical database size, what are the limits there?
none.
> I have seen that it was 4 gig on Linux from a 2000 message, but what
> about now? Have we found way's past that?
It has never been 4 gig. It was once, a long time ago, 2 gig for a table
I believe. That was fixed years ago.
> Thanks in advance, will give more detail - just looking for some open
> directions and maybe some kicks to fuel my thought in other areas.
Import in bulk, either using copy or wrap a few thousand inserts inside
begin;end; pairs.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
2004-02-03 17:30 ` Re: Database conversion woes... scott.marlowe <scott.marlowe@ihs.com>
@ 2004-02-03 22:27 ` Kevin Carpenter <kevin.carpenter@icrossing.com>
0 siblings, 0 replies; 9+ messages in thread
From: Kevin Carpenter @ 2004-02-03 22:27 UTC (permalink / raw)
To: pgsql-performance
First just wanted to say thank you all for the quick and helpful
answers. With all the input I know I am on the right track. With that
in mind I created a perl script to do my migrations and to do it based
on moving from a db name to a schema name. I had done alot of the
reading on converting based on the miss match of data types that MySQL
likes to use. I must say it is VERY nice having a intelligent system
that say won't let a date of '0000-00-00' be entered. Luckily I didn't
have to deal with any enumerations.
So the conversion goes on. I will definitely be back and forth in here
as I get the new queries written and start migrating all I can back into
the pg backend using plpgsql or c for the stored procedures where
required. The mammoth replicator has been working well. I had tried
the pgsql-r and had limited success with it, and dbmirror was just
taking to long having to do 4 db transactions just to mirror one
command. I have eserv but was never really a java kind of guy.
Alright then - back to my code. Again thanks for the help and info.
Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
@ 2004-02-03 19:01 ` Josh Berkus <josh@agliodbs.com>
4 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2004-02-03 19:01 UTC (permalink / raw)
To: kevin@valenfor.com; pgsql-performance
Kevin,
> With the size of each single db, I don't
> know how I could put them all together under one roof, and if I was
> going to, what are the maximums that Postgres can handle for tables in
> one db? We track over 2 million new points of data (records) a day, and
> are moving to 5 million in the next year.
Use schemas per Scott's suggestion. This will also ease the sharing of data
between "databases".
> Second what about the physical database size, what are the limits there?
> I have seen that it was 4 gig on Linux from a 2000 message, but what
> about now? Have we found way's past that?
The biggest database I've ever worked with was 175G, but I've seen reports of
2TB databases out there. We don't know what the limit is; so far it's always
been hardware.
> Thanks in advance, will give more detail - just looking for some open
> directions and maybe some kicks to fuel my thought in other areas.
Come back to this list for help tuning your system! You'll need it, you've
got an unusual set-up.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
@ 2004-02-03 19:29 ` Richard Huxton <dev@archonet.com>
4 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-02-03 19:29 UTC (permalink / raw)
To: kevin@valenfor.com; pgsql-performance
On Tuesday 03 February 2004 16:42, Kevin Carpenter wrote:
>
> Thanks in advance, will give more detail - just looking for some open
> directions and maybe some kicks to fuel my thought in other areas.
I've taken to doing a lot of my data manipulation (version conversions etc) in
PG even if the final resting place is MySQL.
It's generally not too difficult to transfer data but you will have problems
with MySQL's more "relaxed attitude" to data types (things like all-zero
timestamps). I tend to write a script to tidy the data before export, and
repeatedly restore from backup until the script corrects all problems.Not
sure how convenient that'll be with dozens of gigs of data. Might be
practical to start with the smaller databases, let your script grow in
capabilities before importing the larger ones.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
@ 2004-02-03 19:59 ` Christopher Browne <cbbrowne@acm.org>
4 siblings, 0 replies; 9+ messages in thread
From: Christopher Browne @ 2004-02-03 19:59 UTC (permalink / raw)
To: pgsql-performance
kevin@valenfor.com ("Kevin Carpenter") writes:
> I am doing a massive database conversion from MySQL to Postgresql for a
> company I am working for. This has a few quirks to it that I haven't
> been able to nail down the answers I need from reading and searching
> through previous list info.
>
> For starters, I am moving roughly 50 seperate databases which each
> one represents one of our clients and is roughly 500 megs to 3 gigs
> in size. Currently we are using the MySQL replication, and so I am
> looking at Mammoths replicator for this one. However I have seen it
> only allows on DB to be replicated at a time. With the size of each
> single db, I don't know how I could put them all together under one
> roof, and if I was going to, what are the maximums that Postgres can
> handle for tables in one db? We track over 2 million new points of
> data (records) a day, and are moving to 5 million in the next year.
I'll be evasive about replication, because the answers are pretty
painful :-(, but as for the rest of it, nothing about this sounds
challenging.
There is room for debate here as to whether you should have:
a) One postmaster and many database instances,
2) One postmaster, one (or a few) database instances, and do the
client 'split' via schemas, or
iii) Several postmasters, many database instances.
Replication will tend to work best with scenario 2), which minimizes
the number of connections that are needed to manage replication;
that's definitely a factor worth considering.
It is also possible for it to be worthwhile to spread vastly differing
kinds of activity across different backends so that they can have
separate buffer caches. If all the activity is shared across one
postmaster, that means it is all shared across one buffer cache, and
there are pathological situations that are occasionally observed in
practice where one process will be "trashing" the shared cache,
thereby injuring performance for all other processes using that back
end. In such a case, it may be best to give the "ill-behaved" process
its own database instance with a small cache that it can thrash on
without inconveniencing others.
Jan Wieck is working on some improvements for buffer management in 7.5
that may improve the situation vis-a-vis buffering, but that is
certainly not something ready to deploy in production just yet.
> Second what about the physical database size, what are the limits
> there? I have seen that it was 4 gig on Linux from a 2000 message,
> but what about now? Have we found way's past that?
There's NO problem with having enormous databases now; each table is
represented as one or more files (if you break a size barrier, oft
configured as 1GB, it creates an "extent" and extends into another
file), and for there to be problems with this, the problems would be
_really crippling_ OS problems.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"We come to bury DOS, not to praise it."
-- Paul Vojta <vojta@math.berkeley.edu>, paraphrasing a quote of
Shakespeare
^ permalink raw reply [nested|flat] 9+ messages in thread
* Database conversion woes...
@ 2004-02-03 22:29 Kevin <kevin@valenfor.com>
2004-02-03 23:34 ` Re: Database conversion woes... Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 9+ messages in thread
From: Kevin @ 2004-02-03 22:29 UTC (permalink / raw)
To: pgsql-performance
First just wanted to say thank you all for the quick and helpful
answers. With all the input I know I am on the right track. With that
in mind I created a perl script to do my migrations and to do it based
on moving from a db name to a schema name. I had done alot of the
reading on converting based on the miss match of data types that MySQL
likes to use. I must say it is VERY nice having a intelligent system
that say won't let a date of '0000-00-00' be entered. Luckily I didn't
have to deal with any enumerations.
So the conversion goes on. I will definitely be back and forth in here
as I get the new queries written and start migrating all I can back into
the pg backend using plpgsql or c for the stored procedures where
required. The mammoth replicator has been working well. I had tried
the pgsql-r and had limited success with it, and dbmirror was just
taking to long having to do 4 db transactions just to mirror one
command. I have eserv but was never really a java kind of guy.
Alright then - back to my code. Again thanks for the help and info.
Kevin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Database conversion woes...
2004-02-03 22:29 Database conversion woes... Kevin <kevin@valenfor.com>
@ 2004-02-03 23:34 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-02-03 23:34 UTC (permalink / raw)
To: Kevin <kevin@valenfor.com>; pgsql-performance
On Tuesday 03 February 2004 22:29, Kevin wrote:
> The mammoth replicator has been working well. I had tried
> the pgsql-r and had limited success with it, and dbmirror was just
> taking to long having to do 4 db transactions just to mirror one
> command. I have eserv but was never really a java kind of guy.
When this is over and you've got the time, I don't suppose you could put
together a few hundred words describing your experiences with the Mammoth
replicator - there are a couple of places they could be posted.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-02-03 23:34 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-03 16:42 Database conversion woes... Kevin Carpenter <kevin@valenfor.com>
2004-02-03 17:00 ` Jeff <threshar@torgo.978.org>
2004-02-03 17:30 ` scott.marlowe <scott.marlowe@ihs.com>
2004-02-03 22:27 ` Kevin Carpenter <kevin.carpenter@icrossing.com>
2004-02-03 19:01 ` Josh Berkus <josh@agliodbs.com>
2004-02-03 19:29 ` Richard Huxton <dev@archonet.com>
2004-02-03 19:59 ` Christopher Browne <cbbrowne@acm.org>
2004-02-03 22:29 Database conversion woes... Kevin <kevin@valenfor.com>
2004-02-03 23:34 ` Richard Huxton <dev@archonet.com>
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