Splitting tables or apps across databases
In order to scale at some point it might make sense to split out some tables or apps which allow it as they might be better off with a different replication methods, etc.
A first attempt we do right now with the activity table. In order to make use of this, the app/table needs to match the following criteria:
No other apps are allowed to have queries directly to the table
No JOINs are performed between this table and any other tables not on this new separate connection
The app needs to support a connection parameter prefix
In case of the activity app the prefix is activity_. If a database config is not specified it falls back to the normal database configuration option for this value:
activity_dbuser falling back to dbuser
activity_dbpassword falling back to dbpassword
activity_dbname falling back to dbname
activity_dbhost falling back to dbhost
activity_dbport falling back to dbport
activity_dbdriveroptions falling back to dbdriveroptions
Initial splitting
For the initial split the affected tables have to be copied over to the new database, in case of the activity app these are:
oc_activity
oc_activity_mq
Enable maintenance mode
Make sure optional database changes are applied:
occ db:convert-mysql-charset
occ db:convert-filecache-bigint
occ db:add-missing-columns
occ db:add-missing-indices
occ db:add-missing-primary-keys
Specify the desired configuration values
Copy the 2 tables to the new database
Disable maintenance mode
Migrations on updates
We will try to avoid migrations on those tables in the future, but it might be necessary at some point. We hope to have a dedicated plan by the time this happens. For now a potential way would be:
Enable maintenance mode
Update as usual
Execute manual queries for schema changes provided by the app authors
Execute manual queries for data changes provided by the app authors
Disable maintenance mode