Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

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

RE: [cgiapp] "Best free DB for a web-based Perl app" response results...

Hi John --

> The clear choice from these responses is Postgres because of=20
> 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=20
> 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-


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

Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

> 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. =3D we are right. that's how they get starte=
d :)

Jeff.

Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

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.

Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

------=_Part_45490_28201333.1133463844893
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>
> 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 performanc=
e
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

------=_Part_45490_28201333.1133463844893--

Re: [cgiapp] Re: "Best free DB for a web-based Perl app" response results...

On 12/1/05, 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. ; )
>
> 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.

Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

--d9ADC0YsG2v16Js0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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

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

--d9ADC0YsG2v16Js0
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iQDQAwUBQ5AfNFLjemazOuKpAQH0+gXUChEEqU0R7oueC/SMU2xRoCA+WLrR DTwU
ZdSL4HzdZdknYdZJbD3nOtsMpi2Y+dA5Ducjh0kHzJJ3EgMyMRUn2OHmtLVc tatT
mtbKDgvYrO/Jexw3uuv2OM+M4Kafk8KMBgIOiS+23Z8ddwimhYmOWUozMFDn 40b5
D2z3+xTqLYLWR/FdLEXt/9RM0WS/EekNF+FfZQjc77fqBvyyqOlSocUcwTCN gvI7
ilWPenWkrpvT3P27OH3B0wJQpA==
=ltOr
-----END PGP SIGNATURE-----

--d9ADC0YsG2v16Js0--

Re: [cgiapp] "Best free DB for a web-based Perl app" response results...

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.