What kind of database are you running? If you’ve followed Zammad’s advice and you are running PostgreSQL, you can see where the majority of diskspace is being used. It’ll probably be in the article attachments. You could delete old tickets or spam via the Scheduler, check which ones have unusually big attachments and delete those tickets. You’ll also have to reclaim diskspace in PostgreSQL afterwards though.
Please check the PostgreSQL Wiki on Disk Usage for information on how to see which table contains the most data. It’ll likely be a pg_toast relation, containing the attachments.
For comparison, here is a TEST system, containing barely any data:
zammad=# 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.translations | 18 MB
public.translations_pkey | 3248 kB
public.index_translations_on_source | 1520 kB
public.index_translations_on_locale | 1320 kB
pg_toast.pg_toast_2618 | 480 kB
public.histories | 464 kB
public.users | 456 kB
public.index_active_job_locks_on_active_job_id | 320 kB
public.http_logs | 272 kB
public.delayed_jobs_pkey | 264 kB
public.activity_streams | 264 kB
public.index_active_job_locks_on_lock_key | 248 kB
public.active_job_locks_pkey | 224 kB
public.delayed_jobs_priority | 216 kB
public.channels | 208 kB
public.ticket_articles | 184 kB
public.index_activity_streams_on_permission_id_group_id_created_at | 176 kB
public.settings | 176 kB
pg_toast.pg_toast_2619 | 160 kB
public.sessions | 152 kB
(20 rows)
The following is from one of our PROD systems, with lots of articles, customers and attachments (the pg_toast relation at the top). We’ll probably need to prune this at some point, as the indices are getting a bit large:
zammad=# SELECT nspname || '.' || relname AS "relation",
zammad-# pg_size_pretty(pg_relation_size(C.oid)) AS "size"
zammad-# FROM pg_class C
zammad-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
zammad-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
zammad-# ORDER BY pg_relation_size(C.oid) DESC
zammad-# LIMIT 20;
relation | size
-----------------------------------------------------------------------+---------
pg_toast.pg_toast_17090 | 41 GB
public.histories | 1065 MB
public.ticket_articles | 780 MB
public.index_histories_on_value_to | 528 MB
pg_toast.pg_toast_17090_index | 467 MB
public.index_histories_on_o_id_and_history_object_id_and_related_o_id | 383 MB
public.index_histories_on_value_from | 316 MB
public.index_histories_on_history_attribute_id | 286 MB
pg_toast.pg_toast_17695 | 284 MB
public.index_histories_on_id_from | 283 MB
public.index_histories_on_related_history_object_id | 283 MB
public.index_histories_on_id_to | 282 MB
public.index_histories_on_history_object_id | 281 MB
public.index_histories_on_related_o_id | 278 MB
public.index_histories_on_o_id | 274 MB
public.index_histories_on_created_by_id | 264 MB
public.index_histories_on_history_type_id | 228 MB
public.store_provider_dbs | 223 MB
public.histories_pkey | 205 MB
public.index_histories_on_created_at | 205 MB
(20 rows)
You could migrate the attachments from the database to a directory on disk, for instance on a new disk or dedicated mountpoint. This has both advantages and disadvantages compared to storing the attachments in the database. Read this for more information on storage providers
This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.