Реплікація У веб додатках зазвичай домінують запити на отримання інформації - читання коментарів, постів, призначених для користувача даних і т.д. Таким чином слабким місцем є операція читання і саме її потрібно масштабувати. Для вирішення цього завдання використовується механізм реплікації - один сервер призначається головним (майстер-сервером) і виконує всі запити модифікації даних, а інші сервера (слейв) обробляють тільки запити отримання даних. При зміні або додаванні даних, майстер сервер повідомляє всі слейв сервера, таким чином підтримуючи актуальність даних.

Крім того, реплікація використовується для географічного розподілу серверів (наприклад один сервер в Америці, інший в Європі).

Принцип роботи реплікації Майстер сервер при виконанні модифікацій пише всі зроблені зміни в лог, слейв сервера з певною періодичністю перевіряють лог на предмет появи нових даних і якщо вони є - виконують аналогічні дії зі своїми даними.

Реплікаційні схеми Схема зазвичай використовується в додатках з домінуючими запитами на читання - всі запити на зміну бази направляються майстер сервером, тоді як запити на читання розподіляються між слейв.

Зазвичай в додатку вказується список серверів (пул) призначених для читання, з якого mysql клієнт певним чином (випадковим або за вказаним алгоритмом) обирає сервер для виконання запиту, таким чином балансуючи навантаження між усіма серверами.

Недолік схеми очевидний - при виході з ладу майстер сервера, всі запити на модифікацію і додавання даних не будуть виконуватися.

Ланцюжок майстер серверів  Дуже зручний для географічного розподілу даних. Наприклад, ставимо сервера в Європі, Азії та Америці, налаштовуємо їх в ланцюжок і вчимо додаток обирати сервер в залежності від регіону. Таким чином отримуємо виграш за рахунок зменшення шляху подорожі запиту до сервера.

При збільшенні навантаження на один з регіональних серверів до нього легко можна додати кілька слейв.

Недолік схеми аналогічний попередньому - при виході з ладу одного з майстер-серверів, ланцюжок буде порушений, але регіональні сервери, які залишилися, працюватимуть незалежно, що значно краще за попередній варіант.

Два майстри, багато слейв  Схема є ланцюжком з двох майстер серверів. Обидва майстри виконують запити на модифікацію даних і мають рівну кількість слейв. Таким чином при виході з ладу одного майстра, додаток продовжить працювати з другим і його слейв.

Розглянемо на прикладі налаштування реплікації між майстром і слейв.

На слейв потрібно створити користувача, яким він буде авторизуватися на майстер. Бажано створити аналогічного користувача й на майстрі, на випадок якщо він вийде з ладу і доведеться зробити слейв майстром, а майстер, після виправлення помилки, слейв.

CREATE USER 'repl' @ '%' IDENTIFIED BY 'replpass';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO repl @ '%';

Кожен сервер в реплікаційній схемі повинен мати унікальний номер (ціле число), яке потрібно прописати в my.cnf в секції [mysqld].

Наприклад для майстра:

server-id = 1

Для слейв:

server-id = 2

Крім того, на майстра потрібно включити лог, для чого туди ж вписуємо:

log-bin

Рестартуємо обидва сервери, після чого майстер вже готовий до роботи, а слейв потрібно вказати хто його майстер, для цього:

CHANGE MASTER TO MASTER_HOST = 'master.example.com', MASTER_USER = 'repl', MASTER_PASSWORD = 'replpass';

START SLAVE;

Все, реплікація між серверами налаштована, тепер після виконання запиту модифікації на майстра, дані оновляться і на слейв. Просто і сердито.

Якщо реплікацію потрібно налаштувати для вже існуючої бази, перед запуском обидва сервери повинні мати однакові дані.

Партиціювання Іноді зустрічаються таблиці з деяким логічним угрупуванням даних, наприклад, список покупок користувача або лог дій можна згрупувати за датою. І коли більшість запитів працює з групами (наприклад, цікавить тільки статистика за рік), тоді є сенс зберігати дані розбиті на групи безпосередньо в БД. Процес поділу даних і зберігання їх у вигляді деяких груп і називається партиціюванням.

Розглянемо на прикладі таблиці:

CREATE TABLE employees (

    id INT NOT NULL,

    fname VARCHAR (30),

    lname VARCHAR (30),

    hired DATE NOT NULL DEFAULT '1970-01-01',

    separated DATE NOT NULL DEFAULT '9999-12-31',

    job_code INT NOT NULL,

    store_id INT NOT NULL

)

Типи партиціювання: 1. За діапазоном

Кожне розбиття містить дані, які належать зазначеному діапазону значень колонки.

PARTITION BY RANGE (store_id) (

    PARTITION p0 VALUES LESS THAN (6),

    PARTITION p1 VALUES LESS THAN (11),

    PARTITION p2 VALUES LESS THAN (16),

    PARTITION p3 VALUES LESS THAN (21)

);

У партицію p0 потраплять всі рядки в яких store_id <6.

2. За списком значень

Кожне розбиття містить дані, які мають певне значення в колонці.

PARTITION BY LIST (store_id) (

    PARTITION pNorth VALUES IN (3,5,6,9,17),

    PARTITION pEast VALUES IN (1,2,10,11,19,20),

         PARTITION pWest VALUES IN (4,12,13,14,18),

     PARTITION pCentral VALUES IN (7,8,15,16)

);

Наприклад в партицію pNorth потраплять всі рядки в яких store_id = 3, 5, 6, 9, 17.

3. За хешем

Таблиця розбивається по хешу значення деякої колонки.

4. За ключем

Аналогічно до попереднього методу, але по ключу.

Останні два методи не дають можливості вказати в яку з партицій потрапить рядок, а тільки кількість партицій, механізм розподілу MySQL бере на себе.

Партиції можна розбивати на підпартиції, які можна далі партиціонувати і т.д.

При цьому при складанні запиту не потрібно думати де і в якому вигляді зберігаються дані, просто пишемо запит до таблиці employees, а MySQL визначить для яких партицій його потрібно виконати.

У реальному житті партиції використовуються не часто, тому, що в більшості випадків індексу досить, але все ж потрібно пам'ятати про їх існування.

Шардінг Шардінг - розвиток партиціонування - розбиття даних на групи і зберігання будь-якої групи на окремому сервері (Шарден). В цьому випадку група не обов'язково включає одну таблицю, це може бути кілька таблиць, які містять одне ціле. Наприклад, маючи багато зареєстрованих користувачів, дані яких лежать в декількох таблицях (реєстраційні дані, історія покупок, лог дій), на кожній Шардена буде зберігатися повна інфраструктура (всі таблиці), але з даними тільки тих, хто поточної Шарден (наприклад одна Шарда - користувачі з прізвищами від А до Д, друга Д-К і т.д).

MySQL не підтримує автоматичного шардінга, тому його доводиться робити на рівні додатку, вибираючи в залежності від запиту до сервера. Зазвичай створюється параметризовані пул серверів (в прикладі вище таким параметром була б перша буква прізвища) і при виконанні кожного запиту, за цим параметром вибирається потрібний сервер.

Шардінг це остання міра масштабування (коли реплікація вже не рятує), не тільки через складність реалізації, а й через ускладнення роботи з даними. Оскільки робота з Шардена відбувається на рівні додатку, ми автоматично відмовляємося від тригерів, збережених процедур і інших вбудованих в БД можливостей, які повинні працювати з усіма даними, оскільки на рівні БД кожної Шардена існує тільки її набір даних і вона нічого не знає про інших.

Тому перед тим, як впроваджувати MySQL шардінг, потрібно дуже добре подумати і бути точно впевненим, що іншого шляху немає.