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...).

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

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...

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.

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...

@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.

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



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



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

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ć.

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.

tak sa wrzucane rozpakowane pliki z merlina skrypt robi reszte

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 ?

Chodzi mi o kod jakim rozbijasz xml'a

W tej chwili jestem w pracy i nie mam dostepu do zrodel . Na weekendzie postaram sie cos wkleic

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

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...

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 )

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.