PostgreSQL vs MySQL: Extremely Different Performance

Question

We were doing a simple test with MySQL 5.5 and PostgreSQL 9.0.4. The test is so simple. we are inserting to table in a loop. we have simple table with one field integer autoincrement primary key and some text fields.

The client program is written in JAVA for PostgreSQL. The client program is written in PHP(running from command line) for MySQL.

PostgreSQL is running under Windows XP Service pack 3. MySQL is running under Fedora 15. MySQL is also using InnoDB as storage engine.

All of the configuration left as default. no changes(Installation Configuration)

PostgreSQL is doing about 1063 INSERT Transactions Per Second. MySQL is doing about 30 INSERT Transactions Per Second.

I think there must be a problem. Is there any or it is normal?

Answer 1

Nice question. Absolutely problem can be solved by direct request to Mysql Via terminal(Shell).

I mean when you use php via command, there have so time wasting. I tried to do this with this query:

insert into test(`id`,`txt`)values
 ('0','hello world'),
 ('1','hello world'),
 ('2','hello world'),
 ('3','hello world'),
.
.
.
 ('500','hello world');

And result was:

Query OK, 501 rows affected (0.06 sec)
Records: 501  Duplicates: 0  Warnings: 0

MySQL in my System, handle these request in 833 inserts per second.

Answer 2

maybe, if you setup your mysql and pgsql on exactly the same system and use exactly the same client you could compair results...

Also, have you checked your settings ?

I don't think that you have reliable results for comparisson.

Answer 3

By default, InnoDB will flush data to disk after each commit. If you are using PHP to send a single insert query at a time, each query will likely be autocommitted and cause a disk flush.

You can change this behaviour by setting innodb_flush_log_at_trx_commit = 2 in your my.cnf, which will make sure files are only flushed to disk once per second. That will give you siginificantly better performance for mass inserts.

---

Much of the confusion is probably caused by your test environments being vastly different. Knowing how typical Java/JDBC code looks compared to typical PHP code, I'm guessing that the Java version runs all these inserts in a single transaction while the PHP version instead relies on autocommit which will cause a much larger number of transactions.

Answer 4

It sound like you need to configure your InnoDB Buffer Pool.

First do the following: add these lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_log_buffer_size=32M
innodb_buffer_pool_size=2G
innodb_log_file_size=2047M
innodb_open_files=25000

Then, try the following:

cd /var/lib/mysql
rm -f ib_logfile[01]
service mysql start

Starting up mysql will cause the mysqld server process to recreate ib_logfile0 and ib_logfile1

To make a level playing field, you will have to set similar buffer for PostgreSQL.

Without setting anything, you quickly learned that PostgreSQL's default settings perform much better than MySQL's default settings. Once InnoDB is properly configured, now you are talking apples-to-apples.