Quassel IRCFor quite a while now I've been using Quassel IRC as my IRC client of choice. It is somewhat unusual as it is built on a client/core basis - there is a core that runs on a server and is permanently connected to IRC, and a client that connects to the core and lets you interact with IRC. In this way you can connect from anywhere and have access to all the backlog etc. It is still missing some quite major components (such as scripting, dcc, ...), but for now I like it enough to put up with that.

Another difference to most clients is that the backlog (and settings etc.) are held in a database and not in text files. This has both advantages and disadvantages - it is quicker for most operations, but less easy to e.g. search or publish logs (for searching see Quasselsuche. It requires that you run a web server, but works very well.). Quassel supports two database backends - SQLite and PostgreSQL of which the the default is SQLite. SQLite works just fine, but PostgreSQL scales better, so over time (and sooner if you are in a lot of/high traffic channels it can make sense to change. My SQLite database recently started approaching 1 GB, so I thought I'd switch and see how it went.

There is a pretty complete guide to migrating on the Quassel website which I followed. All seemed to go well, and I restarted my core. I immediately ran into a problem - the initial fetching of the backlog on client connect was very slow. Very very slow. It was taking more than seven minutes (to transfer a few thousand messages over a local network) and in the process timing out the IRC connection. I later learnt that this is because fetching the backlog "stalls" the core and so during those seven or more minutes it is not keeping the connection alive. A quick search of the quassel bug tracker located Bug #680: slow sql queries with postgres backend and prepared statements. This indeed seemed to be what I was suffering from. I ran the test query on the bug report:

PREPARE quassel_3 AS SELECT messageid, time, type, flags, sender, message FROM backlog JOIN sender ON backlog.senderid = sender.senderid WHERE bufferid = $1 ORDER BY messageid DESC LIMIT $2;
EXPLAIN ANALYZE EXECUTE quassel_3(13,1000);

As suspected, it was very slow - 14956.635 ms.

I followed the advice given on the bug report, and ran:

ALTER ROLE quassel SET random_page_cost='2';

This helped quite a lot, the query now took 5179.511 ms - still too slow. I now started googling a bit for how to improve PostgreSQL performance, and found that by default the settings are quite conservative. Following the advice on their website I did the following:

Increase the shared_buffers variable to 192 MB - this brought the query down to just under 5 s.

Increase the effective_cache_size variable to 256 MB - this got the query down to just 10 ms.

Success thought I! I restarted the core and connected the client, only to find that it was a slow as ever. Out of desperation I changed the backlog fetching method. I had had it set to "fetch unread", I now set it to "fetch fixed amount". This should have been slower in terms of purely retrieving the messages, as before when doing my tests on how long backlog fetching had taken I had connected the core, marked everything read, then disconnected and immediately reconnected. This should have made sure that there was next to no unread messages to fetch. Imagine my surprise then when with "fetch fixed amount" selected the backlog got fetched within a couple of seconds. I can only imagine that it is the checking how many messages are unread that takes all the time. Either way, I don't consider it fixed, only worked-around.