[PERFORM] MySQL is faster than PgSQL but a large margin in my program... any ideas why?
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 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] MySQL is faster than PgSQL but a large margin in my program... any ideas why?
* Madison Kelly ([EMAIL PROTECTED]) wrote: > 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? I doubt the issue is with the COPY command being slower than INSERTs (I'd expect the opposite generally, actually...). What's the table type of the MySQL tables? Is it MyISAM or InnoDB (I think those are the main alternatives)? IIRC, MyISAM doesn't do ACID and isn't transaction safe, and has problems with data reliability (aiui, equivilant to doing 'fsync = false' for Postgres). InnoDB, again iirc, is transaction safe and whatnot, and more akin to the default PostgreSQL setup. I expect some others will comment along these lines too, if my response isn't entirely clear. :) Stephen signature.asc Description: Digital signature
Re: [PERFORM] MySQL is faster than PgSQL but a large margin in my program... any ideas why?
On Wednesday 21 December 2005 20:14, Stephen Frost wrote: > * Madison Kelly ([EMAIL PROTECTED]) wrote: > > 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? > > I doubt the issue is with the COPY command being slower than INSERTs > (I'd expect the opposite generally, actually...). What's the table type > of the MySQL tables? Is it MyISAM or InnoDB (I think those are the main > alternatives)? IIRC, MyISAM doesn't do ACID and isn't transaction safe, > and has problems with data reliability (aiui, equivilant to doing 'fsync > = false' for Postgres). InnoDB, again iirc, is transaction safe and > whatnot, and more akin to the default PostgreSQL setup. > > I expect some others will comment along these lines too, if my response > isn't entirely clear. :) Is fsync() on in your postgres config? If so, that's why you're slower. The default is to have it on for stability (writes are forced to disk). It is quite a bit slower than just allowing the write caches to do their job, but more stable. MySQL does not force writes to disk. ---(end of broadcast)--- TIP 6: explain analyze is your friend
