SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 28.03.2011 14:18:29

ravlio
Участник
Зарегистрирован: 27.03.2011
Сообщений: 3

Оптимизация записи и группировки статистики под большим трафиком

Добрый день. Прошу помочь решить комплексную задачу. То что есть сейчас: есть поток живого трафика 1-2млн уникальных посетителей в сутки. Есть софт, выполняющий функцию логирования и нормализации каждого захода. Как сейчас это работает:

Каждый посетитель при заходе наделяется рядом аттрибутов, которые являются группирующими (site_id, partner_id, country_id, date) Записи кладутся в следующую таблицу:

Код:

CREATE TABLE IF NOT EXISTS `stats_last` (
  `hash` varbinary(32) NOT NULL DEFAULT '0',
  `site_id` int(11) NOT NULL DEFAULT '0',
  `partner_id` int(11) NOT NULL DEFAULT '0',
  `country_id` int(11) NOT NULL DEFAULT '0',
  `date` int(11) unsigned DEFAULT NULL,
  `hit` int(11) NOT NULL,
  PRIMARY KEY (`hash`),
  KEY `site_id` (`site_id`),
  KEY `partner_id` (`partner_id`),
  KEY `country_id` (`country_id`),
  KEY `date` (`date`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

Кладутся следующим образом:

Код:

$hash=md5($site_id.$partner_id.$country_id.$date);

mysql_query("INSERT INTO stats_last SET site_id=$site_id, partner_id=$partner_id, $country_id=$country_id, $date=$date, hit=1 ON DUPLICATE ENTRY UPDATE stats_last SET hit=hit+1 WHERE hash='$hash'");

Т.е. хэш используется ввиде уникального первичного ключа для того, чтобы упростить задачу проверки наличия записи и инкремента hit


Далее, раз в минуту таблица stats_last копируется в таблицу stats_last_fixed (INSERT INTO stats_last_fixed SELECT * FROM stats_last) После чего работа ведется с таблицей stats_last_fixed.

Далее идет нормализация:

Код:

$bysite=mysql_result("SELECT SUM(hit) FROM stats_last_fixed GROUP BY site_id");
$bypartner=mysql_result("SELECT SUM(hit) FROM stats_last_fixed GROUP BY partner_id");
$bycountry=mysql_result("SELECT SUM(hit) FROM stats_last_fixed GROUP BY country");
$bydate=mysql_result("SELECT SUM(hit) FROM stats_last_fixed GROUP BY date");

// и далее вставка в таблицы stats_bysite, stats_bypartner, stats_bycountry, stats_bydate
// hash уже вычисляется вида $hash=md5($site_id.$date);

Пример нормальной таблицы:

Код:

CREATE TABLE IF NOT EXISTS `stats_bysite` (
  `hash` varbinary(32) NOT NULL DEFAULT '0',
  `site_id` int(11) NOT NULL DEFAULT '0',
  `date` int(11) unsigned DEFAULT NULL,
  `hit` int(11) NOT NULL,
  PRIMARY KEY (`hash`),
  KEY `site_id` (`site_id`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Т.е. хеш здесь упраздняется до двух атрибутов - основного (в данном случае, site_id) и дополнительного (date) Т.к. можно, к примеру, посмотреть статистику по сайту за определенное время.

На этом логирование заканчивается. Остальные запросы касаются выборки статистики и они не грузят систему.

Система показала себя хорошо, но на большом трафике в 2млн уникальных заходов на сервере dual xeon 8gb RAM происходят тормоза в двух местах: во время записи в stats_last и во время нормализации (на самом деле группирующих атрибутов больше, порядка 10 штук, соответственно, stats_last раскидывается по 10и постоянно растущим таблицам) Плюс, чем больше накапливается статистики, тем тормознее становится вся система.

Просьба покритиковать данное решение. Я чувствую, что нормальные таблицы можно как-то архивировать. К примеру, делать stats_bysite1hour, stats_bysite1day, stats_bysite1week Чтобы работа на больших нагрузках велась с маленькими таблицами. Но вот только как это дело автоматизировать? Если я сделаю stats_bysite1hour, то выиграю в скорости записи туда раз в минуту. Но раз в час мне надо будет склеивать все данные с таблицей stats_bysite1day - не потеряю ли я в производительности здесь? И как не потерять данные при этом, как не подвесить всё?

Если делать архивы, то как делать выборку для отображения статистики? Если я сейчас делаю просто SELECT SUM(hit) FROM stats_bysite WHERE site_id=10, то, чтобы достать статсы из архива, нужно будет делать джойны всех таблиц - опять же, потеря в скорости.

Есть ли какие-то стандартные средства у MySQL для подобного рода задач? Может есть уже готовые примеры? У других ведь как-то всё работает, не тормозит.. Интересно, как работают spylog, liveinternet и другие счетчики..

Неактивен

 

#2 28.03.2011 21:33:05

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Оптимизация записи и группировки статистики под большим трафиком

Кажется, я на этот вопрос уже отвечал smile

Счетчики работают, используя два дополнительных принципа:
а) они никогда не считают онлайн
б) они используют шардирование (т.е. разные пользователи складываются
на разные машинки)

Можете, например, складывать «сырые» данные в табличку MEMORY. Если пользо-
ватель сделал 2-3 хита на сайте, Вы на нем сэкономили в 2-3 раза. Раз в некоторое
количество времени (например, раз в 10 минут) нужно эту табличку агрегировать и
помещать в постоянное хранилище. Опять же — это хранилище не стоит хранить
бесконечно. Данные следует агрегировать и помещать в б́ольшую табличку.
Наконец, можно эти б́ольшие таблички складировать по месяцам (в конце концов,
это же архив) и сжимать (myisampack) для экономии места.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson