I gotta have my orange juice.

Jesu, Juva

Archive for the ‘Performance’ Category

PostgreSQL foreign keys and indexes

with one comment

[PostgreSQL]If you’re a frequent user of MySQL, you may be familiar with the fact that all MySQL table constraints automatically create indexes for you.  This is true of the InnoDB foreign key constraints, for which “an index is created on the referencing table automatically if it does not exist.”

If you’re switching or considering a switch to PostgreSQL, you should be aware that not all PostgreSQL table constraints will automatically create indexes for for you.  In PostgreSQL, a UNIQUE or PRIMARY KEY constraint on one or more fields will implicitly create an index for you.  However, in PostgreSQL a FOREIGN KEY constraint will not automatically create an index for you.

For each of your foreign key constraints, you should evaluate whether you want to create an index.  You may want to do this for optimizing your own queries, but be aware that it can also help to speed up DELETE queries on the referenced table and UPDATE queries on the referenced field.  This is because any foreign key reference must be located to enforce whatever ON DELETE and ON UPDATE behavior is in effect for the constraint.

Written by Scott Moonen

December 19, 2008 at 11:15 am

Automatically minify your Javascript and CSS

with 3 comments

For best performance, it is recommended that you minify the Javascript and CSS that your web application uses.  What this involves is removing all unnecessary whitespace and comments.  So, for example, the following CSS:

body
{
  margin: 5px 10px 10px 10px;
  font-family: arial;
}

would look like this after being minified:

body{margin:5px 10px 10px 10px;font-family:arial;}

And similarly for Javascript. It is common to configure your server to perform GZip compression on files that it serves, including Javascript and CSS, and this can significantly reduce the time that it takes for browsers to load your pages. But minification when used with GZip usually helps to compress the files just a little bit further. And unlike GZip, which only compresses the file only as it is sent over the internet, minification compresses the file as it is seen by a browser. This allows the browser to parse it faster; additionally, smaller files are more likely to be cached by the browser.

It is common to manually minify your Javascript and CSS as part of deploying your application, saving a minified copy on your server either manually or as part of an automatic deployment script. But it is also possible to create custom Apache output filters to perform the minification for you. This gives you the best of both worlds — you can edit your files directly without their being minified, but you don’t have to engineer a minification process for when you deploy your application. Here’s how to do it, first for Javascript and then for CSS.

Javascript

  1. Ensure you have the Apache mod_ext_filter extension installed.

  2. Download the jsmin.py Python script from Douglas Crockford’s website. (There are also other languages available.)  Save it in your Python installation’s site-packages folder (possibly /usr/lib/python2.x/site-packages/).

  3. Add the following lines to your main Apache config file (httpd.conf, apache2.conf, etc.):

    <IfModule mod_ext_filter.c>
      ExtFilterDefine jsmin \
                      mode=output \
                      intype=application/x-javascript \
                      outtype=application/x-javascript \
                      cmd="/usr/bin/python /usr/lib/python2.4/site-packages/jsmin.py"
    </IfModule>
    
  4. Add the following statement to the context where you would like to minify your Javascript files (you can place this in your server config, but also within a virtual host configuration, a directory directive, or even a .htaccess file if FileInfo overrides are allowed):

    AddOutputFilter jsmin js
    

    This will cause all files with extensions ending in .js to be run through the Javascript minify filter before being sent to a browser. If you have some Javascript without the .js extension, you can add additional extensions, or you can use the AddOutputFilterByType directive instead to apply the filter to any content with the application/javascript MIME type. With appropriate mod_expires directives you can cause these files to be cached for a long time by browsers, thereby ensuring that the minify filter is not run more than necessary.

For debugging purposes you should ensure that the minify filter is applied only to your production server and not to your development server. Until you have verified the correctness of your Javascript it will be harder to locate Javascript errors within minified code!

CSS

  1. Ensure you have the Apache mod_ext_filter extension installed, as above.

  2. Install the cssmin Ruby gem:

    gem install cssmin
  3. Add the following lines to your main Apache config file (httpd.conf, apache2.conf, etc.):

    <IfModule mod_ext_filter.c>
      ExtFilterDefine cssmin \
                      mode=output \
                      intype=text/css \
                      outtype=text/css \
                      cmd="/usr/bin/ruby -e 'require \"rubygems\"; require \"cssmin\"; puts CSSMin.minify(STDIN)'"
    </IfModule>
    
  4. Add the following statement to the context where you would like to minify your CSS files (you can place this in your server config, but also within a virtual host configuration, a directory directive, or even a .htaccess file if FileInfo overrides are allowed):

    AddOutputFilter cssmin css
    

    This will cause all files with extensions ending in .css to be run through the CSS minify filter before being sent to a browser. If you have some CSS without the .css extension, you can add additional extensions, or you can use the AddOutputFilterByType directive instead to apply the filter to any content with the text/css MIME type. With appropriate mod_expires directives you can cause these files to be cached for a long time by browsers, thereby ensuring that the minify filter is not run more than necessary.

Written by Scott Moonen

August 17, 2008 at 9:36 pm

A performance comparison of AF_UNIX with loopback on Linux

with 2 comments

[PostgreSQL]On various Linux hosts I use either MySQL or PostgreSQL for my back-end database.  Typically the database is running on the local host, and this raises the question of how to connect to the database.  For both MySQL and PostgreSQL it is possible to connect using a local AF_UNIX connection, or using an AF_INET connection over the loopback socket (address 127.0.0.1).

I had thought that it might actually be more efficient to connect over loopback; for my day job I work on the TCP/IP stack for the z/OS operating system, and I know firsthand that our TCP/IP implementation is heavily optimized, including specific optimizations for local traffic.

I decided to test this hypothesis by comparing both the throughput and latency of AF_UNIX and AF_INET connections.  I was running on a Celeron Dual-core system running Debian 4.0 with kernel version 2.6.18-4-686.  The results disproved my hypothesis, at least for Linux:

  • AF_UNIX:
    • 100GB transferred in 80.575200 s
    • 100 million 8-byte messages:
      • Average latency 14.463910 μs
      • Standard deviation 0.003376 μs
  • AF_INET, loopback address
    • 100GB transferred in 226.717520 s
    • 100 million 8-byte messages:
      • Average latency 1133.444424 μs
      • Standard deviation 0.067419 μs

So, for both throughput and latency, on the Linux platform AF_UNIX is a superior choice to AF_INET.  For local MySQL and PostgreSQL connections you should use a local socket rather than the loopback socket.

Written by Scott Moonen

April 5, 2008 at 6:03 pm