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

Re: Speed Question

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Noah Silverman <noah(at)allresearch(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Speed Question
Date: 2002-12-20 23:59:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> Speed is one of our major concerns, so before switching we've decided 
> to perform some speed tests.
>  From what I understand, Postgresql is NOT as fast as Mysql,

This is a PR myth spread by MySQL AB.   The truth is:

1) PostgreSQL, unconfigured and not optimized, is indeed slower than MySQL 
out-of-the-box.  MySQL is meant to be idiot-proof; PostgreSQL is not, 

2) Nobody has yet come up with a database benchmark that both MySQL AB and the 
PostgreSQL team are willing to accept; depending on whose benchmark you use, 
either could be faster -- and neither benchmark may approximate your setup.

> We've installed the software and have run some basic insert, index and 
> query tests that
> seem ridiculously slow.  I can't help thinking that we are doing 
> something wrong, or
> don't have things configured for optimal performance.

Almost undoubtedly.   Have you modified the postgresql.conf file at all?  
Where are your database files located on disk?  How are you construting your 

> We've performed these same tests on Mysql and then run dramatically 
> faster.

Without transations?  Sure.   Turn off transaction logging, and PostgreSQL 
runs faster, too.

> Here's the initial performance test results and issues...
> Table configuration:
> speedtest( prop1 integer, prop2 integer, prop3 integer, prop4 integer);
> indexes on each of the four individual property fields
> Each record consists of four random integers, uniformly distributed,
> between 0 and 1000.  The integers are computed in the perl script
> used to populate the table, not using an SQL random() function.
> Hardware configuration:  P3-500, 384MB ram, *unloaded* system.
> Software configuration: Linux 2.4.20, reiserfs, standard slackware 
> install.

You haven't mentioned your PostgreSQL memory settings, by which I assume that 
you haven't configured them.  This is very important.

> Issue #1:  Speed of inserts is relatively slow.  100000 inserts is 
> taking
> roughly 10 minutes.  This isn't EVIL, but mysql appears to be about
> ten times faster here.  Is there something we could do to the indexes
> differently?  Disable transactions?  Is there a more "raw" insert, which
> may not set off triggers?

Bundle them in a single transaction.  Move pg_xlog to a seperate drive from 
the database.

> Issue #2:  It doesn't appear as though multiple indexes are being used.
> ie:  select count(*) from speedtest where (prop1 between 100 and 200)
> and( prop2 between 100 and 200) and (prop3 between 100 and 200)
> and (prop4 between 100 and 200)  formulates a query plan that only
> uses one index.  The following is pasted from the 'explain select' ---

That's correct; Postgres will only use a single index on this query.  If you 
want to reference all columns, create a multi-column index.  Note that, 
however, Postgres is likely to reject the index as it is just as large as the 
table.  In this way, your test is insufficiently like real data.

Good luck.  Why not use the Open Database Benchmark for testing, instead of 
inventing your own?

-Josh Berkus
 Aglio Database Solutions
 San Francisco

In response to

pgsql-performance by date

Next:From: Noah SilvermanDate: 2002-12-21 00:10:49
Subject: Re: Speed Question
Previous:From: Tom LaneDate: 2002-12-20 23:50:25
Subject: Re: Speed Question

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