Doug Bryant

Tech thought and notes

OSCON 2005 - State of the Opensource Databases

Ingres _ by CA_

Ingres r3 is the version which was put into opensource.

Features

  • Value based table partitioning (a-c go here d-x go here, etc)
    • can partition tables based on value of records
    • No application change required
  • parallel query execution
    • may utlize more than one cpu on MP machines for a single query.
    • single processor machines also benefit
  • Advanced query optimization techniques (query decomposition, flattening, and rewrite)
  • Federation through ingres star
    • support two phase commits for distributed transactions
  • Replication of master at one or more slaves
    • can also have multiple masters where the other master acts as a slave when doing updates to one or the other
  • next release to support grids
  • admittly driver support is lacking
  • winblows only db gui

MySQL with David and Monty

  • runs on 87 gazillion platforms
  • Connector/MXJ – embedded jdbc within java (embedded java database???)
  • Storage engine slides…
  • Special storage engines
    • Archive – logging data you don’t want to delete or update
    • Blackhole – allows replication but throws away all data
    • MyISAM for logging: prefix locking allows fully concurrent inserts and reads

MySQL 5.0

<ul>
    <li>Stored procedures</li>
    <li>Triggers</li>
    <li>Views</li>
    <li>XA &#8211; distributed commits across different databases</li>
    <li>Data dictionary</li>
    <li>Server side cursors</li>
    <li>Precision Math &#8211; exact calculations with well defined rounding and atleast 56 digits precision.  Very fast w/ static memory allocation</li>
    <li>Strict mode &#8211; ability to get rollback/errors instead of closest value/warning messages (eh? can tell you when it is a bad date, 02/31/2005)</li>
    <li>Federated storage engine</li>
    <li>greedy optimizer (fast multi table joins)</li>
    <li>instance manager (replaces mysqld_safe script)</li>
    <li>extenstion to <span class="caps">LOAD</span> <span class="caps">DATA</span> for doing transformations/calculations at time of load</li>
    <li>5.0 still has 327 bugs but 16 really bad bugs at time of this writing.</li>
</ul><ul>
<li>Upcoming features
<ul>
    <li>partitioning (needed for 20 petabytes that one user is planning)</li>
    <li>replication additions &#8211; row based (physical) replication (normal is logical) + multi source replication</li>
    <li>global backup api</li>
    <li>mysql cluster w/ disk data (non indexed columns)</li>
    <li>Hash  Merge joins</li>
</ul></li>

Firebird w/ Ann ?

  • High concurrency, high performance
  • low administration costs
  • flexible architecture
  • active project

Development seems to be fairly slow going. Borland reniged the opensource license and Firebird is a fork. First releases were mostly bug fixes. 1.0 and 1.5 can share same data file.

Firebird Strengths

<ul>
    <li>Flexible architecture</li>
    <li>processed based (fork)</li>
    <li>multi-threaded shared server</li>
    <li>embedded</li>
    <li>low admin cost
    <ul>
        <li>self balancing indexes</li>
        <li>cooperative garbage collection</li>
        <li>single file database</li>
        <li>transportable backup</li>
    </ul></li>
</ul><p><i>Firebird future (2.0)</i></p>
  • currently in alpha 3
  • global temporary tables
  • execute block
  • physical backup

Vulcan

  • in parallel development
  • fork to work on 64bit arch
  • internal sql

Postgresql 8.1

8.1 features are frozen now. beta release in mid-august or early september.

Key New Features:

  • Indexes combined using bitmaps – index any attributes you want to query on later. then when you do a join across different attributes, the engine bitmaps the indexes and can perform very fast lookups (knows where to find pages and other data structures)
  • Two-phase commit for distributed transactions
  • Automatic vaccuum process
  • Global shared buffer lock removed, improves SMP support
  • Functions returning multiple out parameters (oracle feature)
  • Shared row locks

Pervasive, Green Plum and EnterpriseDB and Unisys building products/businesses around Postgres. Approx 1.5 million downloads of 8.0.×. and lots of new users (notable NWS and Ameritrade)