After looking into migrating to PostgreSQL, which seems to be a popular pastime among database people (migrating, not looking into), I decided to do my own benchmarks. Here are the results of the simple ones (I have yet to code the complex ones). I wrote all the code in perl, and ran it on quentin. I use InnoDB for mysql, with defaults, and everything default on postgres.

scott@quentin:~/benchmarking$ psql --version
psql (PostgreSQL) 8.3.7
contains support for command-line editing
scott@quentin:~/benchmarking$ mysql --version
mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (powerpc) using readline 5.2
scott@quentin:~/benchmarking$ uname -a
Linux quentin 2.6.26-2-powerpc #1 Thu May 28 21:45:49 UTC 2009 ppc GNU/Linux
scott@quentin:~/benchmarking$ mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 279
Server version: 5.0.51a-24+lenny1 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use benchmarking;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe simple;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| K     | varchar(10) | NO   | MUL | NULL    |       |
| Value | varchar(10) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> exit
Bye
scott@quentin:~/benchmarking$ psql benchmarking
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

benchmarking=# \d simple
           Table "public.simple"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 k      | character varying(10) | not null
 value  | character varying(10) | not null
Indexes:
    "key" btree (k)

benchmarking=# \q
scott@quentin:~/benchmarking$ all
MySQL Benchmarks:
Connecting...  took 0.128844022750854 seconds
Preparing tables...  took 0.0636351108551025 seconds
Compiling random numbers to insert...  took 0.485441923141479 seconds
Performing 50,000 simple INSERTs...
  took 64.5562191009521 seconds
Performing 200 simple SELECTs from index...
  took 43.9555008411407 seconds
Performing 200 simple SELECTs without index...
  took 42.9843010902405 seconds
Disconnecting...  took 0.000300168991088867 seconds

PostgreSQL Benchmarks:
Connecting...
  took 0.140900135040283 seconds
Preparing tables...
  took 0.100701093673706 seconds
Compiling random numbers to insert...
  took 0.484206199645996 seconds
Performing 50,000 simple INSERTs...
  took 95.7518320083618 seconds
Performing 200 simple SELECTs from index...
  took 0.144593954086304 seconds
Performing 200 simple SELECTs without index...
  took 12.3661141395569 seconds
Disconnecting...
  took 0.000296115875244141 seconds
scott@quentin:~/benchmarking$

Related posts: