Oplossing (Samenvatting)
Details zijn hieronder uitgewerkt.
-
Verwijder onnodige bestanden:
- Verwijder logbestanden en andere bestanden die niet meer nodig zijn.
-
Optimaliseer de database:
REINDEX TABLE job_jobarchive;Als alternatief kun je de index verwijderen:
DROP INDEX [index_name] ON job_jobarchive; -
Opschonen en analyseren:
VACUUM ANALYZE job_jobarchive; -
Gebruik
pg_repack:In de shell, als gebruiker
postgres, voer het volgende uit om de tabel te herstructureren:pg_repack -d dbname -t job_jobarchive
Terwijl
VACUUM FULL job_jobarchive;
Werkt niet omdat er een foutmelding optreedt.
user_de=# VACUUM FULL job_jobarchive;
ERROR: could not extend file "base/98628770/1388599947.114": wrote only 4096 of 8192 bytes at block 14990040
HINT: Check free disk space.
Dus we waren een aantal dagen aan het experimenteren en zijn gegaan van
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 892K 13G 1% /run
/dev/md2 934G 886G 381M 100% /
tmpfs 63G 0 63G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 4.0M 0 4.0M 0% /sys/fs/cgroup
/dev/nvme1n1 938G 733G 158G 83% /media/backup
/dev/md1 487M 82M 381M 18% /boot
naar
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 1.1M 13G 1% /run
/dev/md2 934G 231G 656G 27% /
tmpfs 63G 6.2M 63G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 4.0M 0 4.0M 0% /sys/fs/cgroup
/dev/nvme1n1 938G 733G 158G 83% /media/backup
/dev/md1 487M 82M 381M 18% /boot
tmpfs 13G 0 13G 0% /run/user/0
Dat ging erg goed.
Maar wat heb ik precies gedaan, en wat moet ik nog automatiseren om dit in de toekomst te voorkomen?
service pgpool2 status
Inloggen
psql -h localhost -U username -d dbname
Verwijder alle records die zijn gesloten, offline zijn gezet en geen contactgegevens van bedrijven bevatten. Deze worden via XML door een derde partij gepubliceerd en vervolgens gearchiveerd.
DELETE
FROM job_jobarchive
WHERE (email IS NULL OR email = '') AND for_index = FALSE;
SELECT COUNT(*)
FROM job_jobarchive
WHERE (email IS NULL OR email = '') AND for_index = FALSE;
SELECT COUNT(*)
FROM job_jobarchive
WHERE for_index = FALSE;
SELECT COUNT(id)
FROM job_jobarchive
WHERE payed = FALSE;
De logica is dat we een query uitvoeren waarbij alle clicks en views worden geüpdatet voor gearchiveerde jobs op basis van hun jobID, zodat...
SELECT COUNT(*)
FROM job_jobarchive
WHERE payed = TRUE;
Toon alle jobs in het archief die ooit direct of indirect betaald zijn aangeklikt en in het verleden verkeer genereerden toen ze nog open waren.
user_de=> SELECT COUNT(*)
user-> FROM job_jobarchive
user-> WHERE payed = TRUE;
count
---------
7410945
(1 row)
Met deze query kan ik alle jobs in het archief identificeren die ooit verkeer hebben gegenereerd. Alleen de aangeduide jobs blijven behouden voor beveiligings- en statistische doeleinden, terwijl de rest wordt verwijderd."
UPDATE job_jobarchive
SET payed = TRUE
WHERE job_id IN (SELECT id FROM viewjob_viewjob);
Ja, inderdaad, een kleine verwijdering was niet voldoende. Ik moest niet alleen logs en dergelijke verwijderen, maar ook meer grondig te werk gaan.
ERROR: could not extend file "base/98628770/143848844.118": wrote only 4096 of 8192 bytes at block 15532936
HINT: Check free disk space.
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 892K 13G 1% /run
/dev/md2 934G 886G 381M 100% /
tmpfs 63G 0 63G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 4.0M 0 4.0M 0% /sys/fs/cgroup
/dev/nvme1n1 938G 733G 158G 83% /media/backup
/dev/md1 487M 82M 381M 18% /boot
Verwijderen laste 7 dagen.
DELETE FROM job_jobarchive
WHERE added >= date_trunc('week', current_date)
AND added < date_trunc('week', current_date) + interval '7 days';
pg_repack -d dbname -t job_jobarchive
Het probleem is dat de database in een staat verkeert waarin VACUUM niet meer kan worden uitgevoerd. Daardoor levert het verwijderen van data geen vrijgekomen ruimte op in de database.
Ik moet dus op de een of andere manier alsnog VACUUM uitvoeren.
Mijn plan is om eerst Elastic te stoppen en de archive-index te verwijderen, aangezien deze 87 GB in beslag neemt. In deze configuratie draaien naast de databaseservers ook Elastic-instanties voor betere prestaties en horizontale schaalbaarheid.
curl -X GET "http://138.201.8.xxx:9200/_cat/indices?v"
root@Ubuntu-2010-groovy-64-minimal /var # curl -X DELETE "http://138.201.8.xxx:9200/archive"
{"acknowledged":true}root@Ubuntu-2010-groovy-64-minimal /var # curl -X GET "http://138.201.8.xxx:9200/_cat/indices?v"
dan verwijderen wat er over blijft.
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 900K 13G 1% /run
/dev/md2 934G 799G 88G 91% /
tmpfs 63G 0 63G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 4.0M 0 4.0M 0% /sys/fs/cgroup
/dev/nvme1n1 938G 733G 158G 83% /media/backup
/dev/md1 487M 82M 381M 18% /boot
tmpfs 13G 0 13G 0% /run/user/0
Er is nog steeds niet genoeg ruimte beschikbaar om VACUUM FULL of pg_repack uit te voeren.
postgres@Ubuntu-2010-groovy-64-minimal:~$ pg_repack -d dbname -t job_jobarchive
INFO: repacking table "public.job_jobarchive"
ERROR: query failed: ERROR: could not extend file "base/98628770/1333137786.97": wrote only 4096 of 8192 bytes at block 12748168
HINT: Check free disk space.
DETAIL: query was:
postgres@Ubuntu-2010-groovy-64-minimal:~$
postgres@Ubuntu-2010-groovy-64-minimal:~$ psql -U postgres -d dbname
df -h
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 1.1M 13G 1% /run
/dev/md2 934G 760G 127G 86% /
tmpfs 63G 256K 63G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 4.0M 0 4.0M 0% /sys/fs/cgroup
/dev/nvme1n1 938G 733G 158G 83% /media/backup
/dev/md1 487M 82M 381M 18% /boot
tmpfs 13G 0 13G 0% /run/user/0
schemaname | tablename | table_size | bloat_size | total_size
------------+----------------+------------+------------+------------
public | job_jobarchive | 646 GB | 6045 MB | 651 GB
Er is momenteel slechts 6 GB vrije ruimte beschikbaar. Ik moet een mechanisme ontwikkelen om alles grondig op te schonen en ongeveer 90% van de data te verwijderen. Alleen gegevens met e-mails en gegevens die aangeklikt zijn, moeten behouden blijven.
We kunnen dit doen zodra we zien dat het verwijderen van data daadwerkelijk resulteert in een afname van de databasegrootte. Op dit moment verwijder ik data, maar de grootte blijft hetzelfde. Wel heb ik een index verwijderd, wat direct 10 GB aan ruimte opleverde.
Vervolgens moet ik al mijn tabellen met statistieken één voor één nalopen en aanduiden welke records payed = True moeten worden in het archief. Dit maakt duidelijk dat deze records in het archief mogen blijven.
Jobs met een e-mailadres worden automatisch gemarkeerd als payed = True, omdat ze contactgegevens bevatten en betalende klanten vertegenwoordigen.
Payed = True in het archief betekent in dit geval dat het record belangrijk is en behouden moet blijven.
For_index = False hoeveel?
De oplossing om dergelijke problemen in de toekomst te voorkomen is door records die essentieel zijn te markeren met payed = true. Dit zorgt ervoor dat alleen belangrijke gegevens behouden blijven, terwijl overige data efficiënt verwijderd kan worden. Zo blijft de database beheersbaar en geoptimaliseerd.
+ Alle posts die momenteel een e-mailadres bevatten, zijn er 10 miljoen.
- Tabellen die hierbij betrokken zijn:
viewjob_viewjobapplicationclickscountspontaneousapplication
SELECT COUNT(*) FROM job_jobarchive WHERE payed = TRUE;
count
----------
18809826
SELECT
column_name,
data_type,
is_nullable,
character_maximum_length
FROM
information_schema.columns
WHERE
table_name = 'spontaneous_spontaneousapplication';
UPDATE job_jobarchive
SET payed = TRUE
WHERE job_id IN (
SELECT job_id
FROM viewjob_viewjob
);
UPDATE job_jobarchive
SET payed = TRUE
WHERE job_id IN (
SELECT job_id
FROM job_applicationclickscount
);
UPDATE job_jobarchive
SET payed = TRUE
WHERE job_id IN (
SELECT job_id
FROM spontaneous_spontaneousapplication
);
UPDATE job_jobarchive
SET payed = TRUE
WHERE email IS NOT NULL AND email != '';
DELETE FROM job_jobarchive
WHERE payed = FALSE
bloat_info
WITH bloat_info AS (
SELECT
nspname AS schemaname,
relname AS tablename,
pg_size_pretty(pg_table_size(C.oid)) AS table_size,
pg_size_pretty(pg_total_relation_size(C.oid) - pg_table_size(C.oid)) AS bloat_size,
pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
FROM
pg_class C
JOIN
pg_namespace N ON (C.relnamespace = N.oid)
WHERE
C.relkind = 'r' AND N.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT * FROM bloat_info WHERE tablename = 'your_table_name';
Server met informatie Archive leeg maken omdat Postgresql heeft volledige disk heeft ingenomen. Zo los je het op.
PostgreSQL noodherstel: grote archieftabellen verplaatsen naar aparte disk met tablespace
Deze case beschrijft hoe ik een PostgreSQL database opnieuw stabiel kreeg toen de hoofddisk van de server bijna vol liep door zeer grote archieftabellen. Het doel was:
- de site terug online krijgen
- de grootste tabellen van de root disk halen
- ruimte terug vrijmaken voor het OS
- later retention deletes en compaction verderzetten
Situatie
De root disk zat bijna vol en PostgreSQL-tabellen namen extreem veel ruimte in. Een eerste controle toonde onder meer:
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
Belangrijkste tabellen:
job_jobarchiveongeveer 574 GBjob_archiveretentionsignalongeveer 97 GBjob_jobongeveer 43 GBjob_jobarchive_locationsongeveer 32 GB
Filesystem status op het kritiek moment:
df -h
Root was bijna vol, terwijl een extra disk beschikbaar was op /media/backup.
Eerste analyse
Voor job_jobarchive heb ik eerst nagegaan hoeveel rows en hoe de ruimte verdeeld was:
SELECT count(*) AS rows
FROM job_jobarchive;
SELECT
pg_size_pretty(pg_relation_size('job_jobarchive')) AS table_size,
pg_size_pretty(pg_indexes_size('job_jobarchive')) AS index_size,
pg_size_pretty(pg_total_relation_size('job_jobarchive')) AS total_size;
Resultaat:
- ongeveer 73,705,494 rows
- heap/table: ongeveer 92 GB
- indexen: ongeveer 3.1 GB
- totaal: ongeveer 470 GB
Dat betekende meteen dat de ruimte niet in de indexen zat, maar vooral in TOAST. Daarna:
SELECT
c.reltoastrelid::regclass AS toast_table
FROM pg_class c
WHERE c.relname = 'job_jobarchive';
SELECT
pg_size_pretty(pg_total_relation_size('pg_toast.pg_toast_143848844')) AS toast_total_size;
De TOAST-tabel bleek ongeveer 375 GB te zijn.
Daarna heb ik gekeken welke tekstvelden dat verklaarden:
SELECT
count(*) AS total_rows,
count(description) AS rows_with_description,
count(raw_text) AS rows_with_raw_text,
count(raw_text2) AS rows_with_raw_text2
FROM job_jobarchive;
SELECT
round(avg(length(coalesce(description, '')))) AS avg_description_len,
round(avg(length(coalesce(raw_text, '')))) AS avg_raw_text_len,
round(avg(length(coalesce(raw_text2, '')))) AS avg_raw_text2_len,
max(length(coalesce(description, ''))) AS max_description_len,
max(length(coalesce(raw_text, ''))) AS max_raw_text_len,
max(length(coalesce(raw_text2, ''))) AS max_raw_text2_len
FROM job_jobarchive;
Conclusie:
descriptionstond op alle rowsraw_textstond op alle rowsraw_text2slechts op een minderheid- de echte opslagvreter was dus de combinatie
description+raw_text
Waarom pg_repack eerst faalde
Na het verplaatsen van de grote archieftabel naar een aparte disk probeerde ik:
sudo -u postgres pg_repack -d vindazo_de -t job_jobarchive
Dat faalde met:
ERROR: could not extend file "pg_tblspc/..."
HINT: Check free disk space.
De reden: pg_repack maakt een nieuwe compacte kopie van de tabel in de huidige tablespace van die tabel. Omdat
job_jobarchive al in een tablespace op de aparte disk stond, moest er daar voldoende vrije ruimte zijn voor een nieuwe compacte
kopie. Dat was op dat moment niet het geval.
Oplossing: tabel tijdelijk repacken op root en daarna terug verplaatsen
De effectieve oplossing was:
- de grote archieftabel eerst van root naar een aparte disk verplaatsen
- ruimte terug op root vrijmaken
- een tijdelijke tablespace op root gebruiken voor repack
- daarna eventueel opnieuw terug verplaatsen naar de aparte archive disk
Stap 1: aparte tablespace maken op extra disk
Ik heb een aparte PostgreSQL tablespace aangemaakt op /media/backup. Die disk blijft permanent bruikbaar voor archieftabellen.
sudo mkdir -p /media/backup/pg_tblspc_archive_cold
sudo chown postgres:postgres /media/backup/pg_tblspc_archive_cold
sudo chmod 700 /media/backup/pg_tblspc_archive_cold
sudo -u postgres psql -d postgres -c "CREATE TABLESPACE archive_cold LOCATION '/media/backup/pg_tblspc_archive_cold';"
Stap 2: grote archieftabel naar aparte disk verplaatsen
sudo -u postgres psql -d vindazo_de -c "ALTER TABLE job_jobarchive SET TABLESPACE archive_cold;"
Daarmee verhuisde de tabel fysiek naar de extra disk, en de root disk kreeg opnieuw ademruimte.
Stap 3: tijdelijke repack tablespace op root maken
Omdat pg_repack op de archive disk faalde door te weinig vrije ruimte, heb ik tijdelijk een tablespace op root gemaakt:
sudo mkdir -p /var/lib/postgresql/ts_repack_root
sudo chown postgres:postgres /var/lib/postgresql/ts_repack_root
sudo chmod 700 /var/lib/postgresql/ts_repack_root
sudo -u postgres psql -d postgres -c "CREATE TABLESPACE repack_root LOCATION '/var/lib/postgresql/ts_repack_root';"
Stap 4: job_jobarchive repacken naar repack_root
sudo -u postgres pg_repack -d vindazo_de -t job_jobarchive --tablespace=repack_root
Belangrijk: --tablespace=repack_root is niet alleen tijdelijke scratch space. Het eindresultaat van de gerepackte tabel komt
effectief in die opgegeven tablespace terecht.
Dat betekent:
- de tabel wordt compact herschreven
- de oude grote versie verdwijnt
- ruimte in de vorige tablespace komt vrij
Na deze stap kwam ongeveer 100 GB vrij.
Stap 5: controleren waar tabellen nu staan
SELECT
c.relname,
COALESCE(t.spcname, 'pg_default') AS tablespace
FROM pg_class c
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relname IN (
'job_jobarchive',
'job_archiveretentionsignal',
'job_jobarchive_locations',
'job_jobarchive_categories'
)
ORDER BY c.relname;
Retention deletes opnieuw opstarten
Om oude archive rows die al op decision='delete' stonden effectief te verwijderen, gebruikte ik deze Django command:
cd /home/sites/deployment/vindazo2/branches/de_vindazo
python manage.py purge_archive_retention_deletes --batch-size=500 --delete-signals --yes
Belangrijk:
--delete-signalsverwijdert ook de signal rows uitjob_archiveretentionsignal- zonder
--yesis het alleen dry-run
Vooraf kun je controleren hoeveel nog gemarkeerd staat:
SELECT decision, count(*)
FROM job_archiveretentionsignal
GROUP BY decision
ORDER BY decision;
SELECT count(*)
FROM job_jobarchive a
JOIN job_archiveretentionsignal s ON s.job_id = a.job_id
WHERE s.decision = 'delete';
Retention decisions opnieuw berekenen
Als de evaluatie opnieuw gestart moest worden:
cd /home/sites/deployment/vindazo2/branches/de_vindazo
python manage.py evaluate_archive_retention --min-age-days 180
Voor alles volledig opnieuw herberekenen:
python manage.py evaluate_archive_retention --min-age-days 180 --recheck-all
Controle van ontbrekende database-indexen
Tijdens troubleshooting bleek dat er veel PostgreSQL-indexen ontbraken, onder meer op job_archiveretentionsignal. Een snelle check
kan via:
python manage.py sqldiff job -t
Of voor alle apps:
python manage.py sqldiff -a -t
Daarnaast is deze SQL nuttig om huidige indexen te inspecteren:
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename IN ('job_job', 'job_archiveretentionsignal', 'job_jobarchive_locations')
ORDER BY tablename, indexname;
Belangrijke lessen
- Gewone VACUUM of autovacuum geeft meestal geen OS-space terug. Het maakt intern vrije ruimte, maar krimpt het bestand niet.
- pg_repack en VACUUM FULL herschrijven de tabel wel. Daarvoor is tijdelijke extra ruimte nodig.
- Als pg_repack faalt in een tablespace, controleer dan vrije ruimte in die specifieke tablespace.
- Gebruik tablespaces om grote archieftabellen van de root disk te halen.
- Draai grote repacks één voor één, niet parallel.
- Controleer eerst of het probleem in indexen, heap of TOAST zit. In deze case zat het echte probleem bijna volledig in TOAST.
Belangrijkste commando’s uit deze case
Schijfruimte controleren
df -h
Grootste tabellen zoeken
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
Tablespace op aparte disk maken
sudo mkdir -p /media/backup/pg_tblspc_archive_cold
sudo chown postgres:postgres /media/backup/pg_tblspc_archive_cold
sudo chmod 700 /media/backup/pg_tblspc_archive_cold
sudo -u postgres psql -d postgres -c "CREATE TABLESPACE archive_cold LOCATION '/media/backup/pg_tblspc_archive_cold';"
Tabel verplaatsen naar aparte disk
sudo -u postgres psql -d vindazo_de -c "ALTER TABLE job_jobarchive SET TABLESPACE archive_cold;"
Tijdelijke repack tablespace op root maken
sudo mkdir -p /var/lib/postgresql/ts_repack_root
sudo chown postgres:postgres /var/lib/postgresql/ts_repack_root
sudo chmod 700 /var/lib/postgresql/ts_repack_root
sudo -u postgres psql -d postgres -c "CREATE TABLESPACE repack_root LOCATION '/var/lib/postgresql/ts_repack_root';"
Tabel repacken naar repack_root
sudo -u postgres pg_repack -d vindazo_de -t job_jobarchive --tablespace=repack_root
Retention delete uitvoeren
cd /home/sites/deployment/vindazo2/branches/de_vindazo
python manage.py purge_archive_retention_deletes --batch-size=500 --delete-signals --yes
Retention beslissingen herberekenen
python manage.py evaluate_archive_retention --min-age-days 180
Samenvatting
De kern van het herstel was niet simpelweg “vacuum draaien”, maar:
- begrijpen waar de ruimte echt zat
- de grootste archieftabel naar een aparte disk verplaatsen
- tijdelijke werkruimte voorzien voor
pg_repack - retention deletes opnieuw opstarten
- missing indexes en verdere optimalisatie daarna aanpakken
In deze case bleek de grootste boosdoener niet de indexen te zijn, maar enorme TOAST-opslag door de combinatie van description en
raw_text op tientallen miljoenen archive rows.
PostgreSQL archiefopruiming op twee productie-servers: hoe we honderden GB terugwonnen
Deze case beschrijft hoe we op twee productie-omgevingen grote hoeveelheden schijfruimte hebben teruggewonnen door PostgreSQL archieftabellen, retention-signals, sessies en logging op te ruimen.
De algemene aanpak was op beide servers hetzelfde:
- eerst analyseren welke database echt actief is
- daarna bepalen welke tabellen de meeste ruimte innemen
- retention-data en overbodige signal rows verwijderen
- pas daarna
VACUUM FULLofpg_repackgebruiken - ook niet-database oorzaken zoals PostgreSQL logbestanden en Django sessions meenemen
1. Eerste les: verifieer altijd de echte actieve database
De eerste valkuil was eenvoudig: handmatig inloggen op PostgreSQL betekent niet automatisch dat je dezelfde database bekijkt als de webapplicatie.
Daarom moet de analyse altijd beginnen vanuit Django zelf:
python manage.py dbshell
Of expliciet:
python manage.py shell -c "from django.conf import settings; print(settings.DATABASES['default'])"
Belangrijke les: als je handmatig in de verkeerde database inlogt, analyseer en vacuum je de verkeerde omgeving.
2. Controleer waar PostgreSQL fysiek draait
Op een van de servers draaide PostgreSQL niet op de root filesystem, maar op een aparte mount. Dat verklaarde meteen waarom de grootste druk
niet op / zat, maar op een andere schijf.
De juiste controle is:
sudo -u postgres psql -d postgres -c "SHOW data_directory;"
Daarna kun je die locatie koppelen aan het juiste filesystem met:
df -h /pad/uit/data_directory
Dit is belangrijk, want anders kun je denken dat een databaseprobleem op root zit, terwijl de echte datadir elders staat.
3. Analyseer eerst de grootste tabellen
Op beide servers begon de analyse met dezelfde query:
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
De grootste tabellen waren typisch:
job_jobarchivejob_archiveretentionsignaljob_jobarchive_locationsdjango_sessioncrawler_pagemailing_message
Belangrijke les: niet alleen archieftabellen zijn groot. Ook sessies, mailingtabellen en crawllogs kunnen tientallen GB innemen.
4. Retention-signals analyseren voor je iets verwijdert
Voor archief-retentie gebruikten we eerst deze queries:
SELECT decision, count(*)
FROM job_archiveretentionsignal
GROUP BY decision
ORDER BY decision;
SELECT count(*)
FROM job_jobarchive a
JOIN job_archiveretentionsignal s ON s.job_id = a.job_id
WHERE s.decision = 'delete';
Dat gaf een belangrijk onderscheid:
- soms waren er nog miljoenen rows met
decision='delete' - maar de gekoppelde archive-vacatures zelf waren al weg
In zo’n geval bevat job_archiveretentionsignal alleen nog restdata, en is het logisch om vooral die signal rows op te schonen.
5. Wanneer pure SQL beter is dan een management command
Als de gekoppelde archive rows al verwijderd zijn, is pure SQL vaak sneller en eenvoudiger dan een ORM-gestuurde cleanup.
Voorbeeld:
DELETE FROM job_archiveretentionsignal
WHERE decision = 'delete';
Dit is efficiënter dan een langlopende management command als:
- de archive rows al weg zijn
- je alleen nog delete-signals wilt opruimen
Na de delete volgt compaction:
VACUUM FULL VERBOSE ANALYZE job_archiveretentionsignal;
6. Django sessions groeien niet vanzelf weg
Een tweede grote verrassing was hoe groot django_session was geworden. In één geval nam die tabel tientallen GB in.
Django verwijdert expired sessions niet automatisch. Daarvoor moet je expliciet:
python manage.py clearsessions
Of, als je iedereen mag uitloggen:
TRUNCATE TABLE django_session;
Daarna opnieuw fysiek compacter maken:
VACUUM FULL VERBOSE ANALYZE django_session;
7. Wanneer pg_repack goed werkt, en wanneer niet
pg_repack is ideaal wanneer je tabellen compact wilt herschrijven zonder langdurige blokkering, maar het is niet altijd
beschikbaar op oudere of afwijkend geconfigureerde servers.
Typische command:
sudo -u postgres pg_repack -d DBNAME -t job_jobarchive
Als pg_repack niet beschikbaar is, blijft de veilige fallback:
VACUUM FULL VERBOSE ANALYZE job_jobarchive;
Belangrijk verschil:
pg_repackhoudt de tabel grotendeels beschikbaar tijdens de operatieVACUUM FULLlockt de tabel volledig
8. Groot verborgen probleem: PostgreSQL logfiles
Op een van de servers bleek een enorm deel van de gebruikte schijfruimte niet in de tabellen te zitten, maar in PostgreSQL logbestanden onder
pg_log.
Dat was zichtbaar via de datadir-analyse: de logmap was groter dan de databestanden zelf.
Controle van de log settings:
SHOW logging_collector;
SHOW log_directory;
SHOW log_statement;
SHOW log_min_duration_statement;
SHOW log_duration;
SHOW log_min_messages;
SHOW log_min_error_statement;
De instellingen waren niet extreem verbose, maar:
logging_collectorstond aan- logs werden lokaal binnen
PGDATAgeschreven - er was geen cleanup-retentie
Op andere servers stond logging_collector uit. Daardoor groeide daar geen gigantische lokale logmap.
Praktische cleanup:
sudo -u postgres psql -d postgres -c "SELECT pg_rotate_logfile();"
sudo find /pad/naar/pg_log -type f -mtime +7 -delete
Belangrijke les: als pg_log binnen de PostgreSQL datadir staat, kan logging zelf de schijf vullen waarop ook je database draait.
9. Resultaten
Na de opruiming op de eerste server daalde het gebruik van de betrokken schijf van zeer hoog naar comfortabel laag. Er kwam ruwweg meer dan de helft van de gebruikte ruimte terug vrij.
Op de tweede server daalde de bezetting van de root filesystem eveneens sterk, met meer dan honderd GB extra vrije ruimte als resultaat.
De exacte getallen verschillen per omgeving, maar het patroon was hetzelfde:
- retention-signals opruimen
- sessies opschonen
- zware tabellen compact maken
- overmatige PostgreSQL logs verwijderen
10. Veiligste volgorde van werken
De veiligste werkwijze was uiteindelijk deze:
- controleer eerst welke database de applicatie echt gebruikt
- controleer waar PostgreSQL fysiek draait via
SHOW data_directory; - haal de grootste tabellen op
- analyseer
job_archiveretentionsignalen kijk of archive rows echt nog bestaan - verwijder eerst delete-signals of andere obvious restdata
- ruim Django sessions op
- ruim overmatige PostgreSQL logs op als die binnen PGDATA zitten
- gebruik pas daarna
VACUUM FULLofpg_repack
11. Handige SQL en commands uit deze case
Actieve database via Django
python manage.py shell -c "from django.conf import settings; print(settings.DATABASES['default'])"
PostgreSQL datadir
sudo -u postgres psql -d postgres -c "SHOW data_directory;"
Grootste tabellen
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
Retention status
SELECT decision, count(*)
FROM job_archiveretentionsignal
GROUP BY decision
ORDER BY decision;
SELECT count(*)
FROM job_jobarchive a
JOIN job_archiveretentionsignal s ON s.job_id = a.job_id
WHERE s.decision = 'delete';
Delete signal rows wanneer archive rows al weg zijn
DELETE FROM job_archiveretentionsignal
WHERE decision = 'delete';
Django sessions opschonen
python manage.py clearsessions
VACUUM FULL voorbeelden
VACUUM FULL VERBOSE ANALYZE job_archiveretentionsignal;
VACUUM FULL VERBOSE ANALYZE django_session;
VACUUM FULL VERBOSE ANALYZE job_jobarchive;
VACUUM FULL VERBOSE ANALYZE job_jobarchive_locations;
VACUUM FULL VERBOSE ANALYZE job_jobarchive_categories;
PostgreSQL logs roteren en opruimen
sudo -u postgres psql -d postgres -c "SELECT pg_rotate_logfile();"
sudo find /pad/naar/pg_log -type f -mtime +7 -delete
Conclusie
De grootste fout die je in dit soort situaties kunt maken, is blind gaan vacuumen zonder eerst te begrijpen waar de ruimte echt zit.
Je moet eerst weten:
- welke database actief is
- op welke schijf PostgreSQL werkelijk draait
- welke tabellen nog zinvolle data bevatten
- welke tabellen alleen nog restdata of signal rows bevatten
- of niet-databasebestanden zoals
pg_loghet echte probleem zijn
In deze cases bleek de grootste winst te komen uit:
- het verwijderen van oude retention-signals
- het opschonen van Django sessions
- het compacter maken van zware tabellen via
VACUUM FULLofpg_repack - het opruimen van PostgreSQL logs op servers waar lokaal verzameld werd
Het resultaat was dat beide productie-omgevingen weer veel veiliger qua vrije schijfruimte kwamen te staan, zonder blinde of onnodig destructieve stappen.
PostgreSQL archiefopruiming op NL: waarom VACUUM FULL hier beter werkte dan pg_repack
In deze case ging het om een grote productie-database waar archieftabellen, retention-signals, sessies en andere zware tabellen veel ruimte
innamen. De eerste gedachte was om pg_repack te gebruiken, maar in de praktijk bleek VACUUM FULL hier de betere keuze.
De reden was eenvoudig:
pg_repackhoudt de tabel grotendeels online, maar blijft wachten als er nog actieve queries op de tabel lopen- daardoor kan een live site of achtergrondproces de operatie eindeloos ophouden
VACUUM FULLneemt zelf de lock, stopt het verkeer op die tabel expliciet, en werkt daardoor in deze situatie voorspelbaarder en sneller
Voor een druk gebruikte productieomgeving is dat een belangrijk verschil. pg_repack is eleganter als de tabel rustig is, maar als
de tabel constant gebruikt wordt, kan VACUUM FULL in de praktijk juist sneller klaar zijn omdat het niet blijft hangen op
terugkerende queries.
Situatie vóór de cleanup
Filesystem gebruik vóór de operatie:
df -h
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 1.3G 12G 11% /run
/dev/md2 1.8T 780G 886G 47% /
tmpfs 63G 636K 63G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
/dev/md1 989M 385M 554M 41% /boot
//u266537.your-storagebox.de/backup 2.0T 798G 1.2T 40% /mnt/backupbox
tmpfs 13G 0 13G 0% /run/user/0
Grootste tabellen vóór de cleanup:
SELECT relname AS table,
pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;
table | size
-----------------------------+---------
job_jobarchive | 455 GB
job_archiveretentionsignal | 53 GB
job_job | 42 GB
mailing_queue | 32 GB
trafficinsights_httplogevent | 22 GB
django_session | 20 GB
crawler_page | 13 GB
job_jobarchive_locations | 12 GB
Belangrijke retention-analyse
Voor we begonnen met compaction, werd eerst gecontroleerd of de retention-signals nog gekoppeld waren aan echte archive rows.
SELECT decision, count(*)
FROM job_archiveretentionsignal
GROUP BY decision
ORDER BY decision;
Resultaat:
decision | count
---------+----------
delete | 62363096
keep | 19946581
Daarna werd gecontroleerd of die delete-signals nog aan echte archive rows hingen:
SELECT count(*)
FROM job_jobarchive a
JOIN job_archiveretentionsignal s ON s.job_id = a.job_id
WHERE s.decision = 'delete';
Resultaat:
count
------
0
Dit was een cruciale observatie. Het betekende:
- de archive-vacatures met
decision='delete'waren al verwijderd - maar de signal rows zelf stonden nog in
job_archiveretentionsignal - die tabel bevatte dus enorme hoeveelheden restdata
Waarom pg_repack hier minder geschikt bleek
Bij gebruik van pg_repack kwam een bekend probleem naar boven: het commando bleef wachten op actieve transacties op de tabel.
De typische melding was:
NOTICE: Waiting for 1 transactions to finish. First PID: ...
Dat betekent in de praktijk:
- de tabel wordt nog gebruikt door webverkeer of een achtergrondjob
pg_repackwil netjes wachten- maar als nieuwe queries blijven binnenkomen, kan die wachttijd heel lang oplopen
Voor een productie-omgeving waar de tabel actief gebruikt wordt, maakt dat pg_repack soms minder praktisch dan het op papier
lijkt.
VACUUM FULL werkte in deze case beter omdat:
- het zelf de lock neemt
- de tabel duidelijk blokkeert voor de duur van de operatie
- de cleanup daardoor voorspelbaar doorgaat
- de operatie niet blijft hangen op een stroom van live queries
Uitgevoerde VACUUM FULL-commands
De cleanup werd uitgevoerd met deze commando’s, één voor één:
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE job_archiveretentionsignal;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE django_session;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE job_jobarchive;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE job_jobarchive_locations;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE job_jobarchive_categories;"
Daarna ook nog voor enkele aanvullende grote tabellen:
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE crawler_page;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE mailing_message;"
sudo -u postgres psql -d vindazo_nl -c "VACUUM FULL VERBOSE ANALYZE mailing_queue;"
Belangrijk: deze werden bewust niet parallel gedraaid. Elke tabel werd volledig afgewerkt voor de volgende startte.
Situatie na de cleanup
Filesystem gebruik na de operatie:
df -h
Filesystem Size Used Avail Use% Mounted on
tmpfs 13G 1.3G 12G 11% /run
/dev/md2 1.8T 378G 1.3T 23% /
tmpfs 63G 440K 63G 1% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
/dev/md1 989M 385M 554M 41% /boot
//u266537.your-storagebox.de/backup 2.0T 798G 1.2T 40% /mnt/backupbox
tmpfs 13G 0 13G 0% /run/user/0
Dat betekent:
- gebruik op
/daalde van 780 GB naar 378 GB - vrije ruimte steeg van 886 GB naar 1.3 TB
- bezetting daalde van 47% naar 23%
Netto winst:
- ongeveer 402 GB teruggewonnen
Tabelgroottes na de cleanup
De grootste tabellen na afloop:
table | size
-----------------------------+---------
job_jobarchive | 129 GB
job_job | 42 GB
mailing_queue | 32 GB
trafficinsights_httplogevent | 22 GB
crawler_page | 13 GB
job_archiveretentionsignal | 7247 MB
trafficinsights_pathstatssnapshot | 4218 MB
job_jobarchive_locations | 2706 MB
job_legacyjoblookup | 2621 MB
icrm_cv | 2247 MB
viewjob_viewjob | 1643 MB
Belangrijkste dalingen:
job_jobarchive: van 455 GB naar 129 GBjob_archiveretentionsignal: van 53 GB naar ongeveer 7.2 GBjob_jobarchive_locations: van 12 GB naar ongeveer 2.7 GB
Waarom deze aanpak werkte
Deze cleanup werkte goed omdat:
- eerst inhoudelijk werd gecontroleerd wat al verwijderd was
- daarna duidelijk werd dat
job_archiveretentionsignalvooral nog restdata bevatte - de zwaarste tabellen sequentieel werden aangepakt
VACUUM FULLde lock- en wachttijdproblemen vanpg_repackvermeed
Met andere woorden: pg_repack is technisch elegant, maar op een actieve productie-tabel kan VACUUM FULL soms
efficiënter zijn omdat het de operatie meteen afdwingt in plaats van te blijven wachten op terugkerende queries.
Belangrijke les uit deze case
Gebruik pg_repack vooral wanneer:
- de tabel relatief rustig is
- je de site zoveel mogelijk online wilt houden
- je geen blokkering wilt op live verkeer
Gebruik VACUUM FULL eerder wanneer:
- de tabel toch voortdurend gebruikt wordt
pg_repackblijft wachten op actieve transacties- je een voorspelbare, harde cleanup wilt
- tijdelijke blokkering van de tabel aanvaardbaar is
Conclusie
In deze NL-case bleek VACUUM FULL uiteindelijk de betere praktische keuze te zijn. Niet omdat het mooier is, maar omdat het in een
drukke productieomgeving betrouwbaarder en sneller klaar was dan pg_repack.
Het resultaat was groot:
- meer dan 400 GB vrije ruimte teruggewonnen
job_jobarchivesterk verkleindjob_archiveretentionsignalvan tientallen GB naar enkele GB teruggebracht- de root filesystem van een risicovolle naar een comfortabele bezettingsgraad gebracht
De belangrijkste les: niet blind kiezen voor de “mooiste” tool, maar voor de aanpak die in jouw productiepatroon het betrouwbaarst doorwerkt.
Comments
Post a Comment