This is not a roundcube specific method, but if you can manage a postgresql restart, you can enable query logging [with duration].

postgresql.conf: log_statement = 'all' log_duration = on

Look here for more logging info: http://www.postgresql.org/docs/current/static/runtime-config-logging.html

This assumes you already have log_destination, log_directory, and log_filename setup.

Begin tailing the log, then login to RC and see what happens. There may be a flood of statements so feel free to copy the log somewhere where you can browse it with an editor or grep/awk. With the duration turned on, you can search/grep for long running queries. When you find one, copy/paste that into the psql command line and execute it. Prefix the query with "explain" to show the query plan. Perhaps it is not using an index as it should, perhaps the index doesn't exist. (Execute \d tablename to describe the table which will show indexes.) Auto-vacuum should be running by default on most modern postgres installs, but perhaps you need to run a "vacuum analyze verbose" (to ensure the query planner choses the indexes) or a "vacuum full" on your RC database.

Finally, check for message caching. Perhaps you had it off in 0.2 and turned on in 0.3?

Good luck.

-gnul

On Wed, Aug 26, 2009 at 2:32 PM, Gavin McCullaghgavin.mccullagh@gcd.ie wrote:

Hi,

On Wed, 26 Aug 2009, Gavin McCullagh wrote:

...

It's possible that our manual upgrade has missed an index somewhere and as a result postgresql is having to work unduly hard.  Does anyone have any suggestions for what I might do to work out what the hold up is?

We still have an old 0.2-beta running alongside and the 100 email pages take 2-4 seconds with it, so it seems like there must be an index missing or something.  Any suggestions what it might be?

Gavin

---

List info: http://lists.roundcube.net/users/

List info: http://lists.roundcube.net/users/

On Aug 26, 2009, at 3:30 PM, Gavin McCullagh wrote:

Hi,

we have a debian lenny server running postgresql 8.3, php 5 and moodle 1.9.5. We have a mail server running cyrus-imapd adjacent to it. For various reasons, we installed roundcube on the moodle server to be a front-end to the cyrus imapd on the other server.

I use PostgreSQL and do not have performance issues, although I don't
have thousands of messages in any mailboxes. I have turned off message caching because it did not increase
performance, although I'm not using the latest RC. ( IMHO, message caching should be done via HTML 5 local storage, but
that's off topic )

Also, when I upgrade I drop the existing database and create a new,
fresh DB. Yeah, this loses some data, but RC isn't the primary access to our
user's e-mail, it is for remote use only.

I did a diff of the PostgreSQL SQL files between 0.2.2 and 0.3RC1 :

chasd$ diff roundcubemail-0.2.2/SQL/postgres.initial.sql
roundcubemail-0.3-RC1/SQL/postgres.initial.sql 0a1,2

-- RoundCube Webmail initial database structure

chasd$ diff roundcubemail-0.2.2/SQL/postgres.update.sql
roundcubemail-0.3-RC1/SQL/postgres.update.sql chasd$

No difference in the schema.

If you have two separate databases for the two versions, you can
compare the output of

pg_dump -s -d <databasename>

from the two databases.

cyrus might also be something to look at. Many on the RC users list have mentioned that dovecot was faster when
they switched from other IMAP servers. I'm not saying you should switch your production environment, but
setting up a test instance of dovecot, copying your mailboxes over,
and pointing a test instance of RC at that dovecot might be an
interesting test. Yeah, and cyrus has features dovecot doesn't have.

On Thu, 27 Aug 2009, chasd wrote:

I use PostgreSQL and do not have performance issues, although I don't
have thousands of messages in any mailboxes.

I'm afraid lots of our users do.

I have turned off message caching because it did not increase
performance, although I'm not using the latest RC.

That seems to have improved us very substantially. This is v0.2.2 so perhaps the RC would be better.

( IMHO, message caching should be done via HTML 5 local storage, but
that's off topic )

It certainly seems excessive for us to use imaproxyd _and_ postgresql for caching.

Also, when I upgrade I drop the existing database and create a new,
fresh DB. Yeah, this loses some data, but RC isn't the primary access to our
user's e-mail, it is for remote use only.

This really isn't acceptable for us. I may just try and transfer the identities and contacts back over.

cyrus might also be something to look at. Many on the RC users list have mentioned that dovecot was faster when
they switched from other IMAP servers.

Cyrus' performance is pretty good, as evidenced by IlohaMail's speed on the same server. If cyrus were that slow, every instance of thunderbird and outlook would be slow too and I wouldn't expect the 100% cpu from postgresql. I'd say it's definitely a database access bottleneck and turning off the caching seems to sort it out.

Yeah, and cyrus has features dovecot doesn't have.

It certainly used to, though I get the impression dovecot has developed quite a bit in recent years.

Gavin

List info: http://lists.roundcube.net/users/

On Thu, 27 Aug 2009, chasd wrote:

I've read where imaproxyd _decreased_ performance in some cases. Is it possible this is one of those cases ? Is there a way to bypass the proxy in your environment for testing
purposes ?

That's interesting. I can switch back and forth with my test version alright.

On a live instance, the trouble is that roundcube appears to create a separate account based on what host it's connecting to. In the case of imaproxyd that's localhost so if you stop using it the hostname changes so new accounts would get created. I guess creative use of /etc/hosts and 127.0.0.1 would allow you to switch.

Gavin

List info: http://lists.roundcube.net/users/