Roundcube is a nice Ajax based webmail, but I was experiencing slow performance. I checked MySQL slow query log and I found queries taking several seconds. Expecially session and cache-update queries were taking as much as 9 seconds. This is really a long time for an interactive and fluid user experience. I’ve checked Roundcube version 0.3.1 and 0.4 and the tables are very similar. My hardware is a 3.0Ghz Xeon quad core server, so you’d expect something more snappy.
At first I checked MySQL configuration values, but I didn’t get the huge performance increase I was looking for. Then I checked the table structure: since where and order by fields where indexed already I was thinking to make the tables in fixed format. Unfortunately those two critical table included a blog field, that is variable length by definition so it’s not possible to achieve fixed length.
So I tried to optimize the fields as I could with the help of the Propose table structure function, but without going too close to the limits suggested, applying common sense and prudence.
The table cache had this structure:
CREATE TABLE cache ( cache_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, cache_key varchar(128) NOT NULL , created datetime NOT NULL DEFAULT '1000-01-01 00:00:00', data longtext NOT NULL, user_id int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(cache_id), CONSTRAINT user_id_fk_cache FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, INDEX created_index (created`), INDEX user_cache_index (user_id,cache_key) )
cache_key is a varchar(128) string, while the maximum length in my db was 17 and it comes from a set of possibilities so it can’t possibly grow longer, except maybe in future version. I decided to set it to char(32), which is a fixed length type and so faster. It also means a smaller key file.
Also we have a data longtext field. A longtext is a data type able to contain up to 4Gb data! The maximum length in my table was actually 1517 bytes only. It’s a bit of overkill to use this type of field, so I changed it to text, a blob type that can hold up to 65535 and is simpler and faster.
I noticed a small speed increase, until I noticed that the table was a InnoDB table. The only reason I can think about to use InnoDB for a cache table is that “ON DELETE CASCADE ON UPDATE CASCADE” statement, since I don’t think there is any code updating user ids (would be crazy), the only use of that is being able to delete cache entry when a user is deleted. This small convenience comes at a high price! MyIsam tables are so much faster. So I removed the foreign key reference and change the table type to MyIsam.
I also noticed that I don’t have cached values older than about two weeks, even if I’m running the system since a few months, so I suppose cache gets cleaned periodically anyway and we don’t even need to clean expired user because will happen in two weeks time anyway.
That was the breakthrough improvement! Query time went under 1 second and didn’t appear in the query log again.
I applied the same concepts to session table and the query time when trying standalone queries went down 0.5s to 0.0005s! In the slow query log I had updates taking as much as 9s, but if they improve a thousand times too, no more query will appear in the query log.
To wrap up I think the performance improvement was amazing, but I will keep the slow query log checked for sometime before declaring this problem solved.
Update: solved the same problem with messages table. It seems there aren’t any slow queries now. :-)
your analysis rocks big time, man !!!
I have applied your changes successfully and I have to say that my roundcube webmail is blazing fast now !!!
so thank you so much to share these very interesting discoveries.
best regards
tias
did you try this on database already running? this changes, did you only do on table cache?
sorry for my bad english
regards
Andres
Hi Andres, yes, after testing, I did it in the live database, with already data in it. Also as you can see from the update at the bottom of the post, I also did the same on the messages table.
Hi,
I know this post is a little bit old but I would like to know if it can improve the performance of Roundcube for me on version 0.7.2.
May I ask how can I know the maximum length of cache_key and data in my DB?
and also may I know the column you have changed in the session and messages table?
Thank you.
cache_key should be 32 chars, data instead can be a text field, i.e. 64k chars, in my particular data set the max length is 1701, but if you’re over 256 chars, you’ve to use a text field anyway.
I am not sure about the revision, but here are my create tables for session and messages, compare them with your table before applying any change of course. Also time your performances, for instance with firebug, before and after any change.
Cheers
CREATE TABLE `messages` (
`message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT ’0′,
`del` tinyint(1) NOT NULL DEFAULT ’0′,
`cache_key` char(32) CHARACTER SET ascii NOT NULL,
`created` datetime NOT NULL DEFAULT ’1000-01-01 00:00:00′,
`idx` int(11) unsigned NOT NULL DEFAULT ’0′,
`uid` int(11) unsigned NOT NULL DEFAULT ’0′,
`subject` varchar(255) NOT NULL,
`from` varchar(255) NOT NULL,
`to` varchar(255) NOT NULL,
`cc` varchar(255) NOT NULL,
`date` datetime NOT NULL DEFAULT ’1000-01-01 00:00:00′,
`size` int(11) unsigned NOT NULL DEFAULT ’0′,
`headers` text NOT NULL,
`structure` text,
PRIMARY KEY (`message_id`),
UNIQUE KEY `uniqueness` (`user_id`,`cache_key`,`uid`),
KEY `created_index` (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1093947 ;
CREATE TABLE `session` (
`sess_id` char(36) NOT NULL,
`created` datetime NOT NULL DEFAULT ’1000-01-01 00:00:00′,
`changed` datetime NOT NULL DEFAULT ’1000-01-01 00:00:00′,
`ip` char(16) NOT NULL,
`vars` text NOT NULL,
PRIMARY KEY (`sess_id`),
KEY `changed_index` (`changed`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Cool! It makes my webmail really a lot faster!
If you found this optimization back in 2010, why isn’t it implemented in the latest version of Roundcube…???
Have you told them about it?
My current RC 0.8.n dB contains these caches so I think yes is the answer:
mysql> describe cache
-> ;
+———–+——————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+——————+——+—–+———————+—————-+
| cache_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| cache_key | varchar(128) | NO | | NULL | |
| created | datetime | NO | MUL | 1000-01-01 00:00:00 | |
| data | longtext | NO | | NULL | |
| user_id | int(10) unsigned | NO | MUL | 0 | |
+———–+——————+——+—–+———————+—————-+
5 rows in set (0.01 sec)
mysql> describe session;
+———+————-+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———————+——-+
| sess_id | varchar(40) | NO | PRI | NULL | |
| created | datetime | NO | | 1000-01-01 00:00:00 | |
| changed | datetime | NO | MUL | 1000-01-01 00:00:00 | |
| ip | varchar(40) | NO | | NULL | |
| vars | mediumtext | NO | | NULL | |
+———+————-+——+—–+———————+——-+
5 rows in set (0.00 sec)
mysql> describe messages;
+————+——————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+————+——————+——+—–+———————+—————-+
| message_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | 0 | |
| del | tinyint(1) | NO | | 0 | |
| cache_key | varchar(128) | NO | | NULL | |
| created | datetime | NO | MUL | 1000-01-01 00:00:00 | |
| idx | int(11) unsigned | NO | | 0 | |
| uid | int(11) unsigned | NO | | 0 | |
| subject | varchar(255) | NO | | NULL | |
| from | varchar(255) | NO | | NULL | |
| to | varchar(255) | NO | | NULL | |
| cc | varchar(255) | NO | | NULL | |
| date | datetime | NO | | 1000-01-01 00:00:00 | |
| size | int(11) unsigned | NO | | 0 | |
| headers | text | NO | | NULL | |
| structure | text | YES | | NULL | |
+————+——————+——+—–+———————+—————-+
15 rows in set (0.00 sec)
The best think would be to drop (or make opional) the InnoDB requirement. My 496Mb VPS suffers greatly with InnoDB.
I’d love to implement these optimizations, but my SQL-fu isn’t strong enough to make them based on what’s written here. Would it be possible to get a script of the requisite changes written in SQL so we can just run them through phpMyAdmin to make these changes? My roundcube is painfully slow.