Web Dev Blog

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.

Administering MySQL is not an easy task. The cryptic my.ini database settings file is hard to set correctly. Also, using the MySQL command line is rather intimidating if you don’t know exactly what you’re doing. Thankfully the good people at MySQL have provided us (the general public) with the MySQL GUI Tools to solve both of these problems for their community MySQL database server. If you haven’t used these tools before, you’re not alone - they are not included with the MySQL distribution and are often overlooked. They are available for Windows, Mac, and Linux and you can download the package here: http://dev.mysql.com/downloads/gui-tools/5.0.html.

One popular database administration tool is PHPMyAdmin. While very useful, PHPMyAdmin is web based and therefore requires a working PHP enabled server to function properly. MySQL GUI tools are very useful in this regard - they install anywhere (even the database server itself) and access your databases through a visual client. No web server needed!

The MySQL GUI Tools consist of the MySQL Administrator, Query Browser, Migration Toolkit, and the Tray Monitor. The most interesting and useful of the two (in my opinion) are the Administrator and the Query Browser.

The Administrator combines many useful features into one interface. You can completely administer your database settings through its menus - it will update the my.ini file for you automatically. It also gives you hints as to what these values should be. You can also administrate database users, view database activity, view database logs. The most useful feature of the Administrator is the ability to backup and restore databases using an easy user interface. You can even go as far to schedule backups, and the program will insert an item into your Windows scheduled tasks that will trigger a database backup at intervals of your choice. There are many commercial applications that charge for this capability.

A screenshot of the database settings feature of MySQL Administrator

A screenshot of the backup feature of MySQL Administrator

The MySQL Query Browser allows the execution of queries and the browsing of database structure of multiple databases at once. You can open up new tabs and have the results of different queries displayed in each one. You can even bookmark a query so it can be executed later. This feature would be perfect for the manual execution of various maintenance queries by database administrators.

You are also allowed to edit data using this tool, and it is done in a very intelligent way. You must go into edit mode before you are allowed to change data, preventing stray clicks from compromising data. When an item is changed, the surrounding box turns red. Your changes are not executed however until you click the apply changes button, adding a way of backing out of your changes easily.

A screenshot of the MySQL Query Browser

The entire MySQL GUI Tools package shares a common connection manager allowing you to keep multiple database’s connection information saved and at your fingertips.

Today during the course of some work, I came across a situation where I had to delete all tables from a MySQL database. Normally when working locally or on a machine where I have full access, I execute the “DROP DATABASE my_data” and then “CREATE DATABASE my_data” statements to completely delete all the tables in it. This works perfectly in this situation, as these statements will also drop all tables, leaving you with a fresh new database.

However, the database account I was issued on this MySQL server did not have the permissions to drop and create a database. In MySQL there is no
“DROP ALL TABLES” statement - this doesn’t exist in MySQL. There were over 100 tables that I had to delete, making manually executing the drop table statment for each table both time consuming and error prone.

To resolve the issue I came up with the following PHP script that will do this operation for you when executed.

Requirements: PHP 4+ and the MySQL extension enabled.

Note:  If you are using views, this script will be unable to drop them along with the tables. If you don’t know what views are, don’t worry about this notice.

 
/* fill in your database name */
$database_name = "my_db";
 
/* connect to MySQL */
if (!$link = mysql_connect("db_host_name", "username", "pass")) {
  die("Could not connect: " . mysql_error());
}
 
/* query all tables */
$sql = "SHOW TABLES FROM $database_name";
if($result = mysql_query($sql)){
  /* add table name to array */
  while($row = mysql_fetch_row($result)){
    $found_tables[]=$row[0];
  }
}
else{
  die("Error, could not list tables. MySQL Error: " . mysql_error());
}
 
/* loop through and drop each table */
foreach($found_tables as $table_name){
  $sql = "DROP TABLE $database_name.$table_name";
  if($result = mysql_query($sql)){
    echo "Success - table $table_name deleted.";
  }
  else{
    echo "Error deleting $table_name. MySQL Error: " . mysql_error() . "";
  }
}

Remember not to leave this script sitting on your server longer than needed, as it is a serious security risk.

Planning on storing IP addresses in MYSQL or any other similar database for your PHP application? If you are like most, you probably were thinking of storing the IP addresses as strings in a varchar(15) row. Well, there actually is a much faster and more efficient way of storing IP addresses. The secret lies in PHP’s ip2long() and long2ip() functions which convert a dotted IP address to and from it’s long integer representation. The IP address can then be stored in the database as an int(11) format. When you need to work with the IP address later, simply convert back to the dotted format using the long2ip() function.

The long format is much faster to query for in MYSQL because you are searching an integer index as opposed to a string index.  Make sure you don’t use “like” in your query or you could lose this speed increase, and make sure you include an index on this column.

Example code in PHP:

$ip_address="212.253.52.1";
$converted_address=ip2long($ip_address);
$sql="SELECT FROM ip_table WHERE ip = $converted_address";
$mysql_query($sql);

In addition to a query speed increase, storing IP addresses as int(11) also has a disk space savings. In my test with 50,000 unique IP addresses, storing them as int(11) used 40% less disk space for the combined data and index than the varchar(15) format. Take note!