julio 15, 2010

Linux - PostgreSQL - Performance


Database initialization

One step that can be taken to optimize your PostgreSQL database is to properly initialize it with initdb. If you are using a PostgreSQL server that came with your OS or have installed it from some package system it may have already been initialized. In these cases you are probably better off removing the PostgreSQL data directory and reinitializing it manually.

To do this you need to execute the following command as the postgres user, or whatever user you use to run postgres under.

initdb -D /var/lib/pgsql/data --locale=C

Please note that the directory location is entirely dependent on your system or your specific setup and should be substituted with whatever convention is used in your situation. What this command does is explicitly initialize the PostgreSQL database with a C locale. If you do not specify the C locale it defaults to whatever your system is set to, which is set to 'en_US.UTF8' on many linux distributions. In these cases, queries with "LIKE" will take a performance hit because PostgreSQL will not use the indices as you would expect it to. Setting it to the C locale from the start eliminates this.

From the Locale Support page in the manual:

The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact.
It slows character handling and prevents ordinary indexes from being used by LIKE.
For this reason use locales only if you actually need them.

If for some reason you cannot reinitialize your database you can also drop specific indexes that involve text fields and recreate them using operator classes.

* http://archives.postgresql.org/pgsql-performance/2007-05/msg00117.php

One other option you should consider is what the default encoding for all your databases should be. If your data requires UTF-8 or just plain ASCII characters it might be a wise choice to specify that via the initdb command:

initdb -D /var/lib/pgsql/data --encoding=UTF8 --locale=C
initdb -D /var/lib/pgsql/data --encoding=SQL_ASCII --locale=C

If you do not do this you need to be sure that you specify the correct encoding when you create the database instance for Chado via createdb.

No hay comentarios:

Se ha producido un error en este gadget.