Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #242

Re: MySQL vs Postgres for a "data warehouse", 5-10 TB

Cross-posted to 2 groups.

Show all headers | View raw

On 2011-09-10, Michael Vilain <vilain@NOspamcop.net> wrote:
> This is why database consultants get paid big bucks.  Answering 
> questions like "what DB product should I buy?" and "how should I lay out 
> my database system?" are all bread and butter for these guys.  And you 
> want that sort of analysis for free?  Huh.

I was hoping that someone in these two groups has faced similar issues
before and would be willing to share their relevant experiences. 

> Basically, unless you're a high power database person who knows the ins 
> and outs of each vendor's products, how to optimize performance (that's 
> $150K/year easy), and can design and administer such systems, I think 
> you're going to have do the research own your own.  

Well, this is what I am trying to do here, do a research.

> Back in my day of system performance monitoring, a customer would
> ask me how their workload would run on a specific hardware
> configuration or why it's running poorly and should they buy the
> larger machine.  Poorly written applications will still perform
> poorly on a bigger machine, just faster.  Spend time designing
> something well with growth, scale, archival, and _yes Virginia,
> fricken backups_ and you'll save yourself some time and stress.
> Developers never think of "how do I back this up?", they just ask
> the datacenter to do it.  After they've gone live.

Well, for better or worse, I have already designed a backup system for
this warehouse. 

But there are issues of large datasets that just do not matter in
small datasets. I liken it to having a pet that happens to be an
elephant. It is not the same as having a mouse, cat or dog for a pet
-- a pet elephant comes with its own set of challenges. 

For example, I remember the horrors and upsets of REPAIR TABLE
statements that took almost a day. That was with a MyISAM table around
40 gigs (I do not remember the size exactly).

This means that I cannot use MyISAM, period, end of story, for
something 100 times the size of the above. 

And that is why I am asking for people with relevant experience to
share what it is. 

> I have no idea how well you've designed your database or your experience 
> in such matters.  You sound like a hardware guy from what you've 
> described. 

I am actually a computer programmer. 

> If this is just personal project, build it using MySQL, run some
> benchmarks, rebuild it with the same data on Postgres and run the
> same benchmarks.  If they're the same, add more data and see what
> happens?  Keep adding more data until you see a difference.  I've
> only used MySQL without transactions, so I can't tell you how well
> it will perform compared to Postgres which I think had them from day
> 1.  Some DBMS' have other ways of indexing tables beyond b-trees
> (DEC's RdB had a hashing method that allowed for very fast reads on
> certain types of tables).  I don't know if MySQL or Postgres offer
> that level of optimization.
>
> I just backup the stuff.

Doing it in both mysql and postgres, is not such a bad idea.

I have backups relatively well covered, as my paranoia about backups
has no bounds. 

i

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

MySQL vs Postgres for a "data warehouse", 5-10 TB Ignoramus5263 <ignoramus5263@NOSPAM.5263.invalid> - 2011-09-10 00:14 -0500
  Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Michael Vilain <vilain@NOspamcop.net> - 2011-09-09 23:30 -0700
    Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Luuk <Luuk@invalid.lan> - 2011-09-10 10:13 +0200
    Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Ignoramus13208 <ignoramus13208@NOSPAM.13208.invalid> - 2011-09-10 09:34 -0500
      Re: MySQL vs Postgres for a "data warehouse", 5-10 TB John Levine <johnl@iecc.com> - 2011-09-10 19:47 +0000
        Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Ignoramus13208 <ignoramus13208@NOSPAM.13208.invalid> - 2011-09-10 16:02 -0500
          Re: MySQL vs Postgres for a "data warehouse", 5-10 TB The Natural Philosopher <tnp@invalid.invalid> - 2011-09-11 15:40 +0100
          Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Axel Schwenke <axel.schwenke@gmx.de> - 2011-09-11 18:11 +0200
            Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Ignoramus21330 <ignoramus21330@NOSPAM.21330.invalid> - 2011-09-11 12:07 -0500
      Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Norman Peelman <npeelmandog@cfl.rr.com> - 2011-09-10 19:06 -0400
  Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-09-11 10:24 +0100
    Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Ignoramus21330 <ignoramus21330@NOSPAM.21330.invalid> - 2011-09-11 07:35 -0500
  Re: MySQL vs Postgres for a "data warehouse", 5-10 TB Bodo <bodo@nomail.org.invalid> - 2011-09-23 13:01 +0200

csiph-web