Mysql vs postgres

From GEANT2-JRA1 Wiki

MySQL vs. PostgreSQL

Author: Maciej Glowiak / PSNC, Last update 25 October 2005

Comparison

The comparsion of the newest, stable and production version of PostgreSQL 8.0 and MySQL 4.1 (MySQL has also development version 5.0).

	PostgreSQL 8.0	MySQL 4.1	MySQL 5.0 (beta)
Operating System	Windows, more than 2 dozen Unix-like operating systems (Linux, all BSDs, HP-UX, AIX, OS X, Unixware, Netware...)	Linux, Windows, FreeBSD, MacOS X, Solaris, HP UX, AIX, and other See: Full list	Linux, Windows, FreeBSD, MacOS X, Solaris, HP UX, AIX, and other See: Full list
ANSI SQL compliance	ANSI-SQL 92/99	Follows some of the ANSI SQL standards; can be run in ANSI mode	Possible; user can run MySQL in more ANSI compatible (ANSI mode)
Performance	Slower	Faster	untested
Sub-selects	Yes	Yes since 4.1	Yes
Transactions	Yes	Yes InnoDB tables only	Yes
Database replication	Yes	Yes	Yes
Foreign key support	Yes	Yes InnoDB tables only	Yes
Views	Yes	No	Yes
Stored procedures	Yes (pl/SQL)	No	Yes (procedural)
Triggers	Yes	No	Yes
Unions	Yes	Yes since 4.0	Yes
Full joins	Yes	No	No planned for 5.1
Constraints	Yes	No	No Planned for 5.1
Cursors	Yes	No	Partial (read only)
Procedural languages (PLs)	Yes PL/pgSQL, PL/Tcl, PL/Perl, PL/Python PL/PHP, PL/Java or user defined	No	Yes Supports stored procedures (persistent modules) languages as defined by ANSI SQL 2003
Vacuum (cleanup)	Yes	Yes by OPTIMIZE TABLE	Yes by OPTIMIZE TABLE
Different table types	No (PostgreSQL has its own inbuilt table types and doesn't use any alternative ones)	Yes MyISAM, InnoDB, MEMORY, BerkeleyDB, MERGE, Archive and NDB (Cluster) (InnoDB has additional functionality) Read additional notes	Yes MyISAM, InnoDB, MEMORY, BerkeleyDB, MERGE, Archive and NDB (Cluster), Federated (InnoDB has additional functionality) Read additional notes
ODBC	Yes	Yes	Yes
JDBC	Yes	Yes	Yes
Other APIs	Most of languages (i.e. Perl, C/C++, .NET, OLE-DB, Tcl/Tk, Python, PHP, ...)	Most of languages	Most of languages
IPv6 support	Yes	No Does it support IPv6? "At the moment not intentionally, but they might work on IPv6 environment."	No Planning in 5.1
WWW	- Homepage, - Manual, - Features	- Homepage, - Manual, - Features	- Homepage, - Manual, - Features, - What's new in 5.0, - Roadmap-what's new in 5.1
Installation	PostgreSQL installation	MySQL installation
Download	Download page	Download page

More details and test results below Table-of-content:

Contents 1 MySQL vs. PostgreSQL 1.1 Comparison 1.2 Additional notes 1.2.1 MySQL table types 1.2.2 Indexing 1.3 Sources 1.4 More detailed comparsions 1.5 Installation 1.6 Summary for JRA1 2 Benchmark 2.1 Disclaimer 2.2 Test procedure 2.3 Hardware 2.4 Software 2.5 Code 2.6 Table 2.7 Sample record 2.8 Data set 2.9 Results 2.9.1 INSERT INTO a VALUES ( . . . ); 2.9.2 SELECT * FROM a; 2.9.3 SELECT * FROM a ORDER BY 4; 2.9.4 DELETE FROM a; 2.10 What are these MyISAM, InnoDB and fsync??? 2.10.1 MySQL (MyISAM and InnoDB) 2.10.2 PostgreSQL (fsync option) 2.10.2.1 I got a lot of comments to that: 2.11 Links 2.12 Manuals 2.13 Download 2.14 Installation 3 Contact 3.1 Author

Additional notes

MySQL table types

(From: Josh Chamas, MySQL):

Note that MyISAM, and other engines, have much functionality that is independent from the rest.

• MyISAM supports full text indexing, OpenGIS RTREE's, and copying tables at the file i/o level.
• InnoDB supports row level locking, MVCC, foreign keys, and hot backup.
• NDB aka MySQL Cluster supports HASH and BTREE indexes, but is primarily used for its high availability shared-nothing architecture.
• ARCHIVE supports zlib compression of row data, and only INSERT/SELECT so can be assured the data has not been tampered with
• MEMORY has no disk storage footprint, very nice for global temporary tables, and support BTREE and HASH indexes
• Gemini used to be supported in older versions

More about different table types supported by MySQL can be found on http://www.developer.com/db/article.php/2235521 .

Indexing

(From: Eric Liu): Postgres supports arbitrary partial indexing whereas mysql does not.

Sources

• Based on Side-by-side comparison of some of the more frequently used features of MySQL and PostgreSQL article,
• MySQL roadmap,
• Open source database software comparsion
• MySQL manual,
• PostgreSQL manual

More detailed comparsions

If you want to compare more databases please visit:

• The database server feature comparisons on MySQL page.

• Comparsion of mysql, pgsql and other databases

Installation

Information about installation of:

• MySQL installation
• PostgreSQL installation

Summary for JRA1

MySQL is simplier than PostgreSQL but can be much faster. MySQL doesn't support many advanced features that may be important in huge relational and complicated databases. But for JRA1 measurement architecture MySQL should be adequate (we probably wouldn't use the most advanced features).

Benchmark

I made a simple benchmark using JDBC connectors. I used default JDBC libraries taken from home sites of MySQL and PostgreSQL.

Disclaimer

The previous test was performed only for GEANT2 JRA1 development. We just wanted to know which database works faster - MySQL or Postgres. I used the default configuration.

After the first article I got a lot of comments. Many people pointed that I had compared default MySQL MyISAM tables with more advanced PostgreSQL tables. I decided to test MySQL InnoDB (which are more advanced, but slower than MyISAM).

I am still using the default configuration. That's right - both databases could be tuned up and work much faster! If you want to test different configuration, please take my benchmark program. Of course you may send me your results. Your comments are always welcome, but don't ask me to test all configurations, options and so on.

Test procedure

The benchmark program:

• generates a set of source data
• connects to databases
• creates tables (MyISAM, InnoDB or Postgres tables)
• inserts data (from the set generated before, so the data is the same for all tests)
• perform SELECT operations
• deletes all data from the table

Hardware

Computer used in testing was Intel Celeron 2.4 GHz, 512 MB RAM. Communication with databases was local only (localhost).

Software

• Operating system - Slackware 10.1 with Linux 2.6.11.6 kernel

• Databases:
  • PostgreSQL 8.0.1
  • MySQL 4.1.9 (released in Jan 2005).

• JDBC drivers:
  • mysql-connector-java-3.1.6-bin.jar
  • postgresql-8.0-310.jdbc3.jar

• Java - Java Runtime Environment 1.5.0_02

Code

Benchmark code is available here The benchmark source code page.

Table

The table I created was:

  CREATE TABLE a (
     id INT PRIMARY KEY,
     number INT NOT NULL,
     category VARCHAR(10),
     description VARCHAR(255)
  );

In MySQL I created tables using TYPE parameter:

  CREATE TABLE a ( . . . ) TYPE MyISAM;

and

  CREATE TABLE a ( . . . ) TYPE InnoDB;

Sample record

 id=100005       (primary key)
 number=229367   (random value, range 1..1000000)
 category=bbb    ("aaa" or "bbb")
 description=
   "347443838512686414057
    081510422273660302804
    763737435066026818127
    227402036010377674002
    2531885133461821"
           (a 100-random-digit-string)

Data set

Input data sets for both databases were the same. Only in testing PostgreSQL with fsync=false I had to restart server and the data set was different. The benchmark is really simple one, so it doesn't support saving and restoring data.

Results

First column contains number of records in the table. Other columns contain approx. times in milliseconds. Time measurement method was System.currentTimeMillis() function in Java.

INSERT INTO a VALUES ( . . . );

            MySQL        MySQL     PostgreSQL    PostgreSQL
INSERT     MyISAM       InnoDB     fsync=true   fsync=false
 5000        3006        34745          17540          4825
10000        4967        71402          14052          8772
15000        7474       103469          21873         13064
20000        9996       143009         110359         17374
25000       12524       170862         101442         21974
30000       15034       186316          51390         26148
35000       17515       119604          49103         30484
40000       20040        58373         158071         35112
45000       21586       262558          65042         39058
50000       23980        68716          73832         43418

X axis - number of records, Y axis - times in ms

SELECT * FROM a;

            MySQL        MySQL     PostgreSQL    PostgreSQL
QUERY1     MyISAM       InnoDB     fsync=true   fsync=false
 5000          45           42            108           104
10000         157          174            141           150
15000         110          113            218           219
20000         240          156            429           300
25000         288          303            420           506
30000         352          251            489           614
35000         378          285            721           738
40000         415          316            864           805
45000         479          491            880           737
50000         525          562           1009           868

X axis - number of records, Y axis - times in ms

SELECT * FROM a ORDER BY 4;

            MySQL        MySQL     PostgreSQL    PostgreSQL
QUERY2     MyISAM       InnoDB     fsync=true   fsync=false
 5000         101          101            202           218
10000         201          207            415           412
15000         309          307            664           864
20000         436          415            879           897
25000         508          515           1206          1223
30000         617          719           1401          1366
35000         722          840           1763          1671
40000         827          958           1909          1928
45000         998          953           2180          2285
50000        1031         1096           2518          2713

X axis - number of records, Y axis - times in ms

DELETE FROM a;

            MySQL        MySQL     PostgreSQL    PostgreSQL
DELETE     MyISAM       InnoDB     fsync=true   fsync=false
 5000           1           78             23            19
10000           2          166             56            40
15000           3          259             79            60
20000           3          330            104            82
25000           3          501            216           154
30000           4          405            733           151
35000           4          490            230           241
40000           5          664            341           235
45000           6          753            578           340
50000           6         1171            614           282

X axis - number of records, Y axis - times in ms

What are these MyISAM, InnoDB and fsync???

MySQL (MyISAM and InnoDB)

MyISAM and InnoDB are MySQL table types. The default one is MyISAM which doesn't support more advanced features. About differences between them you can read in Pros and Cons of MySQL Table Types article.

PostgreSQL (fsync option)

During my first tests I noticed that MySQL is much faster than PostgreSQL (I am talking about default configuration, just after installing the database).

Inserting 20 000 records took:

• 10 seconds in MySQL (MyISAM)

and

• 167 seconds in PostgreSQL

I read that PostgreSQL did (automatic) COMMIT after each INSERT operation.

It means that if you wanted to insert data into the database e.g:

 INSERT a, INSERT b, INSERT c

PostgreSQL would do it like:

 INSERT a, COMMIT, INSERT b, COMMIT, INSERT c, COMMIT.

I wanted to try how fast would PostgreSQL work without auto-commiting. I put all INSERT operations in one block:

 BEGIN (transaction), INSERT a, INSERT b, INSERT c, COMMIT (end of transaction)

I measured that PostgreSQL needed about 6 milliseconds for each COMMIT (on my computer).

So if you have i.e. 100 INSERT operations, they take 203 ms for PostgreSQL, so (statistically) one such an operation takes 2 ms. If you add COMMIT after each INSERT you get:

 100 operations * (2 ms of INSERT + 6 ms of COMMIT) = 100*8 = 800 ms!

The measured result for that was 794 ms! That's the difference.

---

I got a lot of comments to that:

• Emmanuel Guyot explained:

You ask what PostgreSQL does while commiting. There is an option (fsync) that ask to flush data each time the commit is done. You can have more information here: PostgreSQL Run-time Configuration at § 16.4.4.1 (or read below)

This is really time consuming but useful in case a power failure occurs.

I think this is the part that misses in your article: How robust are both databases?

I hope this explanation helps you to understand why the commit is so slow and maybe you'll be able to make a new test without this option.

---

• fsync option in PostgreSQL (from PostgreSQL Run-time Configuration § 16.4.4.1)

If this option is on, the PostgreSQL server will use the fsync() system call in several places to make sure that updates are physically written to disk. This insures that a database cluster will recover to a consistent state after an operating system or hardware crash.

However, using fsync() results in a performance penalty: when a transaction is committed, PostgreSQL must wait for the operating system to flush the write-ahead log to disk. When fsync is disabled, the operating system is allowed to do its best in buffering, ordering, and delaying writes. This can result in significantly improved performance. However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur. (Crashes of the database server itself are not a risk factor here. Only an operating-system-level crash creates a risk of corruption.)

Due to the risks involved, there is no universally correct setting for fsync. Some administrators always disable fsync, while others only turn it off for bulk loads, where there is a clear restart point if something goes wrong, whereas some administrators always leave fsync enabled. The default is to enable fsync, for maximum reliability. If you trust your operating system, your hardware, and your utility company (or your battery backup), you can consider disabling fsync.

---

Links

If you want to get more information about differences between Postgres and MySQL please visit:

• Pros and Cons of MySQL Table Types
• Read the comparsion of mysql, pgsql and other databases

• and something about tuning PostgreSQL for performance - "Power PostgreSQL" by J.Berkus and J.Conway.

• The side-by-side comparison of some of the more frequently used features of MySQL and PostgreSQL

• Professional benchmark between MySQL 3.23 and PostgreSQL 7.1.2 (so both quite old).

Manuals

• MySQL manual,
• PostgreSQL manual

Download

• PostgreSQL download page
• MySQL download page
• Benchmark source code (in Java) with short installation guide

Installation

You will find some installation hints:

• MySQL installation
• PostgreSQL installation

Contact

Author

Please send your comments, suggestions and other to:

• Maciej Glowiak, PSNC (email address: mac at man.poznan.pl)