Przebudowa i shrinkowanie baz DB2

Jak w każdej bazie tak i tu w skutek wstawiania, usuwanie i modyfikacji danych z tabelach dane nie są zapisywane w uporządkowany sposób na dysku. Odczyt tak zdefragmentowanych danych może stopniowo się wydłużać, a przestrzenie tabel mogą stopniowo rozrastać się zapełniając dyski. Warto więc rozważyć przeprowadzenie reorganizacji (przebudowy) obiektów, a następnie shrinkowania przestrzeni tabel.

Należy zwrócić uwagę na miejsce na dyskach, gdyż podczas przebudowy są tworzone klony obiektów, a następnie na ich podstawie przebudowywany obiekt a następnie miejsce jest uwalniane. Tak więc potrzeba miejsca tyle ile zajmuje największy obiekt w bazie.
Można też puszczać przebudowy „po kawałku” zaczynając od największych tabel i obserwując zajętość dysków. Jeśli będzie się zapychać przeczyścimy go (jak to zrobić opisuję dalej) i przeprowadzić reorganizację pozostałych tabel.

PRZEBUDOWA TABEL PRZED SHRINKOWANIEM

Załóżmy że usunęliśmy sporo danych w naszym schemacie z tabel w przestrzeni tabel. W tabeli powstało sporo „dziur” po usuniętych danych.
Na początek sprawdzimy sobie ile zajmuje nasza baza

db2 "SELECT sum(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE)/1024 AS TOTAL_SIZE_IN_MB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' ORDER BY TOTAL_SIZE_IN_MB DESC > "

TOTAL_SIZE_IN_MB
--------------------
               66905

  1 record(s) selected.

Aby przebudować tabele zapełniając te puste miejsca musimy wydac polecenie REORG przeprowadzające defragmentację tabel. Musimy w składni podać nazwę schematu i tabeli.

db2 reorg table schemat.tabela;

Ciężko jest wpisać z ręki wszystkie tabele znajdujące się w danym schemacie więc warto skorzystać ze skryptu który stworzy nam polecenia do przebudowy (reorg’a):

Najpierw możemy sobie wyświetlić rozmiary naszych tabel w schemacie

db2 "SELECT SUBSTR(TABSCHEMA,1,18) TABSCHEMA,SUBSTR(TABNAME,1,30) TABNAME,(DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) AS TOTAL_SIZE_IN_KB FROM SYSIBMADM.ADMINTABINFO WHERE TABSCHEMA NOT LIKE 'SYS%' ORDER BY TOTAL_SIZE_IN_KB DESC"

TABSCHEMA          TABNAME                        TOTAL_SIZE_IN_KB
------------------ ------------------------------ --------------------
SCHEMA1           LSW_PO_VERSIONS                            17601536
SCHEMA1           BPM_COACHNG                                11218944
SCHEMA1           CONTENT                                     6805504
SCHEMA1           LSW_BPD                                     3927040
SCHEMA1           LSW_PO_DEPENDENCY                           2900992
SCHEMA1           BPM_SHARED_OBJECT_INSTANCE                  2664448
SCHEMA1           LSW_CLASS                                   2456576
SCHEMA1           LSW_PROCESS_ITEM                            2025472
...
reszta mniejsza niż 10MB

342 record(s) selected.

W oparciu o to zapytanie stworzymy zapytanie tworzące skrypt do przebudowy wszystkich niesystemowych tabel w schemacie:

db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';'from syscat.tables where type = 'T' and tabschema='SCHEMA1'" > SCHEMA1.reorg.out

Wyświetlamy plik SCHEMA1.reorg.out i sprawdzamy czy prawidłowo się utowrzył:

less SCHEMA1.reorg.out

db2 reorg table SCHEMA1.LA_PO_VERSIONS;
db2 reorg table SCHEMA1.LA_COACHNG;
db2 reorg table SCHEMA1.LA_CONTENT;
db2 reorg table SCHEMA1.LA_BPD;
...
db2 reorg table SCHEMA1.LA_PROCESS_ITEM;
db2 reorg table SCHEMA1.LAW_SCRIPT;
db2 reorg table SCHEMA1.LA_VIEW;
db2 reorg table SCHEMA1.LA_PO_REFERENCE;

Możemy stopniowo kopiować polecenia z pliku i je uruchamiać ale możemy też uruchomić je wszystkie naraz poleceniem:

db2 -tvf SCHEMA1.reorg.out > SCHEMA1.reorg.log

W drugim oknie możemy uruchomić polecenie wyświetlające log z wykonywanego polecenia:

tail –f SCHEMA1.reorg.log

W przypadku powyżej obiekty będą blokowane na czas przebudowy co przy dużej ich wielkości może spowodować zatrzymanie możliwości zapisu do nich. Metoda ta jest stosowana w przypadku odłączenia userów od bazy (zatrzymania aplikacji itd). Aby umożliwić zapis do tabel podczas przebudowy wydajemy polecenie:

db2 REORG TABLE schemat.tabela INPLACE ALLLOW WRITE ACCESS

Po zakończeniu przebudowy uruchamiamy obniżenie poziomu High Water czyli miejsca w bazie do którego możemy „obciąć” plik przestrzeni tabel. Z powodu defragmentacji bazy przed przebudową znajdował się on pewnie w najwyższym punkcie i uniemożliwiał zmniejszenie wielkości pliku przestrzeni tabel.

db2 ALTER TABLESPACE tablespace LOWER HIGH WATER MARK

Jeśli musimy zatrzymać obniżanie High Water wydajemy polecenie:

db2 ALTER TABLESPACE tablespace LOWER HIGH WATER MARK STOP

Po obniżeniu poziomu High Water ścinamy plik przestrzeni tabel redukując jego wielkość.

db2 ALTER TABLESPACE tablespace REDUCE MAX

SHRINKOWANIE PRZESTRZENI TABEL – PRZYKŁAD PRAKTYCZNY

Jeśli już przebudowaliśmy nasze obiekty i dane są zapisane w uporządkowany sposób możemy uwolnić to miejsce zmniejszając rozmiar przestrzeni tabel i zyskując trochę wolnego miejsca na dysku. Podobne działania wykonujemy po tym jak usuniemy jakąś dużą tabelę z przestrzeni tabel. Aby uwolnic miejsce po „dziurze w dysku” gdzie była zapisana tabela należy wykonać shrinkowanie przestrzeni.

Pokażę teraz jak to wygląda w praktyce w przypadku usunięcia tabeli zajmującej sporą ilość miejsca na dysku.

Podłączamy się do naszej bazy.

db2 connect to TESTOWA

Tworzymy nową tabelę PERSONEL w naszej przestrzeni tabel TESTTBS.

db2 "create table MYTABLE(phone1 char(9),phone2 char(9), phone3 char(9)) IN TESTTBS"

Sprawdzamy wielkość przestrzeni tabel

db2 " SELECT varchar(tbsp_name, 30) as tbsp_name, reclaimable_space_enabled,tbsp_free_pages, tbsp_page_top, tbsp_usable_pages FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t ORDER BY tbsp_free_pages ASC"

TBSP_NAME       RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
----------------------------------------------------------------------------------------
TEMPSPACE1                   0                    0                    0                    1
SYSCATSPACE                  1                 2640                21932                24572
TESTTBS                      1                 8000                  160                 8160
SYSTOOLSPACE                 1                 8044                  144                 8188
USERSPACE1                   1                 8064                   96                 8160

  5 record(s) selected.

Wolne miejsce obliczamy według wzoru

TBSP_FREE_PAGES – ( TBSP_USABLE_PAGES - TBSP_PAGE_TOP) czyli 8064 – ( 8160 – 96) = 0

czyli w TBS nie ma wolnej przestrzeni. Jeśli by coś było pamiętajmy że rozmiar podawany jest w stronach i trzeba to przeliczyć zgodnie z wielkością strony dla danej bazy (od 4k do 32k).

Sprawdzamy ilość wierszy w tabeli MYTABLE
db2 "select count(*) as licznik_wierszy from MYTABLE"

LICZNIK_WIERSZY
---------------
              1

  1 record(s) selected.

Oraz zajmowane miejsce na dysku przez foldery:

pwd

/home/db2inst1/db2inst1/NODE0000

du -sk *

94252   BACKUP
229432  SAMPLE
61120   SQL00001
14976   SQL00002
1707012 SQL00003
24      sqldbdir
196688  TESTOWA rozmiar na dysku całej bazy
131140  TOOLSDB

pwd

/home/db2inst1/db2inst1/NODE0000/TESTOWA

du -sk *

98344   T0000000
12      T0000001
32776   T0000002
32776   T0000003 dla przestrzeni tabel TESTTBS
32776   T0000004

Zapełniamy tabelę spora ilością danych, żeby się trochę rozrosła na dysku.

db2 "INSERT INTO MYTABLE SELECT * FROM MYTABLE"

DB20000I  The SQL command completed successfully.

Polecenie wykonujemy około 23 razy 🙂

Jeśli za którym razem zwróci nam komunikat:

Ponowne uruchomienie spowoduje zapchanie się logu transakcji

db2 "INSERT INTO MYTABLE SELECT * FROM MYTABLE"

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

należy wtedy powiększyć wielkość logów transakcji.

db2 "select count(*) as licznik_wierszy from MYTABLE"

LICZNIK_WIERSZY
---------------
        4194304

  1 record(s) selected.

Sprawdzamy rozmiary folderów na dysku

du -sk *

94252   BACKUP
229432  SAMPLE
61120   SQL00001
14976   SQL00002
1707012 SQL00003
24      sqldbdir
360528  TESTOWA    (było 196688)
131140  TOOLSDB

pwd

/home/db2inst1/db2inst1/NODE0000/TESTOWA


du -sk *

98344   T0000000
12      T0000001
32776   T0000002
196616  T0000003    (było 32776) dla Przestrzeni tabel TESTTBS
32776   T0000004

pwd

/home/db2inst1/db2inst1/NODE0000

db2 "SELECT TABSCHEMA, TABNAME, SUM(DATA_OBJECT_P_SIZE) as DATA_SIZE, SUM(INDEX_OBJECT_P_SIZE) as INDEX_SIZE, SUM(LOB_OBJECT_P_SIZE) as LOB_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME like 'MYTABLE' GROUP BY TABSCHEMA, TABNAME"

TABSCHEMA              TABNAME                 DATA_SIZE            INDEX_SIZE           LOB_SIZE
----------------------------------------------------------------------------------------
DB2INST1               MYTABLE                 168064                    0                    0

  1 record(s) selected.

db2 " SELECT varchar(tbsp_name, 30) as tbsp_name, reclaimable_space_enabled,tbsp_free_pages, tbsp_page_top, tbsp_usable_pages FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t ORDER BY tbsp_free_pages ASC"

TBSP_NAME         RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
----------------------------------------------------------------------------------------
TEMPSPACE1                     0                    0                    0                    1
SYSCATSPACE                    1                 2352                22220                24572
TESTTBS                        1                 7008                42112                49120 
SYSTOOLSPACE                   1                 8044                  144                 8188
USERSPACE1                     1                 8064                   96                 8160

  5 record(s) selected.

TERAZ USUWAMY NASZĄ ZAPEŁNIONĄ DANYMI TABELĘ

db2 drop table MYTABLE

DB20000I  The SQL command completed successfully.

db2 "select count(*) from MYTABLE"

SQL0204N  "DB2INST4.PERSONEL" is an undefined name.  SQLSTATE=42704

Sprawdzamy rozmiar pliku tablespace.

du -sk *

94252   BACKUP
229432  SAMPLE
61120   SQL00001
14976   SQL00002
1707020 SQL00003
24      sqldbdir
360528  TESTOWA  (było tyle samo przed usunięciem tabeli)
131140  TOOLSDB

Rozmiar się nie zmienił. Pora zeshrinkować przestrzeń tabel.

Po przebudowie tabel i przed shrinkowaniem przestrzeni tabel wykonujemy obniżenie poziomu HIGH WATER MARK czyli poziomu do którego możemy zeshrinkować przestrzeń tabel.

db2 list tablespaces show detail

Tablespace ID                        = 3
 Name                                 = TESTTBS
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 192
 Useable pages                        = 160
 Used pages                           = 96
 Free pages                           = 64
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1 


W naszym przypadku nie trzeba tego robić, bo High Water mark jest na poziomie Used Pages czyli minimum.

Tu mamy przypadek kiedy tak nie jest

Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x80000
   Detailed explanation:
 Total pages                          = 2116544
 Useable pages                        = 2116512
  Used pages                           = 1436928
 Free pages                           = 679584
  High water mark (pages)              = 2075104
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

Obliczając tak na szybko :
2075104 - 1436928 = 638176 pages *32kb = 19,4GB
Na tym shrinkowaniu przestrzebi powinniśmy uzyskać około 19,4GB miejsca.

Jakby była taka potrzeba aby obniżyć poziom HIGH WATER MARK wykonujemy polecenie

db2 ALTER TABLESPACE TESTTBS LOWER HIGH WATER MARK

DB20000I  The SQL command completed successfully.

db2 LIST TABLESPACE CONTAINERS FOR 0 SHOW DETAIL

            Tablespace Containers for Tablespace 0

 Container ID                         = 0
 Name                                 = /home/db2inst1/db2inst1/NODE0000/TESTOWA/T0000000/C0000000.CAT
 Type                                 = File
 Total pages                          = 24576
 Useable pages                        = 24572
 Accessible                           = Yes 

następnie obniżamy wielkość pliku przestrzeni tabel

db2 ALTER TABLESPACE TESTTBS REDUCE MAX

DB20000I  The SQL command completed successfully.

Plik zostanie zmniejszony.

Przedstawiony przykład był przeprowadzony na małej przestrzeni tabel znajdującej się w bazie DB2 opartej na systemie Windows. W Linuxie jest trochę inaczej 🙁

SHRINK NA SYSTEMACH LINUX

Na systemach Linux po uruchomieniu reorganizacji a następnie wykonanie polecenia

db2 ALTER TABLESPACE TESTTBS REDUCE MAX

następuje stopniowe baaaardzo powolne obniżenie wartości TBSP_PAGE_TOP, która odpowiada wartości HIGH WATER MARK. Może to trwać nawet kilkadziesiąt godzin. Wartość do której ma się obniżyć wartość TBSP_PAGE_TOP można obliczyć ze wzoru:

TBSP_USABLE_PAGES - TBSP_FREE_PAGES

lub sprawdzając poziom wartości USED PAGES

db2 list tablespaces show detail

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x80000
   Detailed explanation:
 Total pages                          = 1912640
 Useable pages                        = 1912608
 Used pages                           = 1434016
 Free pages                           = 478592
 High water mark (pages)              = 1912544
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

db2 "ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK STOP"
DB20000I The SQL command completed successfully.

TBSP_NAME                      RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
------------------------------ ------------------------- -------------------- -------------------- --------------------
USERSPACE1                                             1               679520              1966464              2116512

Próba uruchomienia w czasie trwania obiżania WATER MARK’a (TBSP_PAGE_TOP) powoduje błąd :

db2 "ALTER TABLESPACE USERSPACE1 REDUCE MAX"

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL1523N  An extent movement operation attempted to access the table space
named "USERSPACE1" while another process was already accessing that table
space. Reason code = "15".  SQLSTATE=55039

Kiedy wartości TBSP_PAGE_TOP przestaną się zmniejszać i osiągną wartość USED PAGES możemy przystąpić do shrinkowania tablespace do poziomu High Water Mark.

db2 ALTER TABLESPACE TESTTBS REDUCE MAX

DB20000I  The SQL command completed successfully.

Ten proces trwa szybko gdyż „powyżej” High Water Marka nie ma zapisanych żadnych danych.

db2 " SELECT varchar(tbsp_name, 30) as tbsp_name, reclaimable_space_enabled,tbsp_free_pages, tbsp_page_top, tbsp_usable_pages FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t ORDER BY tbsp_free_pages ASC"

TBSP_NAME         RECLAIMABLE_SPACE_ENABLED TBSP_FREE_PAGES      TBSP_PAGE_TOP        TBSP_USABLE_PAGES
----------------------------------------------------------------------------------------
TEMPSPACE1                    0                    0                    0                    1
USERSPACE1                    1                    0              1434016              1434016
SYSCATSPACE                   1                 2344                22228                24572
SYSTOOLSPACE                  1                 8044                  144                 8188
USERSPACE1                    1                 8064                   96                 8160

  5 record(s) selected.

Rozmiar bazy się zmniejszył. Wartość TBSP_FREE_PAGES spadła do zera (nie ma wolnego miejsca w przestrzeni tabel) a co za tym idzie zajętość pliku i ilość danych w pliku są takie same (TBSP_PAGE_TOP = TBSP_USABLE_PAGES)

Powinniśmy odzyskać miejsce na dysku po usuniętych danych z tabel.

Ten wpis został opublikowany w kategorii DB2 i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.