Speeding up your ownCloud on small systems such as the Raspberry Pi with Sqlite

I had seriously considered to use a raspberry pi system for setting up my ownCloud server. But the old pi had pretty much the same capabilities as my old mobile phone with cyanogen. And the phone has Wifi and some flash space already included. So I opted for the phone. As with most pi installations, the performance was not great. But I found some easy tweaks I haven't seen anywhere else to significantly improve the performance. I'm assuming you're already using the usual tweaks such as opcache(this will usually help more than the following tweaks!) and using cron.php. Please backup your owncloud.db before you start! You will need the sqlite3 tool (sudo apt-get install sqlite3).

In your config.php you can include these settings for more speed:
  'sqlite.journal_mode' => 'WAL', // works with ownCloud 8 and higher
  'filesystem_check_changes' => 0,
The journal setting should improve mostly the speed for writing new information, such as during a calendar sync. Telling not to check the filesystem for changes is useful if you only change files via owncloud, not via external tools. 

Some more settings we will directly insert into the database with the sqlite3 tool:
sqlite3 /var/www/owncloud/data/owncloud.db 'PRAGMA journal_mode=wal; PRAGMA cache_size=5000; PRAGMA default_cache_size=5000;'

This repeats the journal setting. I'm not sure if that's necessary. The cache setting significantly improves the performance. I set it to 5000 because that incorporates the entire database size for me. That will be up to roughly 5 MB of memory. But I think they're well spent. It might be the most important setting, but I haven't found a good way to benchmark this. There are further settings for higher performance, such as temp_store, synchronous=1, but I'm not sure how to make them stick in the database.

Finally, I recommend to add another cron job for a nightly database cleanup:
0 3  *  *   *        nice sqlite3 /var/www/owncloud/data/owncloud.db 'VACUUM'
This cleans up the database every morning at 3 AM. It should all go in one line. I haven't checked whether cron.php does something similar. But I think it doesn't.

Update: For the advanced users, you can patch the code to include these settings to enable all sqlite tuning: Modify the file /var/www/owncloud/lib/private/db/sqlitesessioninit.php to add these lines:

        public function postConnect(ConnectionEventArgs $args) {
                $sensitive = ($this->caseSensitiveLike) ? 'true' : 'false';
                $args->getConnection()->executeUpdate('PRAGMA case_sensitive_like = ' . $sensitive);
                $args->getConnection()->executeUpdate('PRAGMA synchronous = 1 ');
                $args->getConnection()->executeUpdate('PRAGMA journal_mode = wal ');
                $args->getConnection()->executeUpdate('PRAGMA cache_size = 5000 ');
                $args->getConnection()->executeUpdate('PRAGMA temp_store = 2 ');
 * https://www.sqlite.org/pragma.html#pragma_cache_size
 * https://www.sqlite.org/pragma.html#pragma_synchronous
 * https://www.sqlite.org/pragma.html#pragma_temp_store
 * https://www.sqlite.org/tempfiles.html
 * https://www.sqlite.org/inmemorydb.html

Feedback and benchmarks welcome! If you find bugs please note that in the comments. Also please inform the developers you're using these tips by linking to this post in case you report a bug after using these tweaks. It may help with solving a problem.

No comments:

Post a Comment

I appreciate comments. Feel free to write anything you wish. Selected comments and questions will be published.