https://doi.org/10.26552/com.C.2008.4.

53-57

REVIEW

Svetlana Andjelic – Slobodan Obradovic – Branislav Gacesa *

A PERFORMANCE ANALYSIS OF THE DBMS

– MySQL vs PostgreSQL

Abstract: Modern communications among business subjects imply an exchange of a lot of data and information. To enhance that process
in all its aspects, electronic data storing and processing is mandatory and enabled by an application of a DBMS (Date Base Managment
System). This paper shows the performances analysis of the two most popular open source DBMSs - MySQL and PostgreSQL. First, some
characteristics of these DBMSs are shortly described. Then the applied procedure of the performed testing is described. That is, the query
(select, insert, delete and order by) execution times were measured for the both DBMSs mentioned, and the results shown in tabular and
graphical forms. The goal of this paper is to make the choice of an adequate DBMS easier for future users.
Key words: open source DBMS, MySQL, PostgreSQL, ACID

1. Introduction The biggest downside to this system is not checking of the ref-
erential integrity. External keys are supported by syntax, which
Nowadays, it is virtually impossible to imagine the function- can be misleading, because they are not actually applied. The current
ing of a larger system without some sort of electronic data storage. version neither supports views, nested queries nor stored proce-
DBMS is certainly the most often used data management and per- dures.
manent storage system.
The run-time environment for PHP MySQL contains two sig-
It is interesting to note that the United Nations (UN) have nificant flaws, which enable abusers to take control of the server
recommended to their members to use the open source software, through the memory_limit function, thus circumventing the secu-
especially in the areas of health protection, education and interna- rity mechanisms in the strip_tags function. In the meantime, PHP
tional commerce. According to UN, open source software is the group has anounced its first final environment version of genera-
most adequate means for the development of their members. tion 5.0. The most important new features are the building-in of
the Zend Engine II library with a new object model, rewamped
In the paper, some of the basic characteristics of the most support for XML based on libxml2 library, built-in support for
often used open source DBMS – MySQL and PostgreSQL – are SOAP and the new MySQLi add-on for working with version 4.1
described first. Second, more important part of this paper is ded- of the MySQL server. The latest versions are MySQL 5.1 through
icated to the description of the testing performed on those DBMS 5.1.21-beta and MySQL 6.0 Falcon. Falcon has been specially
and the exposition of the results obtained. developed for systems that are able to support larger memory archi-
tectures and multi-threaded or multi-core CPU environments.

2. The basic characteristics of the DBMS – MySQL &

PostgreSQL 2.2. PostgreSQL

2.1. MySQL PostgreSQL is an object-relational database management system

(ORDBMS), based on POSTGRES version 4.21 developed in the
MySQL is an open source database management system, con- University of California Computer Sciences Department at Berkley.
ceived and implemented to rival the MS SQL. It reaches that aim
to a degree, especially so when less voluminous and simpler tasks The first demo version became operative in 1987. Berkley's
are considered. POSTGRES was officially completed with version 4.2. In 1994,
MySQL is renowned for its speed and reliability. That's why Andrew Yu and Jolly Chen implemented the POSTGRES inter-
it's one of the most popular database management systems on the preter for SQL. Under its new name, Postgres95, it was published
web. Of the data base access technologies, there exist drivers for on the web as an open-source version of the original POSTGRES
ODBC, JDBC i OleDB, as well as libraries for C, Delphi, Perl, Berkley code in 1996. It supports a large part of the SQL standard
Python, PHP and TCL. and provides for many modern characteristics: complex queries,

* Svetlana Andjelic1, Slobodan Obradovic2, Branislav Gacesa3

1
The Railway College of Vocational Studies, Belgrade, Serbia, E-mail: angeo@verat.net or svetangela@gmail.com
2
Megatrend University, Belgrade, Serbia
3
Telekom Srpske A. D., SRJ Internet Srpske, Banja Luka, Bosna and Herzegovina

COMMUNICATIONS 4/2008 G 53
 REVIEW

external keys, triggers, views and transactional integrity. Newer MySQL vs PostgreSQL [1] Table 1
versions are 8.1 & 8.2, and 8.2.4 is the latest.
PostgreSQL 8.0 MySQL 4.1 MySQL 5.0
Operating System Windows, Linux, Linux,
PostgreSQL has native programming interfaces for C/C,
Linux, all Windows, Windows,
Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and
BSDs, HP-UX, FreeBSD, FreeBSD,
exceptional documentation. PostgreSQL supports international AIX, OS X, MacOS X, MacOS X,
character sets, multibyte character encodings, Unicode, and is locale- Unixware, Solaris, HP Solaris, HP
aware for sorting, case-sensitivity, and formatting. PostgreSQL Netware... UX, AIX, and UX, AIX, and
runs on all major operating systems, including Linux, UNIX other other
(AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and ANSI SQL compli- ANSI-SQL Possible Possible
Windows. PostgreSQL is fully ACID compliant, has full support ance 92/99
for foreign keys, joins, views, triggers, and stored procedures (in Sub-selects Yes Yes Yes
multiple languages). Transactions Yes Yes Yes
InnoDB tables
Although not the fastest, the PostgreSQL database has been
Database replication Yes Yes Yes
characterised as the most advanced on many tests. Inspired by
Foreign key support Yes Yes Yes
Oracle, from the very beggining it supported transactions, triggers,
InnoDB tables
referential integrity and matched procedures (in contrast to the
Views Yes No Yes
MySQL). The recommendations for its use refer more to the proven
quality and robustness than to the performances themselves. Stored procedures Yes No Yes
(pl/SQL)
Among others, PostgreSQL users include UNICEF, Cisco and
American Chemical Society. Triggers Yes No Yes
Unions Yes Yes Yes
Full joins Yes No No
2.3. MySQL vs PostgreSQL Constraints Yes No No
Cursors Yes No Partial
The next table (Table 1) contains comparative data among Procedural Yes No Yes
two most popular “Open Source” database today (MySQL and languages
PostgreSQL). Vacuum Yes Yes Yes
Different table types No Yes Yes

3. Test procedure ODBC Yes Yes Yes

JDBC Yes Yes Yes
The benchmark itself consists of following steps and proce- Other APIs Most of Most of Most of
dures: languages languages languages
G generates a set of alerts;
IPv6 support Yes No No
G connects to databases;

G creates tables inserts data;

G perform SELECT operations; G PostgreSQL 8.0.1

G deletes all data from the table; G MySQL 4.1.9
G snort 1.8 (DB v100-103)
When creating, adding to, editing and deleting data from the G ACID 0.9.6b10
base (SELECT, INSERT, ORDER and DELETE), the system
sends various warnings that trigger certain processes. In Figure 1 Alert sets were generated using open source “Nmap” and
these processes are shown for both MySQL versions, as well as “NessusD” vulerability scanners and tools.
for the PostgreSQL database. The diagram was made according to In computer science, ACID (Atomicity, Consistency, Isolation,
the four different types of tables in bases, including the fsync Durability) is a set of properties that guarantee that database trans-
PSQL option. actions are processed reliably. In the context of databases, a single
logical operation on the data is called a transaction. [1]
G Atomicity – refers to the ability of the DBMS to guarantee that

4. Hardware and software characteristics either all of the tasks of a transaction are performed or none of
them are. For example, the transfer of funds can be completed
Tests were performed using Intel SR2200, Xeon 2.4 GHz with or it can fail for a multitude of reasons, but atomicity guarantees
1 Gb RAM. The software was: that one account won't be debited if the other is not credited.
G operating system - Gentoo Linux 2006.1 with Linux 2.6.14 G Consistency – property ensures that the database remains in

kernel, a consistent state before the start of the transaction and after
G datebases: the transaction is over (whether successful or not).

54 G COMMUNICATIONS 4/2008
REVIEW

Fig. 1. Logical Database diagram – snort 1.8 (DB v100-103) and ACID 0.9.6b10 [1]

G Isolation – refers to the ability of the application to make oper- 5. Test results
ations in a transaction appear isolated from all other opera-
tions. This means that no operation outside the transaction can The results which follow were obtained by direct measuring,
ever see the data in an intermediate state; for example, a bank and obeying the previously described test procedure.
manager can see the transferred funds on one account or the
other, but never on both—even if he ran his query while the
transfer was still being processed. 5.1. INSERT clause
G Durability – refers to the guarantee that once the user has been
notified of success, the transaction will persist, and not be undone. Inserting pregenerated set of 50000 records to both MyISAM
This means it will survive system failure, and that the database and InnoDB MySQL table types and PostgreSQL with fsck
system has checked the integrity constraints and won't need to enabled and disabled (commit after each insert)
abort the transaction. Table 2 shows timed results while executing “INSERT” state-
ment against four different MySQL and PostgreSQLdatabase table.

Tabular display of the time needed for execution of the INSERT clause Table 2
INSERT MyISAM-v4 InnoDB-v4 MyISAM-v5 InnoDB-v5 PgSQL PgSQL
fsync=true fsync=false
5000 3006 34745 1674 42897 17540 4825
10000 4967 71402 2235 85964 14052 8772
15000 7474 103469 3525 128697 21873 13064
20000 9996 143009 4739 168609 110359 17374
25000 12524 170862 5897 212718 101442 21974
30000 15034 186316 6996 261772 51390 26148
35000 17515 119604 8266 297953 49103 30484
40000 20040 58373 9512 337577 158071 35112

COMMUNICATIONS 4/2008 G 55
 REVIEW

The following graph (Graph 1) contains a graphical display of The following graph (Graph 3) shows timed results while exe-
the results shown in Table 2. X-axis shows the number of records, cuting “ORDER BY” statement against four different MySQL and
while y-axis depicts time in milliseconds. PostgreSQLdatabase table. X-axis shows the number of records,
while y-axis depicts time in milliseconds.

5.2. SELECT clause

5.4. DELETE clause
Inserting pregenerated set of 50000 records to both MyISAM
and InnoDB MySQL table types and PostgreSQL with fsck enabled Inserting pregenerated set of 50000 records to both MyISAM
and disabled (commit after each insert) and InnoDB MySQL table types and PostgreSQL with fsck enabled
Table 3 shows timed results while executing “SELECT” state- and disabled (commit after each insert).
ment against four different MySQL and PostgreSQLdatabase Table 4 shows timed results while executing “DELETE” state-
table. ment against four different MySQL and PostgreSQLdatabase
table.

Tabular display of the time needed for execution of the SELECT clause Table 3
SELECT MyISAM-v4 InnoDB-v4 MyISAM-v5 InnoDB-v5 PgSQL PgSQL
fsync=true fsync=false
5000 45 42 44 34 108 104
10000 157 174 71 66 141 150
15000 110 113 114 122 218 219
20000 240 156 248 182 429 300
25000 288 303 201 193 420 506
30000 352 251 235 375 489 614
35000 378 285 262 286 721 738
40000 415 316 293 347 864 805

Graph 1. INSERT clause Graph 2. SELECT clause

The following graph (Graph 2) contains a graphical display of The following graph (Graph 4) contains a graphical display of
the results shown in Table 3. X-axis shows the number of records, the results shown in Table 4. X-axis shows the number of records,
while y-axis depicts time in milliseconds. while y-axis depicts time in milliseconds.

5.3. ORDER BY clause 6. Conclusion

Inserting pregenerated set of 50000 records to both MyISAM Modern communications among business subjects imply
and InnoDB MySQL table types and PostgreSQL with fsck enabled exchange of a lot of data and information. To enhance that process
and disabled (commit after each insert)

56 G COMMUNICATIONS 4/2008
REVIEW

Tabular display of the time needed for execution of the DELETE clause Table 4

DELETE MyISAM-v4 InnoDB-v4 MyISAM-v5 InnoDB-v5 PgSQL PgSQL

fsync=true fsync=false
5000 1 78 1 4 23 19
10000 2 166 2 8 56 40
15000 3 259 3 11 79 60
20000 3 330 3 15 104 82
25000 3 370 4 19 216 150
30000 4 405 4 21 220 151
35000 4 490 4 25 230 241
40000 5 664 6 28 341 235

Graph 3. ORDER BY clause Graph 4. DELETE clause

an electronic data storing and processing is mandatory and recommendations can be given. If both the server and the clients
enabled by the application of a DBMS. are Linux (and transactions are needed), Postgres is recommended.
In the case of Linux/Unix server and Windows clients, then Oracle
From the test results shown one concludes that the MySQL/ certainly is recommended, since ODBC support for MySQL is
MyISAM DBMS performs better than the PostgreSQL. bad, and nonexistent for PostgreSQL.

In the case where, regardless of the integrity of data, the speed Before choosing one of the DBMSs, the criteria relevant to
of query execution is important, one should choose MySQL. Con- the problem should be laid down.
trarywise, PostgreSQL provides greater consistency and smaller
robustness of the base as well as greater security from the system It is to be presumed that the performances of MySQL and
failure. PostgreSQL will converge in the future, aiming to create a fast,
secure and optimally robust DBMS.
The general conclusion cannot be reached, but depending on
the type of the server and types of clients to access the base, some

References

[1] ANDJELIC, S., OBRADOVIC, S., GACESA, B.: A Performance Analysis of the Open Sorce Database, XIII conference YUINFO
2007, Kopaonik, Serbia, ISBN 978-86-85525-02-5
[2] KLINE, K. E., KLINE, D., HUNT, B.: SQL in Nutshell, O'Reilly, 2004
[3] WORSLEY, J. C., DRAKE, J. D.: Practical PostgreSQL, O'Reilly, 2002
[4] RUSSELL, J. DYER, T.: MySQL In A Nutshell, O'Reilly, 2005
[5] HORN, J. W., GREY, M.: MySQL: Essential Skills, McGraw-Hill Professional, 2004
[6] http://support.summersault.com/why_postgresql.html
[7] http://www.postgresql.org
[8] http://www.mysql.com

COMMUNICATIONS 4/2008 G 57