Einladung zu Un(konferenz|camp|meetup) und BBQ am 29.6.2012

(We are addressing a local audience near our offices so we are keeping this post in German. Sorry. Basically we want developers and admins in our area to meet up, exchange ideas, and enjoy BBQ and beer.)

<tldr>Wir wollen uns mit Entwicklern und Admins aus Halle und Umgebung austauschen und laden euch deshalb am 29.6.2012 ab 16:00 zu einer kleinen Unkonferenz und einem BBQ ein.</tldr>

Wir bei gocept tauschen uns gern mit anderen Entwicklern und Admins aus.

Leider ist die nächste tolle Konferenz[1] für uns aber noch eine Weile hin und so lange wollen wir nicht warten. Wir möchten auch gern abseits von Konferenzen mit Entwicklern und Admins aus der Umgebung von Halle ins Gespräch kommen um über Themen wie Webentwicklung und DevOps zu reden.

Wir setzen hier zwar hauptsächlich Python und Linux ein, möchten aber auch über den Tellerrand schauen und hoffen, dass es euch auch so geht und ihr euch angesprochen fühlt. Wir stellen uns nichts Formales vor, eher etwas wie ein Open Space. Jeder mit interessanten Fragen und Themen im Gepäck ist willkommen.

Also: meldet euch an, wenn ihr am Freitag, dem 29.6.2012 ab 16 Uhr noch nichts vorhabt und Halle für euch bequem zu erreichen ist. Ein Etherpad gibt es natürlich auch.

Unser Büro befindet sich in der Forsterstraße 29 in Halle.

Der Lohn am Ende des Tages erwartet euch, wie eingangs versprochen, in unserem schönen Garten: BBQ und Bier.

Bis bald!
die gocept-Entwickler und gocept-Admins

[1] Für uns ist das die PyConDE 2012

Sprint report: Deploying Python web applications – platforms and applications

Last week I met Stephan Diehl, Michael Hierweck, Veit Schiele, and Jens Vagelpohl in Berlin for a sprint. Our chosen topic was “Python web application deployment”. In this post I’d like to recap our discussions, gocept’s perspective on those, and the deployment tool “batou” that we have been incubating in the last months.

Continue reading “Sprint report: Deploying Python web applications – platforms and applications”

Don’t stop PostgreSQL’s autovacuum with your application

The problem

Some weeks ago, we received a complaint from a customer about bad PostgreSQL performance for a specific application. I took a look into the database and found strange things going on: the query planner was executing “interesting” query plans, tables were bloated with lots of dead rows (one was 6 times as big as it should be), and so on.

The cause revealed itself when looking at pg_stat_user_tables:

abc-hans=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;
        relname        | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
-----------------------+-------------+-----------------+--------------+------------------
 object_ref            |             |                 |              |
 archived_current      |             |                 |              |
 archived_item         |             |                 |              |
 pgtextindex           |             |                 |              |
 archived_container    |             |                 |              |
 archived_chunk        |             |                 |              |
 commit_lock           |             |                 |              |
 archived_blob_info    |             |                 |              |
 archived_state        |             |                 |              |
 archived_blob_link    |             |                 |              |
 archived_item_deleted |             |                 |              |
 pack_object           |             |                 |              |
 archived_class        |             |                 |              |
 archived_object       |             |                 |              |
 object_state          |             |                 |              |
 object_refs_added     |             |                 |              |
 blob_chunk            |             |                 |              |

Despite of heavy write activity on the database, no table had ever seen autovacuum or autoanalyze. But why?

As I delved into it, I noticed that PostgreSQL’s autovacuum/autoanalyze was practically stopped in two ways by the application. I’d like to share our findings to help other programmers not to get trapped in situations like this.

Unfinished transactions

It turned out that the application had one component which connected to the database and opened a transaction right after startup, but never finished that transaction:

abc-hans=# SELECT procpid, current_timestamp - xact_start AS xact_runtime, current_query
FROM pg_stat_activity ORDER BY xact_start;
 procpid |  xact_runtime   | current_query

---------+-----------------+-----------------------
   18915 | 11:46:20.8783   | <IDLE> in transaction
   21289 | 11:18:20.07042  | <IDLE> in transaction

Note that the database server was started about 11 ¾ hours ago in this example. Vacuuming (whether automatic or manual) stops at the oldest transaction id that is still in use. Otherwise it would be vacuuming active transactions, which is not sensible at all. In our example, vacuuming is stopped right away since the oldest running transaction is only one minute older than the running server instance. At least this is easy to resolve: we got the developers to fix the application. Now it finishes every transaction in a sensible amount of time with either COMMIT or ABORT.

Exclusive table locks

Unfortunately, this was not all of it: autovacuum was working now but quite sporadically. A little bit of research revealed that autovacuum will abort if it is not able to obtain a table lock within one second – and guess what: the application made quite heavy use of table locks. We found a hint that something suspicious is going on in the PostgreSQL log:

postgres[13251]: [40-1] user=,db= ERROR:  canceling autovacuum task

Searching the application source brought up several places where table locks were used. Example:

stmt = """
LOCK %(table)s IN EXCLUSIVE MODE;
DELETE FROM %(table)s WHERE docid = %%s;
INSERT INTO %(table)s (docid, coefficient, marker, text_vector)
VALUES (%%s, %%s, %%s, %(clause)s)
""" % {'table': self.table, 'clause': clause}

The textindex code was particularly problematic as it dealt often with large documents. Statements like the one above could easily place load on the database server high enough to cause frequent autovacuum aborts.

The developers said that they have introduced the locks because of concurrency issues. As we could not get rid of them, I have installed a nightly cron job to force-vacuum the database. PostgreSQL has shown much improved query responses since then. Some queries’ completion times even improved by a factor of 10. I’ve been told that in the meantime they have found a way to remove the locks so the cron job is not necessary anymore.

Summary

PostgreSQL shows good auto-tuning and is a pretty low-maintenance database server if you allow it to perform its autovacuum/autoanalyze tasks regularly. We have seen that application programs may put autovacuum effectively out of business. In this particular case, unfinished transactions and extensive use of table locks were the show-stoppers. After we have identified and removed these causes, our PostgreSQL database is running smoothly again.

We are currently in the process of integrating some of the most obvious signs of trouble into the standard database monitoring on our managed hosting platform to catch those problems quickly as they show up.

Custom widgets in zope.formlib

zope.formlib has the ability to customize the used widget like this:

class KeywordsManagementForm(five.formlib.formbase.SubPageForm):
    form_fields = zope.formlib.form.Fields(IKeywords)
    form_fields['keywords'].custom_widget = KWSelectWidgetFactory

I do not like this approach for two reasons:

  • the widget has to be set manually every time the specific field is used
  • there is no easy way to get a display widget if the form or field is not editable for the user

Defining a new schema field and registering the widget for this field seems a bit heavy, so I came up with providing a marker interface on the field:

class IHaveSelectableKeywords(zope.interface.Interface):
    """Marker interface to get a special keywords widget."""

class IKeywords(zope.interface.Interface):
    keywords = zope.schema.List(
        title = _("Edit Keywords"),
        value_type = zope.schema.Choice(
            vocabulary=u"uc.keywords.Keywords"))
    zope.interface.alsoProvides(keywords, IHaveSelectableKeywords)

I registered the edit widget and display widget for the IHaveSelectableKeywords interface, so the custom widget does not have to be set in the form like this (edit widget):

<adapter
   for=".IHaveSelectableKeywords
        zope.publisher.interfaces.browser.IBrowserRequest"
 provides="zope.app.form.browser.interfaces.ISimpleInputWidget"
 factory=".KWSelectWidgetFactory"
 permission="zope.Public" />