Skip site navigation (1) Skip section navigation (2)

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

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: MySQL is faster than PgSQL but a large margin in my program... any ideas why?
Date: 2005-12-22 02:03:18
Message-ID: 43AA0966.6090401@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Responses

pgsql-performance by date

Next:From: Michael FuhrDate: 2005-12-22 02:13:46
Subject: Re: Speed of different procedural language
Previous:From: Harry JacksonDate: 2005-12-22 01:20:16
Subject: CPU and RAM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group