Pytanie kilka razy przewijało się przez forum. Opiszę dokładnie do czego potrzebuję, bo każdy przytpadek inny:

główne operacje na bazie: UPDATE i INSERT, rzadziej SELECT.
Ilość wpisów w bazie - na chwilę obecną ok. 5mln, docelowo sądzę, że pomiędzy 50 a 100 mln, może więcej...
Dodatkowe operacje na bazie: co jakiś czas indeksacja Sphinxem - potrzebuję bardzo szybkiego wyszukiwania pełnotekstowego, ze sphinxa też biorę dane, dlatego selecty wywoływane skryptem są bardzo rzadko (chyba że sphinx też korzysta z selecta, jeśli podaje mi wszystkie dane, których potrzebuje?)


zmieniłem silnik na InnoDB, ale wydaje mi się, że dodawanie do bazy trwa dłużej, stąd moje pytanie ;-)

W Twoim wypadku pozostałbym przy InnoDB pomiędzy tymi dwoma silnikami. MyIsam przy dodawaniu i atualizacji rekordów zakłada locka na całą tabelę, to się moze mścić przy dostępie do danych lub próbach zapisu wielu rekordów jednocześnie. InnoDB lockuje tylko rekord. To co wydaje Ci się długie to zapewne aktualizacja indeksów.

a rozwazales nosqlowe bazy ?
mongodb? kasandra?

j.

Testowo wróciłem do MyISAM i.... całość działa dużo szybciej!
Wcześniej czytałem jak działa każdy z tych silników i dlatego właśnie (brak blokowania całej tabeli) wybrałem InnoDB, jednak okazuje się, że (nie mam pojęcia dlaczego) MyISAM działa szybciej. Aktualizacja indeksów mówisz.. Być może, jednak działa to na tyle wolno, że baza zamiast nadrabiać opóźnienie w pobieraniu danych, ma coraz większe opóźnienie (coraz więcej danych w kolejce dodania do bazy danych). Żeby nie było - przy insercie wrzucam jednocześnie co najmniej kilka rekordów (w zależności od dostarczonych danych), max. 100. Update niestety może być tylko jeden (nie licząc CASE). Dodam, że UPDATE i INSERT następują kolejno z tego samego skryptu, nigdy na raz - a właściwie prawie nigdy - bo czasami jest jakiś select. Wcześniej miałem tak, że równolegle były INSERTy i UPDATE, ale i tutaj (wyjaśnijcie mi jakim cudem) MyISAM okazywał się szybszy niż InnoDB.

Innych niż sql baz nie rozważałem głównie dlatego, że ich nie znam. Poza tym, jak już wspominałem potrzebuję bardzo szybkiego wyszukiwania pełnotekstowego, nie mam pojęcia jak tutaj sprawdzają się inne bazy danych.. Poczytam, może coś w tym jest

MyISAM jeśli wrzucasz coś do tabelki i z niej nie kasujesz - działa wyśmienicie

InnoDB ma dużo wolniejszy zapis pojedynczych rekordów (z racji na indeksy) ale za to odczyt i działania równoległe lepiej działają, przy innoDb należy też pamiętać o tym by nie zakładać indeksów podstawowych innych niż numeryczne... bo sortowanie indeksów trwa a innoDb robi to po każdym insercie w zasadzie...

Ja korzystam z MyISAM przy dużych tabelkach z obszernymi rekordami które często się odczytuje a rzadko zapisuje - wówczas poprzez mechanizm concurrent_inserts wszystko działa płynnie i niezawodnie.

InnoDB wykorzystuje do mniejszych tabel gdzie non-stop się coś dzieje ale z umiarkowaniem jeśli chodzi o indeksy

1. Jak rozumiem INSERTy są objęte pojedyńczą transakcją? Inaczej każdy z nich wymusza aktualizację indeksu.
2. Nie baw się w odczucia co do prędkości działania, tylko włącz profiler i sprawdź co zajmuje ile czasu w każdym z zapytań.

o ile:

[SQL] pobierz, plaintext 
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
[SQL] pobierz, plaintext 

to jedna transakcja to tak. Bo właśnie w taki sposób są przygotowywane dane do dodania - INSERT a potem po przecinku.
Updaty lecą 'pojedynczo', bo wiadomo, że nie ma ich jak inaczej zrobić. Fakt, że profilera nie odpalałem (nigdy z niego nie korzystałem...), ale sprawdzałem sam czas wykonywania inserta przez php (czyli mierzę czas przed i po insercie) i zdecydowanie szybciej (a już gigantyczna różnica wychodzi przy update'ach) działa insert w MyISAM.
Czy kilka updateów można upchnąć w jedną transakcję? W tej chwili wysyłam pojedyncze update w jednym zapytaniu. Czy jest możliwość wysłania całego stada updateów w jedym zapytaniu? w MyISAM z tego co wiem, pomijając CASE (które nie za szybko chyba działa), to nie ma takiej możliwości...

Jeszcze troszkę potestuję, ale mam wrażenie, że chyba trzeba będzie zostać przy MyISAM ;-)

A prwdę powiedziawszy nawet nie wiem czy taki INSERT zostanie potraktowany jako jedna operacja czy zbiór kilku. W każdym bądź razie:
1. Wyłącz autocommit.
2. Wykonaj wszystkie INSERT-y i UPDATE-y wewnątrz transakcji.

Użycie profilera jest o tyle istotne, że będziesz wiedział co konkretnie spowalnia pracę bazy danych. Pozwoli Ci to na poprawienie aplikacji pod tym kątem.

MyISAM przy domyślnej konfiguracji działa szybciej niż domyślnie (fatalnie) skonfigurowany innoDB. Jeśli nie możesz albo nie umiesz tuningować bazy pod silnik innoDB to pozostań przy swoim rozwiązaniu.
jeśli natomiast możesz w dowolny sposób zarządzać konfiguracją (a skoro masz sphinxa to raczej tak) to po dobrym dostrojeniu silnika innoDB będzie on szybszy.

Dzięki za wszelkie odpowiedzi... Póki co zostaję przy MyISAM, jednak potestuję z InnoDB i zobaczymy co z tego będzie Tak, mam pełny dostęp do serwera - mam dedyka Kimsufi z oferty OVH. Na początek dobry do nauczenia się konfiguracji i zarządzania serwerem. Na pewno jeszcze Was pomęczę jak będę walczył z optymalną konfiguracją InnoDB...