List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 17 2003 7:47am
Subject:Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?
View as plain text  
Hi,

> > > Jerry,
> > >          Interbase/Firebird looks good on paper. But there are a
couple of
> > > things to watch out for.
> > >
> > > 1) Check the Borland Interbase newsgroups for "corruption" to see if
it is
> > > still an issue. I know from reading their newsgroups a few years back
,
> > > there were quite a few postings. It could of been due to inadequate
> > > hardware or some external source like a misconfigured server or power
> > > failure. There are many sites that use IB without such problems but it
is
> > > worth checking it out (especially how to successfully repair a damaged
> > > IB/FB database because all the tables are stored in one file).
> >
> >Most of these problems have been fixed in Firebird.
> >
> > > 2) Speed problems. I don't know how much data your tables will have,
but
> > > IB/FB has a habit of slowing down as more rows are added/deleted.
> >
> >Really? Howcome there are many people using Firebird without speed
> >problems?
>
> It depends on the size of the tables. WIth 50k rows you're not going to
> notice it. With 50 million rows the slow down becomes apparent.

Then what are you doing? Summing all rows? Counting them? (Cause
a "count" is slow in InterBase/Firebird). Indexed searches etc should
be nearly as fast as with 50K rows.

>
> > >They have
> > > an automated Sweep process that cleans out the updated/deleted rows
> > > (deleted & updated rows still take up space until the database is
swept).
> > > Even so a large db will still slow down. I suspect the problem is due
to
> > > unbalanced indexes. The only way to fix it is to unload all your data
and
> > > reload it back in. Some people do it once a week.
> >
> >That's not true. The sweep process doesn't kick in until there's actually
> >a problem with long running transactions and new transactions.
>
> Updated and deleted rows will remain in the table until a sweep is done.
If
> 100,000 rows are updated, then the old rows will remain in the table and
> the sweep cleans them out. A heavily updated table will need to be swept
> often. When sweeps fail to speed up the table, the data  has to be
unloaded
> and reloaded. That's not something you want to do very often if  you want
> to have a 24/7 installation especially with millions of rows.

There are plenty of systems running or almost running 24/7 without
an unload/reload. The sweep process is different from the "garbage
collection" process, which is what you are describing. Perhaps that's
where I got confused when you started about sweeping. The GC
process has been improved in the recent years in Firebrid and in IB 7
as well. Also, you cannot sweep a table: if you want to sweep, you
will sweep the entire database.

> > > 3) The server CPU load will increase rapidly after the first 5 or 6
users.
> > > In other words IB/FB requires a much faster CPU than the same number
of
> > > users for MySQL. It is a very CPU intensive DB server.
> >
> >Most CPU problems are related to the above mentioned problems.
> >This isn't usually the case. IB/Fb is actually more of a disk-intensive
> >database engine, because of the ability to use very little memory.
> >However, Firebird has some new stuff (and IB does too) that allows
> >it to use more memory when available (and allowed) making it even
> >faster.
>
> IB eats up a lot of memory on large queries, something that should be
> avoided if at all possible. All queries are fetched into memory, and when
> physical memory is exhausted, IB will use the page file (in Windows) and
> swaps the physical memory out to disk. This can be *very* disk intensive.
> I've had IB 6 leave as little as 5MB of physical memory and Windows was
> pretty much dead in the water until the query was closed. (Large queries
> are necessary for report or when summarizing data.) If this has been
> improved in the latest IB/FB version, then this memory problem should be a
> non-issue. The more memory you can throw at IB, the better.

I'm surprised here. Actually, the much heard complaint about IB/Fb is that
it takes _too little_ memory and starts swapping to disk too soon. If you're
having IB eating memory, you have a different problem.

> > >There was an earlier
> > > problem with IB Super Server not running well on multiple processors
(it
> > > actually slowed things down). I believe this has been fixed in IB
version
> > > 6.5. I don't know if this was fixed in FB because FB may be running IB
> > > Classic (single processor) version.
> >
> >"Classic" is not the "single processor" version, it runs much better
> >on multiple CPUs than the "SuperServer" architecture.
> >
> >SS still has these problems on multiple CPU machines, Classic does
> >not.
>
> I was under the impression that Classic version could have only 1 thread
> running at one time (each connection is a separate process), it would
block
> other threads if they referenced the same table(s).

There's no blocking in IB/Fb. Never. Except for a transaction lock (writers
blocking other writers). In "heavy" systems, people prefer the Classic
architecture because it runs better and you can throw in additional CPUs.

> The SuperServer was was
> suppose to solve that with a separate thread for each connection, but had
> some speed issues on SMP machines (ran slower).

Yes, with SMP machines, you need to pin down the SS architecture
to a single processor, it won't work well on multiple CPUs. Firebird
still suffers from this, InterBase 7 has been improved to use multiple
CPUs.

> IB requires a lot more work in tweaking than say MySQL because it is a
much
> more sophisticated  database. Properly configured it can work well, but is
> a higher maintenance database than MySQL and requires more memory and CPU.
> If the programmer needs all those bells and whistles, then IB may be the
> solution they're looking for.

I disagree here. There's not a whole lot you can tweak with IB. Ever
looked at the available configuration parameters?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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