Pracuję nad aplikacją, która wymaga dużo zapisów do bazy danych, około 70% wstawek i 30% odczytów. Ten stosunek obejmuje także aktualizacje, które uważam za jeden odczyt i jeden zapis. Poprzez instrukcje wstawiania wielu klientów wstawia dane do bazy danych za pomocą instrukcji insert poniżej:

$mysqli->prepare("INSERT INTO `track` (user, uniq_name, ad_name, ad_delay_time ) values (?, ?, ?, ?)");

Pytanie brzmi: czy powinienem użyć albo insert_delay, czy też mechanizmu mysqli_multi_query , ponieważ instrukcja insert wykorzystuje ~ 100% procesora na serwerze. Korzystam z silnika InnoDB w mojej bazie danych, więc wstawianie z opóźnieniem nie jest możliwe. Wstawienie na serwerze wynosi ~ 36k / h i odczyt 99,89%, również używam instrukcji select, aby pobrać dane siedem razy w pojedynczym zapytaniu , wykonanie tego zapytania zajmuje 150 sekund na serwerze. Jakiej techniki lub mechanizmu mogę użyć do tego zadania? Moja pamięć serwera to 2 GB, czy powinienem rozszerzyć pamięć ?. Spójrz na ten problem, wszelkie sugestie będą mi wdzięczne.

Struktura stołu:

+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| user            | varchar(100) | NO   |     | NULL              |                |
| uniq_name       | varchar(200) | NO   |     | NULL              |                |
| ad_name         | varchar(200) | NO   |     | NULL              |                |
| ad_delay_time   | int(11)      | NO   |     | NULL              |                |
| track_time      | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+

Obecny stan mojej bazy danych pokazuje 41k wstawień (zapisów), co jest bardzo wolne dla mojej bazy danych.

Ponieważ masz więcej zapisów niż odczytów, chciałbym polecić następujące

Kluczem będzie przyzwoite strojenie InnoDB

Pula buforów (rozmiar: innodb_buffer_pool_size )

Ponieważ InnoDB nie obsługuje INSERT DELAYED , korzystanie z dużej puli buforów InnoDB jest najbliższą rzeczą, jaką można dostać do INSERT DELAYED. Wszystkie DML (INSERT, UPDATE i DELETE) byłyby buforowane w puli buforów InnoDB. Informacje transakcyjne dotyczące zapisów są natychmiast zapisywane w dziennikach ponawiania (ib_logfile0, ib_logfile1). Zapisy publikowane w puli buforów są okresowo opróżniane z pamięci na dysk przez ibdata1 (InsertBuffer dla indeksów pomocniczych, podwójny bufor zapisu). Im większa pula buforów, tym większa ilość INSERTów może być buforowana. W systemie z 8 GB lub więcej pamięci RAM użyj 75–80% pamięci RAM jako rozmiar innodb_buffer_pool_size. W systemie z bardzo małą pamięcią RAM, 25% (w celu dostosowania do systemu operacyjnego).

CAVEAT: Możesz ustawić innodb_doublewrite na 0, aby przyspieszyć zapisywanie jeszcze bardziej, ale ryzykujesz integralnością danych. Możesz także przyspieszyć, ustawiając innodb_flush_method na O_DIRECT, aby zapobiec buforowaniu InnoDB w systemie operacyjnym.

Ponów dzienniki (rozmiar: innodb_log_file_sile )

Domyślnie dzienniki ponawiania mają nazwy ib_logfile0 i ib_logfile1 i będą miały po 5 MB. Rozmiar powinien wynosić 25% wielkości innodb_buffer_pool_size. Jeśli dzienniki ponawiania już istnieją, dodaj nowe ustawienie w my.cnf, zamknij mysql, usuń je i uruchom ponownie mysql .

Bufor dziennika (rozmiar: innodb_log_buffer_size )

Bufor dziennika przechowuje zmiany w pamięci RAM przed przepłukaniem ich do dzienników ponownych. Domyślnie jest to 8M. Im większy bufor dziennika, tym mniej dyskowych operacji we / wy. Zachowaj ostrożność przy bardzo dużych transakcjach, ponieważ może to spowolnić COMMIT o milisekundy.

Dostęp do wielu procesorów

MySQL 5.5 i wtyczka MySQL 5.1 InnoDB mają ustawienia umożliwiające silnikowi pamięci InnoDB dostęp do wielu procesorów. Oto opcje, które musisz ustawić:

• innodb_thread_concurrency określa górną granicę liczby współbieżnych wątków, które InnoDB może utrzymywać otwarte. Zazwyczaj zaleca się ustawienie na to (2 x liczba procesorów) + liczba dysków. W ubiegłym roku nauczyłem się z pierwszej ręki na konferencji w Nowym Jorku w Percona, że ​​należy ustawić tę wartość na 0, aby zaalarmować silnik pamięci InnoDB o znalezieniu najlepszej liczby wątków dla środowiska, w którym działa.
• innodb_concurrency_tickets ustawia liczbę wątków, które mogą bezkarnie ominąć sprawdzanie współbieżności. Po osiągnięciu tego limitu sprawdzanie współbieżności wątków ponownie staje się normą.
• innodb_commit_concurrency ustawia liczbę jednoczesnych transakcji, które można zatwierdzić. Ponieważ wartością domyślną jest 0, brak ustawienia tej opcji umożliwia jednoczesne zatwierdzenie dowolnej liczby transakcji.
• innodb_thread_sleep_delay ustawia liczbę milisekund, w których wątek InnoDB może zostać uśpiony przed ponownym wprowadzeniem kolejki InnoDB. Domyślnie jest to 10000 (10 sekund).
• innodb_read_io_threads (ustaw na 3000) i innodb_write_io_threads (ustaw na 7000) (oba od czasu MySQL 5.1.38) przydzielają określoną liczbę wątków do odczytu i zapisu. Domyślnie jest to 4, a maksymalna to 64. Ustaw je na 64. Ponadto ustaw innodb_io_capacity na 10000.

Zaktualizuj do MySQL 5.5

Jeśli masz MySQL 5.0, uaktualnij do MySQL 5.5. Jeśli masz MySQL 5.1.37 lub starszą wersję, zaktualizuj do MySQL 5.5. Jeśli masz MySQL 5.1.38 lub nowszy i chcesz pozostać w MySQL 5.1, zainstaluj wtyczkę InnoDB. W ten sposób możesz wykorzystać wszystkie procesory dla InnoDB.

INT (2) nadal używa 4 bajtów - może miałeś na myśli TINYINT UNSIGNED?

Ile różnych wartości w setno? Jeśli jest mały, KLUCZ (setno) nigdy nie będzie używany. INSERTing musi zaktualizować ten indeks; usunięcie KLUCZA przyspieszy WKŁADANIE niektórych.

CHAR (10) - Czy flagzawsze ma 10 znaków? A w utf8? Być może mógłbyś użyć flagi VARCHAR (10) CHARACTER SET ascii

Partie wkładek - 100 na raz będzie działać 10 razy szybciej. (Ponad 100 osiąga „malejące zyski”).

Jaka jest wartość automatycznego zatwierdzania? Czy zawijasz każdy WSTAW W POCZĄTKU… COMMIT? Jaka jest wartość innodb_flush_log_at_trx_commit?

Ustaw kolejkę. Aplikacja zapisywałaby w wierszu 1 wiersz na raz, a następnie wyjmowałaby wiersze i wstawiała do bazy danych wsadowo na podstawie liczby wierszy w stosunku do czasu upływającego od ostatniego wstawienia.

Widziałem, gdzie wsadzanie wkładek 10 000 jednocześnie jest najszybsze, więc musisz przetestować, aby znaleźć dobre miejsce.

Możesz stworzyć swój własny prosty system kolejek lub użyć istniejącego. Oto kilka przykładów: HornetQ i File :: Queue . Oto post na SE z listą innych dobrych opcji: Kolejki wiadomości w perl, php, python .