MySQL vs. PostgreSQL

来源：互联网发布：zepto touch.js怎么用编辑：程序博客网时间：2024/06/06 10:04

MySQL vs. PostgreSQL

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

[edit]

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,
- 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:

[hide]

1 MySQL vs. PostgreSQL
- 1.1 Comparison
- 1.2 Additional notes
  - 1.2.1 MySQL table types
- 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

[edit]

Additional notes

[edit]

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 .

[edit]

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

[edit]

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

[edit]

Installation

Information about installation of:

MySQL installation
PostgreSQL installation

[edit]

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).

[edit]

Benchmark

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

[edit]

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.

[edit]

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

[edit]

Hardware

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

[edit]

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

[edit]

Code

Benchmark code is available here The benchmark source code page.

[edit]

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;

[edit]

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)

[edit]

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.

[edit]

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.

[edit]

INSERT INTO a VALUES ( . . . );

            MySQL        MySQL     PostgreSQL    PostgreSQLINSERT     MyISAM       InnoDB     fsync=true   fsync=false 5000        3006        34745          17540          482510000        4967        71402          14052          877215000        7474       103469          21873         1306420000        9996       143009         110359         1737425000       12524       170862         101442         2197430000       15034       186316          51390         2614835000       17515       119604          49103         3048440000       20040        58373         158071         3511245000       21586       262558          65042         3905850000       23980        68716          73832         43418

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

[edit]

SELECT * FROM a;

            MySQL        MySQL     PostgreSQL    PostgreSQLQUERY1     MyISAM       InnoDB     fsync=true   fsync=false 5000          45           42            108           10410000         157          174            141           15015000         110          113            218           21920000         240          156            429           30025000         288          303            420           50630000         352          251            489           61435000         378          285            721           73840000         415          316            864           80545000         479          491            880           73750000         525          562           1009           868

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

[edit]

SELECT * FROM a ORDER BY 4;

            MySQL        MySQL     PostgreSQL    PostgreSQLQUERY2     MyISAM       InnoDB     fsync=true   fsync=false 5000         101          101            202           21810000         201          207            415           41215000         309          307            664           86420000         436          415            879           89725000         508          515           1206          122330000         617          719           1401          136635000         722          840           1763          167140000         827          958           1909          192845000         998          953           2180          228550000        1031         1096           2518          2713

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

[edit]

DELETE FROM a;

            MySQL        MySQL     PostgreSQL    PostgreSQLDELETE     MyISAM       InnoDB     fsync=true   fsync=false 5000           1           78             23            1910000           2          166             56            4015000           3          259             79            6020000           3          330            104            8225000           3          501            216           15430000           4          405            733           15135000           4          490            230           24140000           5          664            341           23545000           6          753            578           34050000           6         1171            614           282

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

[edit]

What are these MyISAM, InnoDB and fsync???

[edit]

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.

[edit]

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.

[edit]

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.

[edit]

Manuals

MySQL manual,
PostgreSQL manual

[edit]

Download

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

[edit]

Installation

You will find some installation hints:

MySQL installation
PostgreSQL installation

[edit]

Contact

[edit]

Author

Please send your comments, suggestions and other to:

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

Retrieved from "http://monstera.man.poznan.pl/jra1-wiki/index.php/Mysql_vs_postgres"

MySQL vs. PostgreSQL