RoundCube perfomance bug with contacts above 1000 in MySQL

[rc-user1]

Huge delays at user's login to RoundCube. If user has about of 2000 messages in the mailbox, the delay is 2-3 minutes. Generally 435 records in table “users”, 1400 in table “contacts”, 333 in “contactgroupmembers”, 56 in “contactgroups”.

Switching RoundCube sql queries logging on shows delay at «UPDATE session SET» query:

[08-Feb-2012 16:05:10 +0000]: query(1): SELECT cache_id, data, cache_key FROM cache WHERE user_id = 84 AND cache_key = 'IMAP.messagecount' ORDER BY created DESC LIMIT 0, 1;

[08-Feb-2012 16:05:10 +0000]: query(1): UPDATE cache SET created = now(), data = 'a:5:{s:5:\"INBOX\";a:2:{s:3:\"ALL\";i:4041;s:6:\" UNSEEN\";i:1924;}s:6:\"Drafts\";a:1:{s:6:\"UNSEEN\ ";i:0;}s:4:\"Sent\";a:1:{s:6:\"UNSEEN\";i:0;}s:4:\ "Junk\";a:1:{s:6:\"UNSEEN\";i:0;}s:5:\"Trash\";a:1 :{s:6:\
"UNSEEN\";i:0;}}' WHERE user_id = 84 AND cache_key = 'IMAP.messagecount';

[08-Feb-2012 16:05:10 +0000]: query(1): UPDATE session SET vars='bGFuZ3VhZ2V8czo1OiJydV9SVSI7aW1
<-- TRUNCATED -->
5MjQ7czo2OiJEcmFmdHMiO2k6MDtzOjQ6IlNlbnQiO2k6MDtzO jQ6Ikp1bmsiO2k6MDtzOjU6IlRyYXNoIjtpOjA7fQ==',chang ed='2012-02-08 16:05:10' WHERE sess_id='61eh0naumln6odsn9pvg8mdki4';

[08-Feb-2012 16:06:29 +0000]: query(1): UPDATE cache SET created = now(), data = 'a:1:{s:5:\"INBOX\";a:2:{s:3:\"ALL\";i:4141;s:6:\" UNSEEN\";i:1924;}}' WHERE user_id = 84 AND cache_key = 'IMAP.messagecount';
[08-Feb-2012 16:06:29 +0000]: query(1): SELECT vars, ip, changed FROM session WHERE sess_id = '6
1eh0naumln6odsn9pvg8mdki4';

Also there are no delays after completely cleaning table «contacts».

All RoundCube settings are defaults. IMAP server on the same host. RoundCube version 0.7.1 (latest). MySQL — 5.1.61 PHP (php-fpm) — 5.3.8 Nginx -1.1.13

Is it possible to fix this RoundCube perfomance bug with contacts above 1000 ?

[alec]

Session has nothing to contacts. Check with disabled caching. The time difference doesn't tell that the query is long-running. You can log long-running queries in mysql log. Enable imap_debug, maybe it will show some delays too.

[rc-user1]

Replying to alec:

Session has nothing to contacts. Check with disabled caching. The time difference doesn't tell that the query is long-running. You can log long-running queries in mysql log. Enable imap_debug, maybe it will show some delays too.

Disabling caching changes nothing. Logging long-running queries in mysql enabled -

my.cnf options:

slow_query_log = ON;

slow_query_log_file = /log/mysql-slow.log

long_query_time = 5

And log/mysql-slow.log is empty.

imap_debug log show no delays. Also other webmail application (AfterLogic? Webmail Lite) on same host/database/users/IMAP-server/etc works perfectly, without any perfomance issues.

[alec]

So, this isn't a long-running-query issue. If you find where's the delay, we would try to fix this (if it's Roundcube issue). Do you use any plugins? Disable them.

[rc-user]

Replying to alec:

So, this isn't a long-running-query issue. If you find where's the delay, we would try to fix this (if it's Roundcube issue). Do you use any plugins? Disable them.

If this isn't a long-running-query issue why truncating table "contacts" solves the problem ? I don't use any plugins - RoundCube has default settings.

[alec]

Maybe this is some weird mysql issue. I have no idea. You can try to change engine of session table to MYISAM (I don't really expect it will help). You need to debug the code (with rcube_timer() and rcube_print_time() functions) to find where's the delay.

[rc-user1]

Replying to alec:

Maybe this is some weird mysql issue. I have no idea. You can try to change engine of session table to MYISAM (I don't really expect it will help). You need to debug the code (with rcube_timer() and rcube_print_time() functions) to find where's the delay.

I moved database to PostgreSQL, converted to MyISAM and back to InnoDB - no changes, delays presented. How to use rcube_timer() and rcube_print_time() functions for debug ? There are no such functions in main.inc.php/db.inc.php

[alec]

Any news on this? Did you try with disabled all plugins? Do you observe these delays only on login? How about going to Settings and back to Mail.

[alec]

No feedback. We're unable to reproduce the issue.