Roundcube: MySQL or SQLite?

Rate this post

Update: As of cPanel 11.4x, Roundcube only supports SQLite.

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 trou

ble 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)

Don't be selfish, share!Tweet about this on TwitterShare on RedditShare on TumblrBuffer this pageDigg thisShare on FacebookFlattr the authorEmail this to someoneShare on Google+Pin on PinterestPrint this pageShare on LinkedInShare on StumbleUpon

3 Comments

  1. Pingback: using sqlite for roundcube | whatever...

  2. am3n Reply

    Hi Vanessa,
    does this convert only happen to existing cpanel’s account on the server…

    OR

    if next the time we add another cpanel’s account, the new account will be automatically uses SQLite

    1. Vanessa Reply

      It should do all existing users and new users automatically. I have seen in the past though that restoring users from other servers where RC was using MySQL caused an RC database to be recreated. Not sure if this was addressed by cPanel or not.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log in