Proč používat innodb_file_per_table?

Nemyslím si, že jde o výkon, ale o management.

Se samostatným souborem na tabulku můžete například ukládat různé databáze na různá úložná zařízení.

Můžete se vypořádat s případem velmi velkých databází v souborových systémech, které nezvládají velké soubory (alespoň odložte problém, dokud jedna tabulka nedosáhne limitu velikosti souboru).

Nemáte nekontrolovaný růst tabulkového prostoru. Pokud máte nějaké velké stoly, které zahodíte, ibdata soubor zůstane malý.

Jedním aspektem, který může mít určitý vliv na výkon, je fragmentace tabulkových dat a indexů, která bude pro každou tabulku omezena. Ale to vyžaduje testování, aby bylo potvrzeno.

Proč používat innodb_file_per_table?

Protože je jednodušší spravovat jednotlivce, protože to lze provést na úrovni souborů. To znamená, že i když je server mimo provoz, stále můžete kopírovat data zkopírováním souborů tabulky, zatímco použití sdíleného tabulkového prostoru znamená buď zkopírovat vše což může být zbytečně velké, nebo najít nějaký způsob, jak spustit server, aby extrahoval data (opravdu nechcete data extrahovat ručně pomocí hex-editoru).

Někdo varoval, že nemůžete jednoduše zkopírovat a vložit .ibd soubory z jednoho serveru na druhý. To může být pravda, ale nemělo by to platit pro zálohy na stejném serveru (používám termín záloha zde v tradičním smyslu vytvoření kopie; to znamená, že se celá věc drasticky nezmění). Navíc ibdata1 se automaticky znovu vytvoří při spuštění (jak je vidět v delete ibdata1 krok většiny příruček „převod na soubor podle tabulky“). Jako takový neděláte je třeba zkopírovat ibdata1 kromě vašeho .ibd soubory (a jejich odpovídající .frm , atd. soubory).

Pokud se pokoušíte obnovit ztracenou tabulku, mělo by stačit zkopírovat její .ibd a .frm soubor a také information_schema (což je hodně menší než ibdata1 ). Tímto způsobem je můžete umístit na fiktivní server a extrahovat svůj stůl, aniž byste museli kopírovat celou, masivní věc.

Nárok na lepší výkon je však sporný. … S innodb_file_per_table je potřeba více diskových I/O operací; a to je významné u komplikovaných JOINů a omezení FOREIGN KEY.

Není překvapením, že výkon bude zcela záviset na konkrétní používané databázi (databázích). Jedna osoba bude mít (dokonce výrazně) odlišné výsledky než jiná.

Je pravda, že bude více diskových I/O operací se souborem na tabulku, ale jen mírně více. Přemýšlejte o tom, jak systém funguje.

• Pro monolitickou databázi:

  1. Server je spuštěn
  2. ibdata1 je otevřen
  3. Čtou se záhlaví a metadata
  4. Struktury a metadata se ukládají do mezipaměti
  5. Dochází k dotazům
     1. Server přistupuje na disk a čte data z již otevřeného ibdata1
     2. Server může ukládat data do mezipaměti

• Pro databázi podle tabulky:

  1. Server je spuštěn
  2. ibdata1 je otevřen
  3. Čtou se záhlaví a metadata
  4. Každý jednotlivě .ibd soubor je otevřen
  5. Záhlaví a metadata se čtou z každého .ibd soubor
  6. Struktury a metadata se ukládají do mezipaměti
  7. Dochází k dotazům
     1. Server přistupuje na disk a čte data z již otevřeného .ibd soubor
     2. Server může ukládat data do mezipaměti

Všimnete si, že když server běží, nemůžete přesunout datové soubory, protože server má pro ně otevřené popisovače. Je to proto, že když se spustí, otevře je a nechá je otevřené. Neotevírá a nezavírá je pro každý jednotlivý dotaz.

Proto na začátku, když se server spouští, je pouze několik dalších I/O operací; ne když běží. Dále, zatímco každý jednotlivec .ibd soubor má svou vlastní samostatnou režii (podpisy souborů, struktury atd.), jsou ukládány do mezipaměti a nejsou znovu načítány pro každý dotaz. Navíc se stejné struktury čtou dokonce i se sdíleným tabulkovým prostorem, takže je potřeba stěží (pokud vůbec nějaká) více paměti.

Má innodb_file_per_table vliv na lepší výkon mysql?

Ve skutečnosti může být výkon ve skutečnosti horší .

Při použití sdíleného tabulkového prostoru lze operace čtení a zápisu někdy/často kombinovat, takže server čte vzorek dat z více tabulek najednou od ibdata .

Pokud jsou však data rozložena mezi více souborů, musí provést samostatnou I/O operaci pro každý jednotlivě.

Samozřejmě to opět zcela závisí na dané databázi; dopad na výkon v reálném světě by závisel na velikosti, frekvenci dotazů a vnitřní fragmentaci sdíleného tabulkového prostoru. Někteří lidé si mohou všimnout velkého rozdílu, zatímco jiní nemusí vidět vůbec žádný dopad.

Tabulkový prostor je sdílen na jediném ibdata; jak mohou vyhrazené tabulkové prostory pro samostatné tabulky ušetřit místo na disku?

To není. Pokud něco, zvyšuje využití disku nějaké.

Nemám 60GB databázi k testování, ale moje „mizerná“ osobní databáze, která obsahuje moji instalaci WordPressu a několik malých tabulek pro osobní použití a vývojové testování, vážila ~30 MB při použití sdíleného tabulkového prostoru. Po převedení na soubor na tabulku se nafoukla na ~85 MB. I po odstranění všeho a opětovném importu to bylo stále>60 MB.

Toto zvýšení je způsobeno dvěma faktory:

• Naprosté minimum velikost pro ibdata1 je z nějakého důvodu 10 MB, i když nemáte nic jiného než information_schema v něm uloženy.

• Se sdíleným tabulkovým prostorem pouze ibdata1 má režii, jako jsou podpisy souborů, metadata atd., ale u jednotlivých tabulek je každý jednotlivý .ibd soubor to všechno má. To znamená, že celkový počet (i s hypotetickými <10MB ibdata1 ) by byl poněkud větší alespoň o:

  GetTotalSizeofOverhead() * GetNumTables()
  

Je zřejmé, že tyto nebudou obrovské se zvyšuje (pokud nepoužíváte hostitele, který omezuje velikost vaší databáze nebo je neukládáte na flash disk atd.), ale přesto se zvyšuje a při přepínání (každý ) tabulku na soubor-na-tabulku můžete zmenšit ibdata1 až na 10 MB, celkový součet bude vždy více než bylo.

Toto je můj důvod, proč VŽDY používám innodb_file_per_table:

Bez souboru na tabulku se soubor ibdata nikdy nekomprimuje, nezmenšuje ani nezmenšuje prostor. Ne, když odstraníte řádek, vypustíte tabulku nebo databázi. Pokud máte aktivní systém řazení, 2GB dat se může během okamžiku stát 20GB souborem.

Řekněme, že si chcete před změnou vytvořit zálohu vaší aktuální 1GB tabulky a poté ji zahodit. Ve vašem ibdata jste uvízli s GB nyní nevyužitého místa. Sakra.

Pravděpodobně existuje nekonečně mnoho příkladů případů, kdy dočasná opatření nafouknou jeden datový soubor, ale stačí říci, že podle mého názoru nikdy neexistuje důvod NEPOUŽÍVAT innodb_file_per_table

Zde je také dobrý příspěvek k přečtení:http://code.openark.org/blog/mysql/reasons-to-use-innodb_file_per_table