SQL-базы Авиабазы

Теги:авиабаза
 
+
-
edit
 

Balancer
Guest

гость
АвиаТОП.
Две таблицы.
avia_top_counts - собственно, счётчики.

id INT(11) - идентификатор ресурса (номер сайта-участника)
count INT(11) - счётчик хитов
start INT(11) - дата запуска счётчика в UNIX Time
ips LONGTEXT - список IP-адресов последних посетителей с временем их заходов, чтобы не считать релоады. В принципе, можно оптимизировать, т.к. является наследием времён plain/text-формата базы.

В данный момент содержит 147 записей суммарным объёмом 17кБ.
При каждом вызове картинки счётчика обновляется по
UPDATE avia_top_counts SET count=count+1 WHERE id='$id';
UPDATE avia_top_counts SET ips='$ips' WHERE id='$id';

Извлекается также несколько раз:
SELECT * FROM avia_top_counts WHERE id='$id'
При каждой генерации иконки считается место в рейтинге:
SELECT * FROM avia_top_counts WHERE 86400*count/(UNIX_TIMESTAMP()-start+1)>$rate AND (count>50 OR UNIX_TIMESTAMP()-start>86400);

avia_top_pages - счётчик посещений отдельных страниц.

url VARCHAR(255) - url считаемой страницы, он же уникальный идентификатор
count INT(11) - счётчик хитов на этой странице
start INT(11) - дата первого обращения в UNIX Time
last INT(11) - дата последнего обращения в UNIX Time (требуется для удаления устаревших страниц, к которым давно не обращались)
title VARCHAR(255) - заголовок страницы (извлекается из <title>...</title>, т.к. операция длительная, то кешируется)

Обновляется при каждом обращении по
UPDATE avia_top_pages SET start=start, count=count+1, last=UNIX_TIMESTAMP() WHERE url='$url';

Извлекается редко, в рейтинге страниц или в случайном переходе.
 

KeyOS

новичок
А сколько в среднем каждый запрос делается в день ?
Ну и souce code неплохо было бы :F
 
+
-
edit
 

Voennich

опытный

KRoN - кроме идентификаторов больше индексов нет ?
Когда у лицемеров не достаёт аргументов, они переходят на персоналии и начинают придумывать характеристики и мотивацию оппонента. Обличённые властью - придумывают несуществующие нарушения правил. Bredonosec - в игноре, пока не выучит школьный курс физики  
+
-
edit
 

=KRoN=
Guest

гость
Запросов, как я уже говорил, точно не знаю. Предпологаю, что ~100тыс. в сутки.

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

Индекс - да, в данном случае только идентификатор ресурса. Все остальные данные берутся/пишутся по нему.
 
+
-
edit
 

Voennich

опытный

KRoN
я не общался с MySQL но если бы это был MS SQL то обязательно добавил индекс
create index avia_top_counts_StartCount
on avia_top_counts (start,count)
и
create index avia_top_counts_CountStart
on avia_top_counts (count, start)


пусть те кто работал с MySQL поболе поправят если я не прав
Когда у лицемеров не достаёт аргументов, они переходят на персоналии и начинают придумывать характеристики и мотивацию оппонента. Обличённые властью - придумывают несуществующие нарушения правил. Bredonosec - в игноре, пока не выучит школьный курс физики  
+
-
edit
 

=KRoN=
Guest

гость
А как оно (теоретическое обоснование) на скорость повлияет? :) Я понимаю, делать хэш на id, но он итак делается. А все эти start, count и т.п. берутся из уже считанной записи.

На самом деле, я в MySQL близок к нулю - его применение в АвиаТОП'е - это моя вторая прога (первая - кеширование индексов форума, третья - поисковая система) с MySQL. Книжек по нему не читал, документацию - только по поиску по ключевым словам CREATE/INSERT/UPDATE :)
 

KeyOS

новичок
Ну индексы ускоряют поиск.
Для всех выборок важно, чтобы любое поле "mama" из "where mama=???" было индексированно.

Тут есть свои плюсы и минусы
Я бы посоветовал почитать How MySQL Uses Indexes, чтобы иметь представление.

В принципе если решишь код показать то добавь к нему описание всех таблиц (из SQL интерфейса можешь запустить "desc table_name;" чтобы получить результат.
 
+
-
edit
 

Voennich

опытный

если на двух пальцах то :
Индексы позволяют уменьшить зону поиска и не перебирать всю таблицу
Когда у лицемеров не достаёт аргументов, они переходят на персоналии и начинают придумывать характеристики и мотивацию оппонента. Обличённые властью - придумывают несуществующие нарушения правил. Bredonosec - в игноре, пока не выучит школьный курс физики  
+
-
edit
 

Mishka

модератор
★★★
Voennich>KRoN
Voennich>я не общался с MySQL но если бы это был MS SQL то обязательно добавил индекс
Voennich>create index avia_top_counts_StartCount
Voennich>on avia_top_counts (start,count)
Voennich
Voennich>create index avia_top_counts_CountStart
Voennich>on avia_top_counts (count, start)


из приведенных выше SQL statements следует, что единственным критерием поиска есть идентификатор ресурса. В этих условиях никаких других индексов не надо - не поможет. Индекс сужает зону поиска в том случае, если по нему он идет. А здесь в where clause только идентификатор и лежит. В почти каждой приличной СУБД можно посмотреть план выполнения SQL statement - в MySQL этому служит EXPLAIN. Например,
EXPLAIN SELECT ...
Это, кстати стоит делать всякий раз при написании SQL statemet - чтобы посмотреть, что ваше мнение совпадает с мнением СУБД. В более сложных СУБД надо обязательно обновлять статистику и кластеризировать таблицы.

Самый сложный SQL statement из приведенных (и кстати, единственный, который не использует идентификатор рессурса) - это поиск словарный, но поскольку там % стоят и до и после, то индексы тоже не конают - только последовательный линейный поиск. Можно предложить схему и решения этих задач, но надо заводить специальные тригеры и таблицы - скажем, каждое слово попадающее в базу подвергаем обработку - ищем все подстроки длиной 1, 2, 3, ну 4 скажем. Объем таких строк ограничер 1 + N + N2 + N3 + N4, где N количество букв в алфавите (алфавит как абстракция). после этого на каждый такой случай и использовать это как индекс в хэш таблице для получения списка индексов где это слово встречвается. Здесь за счет увеличения таблиц косвенной индексации и связанно-подчиненных SQL statements можно получить значительный выигрыш при больших текстовых объемах.
 
+
-
edit
 

Mishka

модератор
★★★
Пардон, был неправ в отношении иконочного SQL - там есть и count и start.
 
+
-
edit
 

Mishka

модератор
★★★
не могу запостить ответ, все время получаю

Sorry, we do not permit this HTML tag: Parenthesis in HTML tag
 
+
-
edit
 

Mishka

модератор
★★★
ладно, по частям.

=KRoN=>SELECT * FROM avia_top_counts WHERE 86400*count/(UNIX_TIMESTAMP()-start+1)>$rate AND (count>50 OR UNIX_TIMESTAMP()-start>86400);

Переделаем в
 
+
-
edit
 
+
-
edit
 

Mishka

модератор
★★★
WHERE start>(UNIX_TIMESTAMP()-1)-86400*count/$rate
 
+
-
edit
 

Mishka

модератор
★★★
Mishka]AND (count]50 OR UNIX_TIMESTAMP()-86400]start)

Mishka]тогда смысл в индексам amvich-a появляется. Если еще и заменить UNIX_TIMESTAMP на вызов аналогичной функции из программы и вычислять выражения, которые должны быть константой с точки зрения базы и передавать их параметрами, то все еще быстрее заработает.

Блин, еле разобрался - знак меньше воспринимался как начало тега и шла ругань. Как только я перенес start в правую половину - все заработало.
 
+
-
edit
 

Mishka

модератор
★★★
Mishka>AND (count>50 OR UNIX_TIMESTAMP()-86400>start)

Mishka>тогда смысл в индексам amvich-a появляется. Если еще и заменить UNIX_TIMESTAMP на вызов аналогичной функции из программы и вычислять выражения, которые должны быть константой с точки зрения базы и передавать их параметрами, то все еще быстрее заработает.

Блин, еле разобрался - знак меньше воспринимался как начало тега и шла ругань. Как только я перенес start в правую половину - все заработало.
 
+
-
edit
 

avmich

координатор

На всякий случай еще раз и полностью.

=KRoN=>SELECT * FROM avia_top_counts WHERE 86400*count/(UNIX_TIMESTAMP()-start+1)>$rate AND (count>50 OR UNIX_TIMESTAMP()-start>86400);

Переделаем в

SELECT *
FROM avia_top_counts
WHERE start > ( UNIX_TIMESTAMP( ) - 1 ) - 86400 * count / $rate
AND ( count > 50 OR UNIX_TIMESTAMP( ) - 86400 > start )

тогда смысл в индексам amvich-a появляется. Если еще и заменить UNIX_TIMESTAMP( ) на вызов аналогичной функции из программы и вычислять выражения, которые должны быть константой с точки зрения базы и передавать их параметрами, то все еще быстрее заработает.
 
+
-
edit
 

Mishka

модератор
★★★
По поводу переиндексации Базы. Можно ведь делать так: делаем копию всех данных (ну, всех изменённых данных), дапускаем процесс в фоновом режиме, который всё потихоньку переиндексирует. Совершенно всё равно, когда он закончит, так как его данные не меняются. Поэтому приоритет у этого процесса низкий, и нагрузку большую он не создаёт. В результате получаем слегка устаревший индекс, на том же компьютере.
 
+
-
edit
 

avmich

координатор

Во, надо еще попробовать Stored Procedure - они храняться оттранслированными. А трансляция занимает много времени.
 
+
-
edit
 

avmich

координатор

Под переиндексацией Вы понимаете drop/create index + statistics processing?
 
+
-
edit
 

Mishka

модератор
★★★
Ну, например? Или как там это делается в Базе?
 
+
-
edit
 

Mishka

модератор
★★★
Да фиг его знает, я только сгрузил MySQL и доки к нему и сижу читаю и играюсь в свободное от работы время. Я как все более с Informix-ом работал. Там я могу сказать что-то определнное - индексы сами пересчитывались, а вот статистику можно было запускать ручками, в фоновом режиме. А статистика в Informix-е играет офигенно большую роль при составлении плана исполнения SQL statements. Особенно при граммотной кластеризации таблиц - он учитывает количество данных на кластере и порядок их обхода, а так же может поменять местами обход таблиц в joint-е или порядок применения индексов. По своей мощи вроде был поумнее чем у Oracle, только одно было плохо - нельзя было этот ум отключить.
 
+
-
edit
 

Mishka

модератор
★★★
В общем, если указанные SQL-ки, это все, что используется на авиабазе, то надо немного переделать и все должно быть хорошо. Надо бы еще посмотреть и исходный код. Если у нас CGI стартует на каждый вызов, то я бы завел процесс с shared memory, который бы открыл сеанс с СУБД, подготовил бы все SQL statements, а из прог бы присоединялся к shared memory и путем небольшой библиотечки ставил бы запросы к этому процессу. Если надо, то это я могу написать, только операционнку надо знать. Т.к. у них немного работа с shred memory разная - да и примитивы синхронизации разные. Я делал такое уже для HP-UX, AIX, Dynix/ptx. Под Фряху и Линух еще не делал :)
 
+
-
edit
 

Balancer
Guest

гость
Если оптимизировать сервер под Базу, то можно какой-нибудь mod для Апача написать, который реализует Форумы... если такого нет ещё... Тогда проблема скриптов (т.е. дорогого создания процесса для обработки каждого запроса - а это, думаю, существенная часть нагрузки) уменьшится. Недостаток - менее конфигурируемое решение. Ну, с другой стороны, костяк функциональности мало меняется...

Наверное, я что-то придумываю...
 
+
-
edit
 

Balancer
Guest

гость
Mishka>Переделаем в
Mishka>SELECT *

Попробую.

avmich>дапускаем процесс в фоновом режиме, который всё потихоньку переиндексирует. Совершенно всё равно, когда он закончит

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

, так как его данные не меняются. Поэтому приоритет у этого процесса низкий, и нагрузку большую он не создаёт. В результате получаем слегка устаревший индекс, на том же компьютере.
 

в начало страницы | новое
 
Поиск
Настройки
Твиттер сайта
Статистика
Рейтинг@Mail.ru