Статистика користувачів 2ГІС: правила ETL і препроцессінг даних

    
 
Щоб розуміти переваги користувачів і оцінювати ефективність роботи сервісів 2ГІС, ми збираємо знеособлену інформацію. Наші замовники — це продакт-менеджери, представники комерції та маркетингу, партнери та рекламодавці, які дивляться статистику в особистому кабінеті.
 
Призначена для користувача статистика нараховує від 21 до 27 параметрів. Вона включає в себе місто, рубрику, фірму і так далі.
 
Велика кількість параметрів подій веде до великої кількості звітів: сумарні показники, середні значення, відхилення, топ-10, -100, -1000 і ще багато чого. При такому розкладі важко передбачити, яка саме інформація стане в нагоді завтра. А коли ця необхідність з'явиться, надати дані буде потрібно as soon as possible.
 
Знайомо?
 
 В цифрах
26 млн користувачів 2ГІС формують в добу близько 200 млн подій. Це приблизно 2400 rps, які треба отримати, обробити і зберегти. Отримані дані потрібно оптимізувати для довільних Ad hoc і аналітичних запитів.
 
Задача полягає в наступному:
— Підготувати дані (ETL). Це найбільш значимий і трудомісткий етап.
— Розрахувати агрегати (препроцессінг).
 
Для початку вирішимо перше питання.
 
 Як було раніше
Колись давно система нашої бізнес-аналітики виглядала зовсім інакше. Вона відмінно підходила для роботи з невеликою кількістю міст, але коли ми вийшли на нові ринки, ця система виявилася громіздкою і незручною:
 
 
     
  1. Дані зберігалися в НЕ розбитих по секціях таблицях і оновлювалися стандартними «insert» і «update». Операції застосовувалися до всього масиву даних.
  2.  
  3. При нових запитах до даних, таблиці обростали індексами, які:
    а) доводилося перебудовувати при отриманні нових даних;
    б) займали все більше і більше місця.
  4.  
  5. Операції «join» багатомільйонних таблиць були майже неможливі.
  6.  
  7. Адміністративні операції — «резервне копіювання», «стиснення» і «перестроювання індексів» — займали багато часу.
  8.  
  9. Для обробки багатовимірної бази даних доводилося щодня обробляти весь масив даних. Навіть ті, які не змінилися.
  10.  
  11. Аналітичні запити до багатовимірної базі даних також займали багато часу.
  12.  
Тому ми вирішили обробляти дані по-іншому.
 
 Новий підхід
 Секціонування + файли + файлові групи
Секціонування — це подання таблиці в якості єдиної логічної сутності, в той час як її частини — секції — можуть фізично знаходитися в різних файлах і файлових групах.
 
Таблиця розбивається на секції за допомогою функції секціонування. Вона визначає межі діапазонів для значень стовпця секціонування. На основі функції секціонування будується схема секціонування. Вибір функції секціонування є ключовим, тому що профіт в запитах на вибірку даних буде лише при використанні в запиті стовпця секціонування. В цьому випадку схема секціонування вкаже, де шукати необхідні дані.
 
 
 
Найчастіше в якості функції секціонування вибирають час (день, місяць, рік). Це обумовлено історичністю даних: старі дані не змінюються. А значить, що секції, в яких вони знаходяться, можна помістити в файлові групи і звертатися до них тільки в міру необхідності пошуку в старих періодах. Для економії ресурсів їх можна навіть покласти на повільні диски.
 
Ми в якості функції секціонування вибрали місяць, т.к. велика частина запитів будується помісячно.
 
 
 
Однак при цьому з'являється кілька проблем.
 
 
     
  1. Вставка все ще відбувається у велику таблицю. Якщо на ній є індекс, то вставка нових даних призведе до перестроению індексу, а збільшення числа індексів — до неминучого гальмуванню операції вставки нових даних.
  2.  
  3. Для секціонування багатовимірних баз даних Microsoft рекомендує використовувати секції до 20 млн записів. Наші секції виявилися на порядок більше. Це загрожувало нам провалами в продуктивності на етапі препроцессінга. Неконтрольоване зростання розміру секцій міг звести нанівець всю ідею секціонування.
  4.  
Щоб вирішити друге питання, ми збільшили число секцій для кожного періоду. Якщо як функції секціонування використовувати місяць і такий собі порядковий номер секції цього місяця, вийде наступне.
 
 
 
З першою проблемою було складніше. Ми з нею впоралися, але щоб оцінити наше рішення, потрібно знати про Columnstore index.
 
 Columnstore index
Справедливості заради варто сказати, що Columnstore index — це не індекс в класичному розумінні. Він працює по-іншому .
 
MS SQL Server починаючи з версії 2012 року обслуговує Columnstore — зберігання даних в шпальтах. На відміну від звичайного зберігання даних в рядках, інформація там групується і зберігається по 1 стовпцю за раз.
 
Такий формат має ряд переваг:
 
— Читати тільки ті стовпці, які ми запитуємо. Деякі стовпці можуть взагалі ніколи не потрапити в пам'ять.
— Стовпці сильно стискаються. Це скорочує байти, які треба прочитати.
— В індексі Columnstore не існує поняття ключових стовпців. Обмеження числа ключових стовпців в індексі (16) не застосовується до індексів Columnstore. У нашому випадку це важливо, тому що число параметрів (стовпців Rowstore) значно більше 16.
— Індекси Columnstore працюють з секціонуванням таблиць. Columnstore на секціонірованной таблиці має бути вирівняний по секціях з базовою таблицею. Таким чином, некластерізованний індекс Columnstore може бути створений для секціонірованной таблиці, тільки якщо стовпець секціонування є одним з стовпців в цьому індексі. Для нас це не проблема, тому що секціонування виробляється за часом.
 
 
 
«Відмінно!» — Подумали ми. — «Це те що треба». Однак одна особливість Columnstore index виявилася проблемою: в SQL Server 2012 таблиця з індексом Columnstore не може бути оновлена. Операції «insert», «update», «delete» і «merge» неприпустимі.
 
Варіант видалення і перестроювання індексу при кожній операції вставки даних виявився непридатним. Тому завдання ми вирішили за допомогою перемикання секцій.
 
 Перемикання секцій
Повернемося до нашої таблиці. Тепер вона з Columnstore index.
 
 
 
Створимо ще одну таблицю з наступними властивостями:
 
— Все ті ж стовпці і типи даних;
— Таке ж секціонування, тільки по 1 секції для кожного місяця;
— Без Columnstore index.
 
 
 
В неї ми заливаємо нові дані: будемо перекладати звідти секції в стабільну таблицю.
 
Поїхали.
 
 Крок 1 Визначаємо секції, що вимагають перемикання. Нам потрібні секції по 20 млн записів. Завантажуємо дані і на певній ітерації виявляємо, що одна із секцій наповнилася.
 
 
 
 Крок 2 У стабільній таблиці створюємо секцію під перемикані дані. Секцію треба створити у відповідній файлової групі — Жовтень 2013. Наявна порожня секція (14) в файлової групі вересня нам не підходить. Секцію (15) створюємо для завантаження туди даних. Плюс, робимо одну зайву секцію (16), яку будемо «розмножувати» наступного разу, т.к. завжди для розмноження потрібна одна порожня секція в кінці.
 
 
 
 Крок 3 Перемикаємо секцію-призначення в проміжну таблицю.
 
 
 
 Крок 4 Заливаємо дані з таблиці для завантаження даних в проміжну таблицю. Після цього на проміжній таблиці можна створити Columnstore index. На 20 млн записів це робиться дуже швидко.
 
 
 
 Крок 5 Перемикаємо секцію з проміжної таблиці в стабільну.
 
Тепер:
 
— Стовпці і типи даних збігаються;
— Нова секція у відповідній цій секції файлової групі;
— Columnstore index у новій секції ми вже створили, і він повністю відповідає індексу стабільної таблиці.
 
 
 
 Крок 6 Для повної чистоти закриємо в стабільній таблиці порожню (14) секцію, яка нам вже не потрібна.
 
 
 
Підсумок — таблиця для завантаження знову готова до прийому даних.
 
 
 
Стабільна таблиця поповнилася однією секцією (15). Остання секція (16) готова до розмноження, ідентичному кроку 2
 
 
 
В цілому завдання досягнута. Звідси можна переходити до препроцессінгу (попередній підготовці) даних.
 
 Обробка багатовимірної бази даних (OLAP)
Нагадаю: нам треба забезпечити online-аналіз даних по довільному набору стовпців з довільною фільтрацією, угрупованням і сортуванням. Для цього ми вирішили скористатися багатовимірної базою даних OLAP, яка також підтримує секціонування.
 
Створюємо секції, ідентичні нашим таблицями. Тільки для основної «стабільної» таблиці нарізаємо секції 1-в-1 відповідно до реляційної базою даних.
А для таблиці завантаження цілком вистачить однієї загальної секції.
 
Тепер ми отримали прораховані агрегати (суми) по декартову добутку всіх параметрів в кожній секції. Під час користувальницького запиту багатовимірна база даних прочитає секції, відповідні запиту, і підсумує агрегати між собою.
 
 
 
 Стиснення старих періодів
 
Отже, ми зробили все, як написано вище, але виявили, що один і той же місяць розмазаний по значному числу секцій. При цьому число секцій для кожного місяця зростає зростанням кількості користувачів, міст покриття, платформ і т.д.
 
Це щомісяця збільшує час підготовки звіту. Та й просто — займає зайве місце на диску.
 
Ми проаналізували вміст секцій одного місяця і дійшли висновку, що можемо стиснути його, агрегируя по всіх значимих полям. Деталізації за часом нам виявилося достатньо з точністю до дати. Стиснення старих періодів особливо ефективно, коли історичні дані вже не надходять і число секцій для стиснення максимально.
 
Як ми це робили:
 
 Крок 1 Визначаємо всі секції одного місяця.
 
 
 
 Крок 2 Додаємо залишки з таблиці завантаження.
 
 
 
 Крок 3 агрегируя по всіх значимих полям. Операція проводиться приблизно раз на місяць, тому тут цілком можна пожертвувати ресурси.
 
На цьому етапі може виявитися, що розмір отриманих секцій не відповідає нашим ідеальним 20 млн. З цим нічого не поробиш. Принаймні, те, що остання секція кожного місяця буде неповною, ніяк не впливає на продуктивність.
 
 
 
 Крок 4 Наприкінці не забуваємо перекроїти багатовимірну базу даних. Повна обробка займає близько 5-6 годин. Це цілком припустимо для щомісячної операції.
 
 
 
 Підсумки
 Секціонування для великих таблиць — це must have
Секціонування дозволяє розмазувати навантаження по файлах, файловим групам і дискам. При цьому навіть з використанням секціонування розмір має значення.
 
Ми ставили перед собою мету сформувати 20-млн секції, щоб використовувати їх надалі для побудови багатовимірної бази даних. У кожному конкретному випадку розмір секції повинен визначатися розв'язуваної завданням.
 
Також критично важлива функція секціонування.
 
 Columnstore index вирішує!
Ми покрили всі Ad hoc запити. У нас немає необхідності створювати / перебудовувати індекси при появі нових задач на вибірку даних.
 
Реалізація Columnstore index в MS SQL Server 2012 фактично дублює вихідну Rowdata таблицю, створюючи таку ж, але з колоночного зберіганням.
 
Проте об'єм даних, займаний індексом значно менше, ніж якби ми створювали набір спеціальних індексів під кожну задачу.
 
Обмеження на insert цілком обходиться перемиканням секцій.
 
 Підсумки в цифрах
Наприклад, одна з таблиць: 3940403086 рядків; 285 887,039 MB
                    
Час виконання запиту Секціонірованние таблиця Секціонірованние таблиця + Columnstore Багатомірна база даних OLAP
Кількість дзвінків 5-го травня з iPhone-версії в Москві 8 хв. 3 сек. 7 сек. 6 сек.
Фізичний розмір подій типу А 285,9 GB 285,9 GB
+ 0,7 GB index
67 GB
 Які ще є варіанти?
 

Чи не MS

Історично склалося, що вся Enterprise-розробка в компанії будується на базі рішень Microsoft. Ми пішли по цьому ж шляху й інші варіанти не розглядали в принципі. Благо MS SQL Server підтримує роботу з великими таблицями на всіх рівнях обробки. Сюди входять:
 
— Реляційна база даних (Data Warehousing);
— Sql Server Integration Services (ETL);
— Sql Server Analysis Services (OLAP).
 
 

MS SQL Server 2014

В SQL Server 2014 функціональність Columnstore була розширена, він став кластерним . Знову надходять дані потрапляють в Deltastore — традиційне (Rowstore) зберігання даних, які в міру накопичення переключаються до основного Columnstore.
Якщо вам не потрібно чітко фіксувати розмір секцій, SQL Server 2014 буде відмінним рішенням для збору, обробки та аналізу користувальницької статистики.
    
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.