Opened 9 months ago

Closed 7 months ago

#13585 closed Bug/Something is broken (fixed)

complete nextcloud upgrade to 13.1

Reported by: https://id.mayfirst.org/jamie Owned by:
Priority: Urgent Component: Tech
Keywords: nextcloud Cc:
Sensitive: no

Description

Unfortunately, I did not complete the nextcloud upgrade to 13.1

I did complete the upgrade of the underlying server to Debian stretch.

However, I chose not to continue the upgrade after I noticed that the postgres database seemed to mushroom in size after the stretch upgrade compared with the backup prior to the upgrade (/root/upgrades/backups/nextcloud.pre.2018.03.28.bloated.backup.sql compared with /var/local/db-backup/postgres/lucius-all.sql.gz).

It seemed similar to #12863.

Also I noticed that it takes a long time for the login to be processed since the stretch upgrade.

Some steps I took:

  • Deleted the grauphel tables (See #10305 - it has been de-commissioned)
  • I also noticed that the oc_activity tables has millions of records - so I set the 'activity_expire_days' to 90 days as suggested here

I also tried to upgrade the database cluster from 9.4 to 9.6 but ran out of space on the database partition.

Next steps:

  • Drop the owncloud database (still there since we switched to nextcloud)
  • Ensure oc_activity table has been reduced
  • Try to understand why the pg dump has grown so huge

Change History (7)

comment:1 Changed 9 months ago by https://id.mayfirst.org/jamie

For reference:

nextcloud=> SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;
            relation            |  size   
--------------------------------+---------
 public.oc_activity             | 6302 MB
 public.activity_filter_by      | 1937 MB
 public.activity_filter_app     | 1759 MB
 public.activity_user_time      | 1457 MB
 public.activity_object         | 761 MB
 public.activity_time           | 516 MB
 public.oc_activity_pkey        | 442 MB
 public.oc_filecache            | 432 MB
 public.fs_storage_size         | 154 MB
 public.fs_parent_name_hash     | 149 MB
 public.fs_storage_path_hash    | 129 MB
 public.fs_storage_mimepart     | 112 MB
 public.fs_storage_mimetype     | 108 MB
 public.oc_filecache_pkey       | 52 MB
 pg_toast.pg_toast_280193       | 38 MB
 public.oc_calendarobjects      | 27 MB
 public.oc_documents_op         | 23 MB
 public.oc_addressbookchanges   | 18 MB
 public.oc_bruteforce_attempts  | 15 MB
 public.addressbookid_synctoken | 9248 kB
(20 rows)

nextcloud=> SELECT COUNT(*) FROM oc_activity;
  count   
----------
 20526354
(1 row)

nextcloud=>

comment:2 Changed 9 months ago by https://id.mayfirst.org/jamie

The reason for the bloat seems to be the oc_activity table:

0 lucius:~/upgrades/backups# grep -n "^COPY " nextcloud.pre.2018.03.28.bloated.backup.sql
7635:COPY oc_accounts (uid, data) FROM stdin;
8645:COPY oc_activity (activity_id, "timestamp", priority, type, "user", affecteduser, app, subject, subjectparams, message, messageparams, file, link, object_type, object_id) FROM stdin;
20534924:COPY oc_activity_mq (mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams) FROM stdin;
20536360:COPY oc_addressbookchanges (id, uri, synctoken, addressbookid, operation) FROM stdin;
20762692:COPY oc_addressbooks (id, principaluri, displayname, uri, description, synctoken) FROM stdin;
20763479:COPY oc_admin_sections (id, class, priority) FROM stdin;
20763494:COPY oc_admin_settings (id, class, section, priority) FROM stdin;
20763524:COPY oc_appconfig (appid, configkey, configvalue) FROM stdin;
20763775:COPY oc_authtoken (id, uid, login_name, password, name, token, type, last_activity, last_check, remember, scope) FROM stdin;
20765081:COPY oc_bookmarks (id, url, title, user_id, description, public, added, lastmodified, clickcount) FROM stdin;
20765593:COPY oc_bookmarks_tags (bookmark_id, tag) FROM stdin;
20766441:COPY oc_bruteforce_attempts (id, action, occurred, ip, subnet, metadata) FROM stdin;
20911806:COPY oc_calendarchanges (id, uri, synctoken, calendarid, operation) FROM stdin;
21038154:COPY oc_calendarobjects (id, calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence, uid, classification) FROM stdin;
21064167:COPY oc_calendarobjects_props (id, calendarid, objectid, name, parameter, value) FROM stdin;
21146625:COPY oc_calendars (id, principaluri, displayname, uri, synctoken, description, calendarorder, calendarcolor, timezone, components, transparent) FROM stdin;
21148344:COPY oc_calendarsubscriptions (id, uri, principaluri, source, displayname, refreshrate, calendarorder, calendarcolor, striptodos, stripalarms, stripattachments, lastmodified) FROM stdin;
21148366:COPY oc_cards (id, addressbookid, carddata, uri, lastmodified, etag, size) FROM stdin;
21159784:COPY oc_cards_properties (id, addressbookid, cardid, name, value, preferred) FROM stdin;
21213780:COPY oc_circles_circles (id, unique_id, name, description, settings, type, creation) FROM stdin;
21213843:COPY oc_circles_groups (circle_id, group_id, level, note, joined) FROM stdin;
21213851:COPY oc_circles_links (id, status, circle_id, unique_id, address, token, key, creation) FROM stdin;
21213866:COPY oc_circles_members (circle_id, user_id, level, status, note, joined) FROM stdin;
21214465:COPY oc_circles_shares (id, unique_id, circle_id, source, type, author, cloud_id, headers, payload, creation) FROM stdin;
21214480:COPY oc_comments (id, parent_id, topmost_parent_id, children_count, actor_type, actor_id, message, verb, creation_timestamp, latest_child_timestamp, object_type, object_id) FROM stdin;
21214507:COPY oc_comments_read_markers (user_id, marker_datetime, object_type, object_id) FROM stdin;
21214526:COPY oc_credentials ("user", identifier, credentials) FROM stdin;
21214534:COPY oc_dav_shares (id, principaluri, type, access, resourceid, publicuri) FROM stdin;
21215135:COPY oc_documents_invite (es_id, uid, status, sent_on) FROM stdin;
21215143:COPY oc_documents_member (member_id, es_id, uid, color, last_activity, status, is_guest, token) FROM stdin;
21215952:COPY oc_documents_op (seq, es_id, member, opspec, optype) FROM stdin;
21332953:COPY oc_documents_revisions (es_id, seq_head, member_id, file_id, save_hash) FROM stdin;
21332961:COPY oc_documents_session (es_id, genesis_url, genesis_hash, file_id, owner) FROM stdin;
21333252:COPY oc_federated_reshares (share_id, remote_id) FROM stdin;
21333260:COPY oc_file_locks (id, lock, key, ttl) FROM stdin;
21333275:COPY oc_filecache (fileid, storage, path, path_hash, parent, name, mimetype, mimepart, size, mtime, encrypted, unencrypted_size, etag, storage_mtime, permissions, checksum) FROM stdin;
22602977:COPY oc_files_trash (id, "user", "timestamp", location, type, mime, auto_id) FROM stdin;
22646641:COPY oc_flow_checks (id, class, operator, value, hash) FROM stdin;
22646656:COPY oc_flow_operations (id, class, name, checks, operation) FROM stdin;
22646671:COPY oc_grauphel_notes (note_id, note_user, note_guid, note_last_sync_revision, note_create_date, note_last_change_date, note_last_metadata_change_date, note_title, note_content, note_content_version, note_open_on_startup, note_pinned, note_tags) FROM stdin;
22660454:COPY oc_grauphel_oauth_tokens (token_id, token_user, token_type, token_key, token_secret, token_verifier, token_callback, token_client, token_lastuse) FROM stdin;
22660505:COPY oc_grauphel_syncdata (syncdata_id, syncdata_user, syncdata_current_sync_guid, syncdata_latest_sync_revision) FROM stdin;
22660637:COPY oc_group_admin (gid, uid) FROM stdin;
22660649:COPY oc_group_user (gid, uid) FROM stdin;
22661035:COPY oc_groups (gid) FROM stdin;
22661049:COPY oc_jobs (id, class, argument, last_run, last_checked, reserved_at, execution_duration) FROM stdin;
22661080:COPY oc_lucene_status (fileid, status) FROM stdin;
22661168:COPY oc_mimetypes (id, mimetype) FROM stdin;
22661316:COPY oc_mounts (id, storage_id, root_id, user_id, mount_point, mount_id) FROM stdin;
22665727:COPY oc_notifications (notification_id, app, "user", "timestamp", object_type, object_id, subject, subject_parameters, message, message_parameters, link, actions, icon) FROM stdin;
22666000:COPY oc_notifications_pushtokens (uid, token, deviceidentifier, devicepublickey, devicepublickeyhash, pushtokenhash, proxyserver) FROM stdin;
22666024:COPY oc_oauth2_access_tokens (id, token_id, client_id, hashed_code, encrypted_token) FROM stdin;
22666039:COPY oc_oauth2_clients (id, name, redirect_uri, client_identifier, secret) FROM stdin;
22668483:COPY oc_preferences (userid, appid, configkey, configvalue) FROM stdin;
22675680:COPY oc_privatedata (keyid, "user", app, key, value) FROM stdin;
22675695:COPY oc_properties (userid, propertypath, propertyname, propertyvalue, id) FROM stdin;
22676514:COPY oc_richdocuments_member (member_id, uid, color, last_activity, is_guest, token, status) FROM stdin;
22676529:COPY oc_richdocuments_wopi (id, owner_uid, editor_uid, fileid, version, canwrite, server_host, token, expiry) FROM stdin;
22689456:COPY oc_schedulingobjects (id, principaluri, calendardata, uri, lastmodified, etag, size) FROM stdin;
22689489:COPY oc_share (id, share_type, share_with, uid_owner, parent, item_type, item_source, item_target, file_source, file_target, permissions, stime, accepted, expiration, token, mail_send, uid_initiator, password, share_name) FROM stdin;
22692842:COPY oc_share_external (id, remote, share_token, password, name, owner, "user", mountpoint, mountpoint_hash, remote_id, accepted) FROM stdin;
22692911:COPY oc_storages (id, numeric_id, available, last_checked) FROM stdin;
22694721:COPY oc_systemtag (id, name, visibility, editable) FROM stdin;
22694734:COPY oc_systemtag_group (systemtagid, gid) FROM stdin;
22694749:COPY oc_systemtag_object_mapping (objectid, objecttype, systemtagid) FROM stdin;
22694767:COPY oc_trusted_servers (id, url, url_hash, token, shared_secret, status, sync_token) FROM stdin;
22694782:COPY oc_twofactor_backupcodes (id, user_id, code, used) FROM stdin;
22694797:COPY oc_users (uid, displayname, password) FROM stdin;
22694806:COPY oc_users_external (backend, uid, displayname) FROM stdin;
22695804:COPY oc_vcategory (id, uid, type, category) FROM stdin;
22696422:COPY oc_vcategory_to_object (objid, categoryid, type) FROM stdin;
0 lucius:~/upgrades/backups#

comment:3 Changed 9 months ago by https://id.mayfirst.org/jamie

Here's a comparison with the backup file right when we switched to nextcloud. oc_activity has always been big, just not this big. Also oc_filecache is huge.

0 lucius:~/upgrades/backups# grep -n "^COPY " nextcloud.pre.2017.08.18.backup.sql 
7635:COPY oc_accounts (uid, data) FROM stdin;
8456:COPY oc_activity (activity_id, "timestamp", priority, type, "user", affecteduser, app, subject, subjectparams, message, messageparams, file, link, object_type, object_id) FROM stdin;
240873:COPY oc_activity_mq (mail_id, amq_timestamp, amq_latest_send, amq_type, amq_affecteduser, amq_appid, amq_subject, amq_subjectparams) FROM stdin;
240926:COPY oc_addressbookchanges (id, uri, synctoken, addressbookid, operation) FROM stdin;
263471:COPY oc_addressbooks (id, principaluri, displayname, uri, description, synctoken) FROM stdin;
264031:COPY oc_admin_sections (id, class, priority) FROM stdin;
264046:COPY oc_admin_settings (id, class, section, priority) FROM stdin;
264076:COPY oc_appconfig (appid, configkey, configvalue) FROM stdin;
264320:COPY oc_authtoken (id, uid, login_name, password, name, token, type, last_activity, last_check, remember, scope) FROM stdin;
265813:COPY oc_bookmarks (id, url, title, user_id, description, public, added, lastmodified, clickcount) FROM stdin;
266310:COPY oc_bookmarks_tags (bookmark_id, tag) FROM stdin;
267129:COPY oc_bruteforce_attempts (id, action, occurred, ip, subnet, metadata) FROM stdin;
295815:COPY oc_calendarchanges (id, uri, synctoken, calendarid, operation) FROM stdin;
322812:COPY oc_calendarobjects (id, calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence, uid, classification) FROM stdin;
340869:COPY oc_calendarobjects_props (id, calendarid, objectid, name, parameter, value) FROM stdin;
403846:COPY oc_calendars (id, principaluri, displayname, uri, synctoken, description, calendarorder, calendarcolor, timezone, components, transparent) FROM stdin;
405304:COPY oc_calendarsubscriptions (id, uri, principaluri, source, displayname, refreshrate, calendarorder, calendarcolor, striptodos, stripalarms, stripattachments, lastmodified) FROM stdin;
405320:COPY oc_cards (id, addressbookid, carddata, uri, lastmodified, etag, size) FROM stdin;
420381:COPY oc_cards_properties (id, addressbookid, cardid, name, value, preferred) FROM stdin;
495619:COPY oc_circles_circles (id, unique_id, name, description, settings, type, creation) FROM stdin;
495637:COPY oc_circles_groups (circle_id, group_id, level, note, joined) FROM stdin;
495645:COPY oc_circles_links (id, status, circle_id, unique_id, address, token, key, creation) FROM stdin;
495660:COPY oc_circles_members (circle_id, user_id, level, status, note, joined) FROM stdin;
495678:COPY oc_circles_shares (id, unique_id, circle_id, source, type, author, cloud_id, headers, payload, creation) FROM stdin;
495693:COPY oc_comments (id, parent_id, topmost_parent_id, children_count, actor_type, actor_id, message, verb, creation_timestamp, latest_child_timestamp, object_type, object_id) FROM stdin;
495709:COPY oc_comments_read_markers (user_id, marker_datetime, object_type, object_id) FROM stdin;
495718:COPY oc_credentials ("user", identifier, credentials) FROM stdin;
495726:COPY oc_dav_shares (id, principaluri, type, access, resourceid, publicuri) FROM stdin;
496190:COPY oc_documents_invite (es_id, uid, status, sent_on) FROM stdin;
496198:COPY oc_documents_member (member_id, es_id, uid, color, last_activity, status, is_guest, token) FROM stdin;
497007:COPY oc_documents_op (seq, es_id, member, opspec, optype) FROM stdin;
614008:COPY oc_documents_revisions (es_id, seq_head, member_id, file_id, save_hash) FROM stdin;
614016:COPY oc_documents_session (es_id, genesis_url, genesis_hash, file_id, owner) FROM stdin;
614307:COPY oc_federated_reshares (share_id, remote_id) FROM stdin;
614315:COPY oc_file_locks (id, lock, key, ttl) FROM stdin;
614330:COPY oc_filecache (fileid, storage, path, path_hash, parent, name, mimetype, mimepart, size, mtime, encrypted, unencrypted_size, etag, storage_mtime, permissions, checksum) FROM stdin;
1820996:COPY oc_files_trash (id, "user", "timestamp", location, type, mime, auto_id) FROM stdin;
1862212:COPY oc_flow_checks (id, class, operator, value, hash) FROM stdin;
1862227:COPY oc_flow_operations (id, class, name, checks, operation) FROM stdin;
1862242:COPY oc_grauphel_notes (note_id, note_user, note_guid, note_last_sync_revision, note_create_date, note_last_change_date, note_last_metadata_change_date, note_title, note_content, note_content_version, note_open_on_startup, note_pinned, note_tags) FROM stdin;
1876025:COPY oc_grauphel_oauth_tokens (token_id, token_user, token_type, token_key, token_secret, token_verifier, token_callback, token_client, token_lastuse) FROM stdin;
1876076:COPY oc_grauphel_syncdata (syncdata_id, syncdata_user, syncdata_current_sync_guid, syncdata_latest_sync_revision) FROM stdin;
1876208:COPY oc_group_admin (gid, uid) FROM stdin;
1876220:COPY oc_group_user (gid, uid) FROM stdin;
1876601:COPY oc_groups (gid) FROM stdin;
1876615:COPY oc_jobs (id, class, argument, last_run, last_checked, reserved_at, execution_duration) FROM stdin;
1876649:COPY oc_lucene_status (fileid, status) FROM stdin;
1876737:COPY oc_mimetypes (id, mimetype) FROM stdin;
1876881:COPY oc_mounts (id, storage_id, root_id, user_id, mount_point, mount_id) FROM stdin;
1879755:COPY oc_notifications (notification_id, app, "user", "timestamp", object_type, object_id, subject, subject_parameters, message, message_parameters, link, actions, icon) FROM stdin;
1879786:COPY oc_notifications_pushtokens (uid, token, deviceidentifier, devicepublickey, devicepublickeyhash, pushtokenhash, proxyserver) FROM stdin;
1879794:COPY oc_oauth2_access_tokens (id, token_id, client_id, hashed_code, encrypted_token) FROM stdin;
1879809:COPY oc_oauth2_clients (id, name, redirect_uri, client_identifier, secret) FROM stdin;
1882253:COPY oc_preferences (userid, appid, configkey, configvalue) FROM stdin;
1887164:COPY oc_privatedata (keyid, "user", app, key, value) FROM stdin;
1887179:COPY oc_properties (userid, propertypath, propertyname, propertyvalue, id) FROM stdin;
1887349:COPY oc_richdocuments_member (member_id, uid, color, last_activity, is_guest, token, status) FROM stdin;
1887364:COPY oc_richdocuments_wopi (id, owner_uid, editor_uid, fileid, version, canwrite, server_host, token, expiry) FROM stdin;
1887613:COPY oc_schedulingobjects (id, principaluri, calendardata, uri, lastmodified, etag, size) FROM stdin;
1887628:COPY oc_share (id, share_type, share_with, uid_owner, parent, item_type, item_source, item_target, file_source, file_target, permissions, stime, accepted, expiration, token, mail_send, uid_initiator, password, share_name) FROM stdin;
1890057:COPY oc_share_external (id, remote, share_token, password, name, owner, "user", mountpoint, mountpoint_hash, remote_id, accepted) FROM stdin;
1890111:COPY oc_storages (id, numeric_id, available, last_checked) FROM stdin;
1891708:COPY oc_systemtag (id, name, visibility, editable) FROM stdin;
1891718:COPY oc_systemtag_group (systemtagid, gid) FROM stdin;
1891733:COPY oc_systemtag_object_mapping (objectid, objecttype, systemtagid) FROM stdin;
1891742:COPY oc_trusted_servers (id, url, url_hash, token, shared_secret, status, sync_token) FROM stdin;
1891757:COPY oc_twofactor_backupcodes (id, user_id, code, used) FROM stdin;
1891772:COPY oc_users (uid, displayname, password) FROM stdin;
1891781:COPY oc_users_external (backend, uid, displayname) FROM stdin;
1892600:COPY oc_vcategory (id, uid, type, category) FROM stdin;
1896426:COPY oc_vcategory_to_object (objid, categoryid, type) FROM stdin;
0 lucius:~/upgrades/backups#

comment:4 Changed 9 months ago by https://id.mayfirst.org/jamie

And lastly, the reason our backup file (/var/local/db-backup/postgres/lucius-all.sql.gz) is so much smaller is because the gzip compression is very efficient - the backup has the same number of lines when uncompressed.

comment:5 Changed 9 months ago by https://id.mayfirst.org/jamie

Now that I've added: 'activity_expire_days' and set it to 90 days (from default 365) we should see the number of records in the oc_activity go down a lot. Our cron job runs every 15 minutes, but this setting seems to run once every 24 hours. So, some time between now and tonight, this will kick in and probably tie up the database for a while.

comment:6 Changed 9 months ago by https://id.mayfirst.org/jamie

I just dropped the old owncloud database. I think we are ready to attempt the upgrade again.

Steps to take:

  • On host, increase the SSD disk space from 20 GB to 50GB (we need the extra space to upgrade the postgres database)
  • Restart host and resize the file system, etc.
  • Follow directions in #11583 to upgrade to most recent cluster.
  • Follow regular upgrade directions.

comment:7 Changed 7 months ago by https://id.mayfirst.org/jamie

  • Resolution set to fixed
  • Status changed from new to closed

The has been completed.

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.