Tag Archives: PostgreSQL

Improving performance by using tmpfs

Today I analyzed disk reads and writes on a server with iotop and strace and found some interesting possible optimizations.

With iotop you can check which processes are reading and writing from the disks. I always press the o, p and a keys in iotop so that it only shows me processes doing I/O and so that it will show accumulated I/O instead of the bandwidth. With the left and right arrows I select on which columns to sort the list.

Once you have identified the processes wich are doing much I/O, you can check what they are reading or writing with strace, for example
# strace  -f -p $PID  -e trace=open,read,write

(you can leave out read and/or write if this gives too much noise)

This way I identified some locations where processes do lots of read and write operations on temporary files.

For nagios I placed /var/lib/nagios3/spool and /var/cache/nagios3 on a tmpfs, for Amavis /var/lib/amavis/tmp and for PostgreSQL /var/lib/postgresql/8.4/main/pg_stat_tm.

Other candidates you might want to consider: /tmp, /var/tmp and /var/lib/php5. There are probably many others, depending on which services you use.

Flash and Java: the end of obsolete technologies

Already for a long time, I am a total opponent of Flash and things are clearly not improving. Quite on the contrary: Flash is still unstable on my systems, often not working correctly or causing browser hangs and it has many serious security flaws, which are fixed rather slowly by Adobe.

Another piece of technology which is annoying is Java. Java is a serious memory hog, does not integrate very well in Linux distributions (no centralized package management system for the gazillion of Java libraries and frameworks), and Java’s new owner, Oracle, is trying to make Java a patent minefield by suing Google.

Recently, Oracle also decided to publish a paying version of the Java Virtual Machine, and it has also doubled prices for MySQL support.

In the meantime, OpenOffice.org, also in hands of Oracle now, has been forked to LibreOffice. Third-party contributors were dissatisfied with Sun/Oracle’s bureaucracy which was a serious limitation for external contributions. It seems that Sun/Oracle’s behaviour to keep total control of OpenOffice.org is now having the total opposite effect: LibreOffice is gaining wide support by many Linux distributors, which will quickly make OpenOffice.org totally irrelevant.

ironically Apple, another company which I dislike about their anti-competitive behaviour, has become an ally by refusing to install Flash on the iPhone and iPad and by deprecating Java support, which makes it likely that Java will not be included in future OS X versions.

As GNOME developer Colin Walters recently stated on his blog: enough is enough! I am regularly installing computers for other users. From now on:

  • I will install Flasblock browser extensions by default, to help protecting against malicious Flash animations and to send out a signal to web developers that they should stop using Flash.
  • I will not install any Java Runtime Environment or Java Development Kit by default, except if explicitly requested by the user.
  • Instead of OpenOffice.org I will install LibreOffice on all Windows and Mac systems I install. For Linux I will keep the default office suite installed by the distribution, but this will also be LibreOffice in about 6 months when all major Linux distributors have published a new release.
  • I will try to use PostgreSQL instead of MySQL for web applications. Currently I already installed a LimeSurvey instance and a MediaWiki instance with PostgreSQL instead of the more common MySQL and moved a Roundcube instance from MySQL to Postgresql. I expect to install a Drupal 7 instance on PostgreSQL in the future.

As users, we have a lot of power to show companies that we do not accept their behaviour. Let’s use that power now!

Migrating from Microsoft SQL Server to PostgreSQL

One of the servers I manage at work is still running Microsoft Windows 2000. This system is hosting a few old forgotten web sites and it runs Microsoft SQL Server containing a few databases still in use. This server was already there when I started working at the university. Fortunately I never had to do much work on it and there never was a serious crash: it would have been a serious headache for me restoring this system because I do not have much knowledge about Windows and SQL Server.

During some spare time at work I decided taking a look at migrating the MS SQL Server databases to PostgreSQL. And all in all it was not very complicated. Fortunately, the databases itself are not very large and have a rather simple structure. The first thing I did, was recreating the structure of the database in PostgreSQL. With a tool like pgAdmin3 this is pretty straightforward. An alternative is to export the database structure in SQL format and adapt the resulting file so that it contains valid PostgreSQL table creation statements. The latter method is explained on the PostgreSQL wiki.

To migrate the data itself, I wrote a little Perl script. Actually I do not have much knowledge about Perl but it is a fairly straightforward language, so this did not prove to be too difficult. I connect to the MS SQL Server database using DBI, the Perl Database Interface, with the ODBC and FreeTDS drivers. The script gets all records from the tables you define, and inserts them in the PostgreSQL database using the PostgreSQL DBI driver.

These are the required packages on a Debian Squeeze system:

libdbd-odbc-perl
libdbd-pg-perl
tdsodbc
unixodbc
tdsodbc

Define the FreeTDS ODBC driver in /etc/odbcinst.ini:

[FreeTDS]
Description	= TDS driver (Sybase/MS SQL)
Driver		= /usr/lib/odbc/libtdsodbc.so
Setup		= /usr/lib/odbc/libtdsS.so
UsageCount	= 2

Then define the database in odbc.ini:

[database]
Driver = FreeTDS
Trace = No
Server = hostname
Port = 1433
Database = databasename

The script assumes that all field names in the PostgreSQL database are using lowercase names and that the MS SQL database is using ISO8859-15 encoding and the PostgreSQL database is using UTF-8 encoding. For every table, I make a migrateTable call with 3 arguments: the table name, an array containing all field names and the name of the primary key field for which you created a sequence (named fieldname_seq).

Once the database itself was migrated, the applications using the database had to be modified to use PostgreSQL. This was not too difficult, because the applications were PHP scripts using ODBC. These additional packages were needed:

odbc-postgresql
php5-odbc

To configure the PostgreSQL ODBC driver, add this in /etc/odbcinst.ini:

[PostgreSQL Unicode]
Description	= PostgreSQL ODBC driver (Unicode version)
Driver		= /usr/lib/odbc/psqlodbcw.so
Setup		= /usr/lib/odbc/libodbcpsqlS.so
Debug		= 0
CommLog		= 0
UsageCount	= 0

Then define the databse in odbc.ini:

[database]
Driver              = PostgreSQL Unicode
ServerName          = localhost
Database            = dbname
Username            = username
sslmode             = require

Then be sure not to call odbc_connect with the SQL_CUR_USE_ODBC option (which was actually needed with FreeTDS to fix some weird errors), because it causes segfaults.

The result? WISE’s publications pages is now using PostgreSQL as a back-end!