opentorrent

Каждый год весной у людей что-то происходит с головой. Иногда всплывают клинические неадекваты. В этом году сезон открыл абонент дом.ру (как оказалось позже, он же - счастливый обладатель выделенки от АБВ и "свистка" от МТС) из Нижнего Новгорода. В пхпбб есть блок по IP-адресам. Но мы выстругали, подобно папе Карло, своего Буратину, который умеет заворачивать сетки одним поворотом деревянного носа.


tracker=# create table banned_networks (network cidr, descr text, status integer not null default 1);
CREATE TABLE
tracker=# insert into banned_networks values (network, descr) values ('95.79.0.0/16', 'Нижегородский дом.ру. Динамика. Оттуда регистрируется пользователь и пишет на форуме мат');
INSERT 0 1
tracker=#


Пишем функцию на перле с использованием мемкэша:


CREATE FUNCTION check_banned_networks(cidr) RETURNS integer AS $$
use Cache::Memcached;
use Net::IPv4Addr qw( :all );
my ($ip) = @_;
my $ttl=600;
my $key='check_banned_networks_'.$ip;
my $cache = Cache::Memcached->new({servers => ["192.168.200.1:63922"], debug => 0});
my $status, $row;
if ( defined($cache->get($key)) ) {$status=$cache->get($key);}
else
{
my $sth = spi_query("SELECT network FROM banned_networks WHERE status=1");
while (defined ($row = spi_fetchrow($sth))) {
if (ipv4_in_network($row->{network}, $ip ) ) {$status=1; break;}

}
$status=0 if !$status;
$cache->set($key, $status, $ttl);
}
$cache->disconnect_all;
return $status;
$$ LANGUAGE plperlu;


Результат работы:


tracker=# SELECT check_banned_networks('95.79.139.117');
check_banned_networks
-----------------------
1
(1 row)

tracker=# SELECT check_banned_networks('92.55.55.55');
check_banned_networks
-----------------------
0
(1 row)



Нужно еще expire_dt добавить, чтобы бан грохался по шедулеру.

Все началось с того, что kolras предложил сделать кнопку или просто автоудаление системных ЛС от ботов, как то: архивирование раздачи, зов сидера и прочая хреновина, которая замусоривает ЛС со временем. В итоге было обдумано, что флудят в ЛС не только боты, поэтому нужен инструмент, который позволяет грохать кучу ЛС парой кликов, не вынуждая проставлять около каждого соответствующую галочку.

У нас давненько висела маленькая задача по улучшению ТТХ форумной части ресурса.


Логика обработки запросов на добавление поста у нас хранится в базе данных, благодаря чему наложить дополнительный фильтр с использованием FTS на слоне сможет даже пионер. ;)

Фильтр реагирует на посты не длиннее 20 символов. Примеры реакций:


Таким образом, при отправке очередного краткого поста со "спасибой" счетчик (см. в шапку топика) с благодарностями увеличится на 1, а самого благодарящего перекинет на первый пост темы.

Да, нужно подкручивать FTS. Но это немножко другая история.

Длительное время висел таск по сабжу. Ровно до тех пор, пока не надоело подменять картинки руками.

Для решения задачи фактически нужны только день и месяц. К году не имеет смысла привязываться по той (более, чем просто очевидной) причине, что привязка к году будет означать то, что дата наступит только один раз.

Исходя из этого спроектирована простая таблица:
create table phpbb_logos (logo_type character varying(20) not null default 'holiday', path character varying(200), day integer, month integer);

В ней есть несколько записей:


Картинка с лого запрашивается сотни и десятки тысяч раз в сутки, поэтому привязка к базе без кэширования будет автоматически означать ровно такое же количество бесполезных запросов, убивающих СУБД.

Мы работаем со слоником, а слоник умеет plperl(u).

Пишем простенькую функцию:


CREATE FUNCTION get_current_logopath() RETURNS character varying AS $$
use Cache::Memcached;
my $ttl=1800;
my $key='phpbb_current_logo';
my $cache = Cache::Memcached->new({servers => ["192.168.200.1:63922"], debug => 0});
my $path;
if ( defined($cache->get($key)) ) {$path=$cache->get($key);}
else
{
my $row;
my $sth = spi_query("
SELECT path FROM phpbb_logos
WHERE
day BETWEEN EXTRACT(DAY FROM NOW()-INTERVAL '60' MINUTE) AND EXTRACT(DAY FROM NOW()+INTERVAL '60' MINUTE)
AND month=EXTRACT(MONTH FROM NOW())
ORDER BY RANDOM() LIMIT 1
");
$row = spi_fetchrow($sth);
$path=$row->{path};
if (!defined($path))
{
$path=spi_fetchrow(spi_query("SELECT path FROM phpbb_logos WHERE logo_type='default'"))->{path};
}
$cache->set($key, $path, $ttl);
}
$cache->disconnect_all;
return $path;
$$ LANGUAGE plperlu;


Лого кэшируем и меняем его с границами плюс-минус 60 минут до-после наступления праздничной даты.



Проверяем кэш:

# echo "get phpbb_current_logo"$'\nquit\r\n' | nc 192.168.200.1 63922
VALUE phpbb_current_logo 0 22
images/logop/logop.png
END


Праздничных логотипов, как видно по запросу, может быть несколько, и они будут выводиться в случайном порядке.

В идеале стоит учитывать еще длительность праздника. К примеру, новогодние праздники не заканчиваются 02.01.хххх. И было бы разумно добавить поля с длительностью и использовать в запросе вместо прибитых гвоздями "60 минут". Сделаем, когда будет не лень. ;)

Учет трафика по данным, сообщаемым трекеру торрент-клиентам, идет постоянными вставками в базу с таймштампами (чтобы можно было вести счетчики помесячные, посуточные и т.д.). Этот трафик постоянно двигается и агрегируется по планировщику. Доселе агрегацию выполнял внешний (перловый) скрипт, который делал 3 выборки, и обновлял счетчики. Так как постепенно мы подобное хозяйство изживаем, то агрегатор был перенесен внутрь БД в виде отдельной функции, что намного удобнее во многих отношениях.

Работа скрипта c одним циклом на двух миллионах записей и 20 тысячах уникальных пользователей:

# time ./agregate.pl

real 1m47.701s
user 0m9.189s
sys 0m5.592s


Работа функции с одним лупом:

tracker=# EXPLAIN ANALYZE select agregate_users_traff();
QUERY PLAN
------------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=0) (actual time=4552.722..4552.722 rows=1 loops=1)
Total runtime: 4552.733 ms
(2 rows)


4.5 секунды против почти двух минут. Замеров делалось несколько, но и без этого очевидно (и я об этом всегда говорю), что нужно доверять базе и то, что можно крутить внутри - нужно крутить внутри. Естественно, без фанатизма и не переходя границы здравого смысла, засовывая в нее все подряд. Прокручивание потоков внутри базы как отдельно взятого модуля системы происходит значительно быстрее, чем когда потоки выводятся во внешнее приложение, которое управляет ими.

$ awk -F '|' '{print $2}' /var/log/torrent/search_plperl_sql.log | grep -e [[:alpha:]] | sort | uniq -c | awk '{if ($1 > 1000) print $0 }' | sort -k1 | tail -n 30

2611 search_text: универ
2657 search_text: закрытая школа
2676 search_text: мальчишник в вегасе
2693 search_text: доктор хаус
2775 search_text: тачки 2
2778 search_text: порно
2848 search_text: мальчишник
2890 search_text: шерлок холмс
2902 search_text: аватар
3111 search_text: елки
3131 search_text: need for speed
3174 search_text: GTA
3362 search_text: время
3619 search_text: теория большого взрыва
3685 search_text: пираты карибского моря
3946 search_text: call of duty
3994 search_text: форсаж
4091 search_text: маша и медведь
4208 search_text: тор
4305 search_text: дневники вампира
4583 search_text: кот в сапогах
5461 search_text: сумерки
5517 search_text: gta
5553 search_text: форсаж 5
5808 search_text: эротика
5863 search_text: трансформеры
6375 search_text: живая сталь
6490 search_text: ужасы
6572 search_text: гарри поттер
6578 search_text: комедия


Точка отсчета - 20 апреля 2011 года.

Некоторое время назад эта хреновина у нас перестала должным образом работать. Писалась и правилась она несколько лет назад ребятами, которые когда-то помогали в разработке, попутно набираясь опыта (или наоборот). PHP-код был признан непригодным и недостойным к поддержке в нынешних реалиях. Поэтому логика была уведена внутрь нашей СУРБД в лице слоника

Функционал следующий: нужно уведомить тех, кто скачал раздачу и засветился сидером, что нужна помощь, нужны сидеры.

Таким образом, у нас имеется следующая функция:


CREATE OR REPLACE FUNCTION call_seed_func (user_id_ integer, topic_id_ integer) RETURNS integer AS $$
DECLARE
  counter INTEGER:=0;
   result INTEGER:=0;
   item RECORD;
   bot_id INTEGER:=-745;
   bot_name CHARACTER VARYING:='Архивариус';
   topic_info RECORD;
   idle_time INTEGER:=86400; -- ограничение на зов сида в секундах
BEGIN
-- Выбираем данные по топику topic_id_
   SELECT f.forum_id, t.topic_title, t.topic_poster, f.forum_name, tor.attach_id, tor.torrent_id, t.call_seed_time
   INTO topic_info
   FROM phpbb_topics t, phpbb_forums f, phpbb_bt_torrents tor
   WHERE
   t.topic_id=topic_id_
   AND t.topic_id=tor.topic_id
   AND tor.active=1
   AND t.forum_id=f.forum_id
   ORDER BY tor.reg_time DESC
   LIMIT 1;
   -- Если давили кнопку меньше суток назад, то отфутболиваем и ничо не делаем
   IF EXTRACT('epoch' FROM (now()))::INTEGER-topic_info.call_seed_time < idle_time THEN return -1; END IF;
   -- Если юзер давит эту кнопку, а сам на раздаче сидит - нифига не делаем снова
   IF (SELECT COUNT(*) FROM phpbb_bt_tracker WHERE expire_time>EXTRACT('epoch' FROM (now()))::INTEGER   AND seeder=1 AND torrent_id=topic_info.torrent_id)>0 THEN return -2; END IF;
   -- Обновляем информацию по топику
   UPDATE phpbb_topics SET call_seed_time=EXTRACT('epoch' FROM (now()))::INTEGER WHERE topic_id=topic_id_;

   FOR item IN (
     SELECT s.user_id, u.user_email, u.user_notify_pm FROM phpbb_bt_users_dl_status s, phpbb_users u
     WHERE
     s.topic_id=topic_id_
     AND s.user_id=u.user_id
     AND s.user_status=2
     AND s.drop_release=0
     AND u.call_seed=1
     AND u.user_id!=user_id_
     )
   LOOP
   counter:=counter+1;
   -- Отправляем ЛС скачавшим
   SELECT send_pm (bot_id, item.user_id, 'Нужна помощь по раздаче "'||topic_info.topic_title||'"! ',
'Здравствуйте!
Ваша помощь необходима в раздаче "'||topic_info.topic_title||'", http://opentorrent.ru/viewtopic.php?t='||topic_id_||'
Если вы решили помочь, но уже удалили torrent-файл, можете скачать его здесь: http://opentorrent.ru/download.php?id='||topic_info.attach_id||'
Надеемся на вашу помощь!',
'', 1, '') INTO result;

   -- Отправляем емейл скачавшим
   IF item.user_email IS NOT NULL AND item.user_notify_pm=1 THEN
   SELECT send_email ('noreply@opentorrent.ru', item.user_email, 'Нужна Ваша помощь в раздаче '||topic_info.topic_title||'', 'Нужна Ваша помощь на раздаче "'||topic_info.topic_title||'", http://opentorrent.ru/viewtopic.php?t='||topic_id_||' .
Посидируйте, пожалуйста, если это возможно.', 'utf-8', 'text/plain') INTO result;
   END IF;

   END LOOP; -- Конец цикла, всех перебрали
   RETURN counter; -- возвращаем количество тех, кому ушло сообщение
  END;
  $$ LANGUAGE plpgsql;

Вызовы send_email() и send_pm() это тоже функции, работающие внутри базы данных, а веб-морда (php-код + шаблоны) это лишь обертка для их вызова.

P.S. все отступы в функции едут, к сожалению, поэтому читабельность страдает.

До перехода на PostgreSQL задачу перемещения в Архив раздач, на которых долго не было сидеров, решал перловый скрипт. После этого рефакторинг его не затронул. Сейчас огромная часть логики у нас крутится внутри СУБД, поэтому было решено не адаптировать внешние костыли, а написать две функции, которые будут составлять тело Архивариуса.

Для форумов, участвующих в процессе архивации-разархивации создана карта:

tracker=# SELECT * FROM phpbb_bt_archive_rules LIMIT 10;
forum_id | archive_forum_id | archive_days | unreg_days
----------+------------------+--------------+------------
15 | 211 | 30 | 120
417 | 211 | 30 | 120
14 | 211 | 30 | 120
342 | 211 | 30 | 120
81 | 211 | 30 | 120
240 | 211 | 30 | 120
158 | 211 | 30 | 120
239 | 211 | 30 | 120
169 | 211 | 30 | 120
374 | 211 | 30 | 120
(10 rows)

forum_id - откуда перемещаем
archive_forum_id - архивный раздел для раздач в forum_id
archive_days - время отсутствия сидеров в сутках на раздаче, необходимое для архивации раздачи
unreg_days - время отсутствия сидеров в сутках на раздаче, необходимое для разрегистрации (де факто - удаления) торрента с трекера

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

Исходный текст процедуры архивации:
( Read more... )

Исходный текст процедуры разархивации:
( Read more... )

Около часа назад мы заменили наш старый коммутатор at-8350g на свежий гигабитный d-link dgs-1210-16. Это второй шаг к кластеризации наших сервисов.

Минувшей ночью дошли руки до выполнения 619го таска, а именно: "Сделать систему как на марковском форуме, завернуть в спойлер, включать из профиля(по умолчанию - откл)".

Родная таблица phpbb_watch_topics для этих целей не очень пригодна. Лазить в таблицу с постами для выявления в каких топиках каждый отдельно взятый пользователь наследил - тоже расточительно по отношению к процессорному времени узла с СУРБД. Для хранения актуальной карты соответствий топиков и юзеров, оставивших посты там, новую таблицу сделал:



tracker=# create table phpbb_topics_withme (user_id integer not null default 0, topic_id integer not null default 0, show_topic boolean default true);
CREATE TABLE


Группа запросов, объединенная в транзакцию, для добавления новых постов на форуме у нас давно перенесена внутрь СУБД. Ее логику поправили таким образом, что при добавлении нового поста она смотрит, есть ли уже в карте (phpbb_topics_withme) связка топика, в который добавляется пост, и юзера, который этот пост сейчас пишет. Если нет, то соответственно добавляется.

Следующий этап это вывод самих таких тем. Обслуживающий эту задачу SQL-код завернут в хранимую процедуру.

Создаем новый тип:

CREATE TYPE ot_topics_with_me_type AS (topic_id INTEGER, topic_title CHARACTER VARYING, forum_id INTEGER, forum_name CHARACTER VARYING, user_id INTEGER, username CHARACTER VARYING, post_id INTEGER, topic_poster INTEGER, topic_replies INTEGER, post_time INTEGER, poster_name CHARACTER VARYING);


Пишем процедуру:

CREATE OR REPLACE FUNCTION ot_topics_with_me (INTEGER) RETURNS SETOF ot_topics_with_me_type AS $$
### Получаем параметры на входе функции
my ($uid) = @_;

my $row, $plan;
### Биндим переменные, необходимые для поиска, в запрос

$plan = spi_prepare('SELECT t.topic_id, t.topic_title, f.forum_id, f.forum_name, u.user_id, u.username, p.post_id, t.topic_poster, t.topic_replies, p.post_time
,(SELECT username FROM phpbb_users WHERE user_id=t.topic_poster) poster_name
FROM
phpbb_topics t,
phpbb_forums f,
phpbb_users u,
phpbb_posts p,
phpbb_topics_withme tw
WHERE
t.forum_id=f.forum_id
AND p.topic_id=t.topic_id
AND p.poster_id=u.user_id
AND p.post_id=t.topic_last_post_id
AND tw.user_id=$1
AND u.user_id!=tw.user_id
AND t.topic_id=tw.topic_id
AND tw.show_topic=true
AND f.cat_id<>30
GROUP BY t.topic_id, t.topic_title, f.forum_id, f.forum_name, u.user_id, u.username, p.post_id, t.topic_poster, t.topic_last_post_id, t.topic_replies, p.post_time
ORDER BY t.topic_last_post_id DESC ', 'INTEGER');

### Генерируем список согласно заданных условий
$sth = spi_query_prepared( $plan, $uid );
while (defined ($row = spi_fetchrow($sth))) {

return_next({
topic_id => $row->{topic_id},
topic_title => $row->{topic_title},
forum_id => $row->{forum_id},
forum_name => $row->{forum_name},
user_id => $row->{user_id},
username => $row->{username},
post_id => $row->{post_id},
topic_poster => $row->{topic_poster},
topic_replies => $row->{topic_replies},
post_time => $row->{post_time},
poster_name => $row->{poster_name},

});
}

return;
$$ LANGUAGE plperl;


Написано на plperl затем, чтобы в будущем можно было легко поставить функцию на рельсы кэширования.

А пока это не нужно. В карте мало записей:

tracker=# select count(*) from phpbb_topics_withme;
count
--------
484723


Скорость работы тоже удовлетворительная:

tracker=# EXPLAIN ANALYZE SELECT * FROM ot_topics_with_me(2);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Function Scan on ot_topics_with_me (cost=0.25..10.25 rows=1000 width=156) (actual time=118.244..118.366 rows=2246 loops=1)
Total runtime: 120.428 ms