As it turns out, the PostgreSQL team has been working incredibly hard
on the 7.1 release. Not only did they solve my pet peeve (the 8k row limit), but
apparently they’ve made massive strides in performance and reliability.
My install of Postgres 7.02 on Geocrawler has not failed in several months now,
in fact Geocrawler recently was noted as one of the most reliable sites on the internet
according to Netcraft’s monthly survey. So I think that says a lot about the PG 7.x
series.
on the 7.1 release. Not only did they solve my pet peeve (the 8k row limit), but
apparently they’ve made massive strides in performance and reliability.
My install of Postgres 7.02 on Geocrawler has not failed in several months now,
in fact Geocrawler recently was noted as one of the most reliable sites on the internet
according to Netcraft’s monthly survey. So I think that says a lot about the PG 7.x
series.
With that in mind, I decided to test out a full port of SourceForge.net
to Postgres. The site was written with a database abstraction layer and it
turned out to be a cinch to get it up and running on Postgres, including
a full import of all production data from MySQL.
to Postgres. The site was written with a database abstraction layer and it
turned out to be a cinch to get it up and running on Postgres, including
a full import of all production data from MySQL.
Not only did the site come up on the first attempt, but it ran fine! In fact,
our very first benchmarks showed Postgres running 6x faster than MySQL on a
very database-intensive page (the
“My Personal Page” for logged-in users).
our very first benchmarks showed Postgres running 6x faster than MySQL on a
very database-intensive page (the
“My Personal Page” for logged-in users).
To make things interesting, we ran the exact same
code base
on MySQL 3.23.26beta
and Postgres 7.1 (CVS pre-beta version). The SQL structures were identical,
except that I had added Foreign Key constraints to the database schema on
Postgres (MySQL doesn’t really support foreign keys). This should have tilted
the performance in favor of MySQL, because MySQL doesn’t do any data validation
on inserts/updates, but Postgres did in this test.
code base
on MySQL 3.23.26beta
and Postgres 7.1 (CVS pre-beta version). The SQL structures were identical,
except that I had added Foreign Key constraints to the database schema on
Postgres (MySQL doesn’t really support foreign keys). This should have tilted
the performance in favor of MySQL, because MySQL doesn’t do any data validation
on inserts/updates, but Postgres did in this test.
Methodology
Now let’s be clear – these tests were run with all processes on the same quad-xeon
400 MHz machine with 1GB RAM. The apache “AB” client, Apache/PHP, and the database
all ran on the same box. In a real-world scenario, you would be wise to
separate your web server and database server if you want maximum performance.
400 MHz machine with 1GB RAM. The apache “AB” client, Apache/PHP, and the database
all ran on the same box. In a real-world scenario, you would be wise to
separate your web server and database server if you want maximum performance.
Further, the “worst” database-intensive pages were chosen. It wouldn’t make
much sense to choose pages that barely touch the database. So most pages
on sourceforge are not going to be this slow and saturate the database
this much.
much sense to choose pages that barely touch the database. So most pages
on sourceforge are not going to be this slow and saturate the database
this much.
The data in both databases was identical and was real life production data.
