[MySQL][PHP] Przeniesienie danych z plików do MySQL, Kwestia szybkości dla INSERT Opcje

[d0m1n1k_]

Witam,
od jakiegoś czasu przymierzam się do migracji z plików na bazę danych jednego z moich pierwszych projektów.
Serwis w tym czasie nagromadził dość dużo cennych dla mnie danych i dorobił się prawie 18 GB plików tekstowych.

Dane są "ładnie" wprowadzone.
Kwestia przenoszenia do MySQL przebiega bez zgrzytów.
Ale martwi mnie sprawa szybkości.
Rekordów jest blisko 800 mln.

Poniższy skrypt mieli dość wolno, średnio 50.000 rekordów na godzinę.
Serwer jest domowy - tzn. laptop z 4GB ramu i Celeronem 2.16 Ghz...
Przy tym tempie to do przyszłego roku nie zdążę (wychodzi jakieś 16.000 godzin - 666 dni) a czas na uczelni zaczyna mnie gonić...

[PHP] pobierz, plaintext 
<?php
 
DEFINE ('DB_USER','****');
DEFINE ('DB_PASS','****');
DEFINE ('DB_HOST','****');
DEFINE ('DB_NAME','****');
 
$connection = mysql_connect (DB_HOST, DB_USER, DB_PASS) OR die
	('Error - nie połączono z bazą danych<br />' . mysql_error() );
mysql_select_db (DB_NAME) OR die ('Error - nie wybrano bazy danych<br />' . mysql_error() );
$set_typ_coding = mysql_query("SET NAMES 'utf8'");
 
set_time_limit(0);
 
$m = 0; //Liczba powtórek
 
if(isset($_GET['id'])){
	$numer = $_GET['id'];
 
	echo "Dotyczy pliku: fizyka_" . $numer . ".txt<br />";
 
	$dane = file('pliki/fizyka_' . $numer . '.txt');
	for($i=0;$max=count($dane),$i<$max;$i++){
	list($old_dane) = explode(';',$dane[$i]);
 
	$check_stats = mysql_query("SELECT `statt` FROM `stare_repozytorium` WHERE `statt` = '$old_dane'");
	if(mysql_num_rows($check_stats) > 0){
		$m++;
	}else{
		$query = mysql_query("INSERT INTO `stare_repozytorium` (`id`, `statt`, `status`, `addeddate`) VALUES (NULL, '$old_dane', '0', NOW())");
	}
 
	}
 
	echo "Duplikatów: <b><font color=\"red\">" . $m . "</font></b>";
 
}else{
	echo "Nie wybrano id pliku";
}
 
?>
[PHP] pobierz, plaintext 

Dla stabilności mojej pracy - przy "odpalaniu" potrafi przywiesić kompa na minutę - każdy plik jest wybierany przeze mnie ręcznie ($_GET['id']).
Bardzo proszę, czy może mi ktoś podpowiedzieć - co mogę zrobić aby migracja przebiegła zdecydowanie szybciej?

Ten post edytował d0m1n1k_ 21.06.2015, 23:49:05

[mmmmmmm]

LOAD + help

[tomxx]

[PHP] pobierz, plaintext 
mysql_connect
[PHP] pobierz, plaintext 

(IMG:style_emoticons/default/facepalmxd.gif)

[Tuminure]

// Bazuję na moim doświadczeniu/wiedzy, nie na faktycznych benchmarkach. Umieszczone w kolejności, od której ja bym zaczął optymalizować kod.

[PHP] pobierz, plaintext 
$check_stats = mysql_query("SELECT `statt` FROM `stare_repozytorium` WHERE `statt` = '$old_dane'");
	if(mysql_num_rows($check_stats) > 0){
		$m++;
	}else{
		$query = mysql_query("INSERT INTO `stare_repozytorium` (`id`, `statt`, `status`, `addeddate`) VALUES (NULL, '$old_dane', '0', NOW())");
	}
[PHP] pobierz, plaintext 

Select, a potem insert działa wolniej niż "insert ignore", który wymaga klucza unikalnego w tabeli. Jeżeli to możliwe to dodawaj wiele wpisów jednym insertem.

[PHP] pobierz, plaintext 
for($i=0;$max=count($dane),$i<$max;$i++){
[PHP] pobierz, plaintext 

Wyrzuć tego counta przed fora. Obecnie ten count sprawdza ilość elementów przy każdej iteracji.

[PHP] pobierz, plaintext 
$dane = file('pliki/fizyka_' . $numer . '.txt');
[PHP] pobierz, plaintext 

file_get_contents działa minimalnie szybciej. Być może warto przepisać kod, aby korzystać z tej funkcji.

[PHP] pobierz, plaintext 
DEFINE ('DB_USER','****');
DEFINE ('DB_PASS','****');
DEFINE ('DB_HOST','****');
DEFINE ('DB_NAME','****');
[PHP] pobierz, plaintext 

Zamiast definiować stałe, wpisz wartości po prostu do kodu. Mniej czytelnie, złe praktyki i takie tam ale znowu troszkę szybciej.

Wyrzuć echo.

Ten post edytował Tuminure 22.06.2015, 09:12:48

[d0m1n1k_]

LOAD + help

Może jakieś rozwinięcie tematu?

(IMG:style_emoticons/default/facepalmxd.gif)

Jak wyżej - czyżbym stosował aż tak zamierzchłe techniki, które nie powinny widzieć światła dziennego w naszych mrocznych czasach? Co w tym takiego strasznego?

---------------------------------------------------
Tuminure

Nigdy nie spotkałem się ze składnią INSERT IGNORE.
Tabela posiada dwa indexy: id - primary, statt - unique
Jak wykorzystać tą składnię aby nie przerywała pętli aż do zakończenia wprowadzania danych?

Count już wyrzucony w fora.

file_get_contents ładuje plik do stringa, a file do tablicy - wolę tak to pozostawić.

Za to co otrzymałem od Ciebie, dziękuję!
Ale proszę o więcej :-)

[memory]

Może jakieś rozwinięcie tematu?

google bulk insert mysql

Ten post edytował memory 22.06.2015, 10:15:05

[mmmmmmm]

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

[d0m1n1k_]

Kod zoptymalizowany i przeniesiony na serwer w firmie (na ssd, po cichu, będzie pracować nocami).
Wczoraj o 22:30 dodałem bazę, skrypt i pliki na ftp'a oraz czynność do harmonogramu zadań.
Od północy do siódmej rano, z nieco poniżej miliona rekordów na starcie, dziś po przyjściu do pracy było już 26.000.004 :-)
Maksymalnie miesiąc nocek i będzie wykonane :-)
Serwer w końcu ciągle na chodzie, a w nocy jego obciążenie nie przekracza 2%.


Dla zainteresowanych:
Laptop (Lenovo G50): Celeron N2840 2x2.16Ghz, 4GB RAM, 500GB SATAIII - wydajność przy pełnym obciążeniu ok. 50.000 rekordów / godzinę;
Serwer (Dell PE1950): 2 x Xeon DualCore 5150 2x2.66GHz, 16GB RAM, 2x60GB SSD SATAIII->SAS - wydajność przy limicie do 25% CPU i 8GB RAM ok. 2.750.000 rekordów / godzinę;

Najlepsze, że taki serwer (poza dyskami 2 x ) kosztuje mniej jak laptop i mógłby przerobić nawet 10M rekordów na godzinę nie przepracowując się...

[prz3kus]

Dziwne to troche, a co tutaj jest wąskim gardłem prcek(mieli teraz na 100%)?

[d0m1n1k_]

Nie jestem pewien co było wąskim gardłem... może sam fakt, że laptop jest laptopem ;-)
Na SSD grzeje aż miło, tylko zastanawia mnie jedno - gdzie mogę sprawdzić jaki jest limit bazy danych / tabeli? Obecnie jest już 50.000.000 wpisów i blisko 6GB danych.

Chciałem zrobić backup i...

1. Brak eksportu do sql
2. export.php: Brakuje parametru: what
3. export.php: Brakuje parametru: export_type
4. Po wejściu do zakładki Struktura, nie widać struktury bazy danych (kolumn) a jedynie możliwe działania oraz informacje o wykorzystanej przestrzeni i statystyki wiersza...
5. Po wejściu do zakładaki Wstaw niema pól do których mógłbym dodać ręcznie dodatkowe wpisy...

Dla jasności typ bazy danych to InnoDB
Wie może ktoś co jest grane?

Ten post edytował d0m1n1k_ 25.06.2015, 19:26:22