| 1 | | == Складирање на податоци и структури |
| 2 | | |
| 3 | | === Теоретска позадина |
| 4 | | |
| 5 | | ==== InnoDB vs MyISAM |
| 6 | | |
| 7 | | InnoDB е транзакциски storage engine и е стандардниот во новите верзии на MySQL. |
| 8 | | |
| 9 | | '''InnoDB''' Главни карактеристики: |
| 10 | | |
| 11 | | * Поддршка за транзакции |
| 12 | | * Може да се користат BEGIN, COMMIT, и ROLLBACK. |
| 13 | | * Обезбедува ACID (Atomicity, Consistency, Isolation, Durability) принципи, што значи дека податоците секогаш остануваат конзистентни. |
| 14 | | * Row-level locking |
| 15 | | * Само редовите што се менуваат се заклучуваат, што овозможува повеќе корисници истовремено да работат без конфликти. |
| 16 | | * Подобро перформанс при повеќе паралелни операции. |
| 17 | | * Foreign Keys |
| 18 | | * Поддржува референтен интегритет (врски меѓу табели преку FOREIGN KEY), со автоматско бришење или ажурирање поврзани редови. |
| 19 | | * Crash recovery |
| 20 | | * Има redo log и undo log, кои овозможуваат автоматско враќање на податоците по пад на серверот. |
| 21 | | * Подобро за апликации со многу INSERT/UPDATE/DELETE операции |
| 22 | | * На пример, системи со резервации (како airportdb). |
| 23 | | |
| 24 | | |
| 25 | | MyISAM е постара, не-транзакциска storage engine, користена во постари MySQL бази. |
| 26 | | |
| 27 | | '''MyISAM''' Главни карактеристики: |
| 28 | | |
| 29 | | * Без транзакции |
| 30 | | * Нема COMMIT и ROLLBACK; ако операцијата се прекине, дел од податоците може да останат неконзистентни. |
| 31 | | * Table-level locking |
| 32 | | * Целата табела се заклучува при INSERT, UPDATE или DELETE. |
| 33 | | * Ова може да создаде застои при повеќе корисници. |
| 34 | | * Брза при читање (SELECT) |
| 35 | | * Добра за read-heavy апликации (на пример, статистички бази или веб-сајтови со многу читања, но малку ажурирања). |
| 36 | | * Нема поддршка за Foreign Keys |
| 37 | | * Одговорноста за интегритет на податоците паѓа на апликацијата, не на базата. |
| 38 | | * Попроста структура |
| 39 | | * Секој table се чува во 3 фајлови (.frm, .MYD, .MYI). |
| 40 | | |
| 41 | | '''Зошто airportdb користи InnoDB?''' |
| 42 | | |
| 43 | | Бидејќи airportdb мора да: одржува конзистентни податоци (на пример, патник не смее да се појави во лет кој не постои), користи foreign keys меѓу табелите, поддржува транзакции, и да има висок степен на паралелност (повеќе корисници резервираат во исто време). |
| 44 | | Затоа InnoDB е најсоодветен избор. |
| 45 | | |
| 46 | | |
| 47 | | ==== Tablespaces |
| 48 | | |
| 49 | | Tablespaces се логички контејнери што ги чуваат табелите и индексите: |
| 50 | | |
| 51 | | Постојат два главни типа: |
| 52 | | * '''System Tablespace''': Една голема, централна датотека (ibdata1) каде InnoDB ги чува сите табели, индекси и системски податоци (како data dictionary и undo logs). Ова е потешко за управување. |
| 53 | | * '''File-per-table''': Ова е модерниот и препорачан пристап). Со оваа поставка, секоја табела што ја креираме (заедно со нејзините индекси) се складира во своја посебна .ibd датотека. На пример, табелата booking би била во booking.ibd. |
| 54 | | |
| 55 | | Зошто е важно? Управување со простор. Со file-per-table, кога ќе избришеме (DROP) табела, едноставно ја бришеме нејзината .ibd датотека и просторот на дискот веднаш се ослободува. |
| 56 | | |
| 57 | | |
| 58 | | ==== Pages / Blocks |
| 59 | | |
| 60 | | Page (или block) е најмала единица што InnoDB ја чита или запишува на диск. |
| 61 | | |
| 62 | | Default големина: '''16 KB.''' |
| 63 | | |
| 64 | | * 4KB - Премногу мал (многу I/O операции) |
| 65 | | * 8KB - Добар, но може подобро |
| 66 | | * 16KB - за општа употреба |
| 67 | | * 32KB - Премногу голем за многу workload-и |
| 68 | | * 64KB - Преголем overhead |
| 69 | | |
| 70 | | Кога би користел '''4KB''' pages? |
| 71 | | * Типично за OLTP системи (Online Transaction Processing) |
| 72 | | * Примери: банкарство, резервации, веб апликации со многу UPDATE/INSERT операции. |
| 73 | | Кога би користел '''32KB''' pages? |
| 74 | | * Типично за OLAP системи (Online Analytical Processing) |
| 75 | | * Примери: системи за известување, аналитика, data warehouse. |
| 76 | | |
| 77 | | Кога се чита ред од табела, InnoDB не чита само тој ред, ја чита целата страница што го содржи. |
| 78 | | Сите операции (INSERT, UPDATE, DELETE) се изведуваат на ниво на страници. |
| 79 | | Пример: Ако читаме еден ред од 1KB, MySQL ќе прочита цела page од 16KB |
| 80 | | |
| 81 | | |
| 82 | | ==== Extents |
| 83 | | |
| 84 | | Extent е група од '''64 последователни pages'''. |
| 85 | | |
| 86 | | Големина: '''64 pages = 64 × 16KB = 1MB''' |
| 87 | | |
| 88 | | Extents се користат за ефикасно управување со простор: наместо да се алоцира страница по страница, InnoDB резервира по цел “extent”. |
| 89 | | |
| 90 | | Segment: Група од extents за специфичен објект (табела/индекс). |
| 91 | | |
| 92 | | Пример: Ако се создава нова табела, InnoDB ќе ѝ додели 1 extent (1MB) простор, кој потоа постепено се пополнува со податоци. |
| 93 | | |
| 94 | | Како функционира заедно: |
| 95 | | |
| 96 | | Табела (во tablespace) |
| 97 | | ↓ |
| 98 | | Segment (група extents) |
| 99 | | ↓ |
| 100 | | Extent (1MB = 64 pages) |
| 101 | | ↓ |
| 102 | | Page (16KB) |
| 103 | | ↓ |
| 104 | | Rows/Index entries |
| 105 | | |
| 106 | | === Визуелизација |
| 107 | | |
| 108 | | ==== Анализа на airportdb storage структура |
| 109 | | |
| 110 | | [[Image(F2 Image 1.png)]] |
| 111 | | |
| 112 | | * Сите 13 табели во листата користат InnoDB storage engine. |
| 113 | | * Табелата '''booking''' е најголема со: |
| 114 | | * '''54,209,984 реда''' |
| 115 | | * '''2,269 MB вкупна големина''' |
| 116 | | Поголемиот дел од табелите имаат многу висок data-to-index ratio: |
| 117 | | * 10 од 13 табели имаат 0.00 MB index size - што значи дека речиси целиот простор е за податоци |
| 118 | | * flight_log: 116.67 MB data vs 21.55 MB index (ratio ~5.4:1) |
| 119 | | * airplane_type: 1.52 MB data vs 0.02 MB index (ratio ~76:1) |
| 120 | | |
| 121 | | === Tablespace конфигурација |
| 122 | | |
| 123 | | [[Image(F2 Image 2.png)]] |
| 124 | | |
| 125 | | Сите Tablespace се Single - Што значи: Секоја табела се чува во свој посебен физички фајл (на пр., booking.ibd, weatherdata.ibd итн...). |
| 126 | | |
| 127 | | Големините овде (File Size / Allocated Size)) се значително поголеми од големината на податоците + индексот од првото барање. |
| 128 | | |
| 129 | | '''Зошто е разликава?''' |
| 130 | | |
| 131 | | Просторот на диск вклучува: |
| 132 | | |
| 133 | | * Податоци (Data Length) |
| 134 | | * Индекси (Index Length) |
| 135 | | * Фрагментација: Празен простор оставен во страниците откако ќе се избришат податоци. |
| 136 | | * Одреден преостанат простор што InnoDB го резервира за идни записи за да минимизира фрагментација при вмeшувања. |
| 137 | | * Системски overhead на самата структура на табелата. |
| 138 | | |
| 139 | | === Page size анализа |
| 140 | | |
| 141 | | [[Image(F2 Image 3.png)]] |
| 142 | | |
| 143 | | * Барањето открива дека InnoDB користи големина на страница од 16KB, што е стандардно! |
| 144 | | * Пример: booking користи 332.544 страници × 16KB = 5.196 MB |
| 145 | | * Секој tablespace има уникатен SPACEID: |
| 146 | | * 35 = airportdb/booking |
| 147 | | * 252 = airportdb/weatherdata_copy |
| 148 | | * 253 = airportdb/weatherdata |
| 149 | | * 4294967294 = mysql (system tablespace) |
| 150 | | |
| 151 | | === Физички фајлови на дискот |
| 152 | | |
| 153 | | [[Image(F2 Image 4.png)]] |
| 154 | | |
| 155 | | === Фрагментација |
| 156 | | |
| 157 | | [[Image(F2 Image 5.png)]] |
| 158 | | |
| 159 | | Кога да правиме дефрагментација (OPTIMIZE TABLE)?: |
| 160 | | * Кога фрагментацијата е над 20-30% |
| 161 | | * После големи бришања или ажурирања |
| 162 | | * Кога физичката големина е многу поголема од логичката |
| 163 | | * Кога перформансите на табелата се влошуваат |
| 164 | | |
| 165 | | Во нашиот случај на табелата '''weatherdata''' не е потребно да се прави дефрагментација! |
| 166 | | (Доколкулки треба да се изврши фрагментација ја користиме следната команда - OPTIMIZE TABLE weatherdata;) |
| 167 | | |
| 168 | | == Buffer Pool Management (Кеширање) |
| 169 | | |
| 170 | | == Организација на записи и табели |
| 171 | | |
| 172 | | == Индексирање и меморија |
| 173 | | |
| | 1 | == |
