Использование MySQL в сервисе дневников LiveInternet.ru Практика, практика, практика Гурьянов Андрей, программист Новиков Лев, системный администратор Любимов Валентин, руководитель разработки

Схема доклада Проблемы при разработке архитектуры Серверное устройство сервиса, Принципы масштабирования базы данных: декомпозиция запросов и таблиц, отказ от JOIN Принципы масштабирования базы данных: ручное партиционирование, схема архив- свежак, репликации Кеширование memcached Планы по развитию и дальнейшему масштабированию архитектуры

Проблемы при разработке архитектуры Текущая нагрузка сервиса дневников: до 500 запросов к php файлам в секунду в пиковое время. Нет страниц, на которые приходится на порядок большее чем к остальным число запросов, на отдачу страницы требуется 5-50 SQL запросов. Средний elapsed-time – 330 ms, до 10 тысяч запросов к MySQL в секунду. Высокая скорость обновления информации (практически при каждом 10м переходе со страницы на страницу), очень много счетчиков (пример – добавление сообщения). Каждая страница сайта может выглядеть по своему для разных пользователей(уровни доступа к разной информации), сложности с кешированием.

Серверное устройство 2 front-end сервера, DNS-balansing, 0w-http 3 сервера с маленькой БД (3-6 Гб) (она целиком в памяти на рамдиске, на том же сервере ее репликация) 1 сервер с большой БД (40 Гб) 5 серверов выполняющих PHP (софт-балансировка нагрузки 0w-http) 3 сервера с memcache (не дает нагрузки на CPU, разнесены на свободные куски памяти других серверов) 1 сервер с backup-репликациями всех БД. Операционные системы – freebsd на серверах с PHP, gentoo на MySQL. Железо – 1-2U сервера AMD64 16Gb. Версии mysql 4.1 MyISAM, php

Принципы масштабирования БД Цель оптимизации – минимизация времени работы запроса(т.е. минимизация ожидания освобождения таблицы) Запрос c JOIN меняем на два запроса – получения ID удовлетворяющих условиям из одной таблицы и получение нужных значений из второй таблицы через WHERE id IN (…). Тогда таблицы могут находиться в разных БД, на разных серверах, такие запросы требуют меньше ресурсов для вычисления, меньше время ожидания высвобождения ресурсов. Минимизация взаимосвязей различных подсервисов в БД. Дублирование редко изменяемых данных в разных таблицах (имя, дата рождения, город и т.п.). Разные части сервиса изначально готовы к переносу их таблиц с сервера на сервер. Минимизация перестроения индексов при insert/update. Например таблица user разделена на четыре части – данные авторизации user, счетчики user_cnt, списки user_list, опции user_options. Первая – почти не меняется, индексы для выборок. Остальные таблицы без индексов, кроме первичного, меняются часто. Плюсы – не происходит дублирования данных, нет деградации кеширования. Минусы – рост числа запросов на страницу (не все в одной, а в разных)

Ручное партиционирование, репликации Задачи все те же – минимизация скорости обработки запросов, минимизация стоимости update/insert. Воображаемые таблицы сообщений и комментариев разбиваются каждая на 101 таблицу. В одной хранятся сообщения за первые N дней (например неделю), в остальных 100 – разделенные по id%100 дневника остальные сообщения (архив). Добавление новых сообщений идет только в таблицу последних сообщений. В архив идут только редкие UPDATE и раз в сутки перенос устаревших сообщений. Обобщенные запросы, типа ленты друзей или ленты последних комментариев показываются только из быстрой таблицы (более старые все равно редко нужны) Репликации используются только для резервного копирования и различных вспомогательных вещей (например поиск по сообщениям).

Кеширование memcached Неудовлетворительность кеширования страниц дневников – их слишком много, они большие (60кб в среднем) и запрашиваются в том числе и старые. Но экспериментируем с этим. Партиционирование также делаем вручную по id дневника (в будущем возможна архитектура, где и данные и отдача дневника всегда на одном сервере, сейчас не так) Дополнительные индексы для БД в memcached – очень эффективно. Например список id записей дневника в обратном порядке. С его помощью отдача страницы дневника сначала берет этот индекс (массив), потом делает выборку по первичному ключу списка сообщений.

Планы по дальнейшему развитию Дальнейшее дробление баз и таблиц, до логического предела еще есть куда двигаться. Исследование возможности полного дублирования той же системы (на этом кластере старые дневники, на этом новые, а общие рейтинги – ленты друзей склеивать) Достижение большей эффективности от кеширования. Использование репликации не только для бекапа. Внешние хранилища разного типа (например поисковый индекс для ленты друзей) По оптимистичным планам к концу года задан рост посещаемости в 3-5 раз от текущих значений.

Спасибо, критикуйте Спасибо за внимание. По всем вопросам пишите Валентин Любимов