== Складирање на податоци и структури === Теоретска позадина ==== 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 структура [[Image(F2 Image 1.png)]] * Сите 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 конфигурација [[Image(F2 Image 2.png)]] Сите Tablespace се Single - Што значи: Секоја табела се чува во свој посебен физички фајл (на пр., booking.ibd, weatherdata.ibd итн...). Големините овде (File Size / Allocated Size)) се значително поголеми од големината на податоците + индексот од првото барање. '''Зошто е разликава?''' Просторот на диск вклучува: * Податоци (Data Length) * Индекси (Index Length) * Фрагментација: Празен простор оставен во страниците откако ќе се избришат податоци. * Одреден преостанат простор што InnoDB го резервира за идни записи за да минимизира фрагментација при вмeшувања. * Системски overhead на самата структура на табелата. === Page size анализа [[Image(F2 Image 3.png)]] * Барањето открива дека 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) === Физички фајлови на дискот [[Image(F2 Image 4.png)]] === Фрагментација [[Image(F2 Image 5.png)]] Кога да правиме дефрагментација (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 - метаподатоци === Визуелизација ==== Тековна конфигурација [[Image(F2 Image 6.png)]] Овој 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 === [[Image(F2 Image 7.png)]] * '''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 е '''ПРЕМАЛ''' === [[Image(F2 Image 8.png)]] * '''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 е премал 🔴 === [[Image(F2 Image 9.png)]] '''Кои податоци се во buffer pool?''' * airportdb.weatherdata - Стари страници: 2,501 | Нови страници: 2,436 * airportdb.flight - Речиси сите страници се нови (991 од 995) * passengerdetails - 10 страници, сите се стари === Cold vs Warm Buffer Test * innodb_buffer_pool_size = 4G * innodb_buffer_pool_instances = 8 * innodb_buffer_pool_chunk_size = 512M == Организација на записи и табели === Теоретска позадина ==== Row Format '''Row Format''' определува како InnoDB ги организира податоците физички на дискот. InnoDB Row Formats: * '''COMPACT:''' Стар format, space-efficient * '''DYNAMIC:''' Default од MySQL 5.7+, поддржува large varchar/blob * '''COMPRESSED:''' DYNAMIC со компресија * '''REDUNDANT:''' Застарен, не се користи ==== Page Structure {{{ ┌─────────────────────────────────────────┐ │ FIL Header (38 bytes) │ ← Page checksum, LSN, page number ├─────────────────────────────────────────┤ │ Index Header (36 bytes) │ ← Number of records, free space ├─────────────────────────────────────────┤ │ File Segment Header (20 bytes) │ ← Links to FSEG ├─────────────────────────────────────────┤ │ System Records (26 bytes) │ ← Infimum & Supremum records ├─────────────────────────────────────────┤ │ │ │ User Records (variable) │ ← Вистинските податоци (редови) │ │ ├─────────────────────────────────────────┤ │ Free Space (variable) │ ← Неискористен простор ├─────────────────────────────────────────┤ │ Page Directory (variable) │ ← Показувачи за брзо пребарување ├─────────────────────────────────────────┤ │ FIL Trailer (8 bytes) │ ← Page checksum (дупликат) └─────────────────────────────────────────┘ }}} Клучни елементи: * '''FIL Header:''' Метаподатоци за page (број, checksum за интегритет, LSN за recovery) * '''User Records:''' Податоците се складираат како B+Tree структура: * '''Leaf nodes''' содржат реални редови * '''Non-leaf nodes''' содржат индекс клучеви и показувачи * Редовите се поврзани во '''DLL''' * '''Page Directory:''' Sparse index за брзо пребарување (binary search) на редови внатре во page * '''Free Space:''' Празен простор за нови INSERT операции (се намалува со додавање редови) ==== Фрагментација '''Фрагментација''' настанува кога се јавува празен простор во pages. Фрагментација се јавува кога: * '''DELETE операции''' * '''UPDATE операции''' * '''Random INSERTs''' Последици: * '''Потрошен disk простор:''' Табелата зафаќа повеќе место од потребно * '''Лоши перформанси:''' Повеќе I/O операции за читање на исти податоци * '''Неефикасен cache:''' Buffer pool се полни со полупразни pages ==== Compression '''InnoDB Compression''' ги намалува барањата за складирање и меморија. Како функционира: * '''Transparent Page Compression:''' * InnoDB компресира секоја page пред запишување на disk * Користи punch hole механизам (OS-level sparse files) * Декомпресија при читање во buffer pool * '''ROW_FORMAT=COMPRESSED:''' * Користи zlib алгоритам * Компресираните pages се складираат како 1KB, 2KB, 4KB или 8KB * Податоците се декомпресираат во buffer pool Предности: * '''Намален disk usage:''' 50-80% помалку простор (зависно од податоците) * '''Намален I/O:''' Помалку блокови за читање/пишување * '''Подобрен cache hit rate:''' Повеќе податоци се во buffer pool Недостатоци: * '''CPU overhead:''' Компресија/декомпресија троши CPU * '''Компресија failures:''' Ако page не може да се компресира доволно, се складира некомпресирано * '''Compression log overhead:''' Extra bookkeeping во buffer pool Кога да се користи компресија: Добро за: * OLAP workloads (read-heavy) * Архивски табели со ретки промени * Големи TEXT/BLOB колони * Ограничен disk/memory Избегнувај за: * OLTP workloads (write-heavy) * CPU-bound системи * Табели со чести UPDATE операции * Мали табели (<1GB) InnoDB го организира складирањето преку row formats, 16KB pages со B+Tree структура, а фрагментацијата и компресијата се клучни за оптимизација на простор и перформанси. === Визуелизација