Roundcube: MySQL to SQLite

Here you can find tutorials and notes for server-side maintenance/configuration

Roundcube: MySQL to SQLite

Postby lik » Fri Jul 09, 2010 1:31 pm

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:
Code: Select all

If you later need to convert specific users, run:
Code: Select all
/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:
Code: Select all
mysqldump –add-drop-table roundcube > /root/roundcube.sql

To restore:
Code: Select all
mysql roundcube < /root/roundcube.sql

The result is an individual SQLite database stored in each email account in /home/user/etc/ 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.

Taken from ... or-sqlite/
Posts: 497
Joined: Wed Dec 15, 2010 3:21 am

Return to Server Side Actions


  • Related topics
    Last post