Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
9  
How can you call such a setup a "test" when the scenarios are not even remotely comparable? Client programs in different languages, DBMS using different operating systems? How do you expect any meaningful results here? –  daff Jul 12 '11 at 21:24

4 Answers 4

up vote 2 down vote accepted

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.

share|improve this answer
    
Circle gets the square !!! (if you remember the gameshow Hollywood Squares) +1 !!! –  RolandoMySQLDBA Jul 12 '11 at 20:50

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.

share|improve this answer
    
MySQL is running in Fedora with ext4. so this has a good benefit for MySQL vs NTFS for PostgreSQL. I myself think PHP is running slowly in command line mode. –  Majid Azimi Jul 12 '11 at 20:22
1  
Yes, but you are trying to compair something that is not meant to be compaired, if you want to benchmark 2 different software, you use them in exactly the same way on exactly the same system. I recon it might be php. If you use ODBC in java or php, you can easily test the difference of pgsql and mysql. –  Lucas Kauffman Jul 12 '11 at 20:25

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.