"Best free DB for a web-based Perl app" response results...

[John Armstrong]

As a word doc...

The most convincing and point-addressing comments were from Simon W.
sim...@cornfield.org.uk - first paragraph.

The clear choice from these responses is Postgres because of its internal
strength over MySql; most notably, in that MySql can easily degrade to a
standstill with 3 concurrent users, at least one doing big queries, whereas
Postgres keeps on sailing. Also, it's more free, for now anyway. A
commitment to Postgres now might get us grandfathered in later on when
they've given up "free" for "money" (for some crazy reason). There was
cross-agreement that Postgres is slower than MySql, but I guess that's the
price paid for having more behind the scenes - more not so up-front,
functionality. Also, Postgres is much more Oracle-like in syntax than
MySql, so if we should ever graduate to big-daddy-Oracle, we can do that
easier.

(See attached file: DB_Compar.doc)

Thanks much to respondants and list managers!!! - John Armstrong -
Sacramento

[John Armstrong]

Jesse Erlbaum - thanks much for your very insightful comments - we stand
corrected - we're back in the MySql camp!!! - John Armstrong - Sacramento

[Jesse Erlbaum]

Hi John --

> The clear choice from these responses is Postgres because of
> its internal
> strength over MySql

I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS
SQL Server, and Informix. I've also been developing web apps for quite
a long time, so I feel my opinions carry *some* weight.

That being said, my preference is still MySQL.

First off, if I want/need Oracle functionality, I'll just use Oracle.
When you compare PgSQL to Oracle on "internal strength", it's a
no-brainer. Nobody is going to argue that PgSQL is better than Oracle.
In fact, PgSQL isn't better than any of the other so-called "ACID"
databases I mention above. And some of those databases are free (or
very nearly so) for small sites.

The biggest criticism that I've heard leveled at MySQL is that "...it's
not ACID. It's more like a file system with a SQL interface." After 10
years developing web/database apps, I've discovered that 80% of the
time, that is exactly what you need.

Web applications are software, but they are very notably different from
client-server software. One of the most significant differences is that
the web is stateless. This means that some advanced features, such as
transactions and cursors are not generally applicable. If I was writing
a client-server application I would have a stateful connection, through
which I could use a cursor to step through results, or hold a
transaction open.

That is not the case on the web. On the web, when a page is rendered,
the application stack is effectively terminated. That's not to say you
*couldn't* devise some scheme to simulate statefulness. You simply
would not *want* to. On the web, every request has to contain all the
information needed to fully articulate a transaction. That means, you
need a FAST database with the tools necessary to get the job done.

That, in a nutshell, is MySQL. Features such as "offset/limit" (which
were practically invented by MySQL, which are not standard SQL, which
don't exist in Oracle, and only exist in PgSQL because they were so
damned useful) are a classic example of why MySQL is the most popular
database in the whole world for web applications. It is the right tool
for the job. Same with the "auto increment" columns. A feature which
didn't exist in Oracle-like databases, but was a practical solution
which made life that much more easy.

There are dozens of other examples like these (such as the MySQL
interactive shell, which beats the pants off of sqlplus, or mysqldump
which annihilates pg_dump). The theme here is that MySQL was created to
be three things:

1. Simple
2. Reliable
3. Fast

Let's not forget that the "P" in Perl stands for "Practical". PgSQL was
created as an academic exercise: "Can we write our own Oracle?" If I
wanted to be "academically correct", I'd be programming in Java. I
don't, and I'm not.

And, BTW: Nearly all those advanced, "academically correct" features
which people point to when pimping PgSQL (row-level locking, stored
procs, transactions, triggers, ref. integrity checking, clustering,
etc.) are available for MySQL right now, or are slated to be available
in the next release. However, PgSQL is still slow, hard to use, and of
questionable reliability.

> Also, Postgres is much more Oracle-like in syntax than
> MySql, so if we should ever graduate to big-daddy-Oracle, we
> can do that
> easier.

I'd like to shoot this idea down pretty quick: The idea that you might
"graduate" to Oracle. I'd like to point to an article in InfoWorld just
two weeks ago:

http://weblog.infoworld.com/article/05/11/17/47FEmainmigrate_1.html

The article is about migrating from a mainframe computer. I direct your
attention to page two:

"Sabre Pushes the Limits. Sabre Holdings -- parent of the Travelocity
online consumer booking service and the Sabre travel reservations and
ticketing system, which handles about 40 percent of worldwide travel
reservations -- is in the midst of one of the largest mainframe
migrations. Todd Richmond, the company's vice president of enterprise
architecture, says Sabre has the world's third-largest implementation of
IBM TPF (Transaction Processing Facility) mainframes. In an effort that
began almost six years ago, however, Sabre has migrated most of its
domestic booking services to four-way, Intel (Profile, Products,
Articles) Itanium-based HP (Profile, Products, Articles) NonStop servers
and a cluster of HP Integrity Itanium-based servers running 64-bit Red
Hat (Profile, Products, Articles) Linux and the MySQL database."

The article goes on to describe what they're doing with MySQL:

"...Sabre will continue to use NonStop servers for database
transactions because they are able to process the 14,000 transactions
per second more reliably across large data sets typical of Sabre's
environment"

So, there you have it: 14,000 transactions per SECOND, handling 40% of
the worldwide travel reservations... And all running on MySQL. So, it
is possible to run a HUGE enterprise using MySQL -- no graduation
necessary: Just good application architecture.

Warmest regards,

-Jesse-

--

Jesse Erlbaum
The Erlbaum Group
je...@erlbaum.net
Phone: 212-684-6161
Fax: 212-684-6226

[Aaron Dancygier]

On Thu, Dec 01, 2005 at 12:24:12PM -0500, Jesse Erlbaum wrote:
> Hi John --
>
> > The clear choice from these responses is Postgres because of
> > its internal
> > strength over MySql
>
> I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS
> SQL Server, and Informix. I've also been developing web apps for quite
> a long time, so I feel my opinions carry *some* weight.

Just look at the list of companies that use mysql. Would google and yahoo use it if it werent up to par?

>

> That, in a nutshell, is MySQL. Features such as "offset/limit" (which
> were practically invented by MySQL, which are not standard SQL, which
> don't exist in Oracle, and only exist in PgSQL because they were so
> damned useful) are a classic example of why MySQL is the most popular
> database in the whole world for web applications. It is the right tool
> for the job. Same with the "auto increment" columns. A feature which
> didn't exist in Oracle-like databases, but was a practical solution
> which made life that much more easy.

Dont forget that time(less typing) saving replace command.

>
> There are dozens of other examples like these (such as the MySQL
> interactive shell, which beats the pants off of sqlplus, or mysqldump
> which annihilates pg_dump). The theme here is that MySQL was created to
> be three things:
>
> 1. Simple
> 2. Reliable
> 3. Fast
>
> Let's not forget that the "P" in Perl stands for "Practical". PgSQL was
> created as an academic exercise: "Can we write our own Oracle?" If I
> wanted to be "academically correct", I'd be programming in Java. I
> don't, and I'm not.
>
>
> And, BTW: Nearly all those advanced, "academically correct" features
> which people point to when pimping PgSQL (row-level locking, stored
> procs, transactions, triggers, ref. integrity checking, clustering,
> etc.) are available for MySQL right now, or are slated to be available
> in the next release. However, PgSQL is still slow, hard to use, and of
> questionable reliability.

MySql has really caught up stored procedures, views, and triggers are new features in 5.0 while replication (since 3.xx), clustering (since 4.1), transactional table types (innodb since 3.xx), have been around for a while. Even with all these new features mysql is still blazing fast.

Aaron Dancygier

[David Steinbrunner]

Robert Hicks wrote:

> "... if i only needed MySQL, i'd use SQL Lite."
>
> Really? I think that would be a poor choice and I like SQLite. ; )

SQLite is an embedded database or at least that is how it is primarily used.
Both Apple and RealBasic are using SQLite as a part of there latest
persistence engines and I'm sure there are more companies solving client
side data issue that have done the same.

At a recent previous job, we created a web based system that installed both
the web server and database on a desktop/laptop. The system requirements
for the system needed to be low enough to run on a windows 95 box with 64 MB
of RAM. The system was a derivative of another complete system that ran on
top of MS SQL and I was able to port it to SQLite with little effort to
greatly reduce installation and licensing issues along with system
requirements.

If you are planning on creating a system that will have low concurrent usage
you will be well served by SQLite but if you plan on making something that
will be hit hard then you will likely need to move to a bigger database
package.

--
David Steinbrunner

[Fred Moyer]

Jesse Erlbaum wrote:
> Hey Fred --
>
>>I would just like to note that speed and reliability are largely
>>dependent on the transaction profile of your application. If your
>>application is read heavy, MySQL is a sound choice. However if your
>>application consists mostly of database writes, PostgreSQL's MVCC [1]
>>architecture and row-level locking capabilities will
>>generally provide
>>superior performance and reliability to MySQL.
>
> I question your assertion that PgSQL's row-level locking is faster than
> MySQL's row-level locking. I'd like to see actual benchmarks before
> saying that one particular approch is faster than another.

Fair enough. I will put together a benchmark using DBI and make the
source and results open for review. I don't believe there currently any
credible benchmarks in existence which can answer this question accurately.

[Jesse Erlbaum]

Hey Fred --

> I would just like to note that speed and reliability are largely
> dependent on the transaction profile of your application. If your
> application is read heavy, MySQL is a sound choice. However if your
> application consists mostly of database writes, PostgreSQL's MVCC [1]
> architecture and row-level locking capabilities will
> generally provide
> superior performance and reliability to MySQL.

I question your assertion that PgSQL's row-level locking is faster than
MySQL's row-level locking. I'd like to see actual benchmarks before
saying that one particular approch is faster than another.

-Jesse-

[Rob Kinyon]

>
> I would just like to note that speed and reliability are largely
> dependent on the transaction profile of your application. If your
> application is read heavy, MySQL is a sound choice. However if your
> application consists mostly of database writes, PostgreSQL's MVCC [1]
> architecture and row-level locking capabilities will generally provide
> superior performance and reliability to MySQL.

The InnoDB tabletype, which is the only tabletype that should be used for
storing real data when doing enterprise-level work, provides row-level
locking and complete ACID transactions. InnoDB also provides MVCC
architecture. As for strength ... Oracle just bought the company that owns
the InnoDB table type. If it wasn't good, Oracle wouldn't have bought it.

A quick primer for those who don't know MySQL from a DBA's perspective:

There are four primary tabletypes - MyISAM, InnoDB, Heap, and NDB. (There
are others, but you won't use them.)
* The default is MyISAM, which is non-transactional, but extremely fast.
It requires table-level locks for concurrency, but you're not going to use
it if you need concurrency. This is the tabletype that provides MySQL with
its reputation for blazing-fast reads.
* Heap tables are temporary in-memory. These guys are even faster than
MyISAM tables, but with no permanency. They also do not provide any sort of
transaction capabilities.
* InnoDB tables are the enterprise tabletype. They provide complete
ACID, foreign keys, and use MVCC. Because of this, they are slower than
MyISAM or Heap tables. Whenever I design an app that uses MySQL tables,
about 90% of them are InnoDB. The rest are MyISAM (for things like sessions
and logs - items that never have concurrency issues).
* NDB tables are MySQL's clustering table type. They provide complete
ACID and MVCC architecture.

Because of these options, I can tune my tables according to my need. Not
everything requires ACID or MVCC. Those are features that have a performance
cost. But, the nice thing is that I can do a query against tables that have
different tabletypes. So, I can query a MyISAM joined to a InnoDB, if I
wanted.

Hopefully this will reduce the FUD.

Rob

[Jeff MacDonald]

On 12/1/05, Robert Hicks <sig...@gmail.com> wrote:
> "... if i only needed MySQL, i'd use SQL Lite."
>
> Really? I think that would be a poor choice and I like SQLite. ; )
>

> Robert

I'll elaborate.

PostgreSQL is already installed. It's what I "grew up with" I've very
comfortable with it and I find it easy to use. So if I'm starting an
application of any reasonable size I grab Pgsql cause it's "right
there".

Alot of our customers are very small mom and pop shops, selling
crafts, advertising their business mostly read only. As well these
users can only be really expected to see a few hundred vists a month,
and probably less than 100 unique visitors. We deal with the truly
"small guys" quite often. For those folks SQL lite is more then
enough. An example is we have one guy that takes bookings for
Opening/Closing swimming pools when the seasons change. So for a year
the most bookings he'd have is 365 x 2, 2 appointments per day.

SQL lite is perfect for this stuff.

Myself and a few of the guys were going into this topic in more depth
in the IRC channel [irc.perl.org #cgiapp]. We we talking more about
the "right answer" to the original posting, not so much which is best
RDBMS.

IMHO, the right answer is none of them are best. Choose one. Start
using it. See if it does what you want, and in a timly fashion. Use it
for a year then evaluate if you need more of if it works. By then
you'll have had more experience to develop your own opinions.

If you are just starting in this type of application etc, chances are
that most of your choices or at least the 2 big free ones will be
adequate. You can haggle over details later.

Why did I start with PostgreSQL ? I was good friends with Marc
Fournier at the time, and he pushed it on me :) Had I have been in
another part of the country, maybe I might have chosen MySQL. I'm
comfortable with it, it suits my needs and puts food on the table.

Jeff.

[Fred Moyer]

Jesse Erlbaum wrote:
> Hi John --
>
>
>>The clear choice from these responses is Postgres because of
>>its internal
>>strength over MySql
>
>
> I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS
> SQL Server, and Informix. I've also been developing web apps for quite
> a long time, so I feel my opinions carry *some* weight.
>
> That being said, my preference is still MySQL.

...

>
> And, BTW: Nearly all those advanced, "academically correct" features
> which people point to when pimping PgSQL (row-level locking, stored
> procs, transactions, triggers, ref. integrity checking, clustering,
> etc.) are available for MySQL right now, or are slated to be available
> in the next release. However, PgSQL is still slow, hard to use, and of
> questionable reliability.

I would just like to note that speed and reliability are largely

dependent on the transaction profile of your application. If your
application is read heavy, MySQL is a sound choice. However if your
application consists mostly of database writes, PostgreSQL's MVCC [1]
architecture and row-level locking capabilities will generally provide
superior performance and reliability to MySQL.

Kind Regards,

- Fred

[1] - MVCC means that you don't have to worry about writers blocking
readers or readers blocking writers.

[Jeff MacDonald]

> in the next release. However, PgSQL is still slow, hard to use, and of
> questionable reliability.

Slow, i'm not going to argue cause it's "fast enough for me" and I
don't have numbers.

Hard to use ? What do you find hard ? I find it aboslutly devine to
use, and mysql to be cludgy and awkward. The reason for that is cause
of my experience and familiarity, I don't blame MySQL.

Questionable Reliability : Where did this come from ? Over the past 6
years I've been writing webapps, postgres has never screwed up on
me... I think your claim is a highly personal opinion.

Regarding teh rest of your email, I have got to agree with you, most
web apps use way more resources than they could possibly need, but you
know what ? As a counter to your argument if you needed Oracle you'd
just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use
SQL Lite.

It's a holy war folks, w.a.r. = we are right. that's how they get started :)

Jeff.

[Jesse Erlbaum]

Hey Jeff --

> Regarding teh rest of your email, I have got to agree with you, most
> web apps use way more resources than they could possibly need, but you
> know what ? As a counter to your argument if you needed Oracle you'd
> just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use
> SQL Lite.

I really don't think your "SQL Lite" analogy is a valid one. Oracle,
PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no
proven track record.

Quick Google hits check:

2,250,000 for "Oracle" +rdbms
756,000 for "MySQL" +rdbms
384,000 for "PostgreSQL" +rdbms
207 for "SQL Lite" +rdbms

A bit of a straw man, wouldn't you say?

[Juan Jose Natera]

Maybe you should try its correct spelling, SQLite :)

[Darren Duncan]

At 2:05 PM -0500 12/1/05, Jesse Erlbaum wrote:
>I really don't think your "SQL Lite" analogy is a valid one. Oracle,
>PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no
>proven track record.
>
>Quick Google hits check:
>
> 2,250,000 for "Oracle" +rdbms
> 756,000 for "MySQL" +rdbms
> 384,000 for "PostgreSQL" +rdbms
> 207 for "SQL Lite" +rdbms
>
>A bit of a straw man, wouldn't you say?

I just *had* to reply to this one.

You made one of the biggest argument mistakes possible, which is
basing judgement on blatently incorrect data.

Namely, you spelled "SQLite" wrong, so of course Google wouldn't find it.

Given the correct spelling, you would get *101,000* hits for SQLite +
rdbms. While lesser than the others, its still the same order of
magnitude as MySQL or PostgreSQL.

2,260,000 for: Oracle rdbms
740,000 for: MySQL rdbms
431,000 for: Postgres rdbms (which also returns 'PostgreSQL' numbers
101,000 for: SQLite rdbms

SQLite is also far from a skunk works project and has a strong proven
track record.

It has been around for a long time and being used in a huge number of
applications and devices. (Most recently, its even built into Mac OS
X 10.4 for its Core Data component.) Many uses aren't even
advertised, since it is public domain and users don't have to say
they're using it.

It also has dozens of active developers and a very busy mailing list.

Its inventer, D Richard Hipp, was also honored with a top award at
OSCON this year due to SQLite being one of the strongest movers and
benefits to the open source community.

So some criticism of SQLite is warranted, such a scalability with
lots of writers, but not what you said.

-- Darren Duncan

[Scott Webster Wood]

Aaron Dancygier <aa...@dancygier.com> wrote:
Just look at the list of companies that use mysql. Would google and yahoo use it if it werent up to par?

Corporate entities (i.e. corporate managers) make decisions that are not always generated from the same motivations as the rest of us. Google and Yahoo using mysql does make a great endorsement, yes. But that's all it is. As a fan of philosophy and deductive reasoning, I have been getting great joy over the past year or so in noticing and pointing out 'deductive fallacies' whenever they are made in public. Politics is ripe with them as are internet SIGs.
For those unfamiliar, this leap in reasoning is known as Argumentum ad Verecundiam fallacy ("Argument from respect/modesty", Latin - a.k.a. Appeal to Authority) or perhaps it might even fall under Ipse Dixit ("He, himself, said it", Latin) fallacy. Similarly, to point out MySQL as being a better choice simply because more people use it (dare I remind you of the popularity of windows xp or internet explorer?) is an Ad Populum fallacy. How's about because it has been around longer? (I don't know if it has or not, but that would be Ad Antiquatum fallacy)

In summation, a fallacy is something that inspires an unjustified leap in drawing a conclusion based on something that is not logically deduced based on the premises. In short, I prefer to stick to the facts - not popularity contests or the like.

SW

---------------------------------
Yahoo! Personals
Single? There's someone we'd like you to meet.
Lots of someones, actually. Yahoo! Personals

[David Emery]

On Date: Thu, 1 Dec 2005 14:05:17 -0500, "Jesse Erlbaum" <je...@erlbaum.net> wrote:
> Hey Jeff --
> > Regarding teh rest of your email, I have got to agree with you, most
> > web apps use way more resources than they could possibly need, but you
> > know what ? As a counter to your argument if you needed Oracle you'd
> > just use Oracle VS PgSQL, in my life, if i only needed MySQL, i'd use
> > SQL Lite.
>
> I really don't think your "SQL Lite" analogy is a valid one. Oracle,
> PgSQL and MySQL are hugely popular. SQL Lite is a skunk works with no
> proven track record.
>
> Quick Google hits check:
>
> 2,250,000 for "Oracle" +rdbms
> 756,000 for "MySQL" +rdbms
> 384,000 for "PostgreSQL" +rdbms
> 207 for "SQL Lite" +rdbms
>

Not that this really matters much , but you get a few more hits if you
spell it correctly...
"Results 1 - 10 of about 3,000,000 for SQLite."

Dave

[Peter J. Holzer]

On 2005-12-01 14:04:04 -0500, Rob Kinyon wrote:
> As for strength ... Oracle just bought the company that owns the
> InnoDB table type. If it wasn't good, Oracle wouldn't have bought it.

I suspect that Oracle had a very different motivation in buying InnoDB.
Controlling core technology used by your competition comes immediately
to mind.

hp

--
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | h...@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

[Tim Bunce]

On Fri, Dec 02, 2005 at 11:17:24AM +0100, Peter J. Holzer wrote:
> On 2005-12-01 14:04:04 -0500, Rob Kinyon wrote:
> > As for strength ... Oracle just bought the company that owns the
> > InnoDB table type. If it wasn't good, Oracle wouldn't have bought it.
>
> I suspect that Oracle had a very different motivation in buying InnoDB.
> Controlling core technology used by your competition comes immediately
> to mind.

Let's keep this thread on-topic please.

Tim.