Tweakers maakt gebruik van cookies, onder andere om de website te analyseren, het gebruiksgemak te vergroten en advertenties te tonen. Door gebruik te maken van deze website, of door op 'Ga verder' te klikken, geef je toestemming voor het gebruik van cookies. Je kunt ook een cookievrije versie van de website bezoeken met minder functionaliteit. Wil je meer informatie over cookies en hoe ze worden gebruikt, bekijk dan ons cookiebeleid.

Meer informatie

Door , , reacties: 0, views: 144.182 •

MySQL vs. PostgreSQL

Even though MySQL is probably the best-know name in the open source database world, a number of excellent alternatives are available. One of them is PostgreSQL, of which the development started in 1986. The project's initial goal was to design a successor of another database that went by the name of Ingres, which explains the name: 'post-Ingres' slowly changed to Postgres and later to PostgreSQL. The current version of the database is 8.1, and it is regarded as being at least as good, if not better, than MySQL. Since the discussion which is 'the best' database is both sensitive and tedious, we avoid it in this article, but it is nevertheless interesting to take a look at how good or bad Tweakers.net runs on PostgreSQL.

We decided to compile a CVS snapshot of PostgreSQL 8.2 (alpha), as the word is that this version scales better than the current official release. Since we ran a beta version of MySQL 5.1, we figured it would be fair to give PostgreSQL a chance as well. When version 8.2 turned out to run without problems as well as very fast, we felt there wasn't a good reason any more to fall back on the latest release.

In contrast to all MySQL versions that we tried, PostgreSQL scales almost perfectly. With a load of ten simultaneous users, the step from one to two cores yields on average an performance increase of 114%, going to four cores improves things by 96%, and the increase to eight cores adds another 77%. This means that eight cores deliver 7.4 times as much as a single core. Another relief in comparison to MySQL comes in the form of stable performance after the maximum is reached: collapses as we saw in MySQL when the loads exceed the servers capacities do not occur. When we only take the heavy loads at the end of the graph into consideration, the lines appear to flatten out, which means that the scaling behaviour is even better: the gains of the core doublings to 2, 4, and 8 are respectively 122%, 104% and 98%, in other words, the performance between one and eight cores differs on average by a factor of nine.

Sun T2000 review - PostgreSQL 8.2 scaling behaviour

PostgreSQL might be called a textbook example of a good implementation of multithreading, making it the ideal application for the UltraSparc T1 to show what it's made of. As long as this does not require too much computing power, but software is capable to split itself effortlessly into independent parts, the T2000 is in its element. The MySQL 5.0.20a graph shown below (the same as on the previous page except for the scale adaptation) illustrates the contrast once more, and demonstrates the importance of not just having multithreading in the software, but also a well-scaling implementation of it.

Sun T2000 review - MySQL 5.0 scaling behaviour (350 scale)

It took a lot of trouble to make our benchmark suitable for PostgreSQL: Tweakers.net’s regular code is highly optimized to satisfy the 'unique personality' of MySQL 4.0. A direct copy of the database and the queries lead to hopelessly bad results, so in order to give PostgreSQL a fair chance, indices were replaced, sub queries were applied, and particular joins were rewritten. This re-optimization yielded an improvement by a factor of four. Still, the effort was far short of the work done over the years to get MySQL to behave properly, which makes us suspect that more performance can be dragged out of PostgreSQL.