MySQL
The numbers for MySQL ring true with what most people already know: it’s a
fast, although lightweight database that will probably serve well for the vast
majority of web sites. However, if you plan on having a high-traffic site (say,
greater than 500,000 pages per day), then forget MySQL as it can tend to fold
up and die under load. Anyone who has ever visited slashdot can attest to the
fragility of its setup (mod_perl and MySQL).
fast, although lightweight database that will probably serve well for the vast
majority of web sites. However, if you plan on having a high-traffic site (say,
greater than 500,000 pages per day), then forget MySQL as it can tend to fold
up and die under load. Anyone who has ever visited slashdot can attest to the
fragility of its setup (mod_perl and MySQL).
But again, the vast, vast majority of web sites fall well under the 15-pages
per second demonstrated by MySQL here. If you ever surpass a sustained 15
pages per second,
you’ll be delighted to fork over the cash for a bigger server or an Oracle
license.
per second demonstrated by MySQL here. If you ever surpass a sustained 15
pages per second,
you’ll be delighted to fork over the cash for a bigger server or an Oracle
license.
Wins
Obviously, the advantage MySQL has over Postgres is performance. It also has
some more powerful admin tools included in the distribution (mysqladmin allows
you to watch processes and queries in-progress), like hot backup, a file
corruption recovery tool and a couple others.
some more powerful admin tools included in the distribution (mysqladmin allows
you to watch processes and queries in-progress), like hot backup, a file
corruption recovery tool and a couple others.
I’m also a fan of MySQL’s command-line tools. You can see database and table
structures using describe and show commands. Postgres’ commands are less
obvious ( d to show a list of tables for instance).
structures using describe and show commands. Postgres’ commands are less
obvious ( d to show a list of tables for instance).
Limitations
The first thing you hear from hard-core database gurus is that MySQL lacks
transactions, rollbacks, and subselects. You’ll really miss transactions if
you’re trying to write a banking application, accounting application, or trying
to maintain some sort of counter that needs to increment linearly over time.
Forget attempting any of those with released versions of MySQL (it should be
noted that the unstable 3.23.x series of MySQL now includes transaction
support).
transactions, rollbacks, and subselects. You’ll really miss transactions if
you’re trying to write a banking application, accounting application, or trying
to maintain some sort of counter that needs to increment linearly over time.
Forget attempting any of those with released versions of MySQL (it should be
noted that the unstable 3.23.x series of MySQL now includes transaction
support).
For many, if not most, web sites out there, MySQL’s limitations can be overcome
with a little elbow grease on the part of the developer. The primary feature
you’ll miss in MySQL is powerful subselect syntax that is present in almost
every other production database. If I had a nickle for every time I could’ve
used subselects in MySQL, I’d be able to buy a case or two of beer. In other
words, this missing feature can be a pain in the neck, but it can be overcome.
with a little elbow grease on the part of the developer. The primary feature
you’ll miss in MySQL is powerful subselect syntax that is present in almost
every other production database. If I had a nickle for every time I could’ve
used subselects in MySQL, I’d be able to buy a case or two of beer. In other
words, this missing feature can be a pain in the neck, but it can be overcome.
Stability
MySQL loses points in the long-term stability department. Simply put, MySQL
gives up the ghost randomly and for no obvious reason after running for
semi-long periods of time (say 30-60 days). Many developers will compile MySQL
“statically” for just that reason, and doing so has helped some people.
gives up the ghost randomly and for no obvious reason after running for
semi-long periods of time (say 30-60 days). Many developers will compile MySQL
“statically” for just that reason, and doing so has helped some people.
That problem again can be overcome with a good pager or a simple crontab entry
that kills and restarts MySQL monthly. Not that I find that at all acceptable,
but it is a solution.
that kills and restarts MySQL monthly. Not that I find that at all acceptable,
but it is a solution.
Where MySQL loses points in the daemon robustness department, it makes up for
it by apparently never corrupting its data files. The last thing you want is
your precious data files fouled randomly, and MySQL does well here. In over a
year of running MySQL, I haven’t ever seen a single case of database
or index corruption. In the same timeframe, I have done 2 or 3 recoveries of a
couple different Postgres databases. (Regardless, backups are always your
best friend, as shown by the database fiasco here on PHPBuilder.)
it by apparently never corrupting its data files. The last thing you want is
your precious data files fouled randomly, and MySQL does well here. In over a
year of running MySQL, I haven’t ever seen a single case of database
or index corruption. In the same timeframe, I have done 2 or 3 recoveries of a
couple different Postgres databases. (Regardless, backups are always your
best friend, as shown by the database fiasco here on PHPBuilder.)
