Temat: Modyfikacja dużej bazy danych

Witam,
mam bazę na PostgreSQL, która aktualnie zajmuje 77GB. Chcę nieco zmodyfikować jej strukturę (usunąć jeden indeks oraz usunąć kolumnę). Sęk w tym, że usunięcie indeksu uruchomiłem ok. 9 rano i do tej pory (15) nie ma efektu. Indeks usuwam (staram się usunąć) poprzez zapytanie wykonywane za pomocą phpPgAdmin . Moje pytanie jest następujące: jak można przyspieszyć (o ile w ogóle się da) modyfikowanie struktury tak dużej bazy danych?
Pozdrawiam,
M. Płonka

Temat: Modyfikacja dużej bazy danych

select *
into tabela_backup
truncate table tabela

operacje.....

insert into ()
select *
from tabela_backup

najlepiej ładować dane paczkami -> małe transakcje

Temat: Modyfikacja dużej bazy danych

Weź pod uwagę, że to usunięcie indeksu może na czymś wisieć i wcale się nie robić tyle czasu, poza tym usunięcie indeksu powinno być raczej szybkie.
phppgadmina nie znam... ale czy masz pewność, że to zapytania usuwające indeks dalej chodzi i czy na czymś nie wisi?

Usunięcie kolumny, z winy MVCC, wymaga przepisania całej tabeli, więc może to potrwać chwilę.

A tak w ogóle to jak wróżenie z fusów, co z tego że baza ma 77GB (pytanie dodatkowe: jak sprawdziłeś że to 77GB?), skoro tabela, którą usuwasz może mieć znacznie mniej.

Żeby baza chodziła szybciej można też zmienić konfigurację bazy... jakbyś podał informacje o strukturze tabel, konfiguracji sprzętu, systemu i samej bazy, to byłoby łatwiej coś doradzić.

Temat: Modyfikacja dużej bazy danych

Dzięki wszystkim za odpowiedzi. Teraz sprawdziłem i na szczęście kolumna oraz indeks zostały usunięte.

Padło pytanie w jaki sposób sprawdziłem rozmiar bazy: phpPgAdmin podaje takową informację i na tym się oparłem. Tabela, a na której wprowadzałem modyfikację była to główna tabela systemu zawierającą największą liczbę danych (w tym 2 pola tsvector) zarówno pod kątem ilościowym jak i "jakościowym" (m.in. wspomniane tsvectory). Dodatkowo tabela ta była praktycznie cały czas zasilana danymi z zewnątrz co też zapewne spowolniło modyfikację jej struktury.

Może pozwolę sobie jeszcze na dodatkowe pytanie: w dniu wczorajszym rozpocząłem modyfikację tabeli. Wówczas baza danych miała rozmiar 47GB. Dziś wartość ta podskoczyła do wspomnianych 77GB, a po usunięciu kolumny oraz indeksu osiągnęła 81GB (średni dzienny przyrost to 500MB, ale nie 4GB na 12h!). Czy to możliwe, czy to raczej błędne odczyty phpPgAdmina? Mogę wykonać VACUUM na bazie ale wiązałoby się z koniecznością zamknięcia dostępu do aplikacji, a przynajmniej wstrzymania ładowania danych do bazy, czyż nie?

Pozdrawiam,
M. Płonka

Temat: Modyfikacja dużej bazy danych

Przy VACUUM nie musisz zamykać dostępu do aplikacji, ale możesz się spodziewać znacznego obciążenia I/O.

Temat: Modyfikacja dużej bazy danych

Kurde, wszystko się porozpierdzielało :| Po modyfikacjach kluczowe zapytanie przestało używać indeksu. Wczoraj w nocy włączyłem VACUUM ANALYZE. Dziś widzę, że rozmiar bazy się nie zmienił (a chyba powinien się zmniejszyć). Ponadto indeks nadal nie jest używany (a był!). To samo zapytanie u mnie na kompie używa indeksu, a na serwerze już nie. Próbowałem przeindeksować indeks ale bez rezultatu.

Może podam szczegóły:

Zapytanie:

explain SELECT "auctions"."start_price" AS "price", SUM(biddings.amount) AS sum_amount

FROM auctions

JOIN "biddings" ON ("biddings"."auction_id" = "auctions"."id")

JOIN "auctions_categories" ON ("auctions_categories"."auction_id" = "auctions"."id")

WHERE "auctions"."finished_at" >= '2010-01-01 00:00:00'

AND "auctions"."finished_at" <= '2010-02-28 23:59:59'

AND title_keywords @@ to_tsquery('public.polish', 'nike')

AND "auctions"."is_new" = 't'

AND "auctions_categories"."category_id" = 15583

GROUP BY "auctions"."start_price"

ORDER BY "auctions"."start_price" ASC

EXPLAIN u mnie:

"Sort  (cost=36.15..36.15 rows=1 width=9)"

"  Sort Key: auctions.start_price"

"  ->  HashAggregate  (cost=36.13..36.14 rows=1 width=9)"

"        ->  Nested Loop  (cost=0.00..36.11 rows=4 width=9)"

"              ->  Nested Loop  (cost=0.00..16.64 rows=1 width=15)"

"                    ->  Index Scan using i_auction_finished_at on auctions  (cost=0.00..8.30 rows=1 width=11)"

"                          Index Cond: ((finished_at >= '2010-01-01 00:00:00'::timestamp without time zone) AND (finished_at <= '2010-02-28 23:59:59'::timestamp without time zone))"

"                          Filter: (is_new AND (title_keywords @@ '''nike'''::tsquery))"

"                    ->  Index Scan using pk_auction_category_auction_id_category_id on auctions_categories  (cost=0.00..8.33 rows=1 width=4)"

"                          Index Cond: ((auctions_categories.auction_id = auctions.id) AND (auctions_categories.category_id = 15583))"

"              ->  Index Scan using pk_bidding_auction_id_bought_at_position on biddings  (cost=0.00..19.42 rows=4 width=10)"

"                    Index Cond: (biddings.auction_id = auctions.id)"

EXPLAIN na serwerze:

Sort  (cost=92338.41..92338.41 rows=1 width=9)

  Sort Key: auctions.start_price

  ->  HashAggregate  (cost=92338.39..92338.40 rows=1 width=9)

        ->  Nested Loop  (cost=6740.93..92338.35 rows=7 width=9)

              ->  Nested Loop  (cost=6740.93..88763.08 rows=4 width=15)

                    ->  Bitmap Heap Scan on auctions  (cost=6740.93..55169.01 rows=2062 width=11)

                          Filter: (is_new AND (finished_at >= '2010-01-01 00:00:00'::timestamp without time zone) AND (finished_at <= '2010-02-28 23:59:59'::timestamp without time zone) AND (title_keywords @@ '''nike'''::tsquery))

                          ->  Bitmap Index Scan on i_auction_title_keywords_description_keywords  (cost=0.00..6740.41 rows=12587 width=0)

                                Index Cond: (title_keywords @@ '''nike'''::tsquery)

                    ->  Index Scan using pk_auction_category_auction_id_category_id on auctions_categories  (cost=0.00..16.28 rows=1 width=4)

                          Index Cond: ((auctions_categories.auction_id = auctions.id) AND (auctions_categories.category_id = 15583))

              ->  Index Scan using pk_bidding_auction_id_bought_at_position on biddings  (cost=0.00..888.08 rows=459 width=10)

                    Index Cond: (biddings.auction_id = auctions.id)

Jak widać na serwerze olewany jest indeks i_auction_finished_at pomimo, że istnieje. O co tutaj chodzi? Jak to naprawić?

// Edit:
Wywaliłem indeks i_auction_title_keywords_description_keywords, który teoretycznie przeszkadza (jest używany zamiast tego, który chcę), ale i to nie pomogło. Sprawdziłem również banalne zapytanie:

select * from auctions where finished_at >= '2010-01-01 00:00:00' and finished_at <= '2010-01-31 23:59:59';

Na lokalu używa indeksu, na serwerze nie. Na serwerze używany jest indeks tylko dla warunku równości czyli np.

select * from auctions where finished_at = '2010-01-01 00:00:00';

Jeszcze w piątek wszystko było OK, teraz mam takie hocki-klocki :/

Z góry pięknie dziękuję za pomoc!Michał Płonka edytował(a) ten post dnia 04.04.10 o godzinie 13:11

Temat: Modyfikacja dużej bazy danych

Michał, jeśli chcesz żeby ktoś Ci pomógł, to podawaj definicję tabel i indeksów. Bez tego może być ciężko...

Z bazami SQL bywa tak, że optymalizator czasami wybiera tylko jeden warunek do indeksu, resztę obsługując na piechotę.

Spróbój usunąć na chwilę JOIN do kategorii. Zobacz czy pomoże.
Jeśli nie to usuwaj po kolei warunki oparte na tabeli "auctions" i zobacz kiedy zacznie być używany indeks którego potrzebujesz w tym zapytaniu.

Temat: Modyfikacja dużej bazy danych

Indeks jest założony na kolumnie timestamp without timezone. Może opiszę co zrobiłem, może to pomoże dojść do tego, co się stało.

Miałem w tabeli kolumnę finished_at o typie timestamp without timezone z założonym indeksem. W zapytaniach indeks był używany, wszystko było ok. Okazało się, że warto spróbować zmienić typ na date i zobaczyć jak wówczas będzie się zapytanie wykonywało (inne zapytanie grupuje dane po dacie, a czas w sumie jest mi zbędny). Dodałem więc kolumnę finished_at_date z indeksem na niej, przepisałem czasy z finished_at do finished_at_date, założyłem indeks. W tym momencie rozmiar bazy podskoczył z 47 do 77GB. Przetestowałem, nic to nie dało, nowy indeks był pomijany. Chciałem więc go usunać oraz usunąć nową kolumnę. Zeszło. Po tej operacji baza wzrosła (!!!) do 81GB. Uruchomiłem VACUUM ANALYZE dla całej bazy - wykonał się, ale rozmiar się nie zmienił. Przetestowałem zapytania i od tego momentu indeks na finished_at nie jest używany!!! Był używany indeks na polu tsvector (EXPLAIN z jednego z moich poprzednich postów). Wywaliłem ten indeks (na tsvetor) ale nic to nie dało. Przebudowałem indeks na finished_at. Uruchomiłem VACCUM FULL ANALYZE auctions czyli tylko dla interesującej mnie tabeli. Do tej pory się nie wykonał. Aha, w międzyczasie usunąłem również kolumnę z opisem aukcji (również tsvector). Zakładałem, że to to pole (wraz z indeksem) jest główną przycyzną olbrzymiego rozmiaru bazy, ale aktualnie baza zajmuje 68GB.

Jedyne co chciałbym teraz uzyskać to powrót do punktu wyjścia czyli wykorzystywania indeksu na finished_at. Może macie jakąś sugestię?

Temat: Modyfikacja dużej bazy danych

Michał Płonka:
Kurde, wszystko się porozpierdzielało :| Po modyfikacjach kluczowe zapytanie przestało używać indeksu. Wczoraj w nocy włączyłem VACUUM ANALYZE. Dziś widzę, że rozmiar bazy się nie zmienił (a chyba powinien się zmniejszyć).

rozmiar bazy zmieni się tylko po wykonaniu VACUUM FULL. Zwykły VACUUM nie odzyskuje miejsca...

Temat: Modyfikacja dużej bazy danych

Ok. 14 uruchomiłem VACUUM FULL ANALYZE na tabeli aukcji, czyli na tej, z którą mam problemy. Na liście procesów do tej pory widzę to polecenie:

uzytkownik	31633	vacuum full analyze auctions;	2010-04-04 14:12:23.846943+02

Przyznam, że jestem trochę w kropce. System po prostu wisi; na szczęście nie jest jeszcze upubliczniony.

Zakładam jednak, że zakończenie VACUUM to kwestia czasu. Co jednak z używaniem indeksu? Jak przywrócić jego używanie?

Temat: Modyfikacja dużej bazy danych

Piotr Likus:
Michał, jeśli chcesz żeby ktoś Ci pomógł, to podawaj definicję tabel i indeksów. Bez tego może być ciężko...

Struktura tabeli:

CREATE TABLE auctions

(

  id integer NOT NULL,

  title character varying(255) NOT NULL,

  title_keywords tsvector NOT NULL,

  start_price numeric(12,2) NOT NULL,

  finished_at timestamp without time zone NOT NULL,

  is_new boolean,

  CONSTRAINT pk_auction_id PRIMARY KEY (id)

)

WITH (

  OIDS=FALSE

);



CREATE INDEX i_auction_finished_at

  ON auctions

  USING btree

  (finished_at);

Piotr Likus:
Spróbój usunąć na chwilę JOIN do kategorii. Zobacz czy pomoże. Jeśli nie to usuwaj po kolei warunki oparte na tabeli "auctions" i zobacz kiedy zacznie być używany indeks którego potrzebujesz w tym zapytaniu.

Tego już próbowałem. Usuwałem poszczególne elementy po kolei i nic. Nawet (co już pisałem wcześniej) teraz takie zapytanie:

select * from auctions where finished_at >= '2010-01-01 00:00:00' and finished_at <= '2010-01-31 23:59:59';

nie używa indeksu podczas gdy uruchomienie go lokalnie powoduje użycie indeksu. Jeśli zmienię je na warunek równości np.

select * from auctions where finished_at = '2010-01-01 00:00:00';

to indeks jest używany. Pierwsze zapytanie wcześniej działało bez zarzutu, na lokalu również śmiga, ale na serwerze za Chiny nie chce już używać indeksu.

Temat: Modyfikacja dużej bazy danych

Jakby to był Oracle, to obstawiłbym brak policzonych histogramów - nie wiem, jak te tematy wyglądają w PostgreSQL :)

Temat: Modyfikacja dużej bazy danych

Ta najprostsza wersja zapytania:

select * from auctions where finished_at = '2010-01-01 00:00:00';

versus

select * 

from auctions 

where 

finished_at >= '2010-01-01 00:00:00' and 

finished_at <= '2010-01-31 23:59:59';

sugorowałaby, że masz po prostu mało selektywny warunek.

1. Zobacz ile masz różnych wartości tego pola.

select count(distinct finished_at) from auctions;

(nie wiem czy działa na PG)
- jeśli ta wartość jest >= od "sporego procentu" - powiedzmy 20% (dokładnie nie wiem) całkowitej liczby rekordów, to optymalizator po prostu olewa taki warunek i obsługuje go na piechotę.

2. "index scan" to tak naprawdę i tak jest mało zabawna sytuacja.
- baza przelatuje wszystkie rekordy, tylko zamiast chodzić po stronach bazy, chodzi po stronach indeksu. Więc tak na prawdę dużo lepiej "u ciebie" nie jest.

3. Spróbuj do pełnego zapytania dodać inne (np. nadmiarowe) warunki.
Np.

AND "auctions"."category_id" = 15583

4. Warunek na "is_new" chyba niewiele Ci pomoże (jeśli chodzi o indeksowalność).

5. Masz indeks na start_price? Spróbuj - może to być to czego szukasz.

6. "Select *" może być mylący dla DBMS-a, do testów używaj:

select start_price from auctions where finished_at = '2010-01-01 00:00:00';

(lub dowolne inne pole, które i tak będziesz wyciągał).

7. Być może warto zmienić pole timestamp na date.
Bo chyba i tak szukasz wg. dni a nie godzin.
Można też zrobić indeks na funkcji.

Najlepiej pomógłby Ci ktoś kto się zajmuje PG na codzień - ja mało tego DBMS-a używałem.Piotr Likus edytował(a) ten post dnia 05.04.10 o godzinie 18:23

Temat: Modyfikacja dużej bazy danych

Piotr Likus:
7. Być może warto zmienić pole timestamp na date. Bo chyba i tak szukasz wg. dni a nie godzin.

To właśnie chciałem zrobić w piątek. Dodałem dla testu drugie pole (typu DATE), postawiłem na nim indeks, uruchomiłem EXPLAIN i indeks nie był używany co mnie nieco zaskoczyło. Wywaliłem więc ta kolumnę wraz z indeksem i od tego czasu indeks na polu wyjściowym nie był używany.

No ale "znalazłem" rozwiązanie. Przypadkowo odpaliłem zapytanie bez EXPLAIN:

SELECT "auctions"."start_price" AS "price", SUM(biddings.amount) AS sum_amount

FROM auctions

JOIN "biddings" ON ("biddings"."auction_id" = "auctions"."id")

JOIN "auctions_categories" ON ("auctions_categories"."auction_id" = "auctions"."id")

WHERE "auctions"."finished_at" >= '2010-01-01 00:00:00'

AND "auctions"."finished_at" <= '2010-02-28 23:59:59'

AND title_keywords @@ to_tsquery('public.polish', '22&iiyama')

AND "auctions_categories"."category_id" = 27735

GROUP BY "auctions"."start_price"

ORDER BY "auctions"."start_price" ASC 

Pojawiło się praktycznie od razu. Wstawiłem do aplikacji wysyłkę maila z zapytaniem oraz z czasem jego wykonania i to konkretne zapytanie wykonuje się:

0.0302839279175

czyli pieronem! :) Rozkładu wartości w polu finished_at teraz nie podam, ale przy jest to ok. 120 różnych wartości (po zrzutowaniu na DATE) dla ok. 15mln rekordów.

Generalnie przez weekend baza została gruntownie przeczyszczona, pozbyłem się kolumny z dużym tsvector'em (okazała się zbędna), odpaliłem VACUUM FULL ANALYZE. Wynikiem tego była redukcja rozmiaru bazy z (w porywach) 81GB do 11GB. Czy te zabiegi mogły mieć wpływ na używanie/nie używanie indeksu? Możliwe, że optymalizator PostgreSQL stwierdził, że lepiej będzie mu wykonać zapytanie bez patrzenia na indeks? Tak jak pisałem wcześniej: lokalnie indeks jest używany, ale lokalnie nie mam aż tylu rekordów (nieco ponad 1mln).

PS: indeks na start_price mam :)

Temat: Modyfikacja dużej bazy danych

1. Proszę się uporać z tym rozrostem bazy. Jeżeli po usunięciu zbędnych kolumn sytuacja dalej będzie się powtarzać, warto podrasować Free Space Map i okolice.
2. PG użyje indeksu jeżeli zwrócony wynik będzie dawać 2% wielkości tabeli, albo mniej. Być może stare statystyki są tu problemem, ale bardziej bym stawiał na rozdzielczość danych. Jeżeli jest 120 różnych wartości w tabeli - to raczej mizerna różnorodność... Ja bym jeszcze zmienił na timestamp i ograniczenie czasu co do sekundy, no ale rewelacji raczej bym się nie spodziewał.
3. Jeżeli dane głównie dochodzą do bazy - proszę rozważyć wprowadzenie klastrowania. W rozumieniu PG oznacza to poukładanie danych wg. indeksu, ale w odróżnieniu np. do MS SQL Servera - dane są jedynie poukładane na dysku w określony sposób a nie upakowane do b-drzewa. Wszystko ma plusy i minusy. Tu minusem jest to, że czasem trzeba dane fizycznie poprzekładać. Plus jest taki, że insert kosztuje tyle co normalnie.

Temat: Modyfikacja dużej bazy danych

@Michał Zaborowski:

1. Rozrost bazy danych został zażegnany. Główną jego przyczyną była kolumna typu tsvector przechowująca dużo danych (opisy produktów). Po rezygnacji z tego pola większość pól to pola liczbowe + kilka krótkich tekstowych (np. nazwa produktu). Rezygnacja z pola opisu była podyktowana głównie rozrostem bazy danych.

2. 120 różnych wartości było po zrzutowaniu daty na pole DATE. Jeśli patrzymy pod kątem pola TIMESTAMP to rozrzut ten jest zdecydowanie większy. Po prostu do bazy dzień w dzień (od ok. połowy grudnia) trafiają dane posiadające znacznik czasu "powstania". Faktycznie interesuje mnie tylko data powstania, a nie godzina, więc te ok. 120 różnych wartości to liczba dni od pierwszego wpisu.

3. Czytałem o klastrach i w akcie desperacji postawiłem klaster na indeksie na polu finished_at (TIMESTAMP). Zapewne to zbieg okoliczności, ale po jego wykonaniu rozmiar bazy spadł z 44GB do 11GB (możliwe, że dopiero wtedy była widoczna zmiana po usunięciu tej dużej kolumny oraz wykonaniu VACUUM FULL ANALYZE), a zapytania zaczęły śmigać (pomimo nie wykorzystywania indeksu).

Serdecznie dziękuję wszystkim za pomoc oraz okazane zainteresowanie.