Błąd tworzenia tabeli przez InnoDB: „Zbyt duży rozmiar wiersza”

Mamy inżynierów, którzy spłaszczają znormalizowaną strukturę db do tabeli tymczasowej w celu wygenerowania raportu. Kolumny są określone jako TEXT NOT NULL(Wiem „dlaczego to robią?”; Załóżmy, że się tym zajmujemy).

Używamy MySQL 5.1.48 Community RHEL5 z wtyczką InnoDB 1.0.9 w systemie Linux.

Podczas korzystania z MyISAM nigdy nie napotkaliśmy limitów wielkości tabeli maksymalnych kolumn lub maksymalnej długości wiersza (podczas dochodzenia osiągnęliśmy maksymalny limit kolumn przy 2598 (2599. powoduje błąd 1117). W InnoDB osiągamy limity. Limity te manifestują się podczas tworzenia tabela (bez wstawiania danych) jako:

BŁĄD 1118 (42000) w wierszu 1: Zbyt duży rozmiar wiersza. Maksymalny rozmiar wiersza dla użytego typu tabeli, nie licząc BLOB, wynosi 8126. Musisz zmienić niektóre kolumny na TEXT lub BLOB

Szukam odpowiedzi na następujące pytania:

1. Jaka jest szczegółowa formuła określania wielkości cząstek w przypadku korzystania z partii kolumn v / v / b / t? Wypróbowałem kilka różnych formałów przy użyciu varchar(N)kolumn (gdzie N wynosi od 1 do 512), zestawu znaków UTF8 (* 3) i tylu kolumn, ile zajmie tabela, aż do niepowodzenia. Żadna z kombinacji, które próbowałem, nie podaje wartości zgodnych z rzeczywistymi wynikami testu.

2. Jakie inne „koszty ogólne” muszę wziąć pod uwagę przy obliczaniu wielkości wiersza?

3. Dlaczego komunikat o błędzie zmienia się z 8126 na 65535, kiedy przechodzę od tworzenia tabel z kolumnami varchar (109) do kolumn varchar (110)?

Odpowiedzi na pytania są złożone, ponieważ różnią się w zależności od formatu pliku InnoDB . Obecnie istnieją dwa formaty, zwane Antelope i Barracuda.

Centralny plik obszaru tabel (ibdata1) jest zawsze w formacie Antelope . Jeśli używasz pliku na tabelę, możesz sprawić, aby poszczególne pliki korzystały z formatu Barracuda , ustawiając innodb_file_format=Barracudaw pliku my.cnf.

Podstawowe punkty:

• Jedna strona danych InnoDB o wielkości 16 KB musi zawierać co najmniej dwa wiersze danych. Dodatkowo każda strona ma nagłówek i stopkę zawierające sumy kontrolne strony i numer sekwencji dziennika i tak dalej. To tam dostajesz swój limit nieco mniejszy niż 8 KB na wiersz.

• Typy danych o stałym rozmiarze, takie jak INTEGER, DATE, FLOAT, CHAR są przechowywane na tej głównej stronie danych i są wliczane do limitu wielkości wiersza.

• Typy danych o zmiennej wielkości, takie jak VARCHAR, TEXT, BLOB są przechowywane na stronach przepełnienia, więc nie liczą się w pełni do limitu wielkości wiersza. W Antelope do 768 bajtów takich kolumn jest przechowywanych na głównej stronie danych, a także na stronie przepełnienia. Barracuda obsługuje dynamiczny format wierszy , więc może przechowywać tylko 20-bajtowy wskaźnik na głównej stronie danych.

• Typy danych o zmiennej wielkości są również poprzedzone 1 lub więcej bajtami, aby zakodować długość. Format wiersza InnoDB ma również szereg przesunięć pól. Więc na ich wiki jest mniej więcej udokumentowana struktura wewnętrzna . [EDYCJA] Dead link - tutaj wygląda teraz lepiej.

Barracuda obsługuje również parametr ROW_FORMAT = COMPRESSED, aby uzyskać dodatkową wydajność pamięci masowej dla danych przelewowych.

Muszę też skomentować, że nigdy nie widziałem, aby dobrze zaprojektowana tabela przekraczała limit wielkości wiersza. To silny „zapachowy kod”, że naruszasz warunki grup powtarzających się w Pierwszej Normalnej Formie.

Moja sytuacja jest nieco inna. Jeden z elementów danych, które muszę przechowywać w każdym wierszu, jest potencjalnie bardzo duży. (Pole danych to LONGBLOB dla dokumentu, który może zawierać wiele osadzonych obrazów. Moja przykładowa baza danych zawiera dokumenty o wielkości od 25 do 30 MB, ale w niektórych przypadkach dokumenty te mogą być większe.) Żadne z rozwiązań, które znalazłem w Internecie, nie przyniosło ulgi . (Zmieniono typ pliku InnoDB na Barracuda, zwiększono rozmiar pliku dziennika, ustaw format wiersza na COMPRESSED.)

Jedynym rozwiązaniem, które dla mnie zadziałało, było przywrócenie MySQL 5.5.x z MySQL 5.6.x.