List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:December 16 2003 5:43pm
Subject:Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
View as plain text  
> >High-end database systems perfer more files, as they can be spread across
> >multiple (usually fast SCSI) disks. Most databases recommend logs be kept
on
> >seperate disks than database files.
> >
> >Changes in the number of files can resolve problems if the problems are
the
> >result of low disk IO and you have money to throw at the problem.
> >
> >David
> >
> >
> >
> True, but this isn't always the case.
>
> Let's look at the almighty InnoDB for instance. It happily uses multiple
> files for the data dictionary (ibdata*) if
> you tell it but, as far as I know, treats them like one big space, not
> doing striping or anything fancy.

It fills up the data-files sequentially (not writing a bit here, a bit
there), but that could change.

Of course, if you have all the data files spread across 5 disks, and you
needed 10 non-sequential
blocks (that aren't in memory), you will probably utilize more than one
disk, which is a good thing.

There is also a size issue. Each of our databases is about 25 gig of data. I
don't want to cram that into one file.
Some of our tables are in the 4-6 gig range. I don't want to be bumping up
against 32-bit file-size limits.

On Oracle, we have 9 tablespaces just for data (and each tablespace is 3
datafiles). Indexes have
their own tablespaces. We have three mirrored disks, so each datafile goes
on one mirrored-set.

Our new server will be hot-swappable a RAID SCSI setup. The more drives you
can get working
at one time, the faster your database server will be (if you end up going to
disk a fair bit).

Backups in Oracle are not done by saving the datafiles. And if you use the
InnoDB hot-backup
tool (which we will have to), the same will apply to MySQL/InnoDB. So the #
of files is not a hinderance
to backups in that case.

Finally, you can cram all your data into one InnoDB datafile if you so
desire. It's one extra file to backup.

In fact, with Oracle, you can just have one big data file. No FRM files or
anything.

> Regarding logs being kept seperately, you will get no argument from me!
> In fact, if you can keep your logs on a
> HA cluster of NAS boxes, in a safe, covered in concrete, connected by
> multiply-redundant fibre-channel links,
> guarded by half the population of Wagga Wagga and plastered with images
> from random links from the awful,
> awful bowels of the internet I still reckon that you'd probably be able
> to find a way to go one better with
> log protection.

Time to cut back on the coffee, my friend :)

Seriously, log files are kept on different disks not for security, but for
performance.Writing a 20-meg file to
disk is a big performance hit; if you are trying to read and write database
data from that disk at the same
time, you'll notice it.

> Look at SQLBase - "Zero Administration" they reckon. Log files do not
> exist seperately, they exist inside the
> same file that holds everything else! Not good! You need to do a
> "reorganise" every few weeks or things start
> to...well..err...suck! Give me MySQL any day!

That's brain-dead.

The best database allows dba's and users to be as flexible as they want/need
to be.

David
Thread
MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jerry Apfelbaum14 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
          • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Tobias Asplund15 Dec
            • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jeremy Zawodny16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Garamond16 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Jeremy Zawodny16 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Sven Köhler15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Juergen Sauer15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies15 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield15 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Curtis Maurand15 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
        • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield16 Dec
          • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
      • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Vinod Kumar Singh16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Dr. Frank Ullrich16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Griffiths16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?David Griffiths16 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Chris Nolan17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies16 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Matthew Stanfield17 Dec
    • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Ed Leafe17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Director General: NEFACOMP7 Jan
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies7 Jan
RE: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Joshua Thomas15 Dec
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos17 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies17 Dec
Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?mos18 Dec
  • Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?Martijn Tonies18 Dec