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

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

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 операции
  • На пример, системи со резервации.

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)

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

Фрагментација

Кога да правиме дефрагментација (OPTIMIZE TABLE)?:

• Кога фрагментацијата е над 20-30%
• После големи бришања или ажурирања
• Кога физичката големина е многу поголема од логичката
• Кога перформансите на табелата се влошуваат

Во нашиот случај на табелата weatherdata не е потребно да се прави дефрагментација! (Доколкулки треба да се изврши фрагментација ја користиме следната команда - OPTIMIZE TABLE weatherdata;)

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

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

Што е Buffer Pool?

Buffer Pool е in-memory кеш (RAM меморија) кој го користи InnoDB storage engine во MySQL за да зачува делови од data pages и index pages што често се користат. Наместо податоците постојано да се читаат од диск, тие се зачувуваат во Buffer Pool за побрз пристап. Тоа е најважниот дел од InnoDB мемориската архитектура.

Зошто е важен?

Disk I/O операциите се ~1000x побавни од пристапот до RAM меморијата. Со чување на често користените податочни страници во меморија, Buffer Pool драстично ја намалува потребата од бавни диск операции, што резултира со значително подобрување на перформансите.

Драматична разлика во перформанси

• RAM пристап: ~100 наносекунди (0.0001 ms)
• SSD пристап: ~0.1 милисекунди (100 µs)
• HDD пристап: ~10 милисекунди

Без Buffer Pool (секој query чита од disk):

• 1,000 queries/sec × 10ms = 10 секунди само за чекање на disk

Со Buffer Pool (податоците се во RAM):

• 1,000 queries/sec × 0.0001ms = 0.1 милисекунди

Како работи?

Buffer Pool користи LRU (Least Recently Used) алгоритам:

• Кога има ограничен простор во меморијата, најстарите (најмалку користени) страници се отстрануваат.
• Новите или често користени страници остануваат во меморијата подолго.

Buffer Pool Pages

Buffer Pool е поделен на страници (pages), обично со големина од 16 KB.

• Секоја страница претставува дел од податочна табела или индекс.
• Кога некој податок се чита од диск, тој се вчитува во една од овие страници во Buffer Pool.
• Ако податокот повторно се бара, MySQL го зема директно од меморија без да оди на диск.

Типови на Pages во Buffer Pool:

• Data Pages - чуваат табелни редови
• Index Pages - чуваат B-tree индекс структури
• Undo Pages - за transaction rollback
• Insert Buffer Pages - за оптимизација на secondary index
• System Pages - метаподатоци

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

Тековна конфигурација

Овој output ја прикажува моменталната конфигурација на InnoDB buffer pool во нашата MySQL база на податоци.

• innodb_buffer_pool_chunk_size: 134217728 (128MB) - Големината на деловите за зголемување/намалување на buffer pool
• innodb_buffer_pool_size: 134217728 (128MB) - (Најважна) Вкупна меморија доделена за кеширање на податоци и индекси
• innodb_buffer_pool_instances: 1 - Број на инстанции на buffer pool

===

• Innodb_buffer_pool_pages_total: 8192 - Вкупно страници во buffer pool: 8,192 страници
• Innodb_buffer_pool_pages_data: 7773 - Страници со корисни податоци: 7,773 страници
• Innodb_buffer_pool_pages_free: 405 - Слободни страници: само 405 (5%)
• Innodb_buffer_pool_pages_misc: 14 - Страници за административни цели: 14 страници
• Buffer pool е 95% полн (7773/8192 страници) - Имаме само 5% слободен простор
• Потврда дека сегашниот buffer pool е ПРЕМАЛ

===

• Buffer Pool Hit Rate %: 94.54% - Ова е многу ниска вредност за buffer pool hit rate!
• Hit Rate = 94.54% значи дека 5.46% од читањата мораат да одат на диск (бавно!)
• За секои 100 читања, 5-6 се бавни читања од диск

Интерпретација:

• >99%: Одлично! 🟢
• 95-99%: Добро, има простор за подобрување 🟡
• <95%: Лошо! Buffer pool е премал 🔴

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

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