Import danych a wydajność serwisu Opcje

[sardpal]

Witam, od pewnego czasu borykam się z pewnym problemem.
Opiekuję się serwisem, który codziennie wymaga aktualizacji bazy danych poprzez import kilku plików XML do bazy danych (MYSQL).
Pliki są wielkośći kilkunastu bądź kilkudziesięciu MB. W celu sprawnego importu dziele je na mniejsze porcje rzędu 1MB a nastepnie importuje pokoleji.
Baza danych zawierająca oferty składa się kilku tabel w ktorych liczba rekordow wacha sie od kilkunastu tysiecy do 1,5 mln rekordow.
W kazdej tabeli jest zdefiniowanych po kilka indeksów (w celu sprawnego dzialania wyszukiwarki w ktorej niezbedne jest łączenie kilku tabel poprzez JOIN)
jednak podczas importu (innstrukcji INSERT , UPDATE), koniecznosc aktualizacji indeksow sprawia ze trwa on o wiele dluzej i przez 3-4 godz serwis staje sie praktycznie nie dostepny dla uzytkownikow. Chodzi o to ze na tabele zapewne nakladane sa blokady podczas wykonywania instrukcji INSERT oraz UPDATE.
Serwis praktycznie zamiera poniewaz nie moze korzystac z tych tabel. Ma ktos moze pomysl jak rozdzielic dostep do zasobow ,
tak aby import oraz serwis dzialal sprawnie?
Serwis jest typu http://travelplanet.pl i mysle ze posiada podobna ilosc ofert (to tak dla zobrazowania z czym mam doczynienia...).

[mhs]

Ciągniesz to z merlina czy z jakiegoś innego systemu?

[nevt]

a jak robisz te aktualizacje? pętlą w PHP i milionami INSERT / UPDATE ?
czy pojedynczym LOAD DATA INFILE ?
zalecam to drugie rozwiązanie - jeżeli źródła danych masz w XML, to zrób w PHP konwerter XML -> CSV i skorzystaj z LOAD DATA INFILE - w MySQL nie ma szybszej metody importu danych z zewnątrz...

Ten post edytował nevt 22.02.2008, 08:05:15

[sardpal]

Witam, tak ciagne z merlina tzn dokladniej mam pliki z merlina ktore wrzucane sa na serwer, no i pozniej import..., pozatym mam kilka plikow w formacie txt i to przy nich w tej chwili jes najwiekszy problem. W pliku 16MB jest ok 100tys ofert. A co do polecanego rozwiazania " LOAD DATA INFILE " nie za bardzo jest mozliwosc jego zastoswania poniewaz dane musza byc importowane do kilku tabel. a co za tym idzie aby zaimportowac ceny do danej oferty wczesniej musi byc zaimportowana sama oferta i pobrany z niej ID. Takze dla kazdej oferty niestety musi chyba byc INSERT lub UPDATE , jak rowniez dla tabel cen , hoteli i pozostalych.

[nevt]

spokojnie to przemyśl...

jeżeli wysilisz się, i napiszesz (nawet niezbyt wydajne) skrypty w PHP (lub innym narzędziu) które na podstawie danych w XML wygenerują odpowiednie pliki CSV - oddzielny plik dla każdej aktualizowanej tabeli...
to wtedy możesz (też w PHP lub innym języku) pętlą przelecieć się po tych tabelkach i zrobić te kilka / kilkanaście LOAD DATA INFILE w odpowiedniej kolejności, żeby nie naruszyć integralności danych w bazie...

a jaka z tego wszystkiego korzyść ? w czasie, kiedy serwer PHP będzie sobie mielił te pliki (XML -> CSV) masz zapewniony normalny dostęp userów do bazy - bo baza w ogóle nie będzie obciążona... za to sam proces aktualizacji danych bazie skróci się znacząco (przy tych ilościach danych o których piszesz nie powinien przekraczać kilku - kilunastu minut)...

wiem, że nie jest to łatwe rozwiązanie, ale nie widzę innej prostej możliwości - może znajdzie się i wypowie ktoś bardziej doświadczony...

możesz wybrać jeszcze inną drogę- poszatkuj te dane na małe porcje (kilka - kilkanaście rekordów) i aktualizuj wstawiając przerwę 2-3 sekundy miedzy kolejnymi porcjami. co prawda cały proces ci się znacznie wydłuży (byle zmieścić się w 24 h) ale za to w międzyczasie userzy będa mieli swobodny dostęp do bazy...

[mhs]

@sardpal - a ten proces importu masz całkowicie zautomatyzowany? Dokładnie chodzi o pobieranie danych z merlina (czyli zipa); jego rozpakowanie i późniejsze sparsowanie xml'a i wrzucenie danych do bazy danych?

Ja wszystko co mam całkowicie zautomatyzowane, ale niestety ponieważ strona stoi hostingu współdzielonym mam problem z tym, że już przy samym rozpakowaniu pliku skrypt alokuje zbyt dużo pamięci serwera i jest lipa z tym. Niestety muszę proces aktualizacji odpalać z innej maszyny.

Ten post edytował mhs 22.02.2008, 10:53:40

[sardpal]

spokojnie to przemyśl...

jeżeli wysilisz się, i napiszesz (nawet niezbyt wydajne) skrypty w PHP (lub innym narzędziu) które na podstawie danych w XML wygenerują odpowiednie pliki CSV - oddzielny plik dla każdej aktualizowanej tabeli...
to wtedy możesz (też w PHP lub innym języku) pętlą przelecieć się po tych tabelkach i zrobić te kilka / kilkanaście LOAD DATA INFILE w odpowiedniej kolejności, żeby nie naruszyć integralności danych w bazie...


a jaka z tego wszystkiego korzyść ? w czasie, kiedy serwer PHP będzie sobie mielił te pliki (XML -> CSV) masz zapewniony normalny dostęp userów do bazy - bo baza w ogóle nie będzie obciążona... za to sam proces aktualizacji danych bazie skróci się znacząco (przy tych ilościach danych o których piszesz nie powinien przekraczać kilku - kilunastu minut)...

wiem, że nie jest to łatwe rozwiązanie, ale nie widzę innej prostej możliwości - może znajdzie się i wypowie ktoś bardziej doświadczony...

jak nie znajde innego rozwiazania problemu faktycznie trzeba bedzie sie nad tym zastanowic

możesz wybrać jeszcze inną drogę- poszatkuj te dane na małe porcje (kilka - kilkanaście rekordów) i aktualizuj wstawiając przerwę 2-3 sekundy miedzy kolejnymi porcjami. co prawda cały proces ci się znacznie wydłuży (byle zmieścić się w 24 h) ale za to w międzyczasie userzy będa mieli swobodny dostęp do bazy...

Wlasnie takie rozwiazanie jest zastosowane jednak nie zdaje ono zabardzo egzaminu, musialy by byc naprawde bardzo male porcje a wowczas ten import trwal by caly dzien albo i dluzej.... A pliki z aktualnymi ofertami sa codziennie nowe

@sardpal - a ten proces importu masz całkowicie zautomatyzowany? Dokładnie chodzi o pobieranie danych z merlina (czyli zipa); jego rozpakowanie i późniejsze sparsowanie xml'a i wrzucenie danych do bazy danych?

nie jest calkowicie zautomatyzowany. Klient wgrywa sobie pliki a z crona odpalam skrypty, ktore dziela pliki na male kawalki i dla kazdego kawalka jest odpalany import po koleji

Ja wszystko co mam całkowicie zautomatyzowane, ale niestety ponieważ strona stoi hostingu współdzielonym mam problem z tym, że już przy samym rozpakowaniu pliku skrypt alokuje zbyt dużo pamięci serwera i jest lipa z tym. Niestety muszę proces aktualizacji odpalać z innej maszyny.

tez mialem ten problem dlatego zastosowalem rozwiazanie o ktorym wyzej napisalem

[netmare]

Ja coprawda nie znam się na MYSQL-u, ale poprę tutaj nevt'a. W postgresie COPY obsługujące pliki csv ładuje w klika sekund (u mnie około 3) ponad 10 MB tekstu więc w MYSQL-u napewno działa to podobnie. Coś mi się nie wydaje, żeby userzy mogli na tym ucierpieć.

[mhs]

nie jest calkowicie zautomatyzowany. Klient wgrywa sobie pliki a z crona odpalam skrypty, ktore dziela pliki na male kawalki i dla kazdego kawalka jest odpalany import po koleji
tez mialem ten problem dlatego zastosowalem rozwiazanie o ktorym wyzej napisalem

czyli rozumiem, że dzielisz plik xml'a na części i dopiero wówczas wrzucasz zawartość do bazy danych?

Generalnie muszę u siebie rozwiązać to w pełni automatycznie, gdyż u mnie nie ma kto wrzucać plików na serwer i też nie bardzo mam koncepcje na to wszystko.

[sardpal]

czyli rozumiem, że dzielisz plik xml'a na części i dopiero wówczas wrzucasz zawartość do bazy danych?

tak sa wrzucane rozpakowane pliki z merlina skrypt robi reszte

[mhs]

możesz mi pokazać, w jaki sposób dzielisz xml'a za pomocą skryptu na mniejsze części? (jeżeli dobrze Cię zrozumiałem)

[sardpal]

możesz mi pokazać, w jaki sposób dzielisz xml'a za pomocą skryptu na mniejsze części? (jeżeli dobrze Cię zrozumiałem)

Chodzi Ci o sama idee czy kod ? (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[mhs]

Chodzi mi o kod jakim rozbijasz xml'a (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[sardpal]

Chodzi mi o kod jakim rozbijasz xml'a (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

W tej chwili jestem w pracy i nie mam dostepu do zrodel (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) . Na weekendzie postaram sie cos wkleic

[mhs]

Będę wdzięczny, gdyż muszę popracować nad tym co w chwili obecnej mam.

[sardpal]

Teraz mi przyszlo do glowy takie rozwiazanie: Importowac oferty do jednego "kompletu" tabel. Nastepnie po zakonczeniu importu robic kopie tych tabel z do drugiego "kompletu" z ktorych bedzie korzystal serwis od strony uzytkownika. Napewno ma to mankamenty ale jakims rozwiazaniem jest, dobrze mysle? Usuniecie starych kopii i utworzenie nowych potrwa kilka minut ale w porownaniu z 3-4 godz jest to znaczaca poprawa...

[dr_bonzo]

eee, uzywasz tabel MyISAM?
bo w INNODB masz lepsze zarzadzanie tranzakcjami - i mozesz jednoczesnie dodawac nowe rekordy (w osobnej transakcji), a userzy beda mieli jednoczesnie dostep do pozostalych rekordow (ktorych: okreslach poziomami izolacji transakcji, np. tylko tych przed importem danych, http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html )

[sardpal]

Uzywam wlasnie MYISAM wiec transakcje odpadaja, nie przewidzialem ze serwis bedzie sie tak rozrastal zwlaszcza jesli chodzi o ilos ofert i ze beda takie problemy podczas importu. Zostane pewno przy tym rozwiazaniu ktore napisalem. A nastepnym razem pewnie blizej sie przygladne mozliwosciom INNODB.

[mhs]

Uzywam wlasnie MYISAM wiec transakcje odpadaja, nie przewidzialem ze serwis bedzie sie tak rozrastal zwlaszcza jesli chodzi o ilos ofert i ze beda takie problemy podczas importu. Zostane pewno przy tym rozwiazaniu ktore napisalem. A nastepnym razem pewnie blizej sie przygladne mozliwosciom INNODB.

Sama konwersja tabel z MyISAM do InnoDB nie jest wielkim problemem.

[SQL] pobierz, plaintext 
ALTER TABLE nazwaTabeli ENGINE = InnoDB;
[SQL] pobierz, plaintext 

A wracając jeszcze do merlina - to wiem, że osobiście zrobiłem taki błąd, że skopiowałem wszystkie dane, które przekazują do tabel u mnie w systemie. W rzeczywistości nie są one wszystkie wykorzystywane w serwisie i w jakimś stopniu obciążają import danych.