Efficiënte Database Opschoning: Problemen Oplossen en Toekomstige Beheerstrategieën

on

Oplossing (Samenvatting)

Details zijn hieronder uitgewerkt.

  1. Verwijder onnodige bestanden:
    • Verwijder logbestanden en andere bestanden die niet meer nodig zijn.
  2. Optimaliseer de database: 
    REINDEX TABLE job_jobarchive;

    Als alternatief kun je de index verwijderen:

    DROP INDEX [index_name] ON job_jobarchive;
  3. Opschonen en analyseren: 
    VACUUM ANALYZE job_jobarchive;
  4. 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_viewjob
    • applicationclickscount
    • spontaneousapplication


 
                         
 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.

Comments

Post a Comment