Re: Postgres performance - Mailing list pgsql-sql

mauro wrote:
>>Not always, AFAICT. The four most common reasons why PG tests slower 
>>than Mysql are:
>>1. You haven't configured or have misconfigured PostgreSQL.
>>2. You are testing a MySQL-tuned application (lots of small, simple 
>>queries, no views, no subselects etc)
>>3. You are only testing one connection (try 10,20,50 simultaneous users 
>>and see who wins then).
>>4. You are not testing the transaction-safe storage systems in MySQL
>>
>>See if you can answer some of the questions above and I'm sure we'll be 
>>able to get your database server running smoothly.
> 
> Hi, 
>   I've used the benchmark
> http://genecensus.org/yeast/cluster/database/mysql/sql-bench/ (ok,
> it's without bench on views, sub-select, transaction,..)

Other points about this benchmark:
1. It's a good 5 years old. Nothing wrong in that, but the fact that it 
hasn't been updated in that time doesn't bode well. If nothing else, it 
is designed to test PostgreSQL version 6.x
2. As you say, it doesn't actually use any of the features of a modern 
database.
3. Although vacuum is mentioned, it's not obvious to me that it's being 
run. Also, I don't see any analyze run of the populated tables.
4. It wasn't immediately obvious to me how the tests were dealing with 
varying amounts of data being cached on different runs.
5. I couldn't see how many simultaneous connections were being tested.
6. In fact, I couldn't find a clear rationale about what these tests 
were supposed to simulate - what sort of environment.

> The database files are in stripe (RAID 0) on two SATA hd (transfer
> rate 50Mb), the filesystem is reiserfs (3.6 format - with checks off),
> no optimation on I/O scheduler,

Largely irrelevant for these particular tests.
> DBMS are in default configuration (so
> I don't benefit nobody).

If you're running with default configuration, you'll want to compare the 
two on a PII-200 with 32MB of RAM. That's roughly the default settings 
for PG's config. PG isn't designed to be run with the default 
configuration settings, it's designed to run almost anywhere.

> Total time:
> Pgsql: 7h 20'
> MySQL: 14' (!!)
> 
> This is the configuration where is running Postgres 8.0 and MySql:
[snipped long list of hardware details/run results]

> What do you think about this?

I think you didn't read my last message. I'll quote the relevent points 
again:
- begin quote -
Not always, AFAICT. The four most common reasons why PG tests slower 
than Mysql are:
1. You haven't configured or have misconfigured PostgreSQL.
2. You are testing a MySQL-tuned application (lots of small, simple 
queries, no views, no subselects etc)
3. You are only testing one connection (try 10,20,50 simultaneous users 
and see who wins then).
4. You are not testing the transaction-safe storage systems in MySQL
- end quote -

How many of these points apply to the benchmark you used? (Hint - it 
looks like all 4 to me).

Of course, if, on your production systems you:
1. Don't intend to configure your database system
2. Don't want views/triggers/subselects/partial indexes/functional 
indexes/...etc
3. Only have one simultaneous user
4. Don't use transactions and don't mind an inconsistent database.

In that case, these test results are relevant, and the right choice is 
clearly MySQL.

If you want to actually come up with some useful test figures, you'll 
want to:
1. Spend a reasonable amount of time learning how to setup and configure 
each system.
2. Understand your users' requirements, and design the tests accordingly.
3. Actually use the database to do what it is designed for.
4. Make sure you aren't using SQL structures that favour one database 
system over another (or have one schema for each database being tested)
5. Account for other factors in your tests - how much time is spent in 
Java/PHP etc. vs time in the database?

Best of luck Mauro, realistic testing is not a simple process and you've 
got a lot of work ahead of you. Don't forget there's the performance 
list that can help with specific problems too.

--  Richard Huxton  Archonet Ltd