cPanel 11.25 introduces a new feature: The ability to have RoundCube use SQLite instead of MySQL. After benchmarking resource usage and performance, I’ve come to the conclusion that SQLite is definitely the best way to go for Roundcube. Having trouble making the decision? This article may help clear things up for you.

Here are the pro’s for running Roundcube over SQLite:

• SQLite is generally faster than MySQL
• Since the SQLite databases are stored in user accounts, it’s accounted for in the user’s disk space usage
• Roundcube PHP processes run as the user instead of cpanelroundcube, so it’s easier to identify problematic webmail users
• It’s easier to restore Roundcube data from backups on a per-user basis
• The numerous failures of InnoDB no longer “break” or affect the former single Roundcube database

CPanel makes it very clear that this is an elective upgrade, meaning, Roundcube will still run over MySQL by default unless you run a conversion, which they state is a one-way process. It can also only be done on an entire server, not only for specific users. To run a server-wide conversion:

/scripts/convert_roundcube_mysql2sqlite

If you later need to convert specific users, run:

/scripts/convert_roundcube_mysql2sqlite <user>

*I highly recommend backing up your Roundcube database in case something goes amiss. While you can’t convert back to MySQL from SQLite data, you can “roll” back to the previous setup by restoring the Roundcube database and running /usr/local/cpanel/bin/update-roundcube –force. To back up your database:

mysqldump –add-drop-table roundcube > /root/roundcube.sql

To restore:

mysql roundcube < /root/roundcube.sql

The result is an individual SQLite database stored in each email account in /home/user/etc/domain.com/user.rcube.db. The database file for the cPanel account is /home/user/etc/domain.rcube.db.

The conversion process for most servers I tried this on was less than an hour – I’m estimating about 1 minute for every 60-80 users you have on a server. So if you have 1000 people on your server, the conversion could take up to 15 minutes. The speed of your server and its residual activity is also a factor.

Additional Resources:

cPanel 11.25 Release Notes (PDF)

Related posts:

• Manually Upgrading MySQL
• Upgrading or Downgrading MySQL
• Installing an SSL Certificate for MySQL
• Disk Space for MySQL Databases in cPanel Show as 0MB
• Manually Rearranging Accounts