MySQL is faster than PgSQL but a large margin in my program... any ideas why?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi all,

On a user's request, I recently added MySQL support to my backup program which had been written for PostgreSQL exclusively until now. What surprises me is that MySQL is about 20%(ish) faster than PostgreSQL.

Now, I love PostgreSQL and I want to continue recommending it as the database engine of choice but it is hard to ignore a performance difference like that.

My program is a perl backup app that scans the content of a given mounted partition, 'stat's each file and then stores that data in the database. To maintain certain data (the backup, restore and display values for each file) I first read in all the data from a given table (one table per partition) into a hash, drop and re-create the table, then start (in PostgreSQL) a bulk 'COPY..' call through the 'psql' shell app.

In MySQL there is no 'COPY...' equivalent so instead I generate a large 'INSERT INTO file_info_X (col1, col2, ... coln) VALUES (...), (blah) ... (blah);'. This doesn't support automatic quoting, obviously, so I manually quote my values before adding the value to the INSERT statement. I suspect this might be part of the performance difference?

I take the total time needed to update a partition (load old data into hash + scan all files and prepare COPY/INSERT + commit new data) and devide by the number of seconds needed to get a score I call a 'U.Rate). On average on my Pentium3 1GHz laptop I get U.Rate of ~4/500. On MySQL though I usually get a U.Rate of ~7/800.

If the performace difference comes from the 'COPY...' command being slower because of the automatic quoting can I somehow tell PostgreSQL that the data is pre-quoted? Could the performance difference be something else?

If it would help I can provide code samples. I haven't done so yet because it's a little convoluded. ^_^;

  Thanks as always!

Madison


Where the big performance concern is when

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
          Madison Kelly (Digimer)
   TLE-BU; The Linux Experience, Back Up
Main Project Page:  http://tle-bu.org
Community Forum:    http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux