Overview
After adding PostgreSQL compatibility to my PHPSimpleChat library, I became interested in its performance versus MYSQL. In my workplace, a large New York based media corporation, MYSQL is quickly becoming the database of choice ahead of the costly Sybase. MYSQL certainly has the momentum of the industry behind it, but what about PostGreSQL? Having advanced features, a solid community following, and easy installation on all major operating systems, PostGreSQL shouldn’t be left out of the race.
Performance
The feature sets of MYSQL 5 and PostGreSQL 8 are roughly equal when using MYSQL’s InnoDB table type, with PostGreSQL having just a few more features. After meeting my needs feature-wise, my next biggest concern is performance. The sites I work with are often content management system (cms) based in popular platforms like Drupal and Wordpress. Complicated Drupal sites can execute several thousand queries for a logged in user for each page view and need a high-performance database to support it. Below, I’ve setup several tests using a single database connection to benchmark both MYSQL and PostGreSQL executing the same queries. In addition to testing MYSQL’s InnoDB table type, which uses row level locking, I also tested MYSQL’s MyISAM and Memory table types. MyISAM was the standard before InnoDB, but uses table level locking, which can cause backups in a highly active insert and update environments. Memory tables are stored entirely in memory, requiring no disk access for queries, but are truncated on every server restart.
Test 1: Select = int with integer index
Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) with 1000 consecutive selects of random rows.
Results: Memory tables were the fastest type, though not by much compared to InnoDB’s other offerings. PostGreSQL’s selects lagged behind.
Test 2: Select = ’string’ with indexed varchar column
Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) and a varchar(32) (character varying in PostGreSQL) populated with random 2 character strings. The test does 1000 consecutive selects on the varchar column looking for random 2 character matches.
Results: Memory tables were the fastest type again, slightly ahead of InnoDB. MyISAM was noticeably slower in indexed string comparisons than it was with integer selects. PostGreSQL’s again lagged behind.
Test 3: Select like ‘%string%’ with indexed varchar column
Setup: 1000 row table setup identically in both MYSQL and PostGreSQL with an auto-incrementing primary key column (serial type column in PostGreSQL) and a varchar(32) (character varying in PostGreSQL) populated with random 8 character strings. The test does 1000 consecutive selects on the varchar column looking for random 2 character matches within the 8 characters using the wildcard operator.
Results: Memory tables fastest. PostGreSQL surprisingly was the next fastest by a significant percentage over InnoDB and MyISAM, with InnoDB being the slowest by far.
Test 4: Inserts
Setup: I did the insert test for inserting both integers and varchars into a table with an additional auto-incrementing primary key column, but the results were so similar that I’m only going to show the results from the integer insert test. The test did 100 consecutive inserts into the table.
Results: Memory tables were predictably the fastest, followed by MyISAM, and then PostGreSQL. Its worth noting that the sequence of PostGreSQL inserts were five times slower than MyISAM and Memory tables. The InnoDB inserts are very slow to the other table types, but its worth noting that is because of the row-level locking. In a highly active read and write environment, InnoDB may actually perform better than the other table types. This is because it does not have to wait for the insert to finish, when a select comes into the queue. With table types that lock on the table level, select queries can get backed up waiting for each insert to complete.
Overall Results
Memory tables are the fastest overall, but are impractical in many situations where data-integrity is a concern. Applications like chat rooms, or quick lookup apps may be ideal for memory tables. MySQL’s default database in version 5 is InnoDB because of its blend of performance and feature set. MyISAM may be faster in some situations, but with multiple request threads in high-write environments, InnoDB is usually faster. PostGreSQL’s performance was surprisingly unimpressive with the sole exception of a varchar wildcard lookup. Unless the performance of PostGreSQL significantly increases with its next release, MYSQL will remain my open-source database of choice.
