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. :-)