Używanie MySQL 5.6 z silnikiem pamięci InnoDB dla większości tabel. Wielkość puli buforów InnoDB wynosi 15 GB, a indeksy Innodb DB + wynoszą około 10 GB. Serwer ma 32 GB pamięci RAM i działa w systemie Cent OS 7 x64.

Mam jeden duży stół, który zawiera około 10 milionów + rekordów.

Otrzymuję zaktualizowany plik zrzutu ze zdalnego serwera co 24 godziny. Plik ma format csv. Nie mam kontroli nad tym formatem. Plik ma rozmiar ~ 750 MB. Próbowałem wstawiać dane do tabeli MyISAM wiersz po rzędzie i zajęło to 35 minut.

Muszę pobrać tylko 3 wartości na linię z 10-12 z pliku i zaktualizować go w bazie danych.

Jaki jest najlepszy sposób na osiągnięcie czegoś takiego?

Muszę to robić codziennie.

Obecnie Flow wygląda następująco:

1. mysqli_begin_transaction
2. Czytaj zrzut pliku linia po linii
3. Zaktualizuj każdy rekord linia po linii.
4. mysqli_commit

Powyższe operacje trwają około 30-40 minut, a przy tym trwają inne aktualizacje, które dają mi

Przekroczono limit czasu oczekiwania na zablokowanie; spróbuj ponownie uruchomić transakcję

Aktualizacja 1

ładowanie danych w nowej tabeli za pomocą LOAD DATA LOCAL INFILE. W MyISAM zajęło to 38.93 secw InnoDB 7 min. 5,21 sek. Potem zrobiłem:

UPDATE table1 t1, table2 t2
SET 
t1.field1 = t2.field1,
t1.field2 = t2.field2,
t1.field3 = t2.field3
WHERE t1.field10 = t2.field10

Query OK, 434914 rows affected (22 hours 14 min 47.55 sec)

Aktualizacja 2

ta sama aktualizacja z zapytaniem dołączenia

UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4

(14 hours 56 min 46.85 sec)

Wyjaśnienia na podstawie pytań w komentarzach:

• Plik zaktualizuje około 6% wierszy w tabeli, ale czasem może to być nawet 25%.
• Są aktualizowane indeksy pól. Tabela zawiera 12 indeksów, a 8 indeksów zawiera pola aktualizacji.
• Aktualizacja nie jest konieczna w ramach jednej transakcji. Może to zająć trochę czasu, ale nie więcej niż 24 godziny. Chcę to zrobić w ciągu 1 godziny bez blokowania całej tabeli, ponieważ później muszę zaktualizować indeks sfinksa, który jest zależny od tej tabeli. Nie ma znaczenia, czy kroki trwają dłużej, o ile baza danych jest dostępna dla innych zadań.
• Mógłbym zmodyfikować format csv w kroku wstępnego przetwarzania. Liczy się tylko szybka aktualizacja i bez blokowania.
• Tabela 2 to MyISAM. Jest to nowo utworzona tabela z pliku csv przy użyciu pliku danych ładowania. Rozmiar pliku MYI wynosi 452 MB. Tabela 2 jest indeksowana w kolumnie field1.
• MYD tabeli MyISAM wynosi 663 MB.

Aktualizacja 3:

oto więcej szczegółów na temat obu tabel.

CREATE TABLE `content` (
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `og_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `keywords` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
  `files` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `category` smallint(3) unsigned NOT NULL DEFAULT '600',
  `size` bigint(19) unsigned NOT NULL DEFAULT '0',
  `downloaders` int(11) NOT NULL DEFAULT '0',
  `completed` int(11) NOT NULL DEFAULT '0',
  `uploaders` int(11) NOT NULL DEFAULT '0',
  `creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `vote_up` int(11) unsigned NOT NULL DEFAULT '0',
  `vote_down` int(11) unsigned NOT NULL DEFAULT '0',
  `comments_count` int(11) NOT NULL DEFAULT '0',
  `imdb` int(8) unsigned NOT NULL DEFAULT '0',
  `video_sample` tinyint(1) NOT NULL DEFAULT '0',
  `video_quality` tinyint(2) NOT NULL DEFAULT '0',
  `audio_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
  `subtitle_lang` varchar(127) CHARACTER SET ascii NOT NULL DEFAULT '',
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `uploader` int(11) unsigned NOT NULL DEFAULT '0',
  `anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
  `scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`record_num`),
  UNIQUE KEY `hash` (`hash`),
  KEY `uploaders` (`uploaders`),
  KEY `tfile_size` (`tfile_size`),
  KEY `enabled_category_upload_date_verified_` (`enabled`,`category`,`upload_date`,`verified`),
  KEY `enabled_upload_date_verified_` (`enabled`,`upload_date`,`verified`),
  KEY `enabled_category_verified_` (`enabled`,`category`,`verified`),
  KEY `enabled_verified_` (`enabled`,`verified`),
  KEY `enabled_uploader_` (`enabled`,`uploader`),
  KEY `anonymous_uploader_` (`anonymous`,`uploader`),
  KEY `enabled_uploaders_upload_date_` (`enabled`,`uploaders`,`upload_date`),
  KEY `enabled_verified_category` (`enabled`,`verified`,`category`),
  KEY `verified_enabled_category` (`verified`,`enabled`,`category`)
) ENGINE=InnoDB AUTO_INCREMENT=7551163 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED


CREATE TABLE `content_csv_dump_temp` (
  `hash` char(40) CHARACTER SET ascii NOT NULL DEFAULT '',
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) unsigned NOT NULL DEFAULT '0',
  `uploaders` int(11) unsigned NOT NULL DEFAULT '0',
  `downloaders` int(11) unsigned NOT NULL DEFAULT '0',
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

a oto zapytanie o aktualizację, które aktualizuje contenttabelę przy użyciu danych zcontent_csv_dump_temp

UPDATE content a JOIN content_csv_dump_temp b 
ON a.hash = b.hash 
SET 
a.uploaders = b.uploaders,
a.downloaders = b.downloaders,
a.verified = b.verified

aktualizacja 4:

wszystkie powyższe testy przeprowadzono na maszynie testowej., ale teraz zrobiłem te same testy na maszynie produkcyjnej, a zapytania są bardzo szybkie.

mysql> UPDATE content_test a JOIN content_csv_dump_temp b
    -> ON a.hash = b.hash
    -> SET
    -> a.uploaders = b.uploaders,
    -> a.downloaders = b.downloaders,
    -> a.verified = b.verified;
Query OK, 2673528 rows affected (7 min 50.42 sec)
Rows matched: 7044818  Changed: 2673528  Warnings: 0

Przepraszam za mój błąd. Lepiej używać łączenia zamiast każdej aktualizacji rekordu. teraz próbuję ulepszyć mpre przy użyciu indeksu sugerowanego przez rick_james, zaktualizuje się po zakończeniu benchmarkingu.

W oparciu o moje doświadczenie wykorzystam LOAD DATA INFILE do zaimportowania twojego pliku CSV.

Instrukcja LOAD DATA INFILE odczytuje wiersze z pliku tekstowego do tabeli z bardzo dużą prędkością.

Przykład znalazłem w internetowym przykładzie ładowania danych . Przetestowałem ten przykład na moim pudełku i działałem dobrze

Przykładowa tabela

CREATE TABLE example (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Column2` varchar(14) NOT NULL,
  `Column3` varchar(14) NOT NULL,
  `Column4` varchar(14) NOT NULL,
  `Column5` DATE NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB

Przykładowy plik CSV

# more /tmp/example.csv
Column1,Column2,Column3,Column4,Column5
1,A,Foo,sdsdsd,4/13/2013
2,B,Bar,sdsa,4/12/2013
3,C,Foo,wewqe,3/12/2013
4,D,Bar,asdsad,2/1/2013
5,E,FOObar,wewqe,5/1/2013

Instrukcja importu do uruchomienia z konsoli MySQL

LOAD DATA LOCAL INFILE '/tmp/example.csv'
    -> INTO TABLE example
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id, Column3,Column4, @Column5)
    -> set
    -> Column5 = str_to_date(@Column5, '%m/%d/%Y');

Wynik

MySQL [testcsv]> select * from example;
+----+---------+---------+---------+------------+
| Id | Column2 | Column3 | Column4 | Column5    |
+----+---------+---------+---------+------------+
|  1 |         | Column2 | Column3 | 0000-00-00 |
|  2 |         | B       | Bar     | 0000-00-00 |
|  3 |         | C       | Foo     | 0000-00-00 |
|  4 |         | D       | Bar     | 0000-00-00 |
|  5 |         | E       | FOObar  | 0000-00-00 |
+----+---------+---------+---------+------------+

IGNORE po prostu ignoruje pierwszy wiersz, który jest nagłówkiem kolumny.

Po IGNORE określamy kolumny (pomijanie kolumny 2), które mają zostać zaimportowane, co odpowiada jednemu z kryteriów w pytaniu.

Oto kolejny przykład bezpośrednio z Oracle: LOAD DATA INFILE przykład

To powinno wystarczyć, aby zacząć.

W świetle wszystkich wymienionych rzeczy wygląda na to, że wąskim gardłem jest samo połączenie.

ASPEKT nr 1: Dołącz rozmiar bufora

Najprawdopodobniej twój rozmiar join_buffer_size jest prawdopodobnie zbyt niski.

Zgodnie z dokumentacją MySQL na temat tego, jak MySQL używa pamięci podręcznej buforu dołączania

Przechowujemy tylko używane kolumny w buforze łączenia, a nie całe wiersze.

W takim przypadku klucze bufora łączenia pozostaną w pamięci RAM.

Masz 10 milionów wierszy razy 4 bajty na każdy klucz. To około 40 milionów.

Spróbuj podnieść go w sesji do 42 milionów (trochę więcej niż 40 milionów)

SET join_buffer_size = 1024 * 1024 * 42;
UPDATE table1 a JOIN table2 b 
ON a.field1 = b.field1 
SET 
a.field2 = b.field2,
a.field3 = b.field3,
a.field4 = b.field4;

Jeśli to załatwi sprawę, przejdź do dodawania tego my.cnf

[mysqld]
join_buffer_size = 42M

Ponowne uruchomienie mysqld nie jest wymagane w przypadku nowych połączeń. Po prostu biegnij

mysql> SET GLOBAL join_buffer_size = 1024 * 1024 * 42;

ASPEKT 2: Dołącz do operacji

Możesz manipulować stylem operacji łączenia, modyfikując optymalizator

Zgodnie z dokumentacją MySQL na temat blokowania połączeń w pętli zagnieżdżonej i złączeń dostępu do klucza wsadowego

Gdy używana jest BKA, wartość join_buffer_size określa, jak duża jest partia kluczy w każdym żądaniu do silnika pamięci masowej. Im większy bufor, tym bardziej sekwencyjny dostęp będzie do prawej tabeli operacji łączenia, co może znacznie poprawić wydajność.

Aby można było użyć BKA, flaga batched_key_access zmiennej systemowej optimizer_switch musi być ustawiona na on. BKA używa MRR, więc flaga mrr musi być również włączona. Obecnie szacowanie kosztów MRR jest zbyt pesymistyczne. Dlatego konieczne jest również wyłączenie mrr_cost_based, aby można było użyć BKA.

Ta sama strona zaleca robienie tego:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

ASPEKT 3: Zapisywanie aktualizacji na dysku (OPCJONALNIE)

Większość zapomina zwiększyć innodb_write_io_threads, aby szybciej zapisywać brudne strony z puli buforów.

[mysqld]
innodb_write_io_threads = 16

Będziesz musiał ponownie uruchomić MySQL dla tej zmiany

SPRÓBUJ !!!

1. CREATE TABLE który pasuje do CSV
2. LOAD DATA do tego stołu
3. UPDATE real_table JOIN csv_table ON ... SET ..., ..., ...;
4. DROP TABLE csv_table;

Krok 3 będzie znacznie szybszy niż rząd po rzędzie, ale nadal zablokuje wszystkie wiersze w tabeli na niebanalny czas. Jeśli ten czas blokady jest ważniejszy niż czas trwania całego procesu, ...

Jeśli nic więcej nie pisze do stołu, to ...

1. CREATE TABLEktóry pasuje do CSV; Indeksy nie tylko to, co jest potrzebne w JOINw UPDATE. Jeśli jest wyjątkowy, zrób toPRIMARY KEY .
2. LOAD DATA do tego stołu
3. skopiuj real_tabledo new_table( CREATE ... SELECT)
4. UPDATE new_table JOIN csv_table ON ... SET ..., ..., ...;
5. RENAME TABLE real_table TO old, new_table TO real_table;
6. DROP TABLE csv_table, old;

Krok 3 jest szybszy niż aktualizacja, zwłaszcza jeśli niepotrzebne indeksy zostaną pominięte.
Krok 5 jest „natychmiastowy”.

Powiedziałeś:

• Aktualizacje wpływają na 6-25% twojego stołu
• Chcesz to zrobić tak szybko, jak to możliwe (<1 godz.)
• bez blokady
• nie musi to być jedna transakcja
• jednak (w komentarzu do odpowiedzi Ricka Jamesa) wyrażasz zaniepokojenie warunkami wyścigu

Wiele z tych stwierdzeń może być sprzecznych. Na przykład duże aktualizacje bez blokowania stołu. Lub unikanie warunków wyścigowych bez użycia jednej wielkiej transakcji.

Ponieważ tabela jest mocno indeksowana, wstawianie i aktualizacje mogą być powolne.

Unikanie warunków wyścigu

Jeśli możesz dodać zaktualizowany znacznik czasu do swojego stołu, możesz rozwiązać warunki wyścigu, unikając jednocześnie rejestrowania pół miliona aktualizacji w jednej transakcji.

Dzięki temu możesz przeprowadzać aktualizacje linia po linii (tak jak obecnie), ale z automatycznym zatwierdzaniem lub bardziej rozsądnymi partiami transakcji.

Unikasz warunków wyścigu (podczas aktualizacji linia po linii), sprawdzając, czy późniejsza aktualizacja jeszcze nie nastąpiła (UPDATE ... WHERE pk = [pk] AND updated < [batchfile date] )

Co ważne, umożliwia to uruchamianie równoległych aktualizacji.

Działa tak szybko, jak to możliwe - Równoległe

Po sprawdzeniu tego znacznika czasu:

1. Podziel plik wsadowy na porcje o rozsądnych rozmiarach (powiedzmy 50 000 wierszy / plik)
2. Równolegle poproś o odczytanie skryptu w każdym pliku i wygenerowanie pliku zawierającego 50 000 instrukcji UPDATE.
3. Równolegle, po (2) zakończeniu, mysqluruchom każdy plik SQL.

(np bashspojrzenie spliti xargs -Psposobów, aby łatwo uruchomić polecenie wieloma względami równolegle. Stopień równoległości zależy ile wątków jesteś gotów poświęcić do aktualizacji )

Duże aktualizacje są związane z operacjami we / wy. Sugerowałbym:

1. Utwórz osobną tabelę, w której będą przechowywane 3 często aktualizowane pola. Nazwijmy jedną tabelę asset_static, w której przechowujesz, cóż, dane statyczne, a drugą asset_dynamic, która będzie przechowywać przesyłające, pobierające i zweryfikowane.
2. Jeśli możesz, użyj silnika MEMORY dla tabeli asset_dynamic . (kopia zapasowa na dysk po każdej aktualizacji).
3. Zaktualizuj swój lekki i zwrotny asset_dynamic zgodnie z aktualizacją 4 (tj. ZAŁADUJ INFILE ... INTO temp; AKTUALIZACJA asset_dynamic a JOIN temp b na a.id = b.id SET [co musi zostać zaktualizowane]. Powinno to zająć mniej niż minuta (w naszym systemie, asset_dynamic ma wierszy, a aktualizacje wpływają na około 6 wierszy w nieco ponad 40 .)
4. Po uruchomieniu indeksatora Sphinx JOIN asset_static i asset_dynamic (zakładając, że chcesz użyć jednego z tych pól jako atrybutu).

Aby UPDATEszybko biegać, potrzebujesz

INDEX(uploaders, downloaders, verified)

Może być na dowolnym stole. Trzy pola mogą być w dowolnej kolejności.

Ułatwi to UPDATE to szybkie dopasowanie wierszy między dwiema tabelami.

I uczyń typy danych takie same w dwóch tabelach (obu INT SIGNEDlub obu INT UNSIGNED).