Форум на Исходниках.RU

Форум на Исходниках.RU https://forum.sources.ru Форум на Исходниках.RU Форум на Исходниках.RU https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666873 Wed, 20 Apr 2016 14:20:51 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666873 Цитата Павел Калугин @ 20.04.16, 12:50

се же писать софт правильно более бюджетное решение ;)

Ну таки да :)]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666859 Wed, 20 Apr 2016 12:50:33 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666859 Цитата JoeUser @ 20.04.16, 11:59

или уменьшать поступающую инфу, либо строить/расширять кластер. ИМХО, если сервак работает более чем на 50% загрузки - это уже "звонок" о том, что не все так хорошо в датском королевстве.

Все же писать софт правильно более бюджетное решение ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666845 Wed, 20 Apr 2016 11:59:59 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666845 Цитата Павел Калугин @ 20.04.16, 11:48

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

Это вопрос из разряда "как записать, если стоит защита от записи" :lol: Ну если сервак не тянет физически поступаемый объем инфы, или уменьшать поступающую инфу, либо строить/расширять кластер. ИМХО, если сервак работает более чем на 50% загрузки - это уже "звонок" о том, что не все так хорошо в датском королевстве.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666841 Wed, 20 Apr 2016 11:48:22 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666841 Цитата JoeUser @ 20.04.16, 11:01

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

нет вопрос как раз про потери потому что ыйд сервер не способен принят такое количество запросов. с инсертами в одни и те же таблицы и параллельным поиском по ним ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666832 Wed, 20 Apr 2016 11:06:28 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666832 Цитата JoeUser @ 20.04.16, 11:01

Осталось только научить syslogd сбрасывать не в системный журнал (как правило это /var/log/messages) или выделенные лог-файлы, а завернуть это пакетными запросами к серверу БД.

"syslog vs syslog-ng" - походу все уже есть :lol:]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666831 Wed, 20 Apr 2016 11:01:36 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666831 Цитата Павел Калугин @ 20.04.16, 10:53

Есть вэбсервер Он , обычно, пишет текстовый лог. посещений

В случае вебсервера - уже есть возможность "не писать лог", а скормить его syslogd, а там хоть трава не расти. В свою очередь, syslogd - асинхронен по сути (сбрасывает кэш по своим правилам). Осталось только научить syslogd сбрасывать не в системный журнал (как правило это /var/log/messages) или выделенные лог-файлы, а завернуть это пакетными запросами к серверу БД. Тормозов как раз тут - по самому минимуму, ибо все происходит асинхронно.

По поводу возможных потерь ... ну если система кривая по настройке и по энергообеспечению. Но это другая композиция из другой оперы.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666826 Wed, 20 Apr 2016 10:53:45 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666826 JoeUser и ? топикстартеру от этого уже все ясно эт раз
что у нас вообще по физике происходит.
Есть вэбсервер Он , обычно, пишет текстовый лог. посещений
Если мне не изменяет память, можно перенаправить эту запись в биде.
Или надо вклиниваться в каждую страницу с кодом который "отгрузит лог".
Или увы, по расписанию брать серверный лог и пакетом его грузить.
что дальше?
Если грузим пакетами нам насрать. Може м и разобрать можем и ....
Если вклиниваемся в работу вэбсервера любой затык на отгрузке лога будет тормозить сервак при синхронном соединении или приведет к потерям данных при асинхронном
Тут нужен анализ что нам дороже. Возможно потеря 10-15 данных на результате анализа и не скажется а возможно нужно все "до копейки".
Если вклиниватся в работу страничек - практически тоже что и с вэбсервером. Или загрузка страницы увеличивается на ожидание отклика или есть риск потерь данных
Вот как бы и все ;)
Где тут найден "клиент" и тем более "тонкий клиент" не понимаю.
Лайт, не лайт тоже не понятно.

Но то что любая обработка затормозит вставку эт факт. На сколько - померяем хохмы для.
К чему ведут "тормоза" при вставке лога - выше указал

Добавлено 20.04.16, 10:54

Цитата Akina @ 20.04.16, 10:33

Для чудес по скоростям достаточно создать таблицу для первичного приёма логов на MEMORY-движке, и пачками переливать из неё поступившие сведения в основную таблицу.

ну или так.. А на обработке "пачки" скорость уже не настолько критична. ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666824 Wed, 20 Apr 2016 10:42:36 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666824 syslog" или какой-нить его форк типа "syslog-ng" с существующим сервером БД. Тогда первичные запросы на запись логов будет принимать не сам сервак БД, а посредник (syslog*). Времени пока особо нет, так поверхностное гугление дало два интересных линка "kiwisyslog" и "Writing syslog messages to MySQL, PostgreSQL or any other supported Database" ... походу мы упорно изобретаем велосипед из дерева :lol:

Пакетные вставки действительно должны дать ощутимый прирост скорости.
А "лайт-нормализацию" можно делать и на тонком клиенте, там же формировать и пачки для записи.

Такие дела.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666822 Wed, 20 Apr 2016 10:33:39 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666822 Цитата JoeUser @ 20.04.16, 10:18

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

Для чудес по скоростям достаточно создать таблицу для первичного приёма логов на MEMORY-движке, и пачками переливать из неё поступившие сведения в основную таблицу.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666821 Wed, 20 Apr 2016 10:31:07 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666821 JoeUserНу это уже про шаманство на очень высоких нагрузках ;) Ну и вставку пакетами сервак делать умеет ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666819 Wed, 20 Apr 2016 10:18:07 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666819 Цитата Павел Калугин @ 20.04.16, 09:57

буду вливать его построчно

Кстати, ознакомься с этой темой.
Есть предположение что, если грамотно реализовать сборку единичных логов в "пакеты", то можно будет выжимать чудеса по скоростям :)]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666816 Wed, 20 Apr 2016 09:57:00 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666816 Цитата JoeUser @ 20.04.16, 09:33

Если у тя есть реальный железный сервак БД (выделенный) - имеет смысл заморачиваться, ибо результаты будут более правдивы, чем у меня с ВМ.

есть, но он стаааренький :)
Примерно как буду делать - возьму живой лог со своих сайтов, догененрю до разумного числа обращений , запихаю в "массив строк" и ... буду вливать его построчно
соответственно с трех - четырех - пяти компуков можно будет прогнать каку. Но тут сразу погрешности будут на "шнурки".]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666813 Wed, 20 Apr 2016 09:33:07 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666813 Цитата Павел Калугин @ 20.04.16, 08:52

Вот над таким собираюсь на вторые майские посидеть.

Цитата Павел Калугин @ 20.04.16, 08:52

и не нитей а приложений

Вот тут не согласен. Для движка БД абсолютно фиолетово от кого ловить соединение. А вот на клиенте накладных расходов (в случае нитей) будет гораздо меньше, что даст большую чистоту теста. Мы же пытаемся имитировать одно соединение с 10..100 компьютеров. Нам накладные расходы по управлению процессами не нужны ни разу.

Добавлено 20.04.16, 09:42

Цитата Павел Калугин @ 20.04.16, 08:52

- тест 2 выполняется "нормализация" с поиском и вставкой если не нашли

И тут момент. ИМХО, вставка URL в свой "каталог URLs" нужно делать в момент создания "материала" (будь то статическая страница, будь то динамическая тема форума, к примеру). А именно поиск+вставка прокатит, если мы хотим собрать статистику по ошибочным обращениям.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666804 Wed, 20 Apr 2016 08:52:17 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666804 - "нагрузка" нужна не с 10 а, хотябы со 100 и не нитей а приложений.
- тест 1 выполняется "инсерт" в таблицу
- тест 2 выполняется "нормализация" с поиском и вставкой если не нашли
- тест 3 выполняется "инсерт" с "подавлением ошибки" и последующим поиском

Тестовое приложение умеет принять код "теста" 1,2,3, принять параметр "кол-во запущенных", задержку до начала теста начать отсчет времени,выполнтить тест, завершить отсчет положить в лог тест, кол-во запросов, время на исволнение
"Пускалка" должна принять номер теста, кол-во "запросов" в тесте, количество стартов, подготовить данные и запустить заданное количество "тестов"
Задержка нужна чтобы все тесты стартовали +- одновременно

Вот над таким собираюсь на вторые майские посидеть.
Сейчас времени нет, удочки собираю :)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666793 Wed, 20 Apr 2016 07:49:34 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666793 Цитата Akina @ 20.04.16, 07:36

но, насколько я понял, ты замеряешь время загрузки большого массива в пустые таблицы.

Да, сперва чистка, потом вакуум, потом загрузка.

Цитата Akina @ 20.04.16, 07:36

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

Попробую вечером посмотреть че можно сделать. Дело в том, что у меня в виртуальных машинах свободного дискового пространства по минимуму - оттого и не могу грузить много. Надо проинспектировать ) Есть еще 4 VM под управлением разных версий линухов, и одна - под Mac OS X. Может быть там есть место, просто не помню.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666790 Wed, 20 Apr 2016 07:36:28 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666790 Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666751 Tue, 19 Apr 2016 20:09:09 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666751 Цитата Akina @ 19.04.16, 14:20

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

Вощем заинтересовался и сделал. Генерируется 10 нитей (threads), каждая нить параллельно пишет лог в БД по 100000 записей. Увы, БД крутится под FreeBSD 9.0, которая запущена под VMWare - отсюда точность хреновенькая.

Вот код:

#!/usr/bin/perl -w

# Подключаем библиотеки

use Time::HiRes qw(gettimeofday);

use threads;

use threads::shared;

use DBI;

# список создаваемых URL

@URLs = ();

@Req = ();

# реквизиты БД

$DBName = "testo";

$UserName = "pgsql";

$DBHost = "192.168.1.47";

$DBPort = "5432";

# счетчики замеров

$TimeRaw = 0;

$TimeId = 0;

$SizeRaw = 0;

$SizeId = 0;

###############################################################################

$|++; if ($^O =~ /win/i) { system("cls"); } else { system("clear"); }

print "\nПоехали тестировать ============================================\n\n";

###############################################################################

# 1.Генерируем 1000 URL'ов произвольной длины в диапазоне 12...255

GenerateUrls(\@URLs);

# 2.Гененируем таблицу запросов (индексы из @URLs)

GenerateReq(\@Req);

# 3.Чистим таблицы и записываем "каталог" URLs в БД

PrepareTables(\@URLs);

# 4.Запускаем 10 потоков за запись "сырых" логов и засекаем время их исполнения

ThreadRawFunc();

# 5.Вызываем вакуум и вычисляем размер полученной таблицы LogsRaw

$SizeRaw = GatheringStats("LogsRaw");

# 6.Чистим таблицы и записываем "каталог" URLs в БД

PrepareTables(\@URLs);

# 7.Запускаем 10 потоков за запись "нормализованных" логов

ThreadIdFunc();

# 8.Вызываем вакуум и вычисляем размер полученной таблицы LogsId

$SizeId = GatheringStats("LogsId");

# 9.Отчет

print "\n Время записи RAW-логов: $TimeRaw сек (полученный размер: $SizeRaw Mb)\n Время записи \"нормализованных\"-логов: $TimeId сек (полученный размер: $SizeId Mb)\n\n";

###############################################################################

sub GenerateUrls {

print " * Генерируем URL'ы ... ";

my $Array = shift;

srand();

for my $I (1..1000) {

my $S = "";

for my $J (0..rand(244)+11) {

$S .= chr(ord('a')+rand(ord('z')-ord('a')));

}

push @{$Array}, $S;

}

print "Ok\n";

}

sub GenerateReq {

print " * Генерируем произвольный массив индексов запросов URLs ... ";

my $Array = shift;

srand();

for my $I (1..100000) {

push @{$Array}, int(rand(999));

}

print "Ok\n";

}

sub PrepareTables {

my $Array = shift;

my $Dbh = DBI->connect("dbi:Pg:dbname=$DBName;host=$DBHost;port=$DBPort","$UserName","",{PrintError => 0});

die "$DBI::errstr\n" if (defined($DBI::err));

# -- чистим таблицу URL

print "\n * Подготавливаем БД:\n";

print " - чистим таблицу URLs ... ";

my $query = "DELETE FROM \"URLs\"";

my $sth = $Dbh->prepare($query);

my $rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- чистим таблицу LogsRaw

print " - чистим таблицу LogRaw ... ";

$query = "DELETE FROM \"LogsRaw\"";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- чистим таблицу LogsId

print " - чистим таблицу LogId ... ";

$query = "DELETE FROM \"LogsId\"";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- сбрасываем счетчики Id таблиц

print " - сбрасываем счетчики Id таблиц ... ";

$query = "ALTER SEQUENCE public.\"URLs_Id_seq\" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART 1 CACHE 1 NO CYCLE";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

$query = "ALTER SEQUENCE public.\"LogsId_Id_seq\" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART 1 CACHE 1 NO CYCLE";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

$query = "ALTER SEQUENCE public.\"Logs_Id_seq\" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 RESTART 1 CACHE 1 NO CYCLE";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- вакуум

print " - вакуум + сбор статистики ... ";

$query = "VACUUM FULL ANALYZE";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- заполняем таблицу URLs

print " - запоняем таблицу URLs ... ";

$query = "INSERT INTO \"URLs\" (\"URL\") VALUES ";

for(my $I=0; $I<scalar(@{$Array}); $I++) {

$query.="('".${$Array}[$I]."')";

$query.= ", " if ($I+1<scalar(@{$Array}));

}

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

print "Ok\n";

# -- переиндексируем таблицу URL

print " - переиндексируем таблицу URLs ... ";

$query = "REINDEX TABLE \"URLs\"";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

$Dbh->disconnect();

print "Ок\n - пауза 30 сек ... ";

sleep(30);

print "Ok\n\n";

}

sub ThreadRawFunc {

print " * Записываем LogsRaw ";

my @Threads;

my $Threads=10;

# Создаём нужное количество потоков

for my $T (1..$Threads) {

push @Threads, threads->create(\&WriteLogsRaw, $T);

}

# Дожидаемся окончания работы всех потоков

my $Start = gettimeofday;

foreach my $T (@Threads) {

$T->join();

}

$TimeRaw = gettimeofday() - $Start;

print " Ok\n";

}

sub ThreadIdFunc {

print " * Записываем LogsId ";

my @Threads;

my $Threads=10;

# Создаём нужное количество потоков

for my $T (1..$Threads) {

push @Threads, threads->create(\&WriteLogsId, $T);

}

# Дожидаемся окончания работы всех потоков

my $Start = gettimeofday;

foreach my $T (@Threads) {

$T->join();

}

$TimeId = gettimeofday() - $Start;

print " Ok\n";

}

sub WriteLogsRaw {

print ".";

my $Dbh = DBI->connect("dbi:Pg:dbname=$DBName;host=$DBHost;port=$DBPort","$UserName","",{PrintError => 0});

die "$DBI::errstr\n" if (defined($DBI::err));

foreach my $I (@{Req}) {

my $query = "INSERT INTO \"LogsRaw\" (\"URL\",\"Timestamp\") VALUES ('".$URLs[$I]."', NOW())";

my $sth = $Dbh->prepare($query);

my $rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

}

$Dbh->disconnect();

}

sub WriteLogsId {

print ".";

my $Dbh = DBI->connect("dbi:Pg:dbname=$DBName;host=$DBHost;port=$DBPort","$UserName","",{PrintError => 0});

die "$DBI::errstr\n" if (defined($DBI::err));

foreach my $I (@{Req}) {

my $query = "INSERT INTO \"LogsId\" (\"IdUrl\",\"Timestamp\") VALUES ((SELECT u.\"Id\" FROM \"URLs\" AS u WHERE u.\"URL\" = '".$URLs[$I]."'), NOW())";

my $sth = $Dbh->prepare($query);

my $rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

}

$Dbh->disconnect();

}

sub GatheringStats {

my $Name = shift;

print " * Поизводим вакуум и вычисляем размер таблицы \"".$Name."\" ... ";

my $Dbh = DBI->connect("dbi:Pg:dbname=$DBName;host=$DBHost;port=$DBPort","$UserName","",{PrintError => 0});

die "$DBI::errstr\n" if (defined($DBI::err));

my $query = "VACUUM FULL ANALYZE";

my $sth = $Dbh->prepare($query);

my $rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

$query = "SELECT (relpages * 8192 / (1024*1024))::int as size_mb FROM pg_class WHERE relname LIKE '".$Name."'";

$sth = $Dbh->prepare($query);

$rv = $sth->execute();

die "Error query on \"$query\": " . $Dbh->errstr . "\n" unless(defined $rv);

my @array = $sth->fetchrow_array();

$sth->finish();

$Dbh->disconnect();

print " Ok\n";

return $array[0];

}

Вот результаты:

Поехали тестировать ============================================

* Генерируем URL'ы ... Ok

* Генерируем произвольный массив индексов запросов URLs ... Ok

* Подготавливаем БД:

- чистим таблицу URLs ... Ok

- чистим таблицу LogRaw ... Ok

- чистим таблицу LogId ... Ok

- сбрасываем счетчики Id таблиц ... Ok

- вакуум + сбор статистики ... Ok

- запоняем таблицу URLs ... Ok

- переиндексируем таблицу URLs ... Ок

- пауза 30 сек ... Ok

* Записываем LogsRaw .......... Ok

* Поизводим вакуум и вычисляем размер таблицы "LogsRaw" ... Ok

* Подготавливаем БД:

- чистим таблицу URLs ... Ok

- чистим таблицу LogRaw ... Ok

- чистим таблицу LogId ... Ok

- сбрасываем счетчики Id таблиц ... Ok

- вакуум + сбор статистики ... Ok

- запоняем таблицу URLs ... Ok

- переиндексируем таблицу URLs ... Ок

- пауза 30 сек ... Ok

* Записываем LogsId .......... Ok

* Поизводим вакуум и вычисляем размер таблицы "LogsId" ... Ok

Время записи RAW-логов: 312.937685012817 сек (полученный размер: 176 Mb)

Время записи "нормализованных"-логов: 391.566915035248 сек (полученный размер: 45 Mb)

Запускал несколько раз, заметил закономерность - чем больше итераций в нити, тем ближе время записи "нормализованного" лога к "сырому". На итерациях порядка 1000 разница была чуть ли не в треть. Больше ждать лениво, да и как говорил - виртуализация портит точность. А на живом железе запустить нет возможности.

Тем не менее ... худший результат ("нормализованная" запись логов) дает нам скорость 2557 записи/сек. Для таких условий, как проводилось тестирование, я считаю результат вполне приемлем.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666733 Tue, 19 Apr 2016 16:00:58 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666733 Цитата JoeUser @ 19.04.16, 14:51

На сайте, коль регистрируется доступ к ресурсам, от силы 1к своих URL, и то это черезчур. Нам надо сделать таблицу 1к URL и сымитировать доступ к ним, с записью лога "сырого" и "нормализованного".

Я имел в виду 1кк уже существующих записей лога, а не уникальных URL. Впрочем, согласен, что 10к уников - многовато.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666729 Tue, 19 Apr 2016 14:51:52 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666729 Цитата Akina @ 19.04.16, 14:20

Сгенери таблицу на, скажем, 1кк URL

Э неее ... На сайте, коль регистрируется доступ к ресурсам, от силы 1к своих URL, и то это черезчур. Нам надо сделать таблицу 1к URL и сымитировать доступ к ним, с записью лога "сырого" и "нормализованного". В своей тестовой программе я лишь заценил возможный пересчет перед записью (на сколько пересчет будет трудоемким). Не думаю, что авторы полнотекстового поиска в БД тупее реализаторов такого же поиска в std::map.

А на счет предложенного тобою теста - боюсь он буден неадекватен, ибо у меня все БД сервера (PostgreSQL и MySQL) запущены под виртуальными машинами, а виртуализация вносит хаос в тесты, уже не раз проверено. Разные запуски одного и того же могут давать результаты вплоть до обратного.

Хотя можно попробовать. Подумаю]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666722 Tue, 19 Apr 2016 14:20:17 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666722 Цитата JoeUser @ 19.04.16, 13:59

Как говорится, получите и распишитесь

Всё это никакого отношения к обсуждаемому выше не имеет.
Если очень хочется, то проделай всё то же на любом SQL-сервере. MySQL, FB, MS SQL CE... неважно, каком. Сгенери таблицу на, скажем, 1кк URL, а потом замерь время вставки 10к записей, из которых 9к дубли, по первой и по второй технологии. Такой эксперимент будет хоть как-то адекватен, особенно если сможешь организовать многопоточную вставку.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666719 Tue, 19 Apr 2016 13:59:22 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666719
RAW: 50499 ms
ID : 23428 ms

Как говорится, получите и распишитесь :lol: Правда второй лог вырос до 0.76Gb, но не суть!

Просто переделал бинарный вывод. Вот переделанный код.

#include <iostream>

#include <fstream>

#include <random>

#include <chrono>

#include <limits>

#include <iomanip>

#include <map>

int main() {

// генерируем набор произвольных Url длиной 10-128 символов

std::map<std::string,uint64_t> ReverseMap;

std::vector<std::string> Urls;

std::random_device Rd;

for (int i=0; i<1000; i++) {

std::string S;

int Len = Rd() % 118+10;

for (int j=0; j<Len; j++) S += (char)('a'+ (Rd() % ('z'-'a')));

std::cout << std::setw(6) << i << ": " << S << std::endl;

Urls.push_back(S);

ReverseMap[S] = i;

}

// генерируем произвольные посещения 1000000

std::vector<int> Get;

Get.reserve(1000000);

for(int i=0; i<1000000; i++) Get.push_back(Rd() % 1000);

char *Buffer = new char[32768*1024];

// записываем "сырые" URL 10000000 шт

std::ofstream fs1("test-raw.dat", std::ios::out|std::ios::binary);

if (!fs1) { std::cout << "беда-1" << std::endl; delete Buffer; return 1;}

fs1.rdbuf()->pubsetbuf(Buffer,32768*1024);

auto start = std::chrono::high_resolution_clock::now();

for (int x=0; x<100; x++)

for (int i=0; i<1000000; i++) fs1.write(Urls[Get[i]].c_str(),Urls[Get[i]].length());

fs1.flush();

fs1.close();

auto stop = std::chrono::high_resolution_clock::now();

std::cout << "RAW:" << std::chrono::duration_cast<std::chrono::milliseconds>(stop - start).count()

<< "ms" << std::endl;

std::cout << std::endl << "Press any key to continue...";

std::cin.get();

// записываем "Id" URL 10000000 шт

std::ofstream fs2("test-id.dat", std::ios::out|std::ios::binary);

if (!fs2) { std::cout << "беда-1" << std::endl; delete Buffer; return 1;}

fs2.rdbuf()->pubsetbuf(Buffer,32768*1024);

start = std::chrono::high_resolution_clock::now();

for (int x=0; x<100; x++)

for (int i=0; i<1000000; i++)

fs2.write(reinterpret_cast<const char *>(&(ReverseMap[Urls[Get[i]]])), sizeof(uint64_t));

//fs2.write(ReverseMap[Urls[Get[i]]], sizeof(uint64_t));

fs2.flush();

fs2.close();

stop = std::chrono::high_resolution_clock::now();

std::cout << "ID :" << std::chrono::duration_cast<std::chrono::milliseconds>(stop - start).count()

<< "ms" << std::endl;

delete Buffer;

return 0;

}

Бинго! :lool:
TestNormalisation_2.exe.7z (, : 204) ]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666713 Tue, 19 Apr 2016 13:21:11 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666713 Цитата Akina @ 19.04.16, 10:22

Цитата JoeUser @ 19.04.16, 09:20

Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице.

Именно!!!

Цитата Павел Калугин @ 19.04.16, 09:32

JoeUser Как верно Акина написал выше - гадание на кофейной гуще.

Цитата Павел Калугин @ 19.04.16, 12:15

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

Павел, а зачем гадать если можно посчитать? :lol:

Я вот не поленился и поставил эксперимент. Суть такова:

Гипотеза

Основное "тонкое" место любой системы, использующей I/O - дисковая подситема - и в первую очередь при простейших счетных операциях. В принципе, это ни разу не гипотеза (для меня, по крайней мере). А не при простейших? Давайте посчитаем плюсы/минусы, да и вообще уместность сбрасывать "нормализованные" логи.

Эксперимент

При грамотной настройке кэширования движка БД малые таблицы со времени очередного обращения полностью улетают в память, большие таблицы кэшируются частично (скорее всего на уровне индексов). Акей) Пусть аналогом закэшированной таблицы будет структура из STL C++ под названием std::map, считаем та же проиндексированная таблица БД . Для эксперимента собираем и запускаем программу, суть которой в том, что

* в первом проходе записываются сырые URL'ы на диск, без каких либо поисков
* во втором проходе осуществляется поиск индекса в std::map по URL, который является ключем, а в файл записывается его численное значение из std::map

Файлы открываются в режиме бинарного I/O, для работы с файлом выделяется программый буффер в 32 метра.

Вот код программы:

#include <iostream>

#include <fstream>

#include <random>

#include <chrono>

#include <map>

int main() {

// генерируем набор произвольных Url длиной 10-128 символов

std::map<std::string,int> ReverseMap;

std::vector<std::string> Urls;

std::random_device Rd;

for (int i=0; i<1000; i++) {

std::string S;

int Len = Rd() % 118+10;

for (int j=0; j<Len; j++) S += (char)('a'+ (Rd() % ('z'-'a')));

std::cout << S << std::endl;

Urls.push_back(S);

ReverseMap[S] = i;

}

// генерируем произвольные посещения 1000000

std::vector<int> Get;

Get.reserve(1000000);

for(int i=0; i<1000000; i++) Get.push_back(Rd() % 1000);

char *Buffer = new char[32768*1024];

// записываем "сырые" URL 100000000 шт

std::ofstream fs1("test-raw.dat", std::ios::out|std::ios::binary);

if (!fs1) { std::cout << "беда-1" << std::endl; delete Buffer; return 1;}

fs1.rdbuf()->pubsetbuf(Buffer,32768*1024);

auto start = std::chrono::high_resolution_clock::now();

for (int x=0; x<100; x++)

for (int i=0; i<1000000; i++) fs1 << Urls[Get[i]];

fs1.flush();

fs1.close();

auto stop = std::chrono::high_resolution_clock::now();

std::cout << "RAW:" << std::chrono::duration_cast<std::chrono::milliseconds>(stop - start).count()

<< "ms" << std::endl;

std::cout << std::endl << "Press any key to continue...";

std::cin.get();

// записываем "Id" URL 100000000 шт

std::ofstream fs2("test-id.dat", std::ios::out|std::ios::binary);

if (!fs2) { std::cout << "беда-1" << std::endl; delete Buffer; return 1;}

fs2.rdbuf()->pubsetbuf(Buffer,32768*1024);

start = std::chrono::high_resolution_clock::now();

for (int x=0; x<100; x++)

for (int i=0; i<1000000; i++) fs2 << ReverseMap[Urls[Get[i]]] << std::endl;

fs2.flush();

fs2.close();

stop = std::chrono::high_resolution_clock::now();

std::cout << "ID :" << std::chrono::duration_cast<std::chrono::milliseconds>(stop - start).count()

<< "ms" << std::endl;

delete Buffer;

return 0;

}

В результате работы имеем:

* Запись "сырых" URL заняла 48566 ms (созданный лог 6.53Gb)
* Просчет индексов URL + их запись заняла 185067 ms (созданный лог 0.37 Gb)

Скорость записи "сырых" URL почти в 4 раза быстрее (26% от от записи ID).
Объем данных, подлежащих записи, для записи по ID в 20 раз меньше (5% от RAW)

По скольку скорость итераций ни чем ограничена не была - считаем что запросы обрабатывались во всю дурь мощности компьютера. А если добавить тормоза, обеспечиваемые сетевым обменом? Если без тормозов, то:

Обеспечивается запись "сырых" URL: 206180 обращений в секунду
Обеспечивается запись "индексов" URL: 54050 обращений в секунду

Это все на домашнем компе Intel Core i7, 8Gb ram, FakeRaid (RAID0) 1Tb, Win 8.1 x64

End of гадание на кофейной гуще! :thanks:

ЗЫ: Бинарник для Win x64 (upx -9) прилагается (VirusTotal.com)

TestNormalisation.exe.7z (, : 228)

Добавлено 19.04.16, 13:44
ЗЫ: Хотя, сорь, во втором случае в лог попадают не бинарные индексы, а символьные числа. Переделаю, отпишу скорости.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666710 Tue, 19 Apr 2016 12:15:48 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666710 Интересно было бы смоделировтаь нагрузку, при которой такая вставка "захлебнется"
И сровнить с просто записью лога без "разбора на иды".. Но, это наверное после майских можно будет поразвлечься.]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666706 Tue, 19 Apr 2016 12:04:31 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666706 Цитата Павел Калугин @ 19.04.16, 11:43

Эту проблему решает структура

CREATE TABLE URLs (

ID integer auto_increment primary key,

URL varchar(255),

INDEX URL (URL) UNIQUE

);

Соответственно просто делаем тупое

INSERT INTO URLs (URL) VALUES (:url);

Если текущий УРЛ отсутствует - вставка выполняется. Если уже присутствует - при выполнении возникает ошибка нарушения уникального индекса, которую мы проигнорируем. Но вне зависимости от того, какой имеется вариант, после выполнения запроса URL в таблице ЕСТЬ. И можно спокойно выполнять

INSERT INTO clicks(dt, url_id)

SELECT now(), id

FROM URLs

WHERE URLs.URL = :url;

]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666704 Tue, 19 Apr 2016 11:43:07 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666704 Цитата Akina @ 19.04.16, 11:07

ошибку дублирования злостно игнорируем

Вот это не понятно. Как по мне - надо сначала найти урл, если не найден то вставить. Это "тормоз вставки" раз. Потом или повторить поиск или выцепить свежесозданный ид. Это "тормоз вставки" 2
Если окажутся "неуникальные" урлы то данные тормоза возрастут или будут ошибки. Опять же затормозится сильно последующая обработка так как всю эту "неуникальность" придется отдельно анализировать]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666698 Tue, 19 Apr 2016 11:07:13 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666698 Цитата Павел Калугин @ 19.04.16, 10:55

не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции

? не понял... чего там подбирать? автоинкремент с этим справится на раз-два. Так что просто делаем вставку в таблицу УРЛов, ошибку дублирования злостно игнорируем, зато гарантированно имеем запись - а затем уже делаем вставку в таблицу лога. А поскольку имеем два несвязанных INSERT, а между ними SELECT заведомо статической записи (а если средства позволяют - то сразу получение генерированного ID соотв. функцией) - то каждый INSERT является самостоятельной транзакцией, так что транзакции по сути и не нужны вовсе.

Вьюхи (которые к тому же любят в самый неподходящий момент захотеть материализоваться) или триггеры - это явно лишнее. Ну то есть триггер вроде бы и мог быть по месту - но, как мне кажется, от записи лога трудно ожидать пакетного поступления записей, а в этих условиях триггер только породит ненужные накладные расходы.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666697 Tue, 19 Apr 2016 10:55:52 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666697 Мне кажется или писать во вьюху с инстед тригером тоже не будет оптимальным решением?]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666690 Tue, 19 Apr 2016 10:22:54 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666690 Цитата JoeUser @ 19.04.16, 09:20

Ну как бы это решение вообще необсуждаемое. Таблица посещений - самая в текущем бизнес-процессе высоконагруженная, следовательно, просто обязана быть максимально компактной. Так что только компактные типы (BIGINT, DATETIME), а всякий текстовый хлам - наружу, и по ссылке.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666680 Tue, 19 Apr 2016 09:32:49 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666680 JoeUser Как верно Акина написал выше - гадание на кофейной гуще.
если там 100 просмотров страниц в день - любое решение будет работать. Если сотни-тысячи в секунду - ой как думать надо шо с этим делать и как.
Вопрос в том, что все предложенные решения (в том числе и решение автора) работают в неких границах по нагрузке. И чем больше обработки при записи потока данных в бд тем уже эти пределы. ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666676 Tue, 19 Apr 2016 09:20:16 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666676 Цитата Павел Калугин @ 19.04.16, 06:22

Потому что нет. Лог только на инсеррт.

Не вижу противоречий. Да и "нормализация" тут совсем ненапряжная. Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице. Не думаю, что по ней поиск будет заметным для БД, если нормально отстроить кэширование движка БД.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666668 Tue, 19 Apr 2016 08:54:08 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666668 Хотя если окажется что сервер типа укоза а логи это посещение всех сайтов укоза тут все равно все придется перерисовывать ;) :D]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666642 Tue, 19 Apr 2016 06:55:11 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666642 Павел Калугин
Да не вижу я предмета для спора. Чтобы иметь хоть какие-то данные для него, надо как минимум знать, о каком конкретно сервере идёт речь, и иметь хоть какие-то данные о БД и нагрузке на неё. А счас что? конь - идеальный, вакуум - сферический...]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666641 Tue, 19 Apr 2016 06:45:42 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666641 Akina, собственно, я написал ровно тоже что и ты ;) Хочешь поспорить?]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666639 Tue, 19 Apr 2016 06:41:14 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666639 Цитата Павел Калугин @ 19.04.16, 06:22

Лог только на инсеррт. А все разборы нормализация потом джобами или еще чем главное чтоб не блокировало таблицу лога на инсерт и не тормозило его.

Обсчёт статистики не дешевле её предрасчёта, а его могут запустить в любой момент (да ещё, как показывает опыт, несколько штук параллельно).]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666631 Tue, 19 Apr 2016 06:22:06 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666631 Цитата JoeUser @ 18.04.16, 20:40

Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.

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

Добавлено 19.04.16, 06:27
Akina в таких сайтах одно посещение это обращение юзера к 3-5 страницам как минимум, нагрузки считаем по "верхнему" пределу итого 100К посещений это 400К обращений в сутки, половина из которых в "пиковые" 4 часа = 50К в час, то есть примерно 14 обращений в секунду. Да, немного. Но уже и не мало ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666625 Tue, 19 Apr 2016 05:57:02 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666625 Цитата JoeUser @ 18.04.16, 16:03

а что делать с накапливаемой информацией?

Я бы вообще рекомендовал двухуровневую структуру.
На первом уровне - сбор логов, т.е. каждое посещение даёт запись в таблицу.
На втором - предрасчётную статистику прошедших периодов с минимальной либо наиболее часто используемой дискретизацией. Для описанного выше случая (чаще всего требуется статистика за предыдущую неделю) предрасчитывать статистику по неделям (если под неделей понимается строго с пн по вс, например) или по дням (если неделя - это строго 7 суток с плавающей границей).
При таком подходе подавляющее большинство запросов будут быстро получать данные из таблицы с предрасчётными значениями, а нестандартные запросы могут как обсчитывать таблицу логов, так и брать основные данные из таблицы предрасчёта, а "хвосты" - из таблицы логов.

Цитата JoeUser @ 18.04.16, 16:03

вопрос может встать, когда в день будет регистрироваться 10000-100000 посещений,

30кк записей в год - объём не чрезмерный. К тому же таблица логов может партиционироваться по времени.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666610 Mon, 18 Apr 2016 20:40:14 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666610 Цитата Павел Калугин @ 18.04.16, 18:55

Правильный вариант хранить лог по полям как отдает его сервер

Во во.
Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666597 Mon, 18 Apr 2016 18:55:15 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666597 Цитата JoeUser @ 18.04.16, 16:03

Предлагаю такой вариант

Правильный вариант хранить лог по полям как отдает его сервер ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666574 Mon, 18 Apr 2016 16:03:32 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666574 Цитата Akina @ 18.04.16, 15:53

На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно.

Как говорится - "аппетит приходит во время еды начальства" :lol:

Цитата n0wheremany @ 18.04.16, 11:36

Исходные данные таблица post с полями id,read. Обновление поля read происходит update post set read=read+1 про просмотре поста.

Да нет же!!! Исходные данные - это не то, что ты сделал. Нет еще этой таблицы.
Есть, к примеру: сервер, который отдает какую информацию по URL.
Нужно не вводить поля read1, read2, ... read78 (это тоже твоя костыльная реализация).
А нужно, к примеру: мочь рассчитать рейтинг запросов в разрезе URL за период времени.

Предлагаю такой вариант

Данные посещений собираются в следующую таблицу "Logs" (здесь и далее - синтаксис PostgreSQL, и "нормализация" тут хромает, ибо URL'ы желательно вынести в отдельную таблицу и организовать с таблицей логов отношения, спецом не делал, чтобы было нагляднее):

CREATE TABLE public."Logs" (

"Id" BIGSERIAL,

"URL" VARCHAR NOT NULL,

"Timestamp" TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,

CONSTRAINT "Logs_pkey" PRIMARY KEY("Id")

) WITHOUT OIDS;

В результате мы учитываем все посещения, и имеем отметку времени вплоть до секунд.

Далее, к примеру, нам нужен рейтинг всех URL, которые были бы посещены хотя бы один (или более) раз за последние две календарные недели, без учета текущей календарной недели.

Делаем запрос:

WITH Rec AS (

SELECT

extract(week from NOW()) - extract(week from L."Timestamp") AS "WeekNum"

FROM

"Logs" AS L

WHERE

extract(week from NOW()) - extract(week from L."Timestamp") BETWEEN 1 AND 2

)

SELECT

FROM (

SELECT

Rec."WeekNum",

Rec."URL",

COUNT(*) AS "Cnt"

FROM

Rec

GROUP BY

Rec."WeekNum", Rec."URL"

) AS J

ORDER BY

J."WeekNum" DESC, J."Cnt" DESC

Получаем нечто типа:

week URL Count

====================

2 post.php 3

2 mail.php 1

2 info.php 1

1 mail.php 1

1 post.php 1

1 info.php 1

Все! Нужный рейтинг получен. Т.е. задачу получение нужных цифр мы решили.

Теперь нужно предусмотреть, а что делать с накапливаемой информацией? Допустим, в день регистрируется 10 посещений. Вообще ниче делать не надо - ибо, 3650 записей в год, 365000 записей может появится за ближайшие 100 лет. Однако, вопрос может встать, когда в день будет регистрироваться 10000-100000 посещений, тогда действительно таблица логов будет пухнуть. А вот тут уже по желанию:

* считать статистику и запоминать ее в таблице статистики
* "старые данные" переностить в архивнуюю БД
* "старые данные" просто удалять после пересчета статистики

Короч, варианты "обслуживания" таблицы логов нужно решать исходя из условий эксплуатации. Тут однозначного ответа нет.
Ну вот как-то так.

Добавлено 18.04.16, 16:15

Цитата Akina @ 18.04.16, 15:53

В пределе - фиксация штампа времени каждого отдельного обращения.

Ну да, в принципе я это выше и описал.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666573 Mon, 18 Apr 2016 15:53:25 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666573 Цитата JoeUser @ 18.04.16, 15:24

"почасовые" данные у тебя теряются.

На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно. Если нужно более мелко - соответствующим образом дробится и диапазон сбора статистики. В пределе - фиксация штампа времени каждого отдельного обращения.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666570 Mon, 18 Apr 2016 15:28:33 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666570 Цитата n0wheremany @ 18.04.16, 11:36

Исходные данные таблица post с полями id,read. Обновление поля read происходит update post set read=read+1 про просмотре поста.

Да нет же!!! Исходные данные - это не то, что ты сделал. Нет еще этой таблицы.
Есть, к примеру: сервер, который отдает какую информацию по URL.
Нужно не вводить поля read1, read2, ... read78 (это тоже твоя костыльная реализация).
А нужно, к примеру: мочь рассчитать рейтинг запросов в разрезе URL за период времени.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666569 Mon, 18 Apr 2016 15:24:07 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666569 Цитата Akina @ 18.04.16, 11:40

Вот именно предложенная мной схема и позволит такой график построить.

Не. Если ты будешь

Цитата Akina @ 18.04.16, 11:00

Если прошло обращение к УРЛу - плюсим единицу за текущую дату

... то "почасовые" данные у тебя теряются.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666545 Mon, 18 Apr 2016 12:10:21 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666545 Цитата Akina @ 18.04.16, 11:00

Для фиксации просмотров должна существовать таблица истории обращений (УРЛ-дата-количество). Если прошло обращение к УРЛу - плюсим единицу за текущую дату, если запись отсутствует - создаём её с количеством = 1.

Даже не так. Каждое обращение отдельной записью. Количество обращений в день - count по этой таблице за день
Для ускорения обработки можно делать разные "срезы" вида кол-во обращений за день/неделю/месяц, "кол-во обращений с группировкой по источнику" и т.д. и т.п.
Расчет срезов проводить в "наименее активное время".
Соответственно для оперативной отчетности остается к последнему срезу добавить данные за текущий.
В общем задача тривиальна и многократно в разных вариациях решена в разных учетных системах. По сути отчет "покажи дебетовый оборот по расчетным счетам за период ... в порядке ..."

Добавлено 18.04.16, 12:22

Цитата n0wheremany @ 18.04.16, 11:20

Я не хочу создавать отдельную таблицу для сохранения просмотров - это глупо и лишняя нагрузка.

это единственное разумное решение. Тупо заливать лог просмотров. Один быстрый инсерт

Цитата n0wheremany @ 18.04.16, 11:20

Но после сохранения (бэкапа) read-read2 равны и сортировка соответственно не адекватна.

Это не бекап.. посмотрите внимательно на это

Цитата Павел Калугин @ 18.04.16, 10:48

update table set read2 = read, read = 0

и все, вот ваш костыль.. Вам надо не копировать а переносить данные. ;) Но все равно это кривизна полная так считать

Цитата n0wheremany @ 18.04.16, 11:20

Решил вопрос пока таким образом - добавил поле read3.

Завтра будете read4 добавлять и так далее? Пока в допустимую "ширину" таблицы не упретесь?

Цитата n0wheremany,
1460978442 @ 12.02.70, 10:28

Но хотелось бы по текущей каким то образом - есть ли варианты без создания доп таблицы

Это не доп. таблица. Это исходные данные об обращениях к сайту, на базе которых можно построить любую отчетность с любой сортировкой. ;)
Например я встречал, когда народ на очень нагруженных сайтах изучает поминутно статистику, с привязками к часовым поясам пользователя и т.д.

Цитата Akina @ 18.04.16, 11:40

Вот именно предложенная мной схема и позволит такой график построить.

а почасовой график? А в разрезе географии?

Цитата n0wheremany @ 18.04.16, 11:36

Задача - отсортировать таблицу по изменению за неделю поля read без создания дополнительных таблиц с минимальной нагрузкой

минимальной нагрузкой на что? На сервер или на руки разработчика? Или на усилия пользователя отчетности? Или на дальнейшее масштабирование отчетности?]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666540 Mon, 18 Apr 2016 11:40:59 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666540 Цитата JoeUser @ 18.04.16, 11:03

Хотя я так бы не делал. Вдруг взбредет потом построить график обращений к URL за сутки?

Вот именно предложенная мной схема и позволит такой график построить.

Цитата n0wheremany @ 18.04.16, 11:20

Я не хочу создавать отдельную таблицу для сохранения просмотров - это глупо и лишняя нагрузка.

Ну вместо того, чтобы решить задачу оптимальным образом, который, кстати, соответствует модели бизнес-процесса, Вы его решаете кривым костылём... ну чё, хозяин - барин.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666537 Mon, 18 Apr 2016 11:36:58 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666537 JoeUser Сори если не понятно )

Исходные данные таблица post с полями id,read. Обновление поля read происходит update post set read=read+1 про просмотре поста.

Задача - отсортировать таблицу по изменению за неделю поля read без создания дополнительных таблиц с минимальной нагрузкой]]> n0wheremany Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666535 Mon, 18 Apr 2016 11:32:51 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666535 n0wheremany, ты написал что ты сделал. Но не указал исходные данные и каков желаемый результат. Одну и туже задачу можно решать разными путями. Соберись с мыслями и напиши вменяемую постановку задачи.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666529 Mon, 18 Apr 2016 11:20:42 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666529
Есть только 1 таблица post с полями id,read. Я не хочу создавать отдельную таблицу для сохранения просмотров - это глупо и лишняя нагрузка. Т. к. поле read и даёт информацию по просмотрам (аналогично SELECТ count(*)).
Сейчас нужно получить изменение этого поля и отсортировать по нему. Т. к. в таблицу post добавляются строки не часто, сортировать по полю read смысла нет - получится что старые строки всегда будут в верху.
Добавил поле read2 что бы сохранять туда данные раз в неделю запросом update table set read2 = read и сортирую строки ORDER BY (read-read2). Но после сохранения (бэкапа) read-read2 равны и сортировка соответственно не адекватна.

Решил вопрос пока таким образом - добавил поле read3. Обновление такое update table set read3 = read2, read2 = readи сортировка соответственно ORDER BY (read2-read3). Т. е. сортировка по изменению предыдущей недели.

Но хотелось бы по текущей каким то образом - есть ли варианты без создания доп таблицы]]> n0wheremany Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666525 Mon, 18 Apr 2016 11:03:46 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666525 Цитата Akina @ 18.04.16, 11:00

Дурацкая (извините за грубость) структура с учётом поставленной задачи.

Вот и я о том же

Добавлено 18.04.16, 11:07

Цитата Akina @ 18.04.16, 11:00

Если прошло обращение к УРЛу - плюсим единицу за текущую дату, если запись отсутствует - создаём её с количеством = 1.

Хотя я так бы не делал. Вдруг взбредет потом построить график обращений к URL за сутки? Мне кажется, лучше просто добавлять. А уже вопрос роста БД-лога решать иными способами (бэкап "оперативных" данных в "архивные" + чистка "оперативных" данных).]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666522 Mon, 18 Apr 2016 11:00:06 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666522 Для фиксации просмотров должна существовать таблица истории обращений (УРЛ-дата-количество). Если прошло обращение к УРЛу - плюсим единицу за текущую дату, если запись отсутствует - создаём её с количеством = 1. Для сортировки по описанному принципу - просто суммируем обращения за последние 7 дней. Общее количество получаем, суммируя без учёта даты (ну или для повышения оперативной производительности храним переопределённое поле общего количества обращений).]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666521 Mon, 18 Apr 2016 10:57:59 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666521 Цитата Павел Калугин @ 18.04.16, 10:48

И все. Какие суммы? какие группировки?

Нахрена дополнительные поля (я имею ввиду read2)? Я так понимаю, речь идет о банальном логе просмотров. Лог и лог, чего тут городить что-то дополнительно, если задача расчета тривиальнейша?]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666518 Mon, 18 Apr 2016 10:48:08 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666518 JoeUser и?
структура по первому посту
Table(URL, read, read2)
при новом просмотре происходит
update table set read = read+1 where URL = ...
И раз в неделю
update table set read2 = read, read = 0
И все. Какие суммы? какие группировки?]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666517 Mon, 18 Apr 2016 10:38:44 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666517 Цитата n0wheremany @ 17.04.16, 20:50

Суть проблемы - если таблица с полем read (записывается кол-во просмотров). Т. к. это поле только увеличивается со временем. необходимо сортировать по разнице за неделю. Т. е. каждую неделю поле read бэкапится в поле read2. Сортировка по полю получится такая ORDER by (read-read2). Но возникает проблема - после очередного бэкапа - сортировка не очевидна, т. к. поля read и read2 равны.
Как решить проблему?

Решается простым запросом. В котором все записи группируются по неделям (из записанной даты извлекается номер недели), в группе же сортируются по дате, в группе берется последний элемент, первый элемент, высчитывается разница просмотров. В результате запроса получается что-то типа "Номер недели", "Количество просмотров за неделю".

ЗЫ: Никакие бэкапы для решения данной задачи не нужны. Бэкапы решают вопросы безопасности данных, либо сброс данных в архив (в бэкап) для предотвращения нежелательного роста БД.]]> JoeUser Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666480 Mon, 18 Apr 2016 07:00:34 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666480 Цитата n0wheremany @ 17.04.16, 20:50

возникает проблема - после очередного бэкапа - сортировка не очевидна, т. к. поля read и read2 равны.

Что за бред? бэкап никак не влияет на данные! А если при "бэкапе" данные изменяются - это ни хрена не бэкап.]]> Akina Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666461 Mon, 18 Apr 2016 04:53:52 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666461 Бухучёт наше всё. В данном случае устройство таблички проводок ;) дата, время, страничка откуда, страничка куда
Соответственно количество посещений за любой период - по данной табличке с группировкой по иду "куда"
И фильтром по периоду ;)]]> Павел Калугин Базы данных: Общие вопросы https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666447 Sun, 17 Apr 2016 20:50:18 +0000 Проблема сортировки по разнице изменения числа https://forum.sources.ru/index.php?showtopic=408634&view=findpost&p=3666447
Суть проблемы - если таблица с полем read (записывается кол-во просмотров). Т. к. это поле только увеличивается со временем. необходимо сортировать по разнице за неделю. Т. е. каждую неделю поле read бэкапится в поле read2. Сортировка по полю получится такая ORDER by (read-read2). Но возникает проблема - после очередного бэкапа - сортировка не очевидна, т. к. поля read и read2 равны.
Как решить проблему?

На текущий момент тупо добавил поле read3 (read - текущее, read2 - неделю назад, read3 - 2 недели назад). Сортируется так ORDER By (read2-read3), т. е. по разнице за пред неделю. Но как сделать адекватную сортировку?]]> n0wheremany Базы данных: Общие вопросы