Складирање на податоци и структури

Теоретска позадина

InnoDB vs MyISAM

InnoDB е транзакциски storage engine и е стандардниот во новите верзии на MySQL.

InnoDB Главни карактеристики:

• Поддршка за транзакции
  • Може да се користат BEGIN, COMMIT, и ROLLBACK.
  • Обезбедува ACID (Atomicity, Consistency, Isolation, Durability) принципи, што значи дека податоците секогаш остануваат конзистентни.
• Row-level locking
  • Само редовите што се менуваат се заклучуваат, што овозможува повеќе корисници истовремено да работат без конфликти.
  • Подобро перформанс при повеќе паралелни операции.
• Foreign Keys
  • Поддржува референтен интегритет (врски меѓу табели преку FOREIGN KEY), со автоматско бришење или ажурирање поврзани редови.
• Crash recovery
  • Има redo log и undo log, кои овозможуваат автоматско враќање на податоците по пад на серверот.
• Подобро за апликации со многу INSERT/UPDATE/DELETE операции
  • На пример, системи со резервации (како airportdb).

MyISAM е постара, не-транзакциска storage engine, користена во постари MySQL бази.

MyISAM Главни карактеристики:

• Без транзакции
  • Нема COMMIT и ROLLBACK; ако операцијата се прекине, дел од податоците може да останат неконзистентни.
• Table-level locking
  • Целата табела се заклучува при INSERT, UPDATE или DELETE.
  • Ова може да создаде застои при повеќе корисници.
• Брза при читање (SELECT)
  • Добра за read-heavy апликации (на пример, статистички бази или веб-сајтови со многу читања, но малку ажурирања).
• Нема поддршка за Foreign Keys
  • Одговорноста за интегритет на податоците паѓа на апликацијата, не на базата.
• Попроста структура
  • Секој table се чува во 3 фајлови (.frm, .MYD, .MYI).

Зошто airportdb користи InnoDB?

Бидејќи airportdb мора да: одржува конзистентни податоци (на пример, патник не смее да се појави во лет кој не постои), користи foreign keys меѓу табелите, поддржува транзакции, и да има висок степен на паралелност (повеќе корисници резервираат во исто време). Затоа InnoDB е најсоодветен избор.

Tablespaces

Tablespaces се логички контејнери што ги чуваат табелите и индексите:

Постојат два главни типа:

• System Tablespace: Една голема, централна датотека (ibdata1) каде InnoDB ги чува сите табели, индекси и системски податоци (како data dictionary и undo logs). Ова е потешко за управување.
• File-per-table: Ова е модерниот и препорачан пристап). Со оваа поставка, секоја табела што ја креираме (заедно со нејзините индекси) се складира во своја посебна .ibd датотека. На пример, табелата booking би била во booking.ibd.

Зошто е важно? Управување со простор. Со file-per-table, кога ќе избришеме (DROP) табела, едноставно ја бришеме нејзината .ibd датотека и просторот на дискот веднаш се ослободува.

Pages / Blocks

Page (или block) е најмала единица што InnoDB ја чита или запишува на диск.

Default големина: 16 KB.

• 4KB - Премногу мал (многу I/O операции)
• 8KB - Добар, но може подобро
• 16KB - за општа употреба
• 32KB - Премногу голем за многу workload-и
• 64KB - Преголем overhead

Кога би користел 4KB pages?

• Типично за OLTP системи (Online Transaction Processing)
  • Примери: банкарство, резервации, веб апликации со многу UPDATE/INSERT операции.

Кога би користел 32KB pages?

• Типично за OLAP системи (Online Analytical Processing)
  • Примери: системи за известување, аналитика, data warehouse.

Кога се чита ред од табела, InnoDB не чита само тој ред, ја чита целата страница што го содржи. Сите операции (INSERT, UPDATE, DELETE) се изведуваат на ниво на страници. Пример: Ако читаме еден ред од 1KB, MySQL ќе прочита цела page од 16KB

Extents

Extent е група од 64 последователни pages.

Големина: 64 pages = 64 × 16KB = 1MB

Extents се користат за ефикасно управување со простор: наместо да се алоцира страница по страница, InnoDB резервира по цел “extent”.

Segment: Група од extents за специфичен објект (табела/индекс).

Пример: Ако се создава нова табела, InnoDB ќе ѝ додели 1 extent (1MB) простор, кој потоа постепено се пополнува со податоци.

Како функционира заедно:

Табела (во tablespace)

↓

Segment (група extents)

↓

Extent (1MB = 64 pages)

↓

Page (16KB)

↓

Rows/Index entries

Визуелизација

Анализа на airportdb storage структура

• Сите 13 табели во листата користат InnoDB storage engine.
• Табелата booking е најголема со:
  • 54,209,984 реда
  • 2,269 MB вкупна големина

Поголемиот дел од табелите имаат многу висок data-to-index ratio:

• 10 од 13 табели имаат 0.00 MB index size - што значи дека речиси целиот простор е за податоци
• flight_log: 116.67 MB data vs 21.55 MB index (ratio ~5.4:1)
• airplane_type: 1.52 MB data vs 0.02 MB index (ratio ~76:1)

Tablespace конфигурација

Сите Tablespace се Single - Што значи: Секоја табела се чува во свој посебен физички фајл (на пр., booking.ibd, weatherdata.ibd итн...).

Големините овде (File Size / Allocated Size)) се значително поголеми од големината на податоците + индексот од првото барање.

Зошто е разликава?

Просторот на диск вклучува:

• Податоци (Data Length)
• Индекси (Index Length)
• Фрагментација: Празен простор оставен во страниците откако ќе се избришат податоци.
• Одреден преостанат простор што InnoDB го резервира за идни записи за да минимизира фрагментација при вмeшувања.
• Системски overhead на самата структура на табелата.

Page size анализа

• Барањето открива дека InnoDB користи големина на страница од 16KB, што е стандардно!
  • Пример: booking користи 332.544 страници × 16KB = 5.196 MB
• Секој tablespace има уникатен SPACEID:
  • 35 = airportdb/booking
  • 252 = airportdb/weatherdata_copy
  • 253 = airportdb/weatherdata
  • 4294967294 = mysql (system tablespace)

Физички фајлови на дискот

Buffer Pool Management (Кеширање)

Организација на записи и табели

Индексирање и меморија