ads' corner

For this month's #PGSQLPhriday, Grant Fritchey asks: What is the one thing you wish you knew while you learn PostgreSQL.

My preferred client for PostgreSQL is psql, and while it is very powerful I like a few features a most:

• \timing
• \watch

Both are internal commands in psql, \timing is there for a very long time, but got improved at some point. \watch came later, but is also there for a couple years now.

Die Unterlagen für das Tutorial beim Perl-Workshop 2009 in Frankfurt/Main am 25.02.2009 sind unter:

http://andreas.scherbaum.la/writings/

zu finden. Folgende Änderungen wurden gegenüber dem Vortrag vorgenommen:

- ein falscher Variablenbezeichner im "Perl" Teil wurde korrigiert

- Beispiele für Nutzung von SPI in Perl Stored Procedures wurden aufgenommen

Viel Spass mit den Unterlagen

Update: Die Unterlagen aller Vorträge gibt es im Wiki: http://conferences.yapceurope.org/gpw2009/wiki?node=Slides

In the last days i monitored the PostgreSQL logfile on my webserver to find slow queries in the Serendipity blog software. I was hunting another bug in the "events" module so i decided it's woth the additional work and maybe i can tweak some settings to speed up the response time a bit.

Now Kris Kennaway did some tests on his own and posted the results to the FreeBSD performance list. The resulting graphs look very similar.

We have a database with production data for a platform and another database with a production database dump for testing queries. We also have a test database but this one has slightly different DDL because it's for developing new features. Now autovacuum is running on this server and keeps vacuuming this test database.
A simple way for stopping autovacuum from even checking this db at all is adding an entry for every table in pg_autovacuum:

INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age)
SELECT oid,false,-1,-1,-1,-1,-1,-1,-1,-1 FROM pg_class WHERE relkind='r';

This adds a stop entry for every table in your database, even for system tables. If you want the autovacuum daemon to just ignore tables from a specific schema, add a "relnamespace=scheme_oid" in the WHERE clause.

Best thing: the collapses for requests per seconds if Mysql has a concurrency greater than 7. Oh, and by the way, they don't state the table type for Mysql so we don't know, if it is the fast and forgetfully MyIsam or the better but slower InnoDB type.

If you experience performance problems in openldap, especially with a back-sql backend, try to deactivate the debugging in openldap.

In particular the debugging mode 128 (access control list processing) needs a lot performance.