Обсуждение: Benchmarks of MySQL, MaxDB, PostgreSQL, and Oracle

Hi!

A person compared the performances of MySQL, MaxDB, PostgreSQL, and
Oracle, and posted his result at
http://laser.dyndns.pgsqldb.com/index.php?rid=1923&S=aeac79693ab902121ccb13957fb65d8c&t=msg&th=5893&start=0&logoff=1

I excerpted his JBuilder benchmark testing script and the results as
follows. Peronally I am not satisfied with PostgreSQL's position in the
result, which shows that PostgreSQL is only faster than MySQL4.1. Can
anyone give some comments on this? Should we ignore all of them?

Regards,

CN
------------
Tested platform: WinXP SP1

public void testInsert()
  {
    java.sql.Connection conn = null;
    try {
      //MYSQL4.1
      conn = java.sql.DriverManager.getConnection(
          "[url]jdbc:mysql://localhost:3333/test[/url]");
      test("MySQL4", conn);
      //MYSQL5
       conn =
       java.sql.DriverManager.getConnection("[url]jdbc:mysql://localhost/test[/url]");
      test("MySQL5", conn);

      //MAXDB
      conn = java.sql.DriverManager.getConnection(
          "[url]jdbc:sapdb://LOCALHOST/TEST?unicode=true[/url]",
          "USERTEST", "USERTEST");
      test("MAXDB", conn);

      // postgresql
      conn = java.sql.DriverManager.getConnection(
          "[url]jdbc:postgresql://localhost:5432/testdb[/url]", "user",
          "1234");
      test("POSTGRESQL", conn);
      //ORACLE
      conn = java.sql.DriverManager.getConnection(
          "jdbc:oracle:thin:@127.0.0.1:1521:mydb", "ssdb", "ssdb");
      test("ORACLE", conn);

    }
    catch (Exception e) {
      e.printStackTrace();
    }

  }
public void test(String db,java.sql.Connection conn) throws Exception
  {
    try
    {
      java.sql.Statement st = conn.createStatement();
      try {
        try
        {
          st.executeUpdate("drop table dm");
        }catch(Exception e)
        {
          ;
        }
        st.execute("Create Table dm(id integer,mc varchar(40))");
      }
      finally {
        st.close();
      }

      java.util.Date d_first = new java.util.Date();
      System.out.println("");
      System.out.print(db);
      System.out.print(" now will start :");
       System.out.println(d_first.getTime());
      conn.setAutoCommit(false);

      java.sql.PreparedStatement pst = conn.prepareStatement(
          "insert into dm(id,mc) values(?,?)");
      for (int i = 0; i < 10000; i++) {
        pst.setInt(1, i);
        pst.setString(2, "benchmark test");
        pst.execute();
      }
      conn.rollback();
      java.util.Date d_last = new java.util.Date();
      System.out.print(db);
      System.out.print(" now END:");

      System.out.println(d_last.getTime());
      System.out.print(db);
      System.out.print(" Use Time ：");
      System.out.println(d_last.getTime() - d_first.getTime());

    }finally
    {
      conn.close();
    }
  }

The result follows (figures are in seconds):

MySQL4 Use Time ：13484
MySQL5 Use Time ：9703
MAXDB Use Time ：10641
POSTGRESQL Use Time ：11547
ORACLE Use Time ：11140

Replacing ROLLBACK with COMMIT in the testing script he got the
following result:

MySQL4 Use Time ：14031
MySQL5 Use Time ：9937
MAXDB Use Time ：10985
POSTGRESQL Use Time ：12860
------------

O CN έγραψε στις Mar 24, 2005 :

> Hi!
>
> A person compared the performances of MySQL, MaxDB, PostgreSQL, and
> Oracle, and posted his result at
> http://laser.dyndns.pgsqldb.com/index.php?rid=1923&S=aeac79693ab902121ccb13957fb65d8c&t=msg&th=5893&start=0&logoff=1

The posting is in chinese (simplified chinese GB2312, but still chinese!).
Also you mentioned Windows XP??
That certainly does not represent a typical situation,
at least as far as postgresql is concerned.
Anyway, i think benchmarks are strongly dependent on the
setup/tuning of each DB, and generally they dont serve
much more than marketing purposes.

However, in some (rare) cases, where care has been taken regarding
test data,tuning,cleanness of definitions,assumptions,etc,
some useful information could be mined out of benchmarks,
but again it always has to be orange against oranges and
apples against apples.

>
> I excerpted his JBuilder benchmark testing script and the results as
> follows. Peronally I am not satisfied with PostgreSQL's position in the
> result, which shows that PostgreSQL is only faster than MySQL4.1. Can
> anyone give some comments on this? Should we ignore all of them?
>
> Regards,
>
> CN
> ------------
> Tested platform: WinXP SP1
>
> public void testInsert()
>   {
>     java.sql.Connection conn = null;
>     try {
>       //MYSQL4.1
>       conn = java.sql.DriverManager.getConnection(
>           "[url]jdbc:mysql://localhost:3333/test[/url]");
>       test("MySQL4", conn);
>       //MYSQL5
>        conn =
>        java.sql.DriverManager.getConnection("[url]jdbc:mysql://localhost/test[/url]");
>       test("MySQL5", conn);
>
>       //MAXDB
>       conn = java.sql.DriverManager.getConnection(
>           "[url]jdbc:sapdb://LOCALHOST/TEST?unicode=true[/url]",
>           "USERTEST", "USERTEST");
>       test("MAXDB", conn);
>
>       // postgresql
>       conn = java.sql.DriverManager.getConnection(
>           "[url]jdbc:postgresql://localhost:5432/testdb[/url]", "user",
>           "1234");
>       test("POSTGRESQL", conn);
>       //ORACLE
>       conn = java.sql.DriverManager.getConnection(
>           "jdbc:oracle:thin:@127.0.0.1:1521:mydb", "ssdb", "ssdb");
>       test("ORACLE", conn);
>
>     }
>     catch (Exception e) {
>       e.printStackTrace();
>     }
>
>   }
> public void test(String db,java.sql.Connection conn) throws Exception
>   {
>     try
>     {
>       java.sql.Statement st = conn.createStatement();
>       try {
>         try
>         {
>           st.executeUpdate("drop table dm");
>         }catch(Exception e)
>         {
>           ;
>         }
>         st.execute("Create Table dm(id integer,mc varchar(40))");
>       }
>       finally {
>         st.close();
>       }
>
>       java.util.Date d_first = new java.util.Date();
>       System.out.println("");
>       System.out.print(db);
>       System.out.print(" now will start :");
>        System.out.println(d_first.getTime());
>       conn.setAutoCommit(false);
>
>       java.sql.PreparedStatement pst = conn.prepareStatement(
>           "insert into dm(id,mc) values(?,?)");
>       for (int i = 0; i < 10000; i++) {
>         pst.setInt(1, i);
>         pst.setString(2, "benchmark test");
>         pst.execute();
>       }
>       conn.rollback();
>       java.util.Date d_last = new java.util.Date();
>       System.out.print(db);
>       System.out.print(" now END:");
>
>       System.out.println(d_last.getTime());
>       System.out.print(db);
>       System.out.print(" Use Time ‘G");
>       System.out.println(d_last.getTime() - d_first.getTime());
>
>     }finally
>     {
>       conn.close();
>     }
>   }
>
> The result follows (figures are in seconds):
>
> MySQL4 Use Time ‘G13484
> MySQL5 Use Time ‘G9703
> MAXDB Use Time ‘G10641
> POSTGRESQL Use Time ‘G11547
> ORACLE Use Time ‘G11140
>
> Replacing ROLLBACK with COMMIT in the testing script he got the
> following result:
>
> MySQL4 Use Time ‘G14031
> MySQL5 Use Time ‘G9937
> MAXDB Use Time ‘G10985
> POSTGRESQL Use Time ‘G12860
> ------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--
-Achilleus

"CN" <cnliou9@fastmail.fm> writes:
> A person compared the performances of MySQL, MaxDB, PostgreSQL, and
> Oracle, and posted his result at
> http://laser.dyndns.pgsqldb.com/index.php?rid=1923&S=aeac79693ab902121ccb13957fb65d8c&t=msg&th=5893&start=0&logoff=1

Ah, your good old basic MySQL-friendly benchmark design: let's test one
client doing one trivial query type over and over, so we can ignore all
those nasty concurrency issues.  Oh, and let's not say anything about
configuration, so no one can tell if the various DBs have been set up
reasonably.

Postgres would probably show up better on platforms other than Windows;
the native Windows port is brand new and hasn't been shaken out at all
as far as performance goes.  In particular, assuming that this was run
with the default fsync mode, it probably suffers badly from the fsync-
is-writethrough-on-Windows problem that was just recently fixed.

> Should we ignore all of them?

Pretty much.  Any "benchmark" testing only one query type, and as poorly
documented as this, is not really worth the trouble to respond to
anyway.

(If I could read Chinese maybe I'd not think it was so poorly
documented, but there surely isn't a lot of info on that page
about the test conditions.)

            regards, tom lane