Re: PostgreSQL vs MySQL - Mailing list pgsql-general

Hi all!

Just my .5c to discussion about MySQL vs PgSQL...

1. no war, please :)
2. last year during same kind discussion I've ported a db_STRESS test to
MySQL and PostgreSQL
   and start on the same Sun machine. I was curious to compare table vs
row?/other locking.
        As we see often: 1. Idea, 2. Realisation... but whithout well
done 2. the 1. is just an Idea...
        The results of this test I've sent to Monthy and Bruce and hope
it was interesting for them
        and their team...
3. both products are great and they are DIFFERENT, that's why you have a
choice :))

Best regards,
-Dimitri



*******************************
*********** db_STRESS
***

Oct. 1999


This test I've called db_STRESS - it's exactly what I mean :)

Database Schema
===============

-- ============================================================
--   Table : ZONE
-- ============================================================
create table ZONE
(
    REF                 CHAR(2)            not null,
    NAME                CHAR(40)           not null
);


-- ============================================================
--   Table : SECTION
-- ============================================================
create table SECTION
(
    REF                 CHAR(2)            not null,
    REF_ZONE            CHAR(2)            not null,
    NAME                CHAR(40)           not null
);


-- ============================================================
--   Table : OBJECT
-- ============================================================
create table OBJECT
(
    REF                 CHAR(10)              not null,
    REF_SECTION         CHAR(2)               not null,
    NAME                CHAR(30)              not null,
    CREATE_DATE         CHAR(12)              not null,
    NOTE                CHAR(100)
);

-- ============================================================
--   Table : HISTORY
-- ============================================================
create table HISTORY
(
    REF_OBJECT          CHAR(10)              not null,
    HORDER              INT                   not null,
    REF_STAT            CHAR(3)               not null,
    BEGIN_DATE          CHAR(12)              not null,
    END_DATE            CHAR(12)                      ,
    NOTE                CHAR(100)
);


-- ============================================================
--   Index :
-- ============================================================

create unique index zone_ref_idx on ZONE( ref );
create unique index stat_ref_idx on STAT( ref );
create unique index section_ref_idx on SECTION( ref );
create unique index object_ref_idx on OBJECT( ref );
create unique index history_ref_idx on HISTORY( ref_object, horder );


So we have relations: OBJECT ===> SECTION ===> ZONE
and OBJECT ===> HISTORY

For each record of OBJECT there are 20 records of HISTORY.


Client Programm
================

According to input options client programm will start two kind of
transactions to database server:

        READ - read randomly OBJECT whole information by OBJECT
reference

        WRITE - delete + insert + modify HISTORY record of random OBJECT
by
reference

Input options:
        time of test in sec. - 180 in my case
        timeout interval between two transactions - 1 sec in my case
        number of READ for one WRITE - 1 (50%), 3 (25%), 20 (5%) and
1000 (0%)
in my case


db_STRESS Description
=====================
start 1, 5, 10, 20, 40, 80, 160, 320, 640 client programms with:
  1, 3, 20, and 1000 READs for one WRITE


During the test each client programm log time of each transaction, at
the end of test a short report is generated about of total number of
transactions (ALL, READ, WRITE), Avg time of transaction (ALL, READ,
WRITE), etc.


Run Conditions
==============
So, this test is running on Enterprise 4500, 12CPU 400Mhz 4Mb cache,
12Gb RAM.
OS - Solaris 2.6
Database has 50.000 OBJECTs, so 1.000.000 records of HISTORY.
Due large RAM all data are cached by file system during test, so no disk

I/O are performed and ONLY database server architecture/realisation is
tested...

MySQL
=====
+ multithreaded architecture, so very small task overhead, should be
well scalled
? locks between internal data, etc.
- table locking for non-SELECT query

PostgreSQL
==========
+ transaction isolation, so no locks between readers and writers
? realisation of internal locks, etc.



> Why we do not use 'INSERT DELAYED' for MySQL?

We have to be sure each transaction is REALLY finished.


> It would be nice if you also could run a tests with reading/writing to

> a lot of different tables (In this case database will scale much
better)

Any database will scale better if you use more tables :)
But usually if you decided to use database server is to manage the
concurent access to data...

That's why it'll be very interesting to compare table locking and row
locking...



> Avg total transaction time should not grow according to the number of
> clients.  In the worst case things could get serialized and not
> utilize all CPU:s, but this should not affect the transaction time.

Not in case of MySQL, because one INSERT will stop all SELECTs, so
transaction time will grow according to the number of clients

Transaction time I mean a time of single transaction (READ or WRITE)...



Options used (different from default) for:
        MySQL:  --skip-locking
        PostgreSQL: -o -F

we will try to go as fast as possible

--low-priority-updates for MySQL is not use - grow WRITE transaction
time and lock clients...


And NOW final results! :))
Use them as you want and be free to present them for your
developpers/users/etc....

You will find graphics in attachment.


MySQL Summary
=============
As I supposed, table locking became very critical for access
concurency...
Any way very stable, no surprise...

*Usage: excellent for read-oriented access with any number of clients
       good for read+write access with small number of clients or writes




PostgreSQL Summary
==================
Excellent performances till 80 users and strange degradation with
more...
Sounds like internal locks/conurency problem...
!!!Several core dumps during the test...

*Usage: excellent for any kind access with small number of clients...


Best regards,
-Dimitri

--
===========================================================
 Dimitri KRAVTCHUK  (dim)           Sun Microsystems
 Benchmark Engineer                 France
 dimitri@france.sun.com             http://goldgate.france
===========================================================