Co jest szybsze: wiele pojedynczych wkładek czy jedna wkładka wielowarstwowa?

Wiem, że odpowiadam na to pytanie prawie dwa i pół roku po tym, jak zostało zadane, ale chciałem tylko dostarczyć kilka twardych danych z projektu, nad którym obecnie pracuję, który pokazuje, że rzeczywiście wykonywanie wielu bloków wartości na insert jest dużo szybsze niż sekwencyjne pojedyncze instrukcje wstawiania bloków wartości.

Kod, który napisałem dla tego benchmarka w C# używa ODBC do odczytu danych ze źródła danych MSSQL (~19 000 wierszy, wszystkie są odczytywane przed rozpoczęciem zapisu), a MySql. NET connector (Mysql.Data.* ) do wstawiania danych z pamięci do tabeli na serwerze MySQL za pomocą gotowych instrukcji. Został napisany w taki sposób, aby umożliwić mi dynamiczne dostosowywanie liczby bloków wartości na przygotowaną wstawkę (tj. Wstaw n wierszy na raz, gdzie mogłem dostosować wartość n przed uruchomieniem.) Przeprowadziłem również test wiele razy dla każdego n.

Wykonywanie pojedynczych bloków wartości (np. 1 wiersz na raz)trwało 5,7 - 5,9 sekund. Pozostałe wartości to "follows": {]}

2 wiersze na raz: 3,5-3,5 sekundy
5 wierszy na raz: 2,2-2,2 sekundy
10 wierszy na raz: 1,7-1,7 sekundy
50 wierszy na raz: 1.17-1.18 sekund
100 wierszy na raz: 1,1-1,4 sekundy
500 wierszy na raz: 1,1-1,2 sekundy
1000 wierszy na raz: 1.17-1.17 sekund

Więc tak, nawet samo Łączenie 2 lub 3 zapisów razem zapewnia dramatyczną poprawę szybkości (runtime cut o współczynnik n), dopóki nie dojdziesz do czegoś między n = 5 A n = 10, w którym momencie poprawa spada znacznie, a gdzieś w zakresie n = 10 do n = 50 poprawa staje się znikoma.

Mam nadzieję, że to pomoże ludziom zdecydować, (a) czy używać idei multipreparate i (b) ile bloków wartości utworzyć na polecenie (zakładając, że chcesz pracować z danymi, które mogą być wystarczająco duże, aby wypchnąć zapytanie poza maksymalny rozmiar zapytania dla MySQL, który moim zdaniem jest 16MB domyślnie w wielu miejscach, ewentualnie większe lub mniejsze w zależności od wartości max_allowed_packet ustawiony na serwerze.)

Głównym czynnikiem będzie to, czy używasz silnika transakcyjnego i czy masz włączony autocommit.

Autocommit jest domyślnie włączony i prawdopodobnie chcesz go zostawić włączony; dlatego każda wstawiona przez Ciebie wstawka wykonuje swoją własną transakcję. Oznacza to, że jeśli wykonasz jedną wstawkę na wiersz, będziesz zatwierdzać transakcję dla każdego wiersza.

Zakładając pojedynczy wątek, oznacza to, że serwer musi zsynchronizować niektóre dane z dysku dla każdego wiersza. Musi poczekać na dane osiągnąć trwałe miejsce przechowywania (miejmy nadzieję, że pamięć RAM z baterią w kontrolerze RAID). Jest to z natury raczej powolne i prawdopodobnie stanie się czynnikiem ograniczającym w tych przypadkach.

Oczywiście zakładam, że używasz silnika transakcyjnego (Zwykle innodb) i że nie zmieniłeś ustawień, aby zmniejszyć trwałość.

Zakładam również, że używasz jednego wątku do wykonania tych wstawek. Korzystanie z wielu wątków trochę mętnieje, ponieważ niektóre wersje MySQL ma grupę roboczą-commit w innodb-oznacza to, że wiele wątków wykonujących własne commity może udostępnić jeden zapis do dziennika transakcji, co jest dobre, ponieważ oznacza to mniej synchronizacji z trwałą pamięcią masową.

Z drugiej strony, wynik jest taki, że naprawdę chcesz używać wkładek wielorzędowych.

Istnieje limit, po przekroczeniu którego staje się przeciwny do produktywności, ale w większości przypadków wynosi co najmniej 10 000 wierszy. Więc jeśli wsadzisz je do 1000 rzędów, prawdopodobnie jesteś bezpieczny.

Jeśli używasz MyISAM, jest mnóstwo innych rzeczy, ale nie będę cię nimi zanudzał. Pokój.

Wyślij jak najwięcej wstawek na drucie jednocześnie. Rzeczywista prędkość wstawiania powinna być taka sama, ale zobaczysz wzrost wydajności dzięki zmniejszeniu napowietrzności sieci.

Ogólnie im mniejsza liczba wywołań do bazy danych, tym lepiej (czyli szybciej, wydajniej), więc staraj się kodować wstawki w taki sposób, aby zminimalizować dostęp do bazy danych. Pamiętaj, że jeśli nie używasz puli połączeń, każdy dostęp do bazy danych musi utworzyć połączenie, wykonać sql, a następnie zerwać połączenie. Sporo nad głową!

Możesz chcieć:

• sprawdź, czy automatyczne zatwierdzanie jest wyłączone
• Otwarte Połączenie
• wysyłanie wielu partii wstawek w jednej transakcji (rozmiar około 4000-10000 wierszy ? widzisz)
• Close connection

W zależności od tego, jak dobrze skaluje się twój serwer (jego definitywnie ok z PostgreSQl, Oracle i MSSQL), wykonaj powyższe czynności z wieloma wątkami i wieloma połączeniami.

MYSQL 5.5 Jedna instrukcja SQL insert zajęła od ~300 do ~450ms. podczas gdy poniższe statystyki dotyczą wielu wbudowanych Stanów wstawiania.

(25492 row(s) affected)
Execution Time : 00:00:03:343
Transfer Time  : 00:00:00:000
Total Time     : 00:00:03:343

Powiedziałbym, że inline jest do zrobienia :)

Ogólnie rzecz biorąc, wiele wstawek będzie wolniejsze ze względu na narzut połączenia. Wykonywanie wielu wkładek na raz zmniejszy koszty napowietrznych na wkładkę.

W zależności od języka, którego używasz, możesz utworzyć wsad w swoim języku programowania/skryptów przed przejściem do db i dodać każdą wstawkę do wsadu. Wtedy będziesz mógł wykonać dużą partię za pomocą jednej operacji connect. Oto przykład w Javie.

Wyłączenie sprawdzania ograniczeń powoduje, że wstawki są znacznie szybsze. Nie ma znaczenia, czy twój stół go ma, czy nie. Na przykład test wyłączania kluczy obcych i ciesz się szybkością:

SET FOREIGN_KEY_CHECKS=0;

To śmieszne jak źle Mysql i MariaDB są zoptymalizowane jeśli chodzi o wstawki. Testowałem mysql 5.7 i mariadb 10.3, nie ma na nich realnej różnicy.

Testowałem to na serwerze z dyskami NVME, 70,000 IOPS, przepustowością 1,1 GB/s seq i jest to możliwe full duplex (odczyt i zapis).
Serwer jest również serwerem o wysokiej wydajności.
Dałem mu 20 GB PAMIĘCI ram.
Baza danych jest całkowicie pusta.

Prędkość jaką otrzymuję to 5000 wstawek na sekundę podczas wykonywania multi wstawianie wierszy (próbowałem z kawałkami danych od 1mb do 10MB)

Teraz wskazówka:
Jeśli dodam kolejny wątek i wstawię do tych samych tabel nagle mam 2x5000 / sek. Jeszcze jeden wątek i mam 15000 total /sec

Rozważ to: podczas wstawiania jednego wątku oznacza to, że możesz sekwencyjnie zapisywać na dysk (z wyjątkiem indeksów). Podczas korzystania z wątków faktycznie pogarszasz możliwą wydajność, ponieważ teraz trzeba zrobić o wiele więcej losowych dostępów. Ale rzeczywistość sprawdzenie pokazuje, że mysql jest tak źle zoptymalizowany, że wątki bardzo pomagają.

Rzeczywista wydajność z takim serwerem to prawdopodobnie miliony na sekundę, procesor jest bezczynny, dysk jest bezczynny.
Powodem jest dość oczywiste, że mariadb tak jak mysql ma wewnętrzne opóźnienia.