На главную
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается для обсуждения вопросов использования баз данных, за исключением составления запросов на SQL. Для этого выделен специальный раздел. Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Chow, Bas, MIF, JoeUser
  
> Проблема сортировки по разнице изменения числа
    Добрый вечер.

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

    На текущий момент тупо добавил поле read3 (read - текущее, read2 - неделю назад, read3 - 2 недели назад). Сортируется так ORDER By (read2-read3), т. е. по разнице за пред неделю. Но как сделать адекватную сортировку?
    Говори, что думаешь И Думай, что говоришь
    nowheredev.ru
      Потом 4е поле, потом 5 и так далее. Есть подозрение что не с того конца задача решается. Я б подошел отсюда:
      Бухучёт наше всё. В данном случае устройство таблички проводок ;) дата, время, страничка откуда, страничка куда
      Соответственно количество посещений за любой период - по данной табличке с группировкой по иду "куда"
      И фильтром по периоду ;)
      Сообщение отредактировано: Павел Калугин -
        Цитата n0wheremany @
        возникает проблема - после очередного бэкапа - сортировка не очевидна, т. к. поля read и read2 равны.

        Что за бред? бэкап никак не влияет на данные! А если при "бэкапе" данные изменяются - это ни хрена не бэкап.
        Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
        Есть претензии ко мне как к участнику? да ради бога.
        Не нравятся мои ответы? не читайте их.
        В общем, берегите себя. Нервные клетки не восстанавливаются.
          Цитата n0wheremany @
          Суть проблемы - если таблица с полем read (записывается кол-во просмотров). Т. к. это поле только увеличивается со временем. необходимо сортировать по разнице за неделю. Т. е. каждую неделю поле read бэкапится в поле read2. Сортировка по полю получится такая ORDER by (read-read2). Но возникает проблема - после очередного бэкапа - сортировка не очевидна, т. к. поля read и read2 равны.
          Как решить проблему?


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

          ЗЫ: Никакие бэкапы для решения данной задачи не нужны. Бэкапы решают вопросы безопасности данных, либо сброс данных в архив (в бэкап) для предотвращения нежелательного роста БД.
          Мои программные ништякиhttps://majestio.info
            JoeUser и?
            структура по первому посту
            Table(URL, read, read2)
            при новом просмотре происходит
            update table set read = read+1 where URL = ...
            И раз в неделю
            update table set read2 = read, read = 0
            И все. Какие суммы? какие группировки?
              Цитата Павел Калугин @
              И все. Какие суммы? какие группировки?

              Нахрена дополнительные поля (я имею ввиду read2)? Я так понимаю, речь идет о банальном логе просмотров. Лог и лог, чего тут городить что-то дополнительно, если задача расчета тривиальнейша?
              Мои программные ништякиhttps://majestio.info
                Дурацкая (извините за грубость) структура с учётом поставленной задачи.
                Для фиксации просмотров должна существовать таблица истории обращений (УРЛ-дата-количество). Если прошло обращение к УРЛу - плюсим единицу за текущую дату, если запись отсутствует - создаём её с количеством = 1. Для сортировки по описанному принципу - просто суммируем обращения за последние 7 дней. Общее количество получаем, суммируя без учёта даты (ну или для повышения оперативной производительности храним переопределённое поле общего количества обращений).
                Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                Есть претензии ко мне как к участнику? да ради бога.
                Не нравятся мои ответы? не читайте их.
                В общем, берегите себя. Нервные клетки не восстанавливаются.
                  Цитата Akina @
                  Дурацкая (извините за грубость) структура с учётом поставленной задачи.

                  Вот и я о том же

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

                  Хотя я так бы не делал. Вдруг взбредет потом построить график обращений к URL за сутки? Мне кажется, лучше просто добавлять. А уже вопрос роста БД-лога решать иными способами (бэкап "оперативных" данных в "архивные" + чистка "оперативных" данных).
                  Мои программные ништякиhttps://majestio.info
                    хм... Поясню по сабжу.

                    Есть только 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). Т. е. сортировка по изменению предыдущей недели.

                    Но хотелось бы по текущей каким то образом - есть ли варианты без создания доп таблицы
                    Говори, что думаешь И Думай, что говоришь
                    nowheredev.ru
                      n0wheremany, ты написал что ты сделал. Но не указал исходные данные и каков желаемый результат. Одну и туже задачу можно решать разными путями. Соберись с мыслями и напиши вменяемую постановку задачи.
                      Мои программные ништякиhttps://majestio.info
                        JoeUser Сори если не понятно )

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

                        Задача - отсортировать таблицу по изменению за неделю поля read без создания дополнительных таблиц с минимальной нагрузкой
                        Говори, что думаешь И Думай, что говоришь
                        nowheredev.ru
                          Цитата JoeUser @
                          Хотя я так бы не делал. Вдруг взбредет потом построить график обращений к URL за сутки?

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

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

                          Ну вместо того, чтобы решить задачу оптимальным образом, который, кстати, соответствует модели бизнес-процесса, Вы его решаете кривым костылём... ну чё, хозяин - барин.
                          Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                          Есть претензии ко мне как к участнику? да ради бога.
                          Не нравятся мои ответы? не читайте их.
                          В общем, берегите себя. Нервные клетки не восстанавливаются.
                            Цитата Akina @
                            Для фиксации просмотров должна существовать таблица истории обращений (УРЛ-дата-количество). Если прошло обращение к УРЛу - плюсим единицу за текущую дату, если запись отсутствует - создаём её с количеством = 1.

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

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

                            это единственное разумное решение. Тупо заливать лог просмотров. Один быстрый инсерт
                            Цитата n0wheremany @
                            Но после сохранения (бэкапа) read-read2 равны и сортировка соответственно не адекватна.
                            Это не бекап.. посмотрите внимательно на это
                            Цитата Павел Калугин @
                            update table set read2 = read, read = 0

                            и все, вот ваш костыль.. Вам надо не копировать а переносить данные. ;) Но все равно это кривизна полная так считать
                            Цитата n0wheremany @
                            Решил вопрос пока таким образом - добавил поле read3.

                            Завтра будете read4 добавлять и так далее? Пока в допустимую "ширину" таблицы не упретесь?
                            Цитата n0wheremany,
                            1460978442 @
                            Но хотелось бы по текущей каким то образом - есть ли варианты без создания доп таблицы

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

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

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

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

                            минимальной нагрузкой на что? На сервер или на руки разработчика? Или на усилия пользователя отчетности? Или на дальнейшее масштабирование отчетности?
                              Цитата Akina @
                              Вот именно предложенная мной схема и позволит такой график построить.

                              Не. Если ты будешь
                              Цитата Akina @
                              Если прошло обращение к УРЛу - плюсим единицу за текущую дату

                              ... то "почасовые" данные у тебя теряются.
                              Мои программные ништякиhttps://majestio.info
                                Цитата n0wheremany @
                                Исходные данные таблица post с полями id,read. Обновление поля read происходит update post set read=read+1 про просмотре поста.

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

                                  На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно. Если нужно более мелко - соответствующим образом дробится и диапазон сбора статистики. В пределе - фиксация штампа времени каждого отдельного обращения.
                                  Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                  Есть претензии ко мне как к участнику? да ради бога.
                                  Не нравятся мои ответы? не читайте их.
                                  В общем, берегите себя. Нервные клетки не восстанавливаются.
                                    Цитата Akina @
                                    На текущий момент информация от ТС предполагает, что суточной дискретизации ему достаточно.

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




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

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

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

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

                                    ExpandedWrap disabled
                                      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, которые были бы посещены хотя бы один (или более) раз за последние две календарные недели, без учета текущей календарной недели.

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

                                    ExpandedWrap disabled
                                      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

                                    Получаем нечто типа:
                                    ExpandedWrap disabled
                                      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 посещений, тогда действительно таблица логов будет пухнуть. А вот тут уже по желанию:

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

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

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

                                    Ну да, в принципе я это выше и описал.
                                    Мои программные ништякиhttps://majestio.info
                                      Цитата JoeUser @
                                      Предлагаю такой вариант

                                      Правильный вариант хранить лог по полям как отдает его сервер ;)
                                        Цитата Павел Калугин @
                                        Правильный вариант хранить лог по полям как отдает его сервер

                                        Во во.
                                        Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.
                                        Сообщение отредактировано: JoeUser -
                                        Мои программные ништякиhttps://majestio.info
                                          Цитата JoeUser @
                                          а что делать с накапливаемой информацией?

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

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

                                          30кк записей в год - объём не чрезмерный. К тому же таблица логов может партиционироваться по времени.
                                          Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                          Есть претензии ко мне как к участнику? да ради бога.
                                          Не нравятся мои ответы? не читайте их.
                                          В общем, берегите себя. Нервные клетки не восстанавливаются.
                                            Цитата JoeUser @
                                            Хотя, если есть вариант, нормализовать на-лету - почему бы и нет.

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

                                            Добавлено
                                            Akina в таких сайтах одно посещение это обращение юзера к 3-5 страницам как минимум, нагрузки считаем по "верхнему" пределу итого 100К посещений это 400К обращений в сутки, половина из которых в "пиковые" 4 часа = 50К в час, то есть примерно 14 обращений в секунду. Да, немного. Но уже и не мало ;)
                                            Сообщение отредактировано: Павел Калугин -
                                              Цитата Павел Калугин @
                                              Лог только на инсеррт. А все разборы нормализация потом джобами или еще чем главное чтоб не блокировало таблицу лога на инсерт и не тормозило его.

                                              Обсчёт статистики не дешевле её предрасчёта, а его могут запустить в любой момент (да ещё, как показывает опыт, несколько штук параллельно).
                                              Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                              Есть претензии ко мне как к участнику? да ради бога.
                                              Не нравятся мои ответы? не читайте их.
                                              В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                Akina, собственно, я написал ровно тоже что и ты ;) Хочешь поспорить?
                                                  Павел Калугин
                                                  Да не вижу я предмета для спора. Чтобы иметь хоть какие-то данные для него, надо как минимум знать, о каком конкретно сервере идёт речь, и иметь хоть какие-то данные о БД и нагрузке на неё. А счас что? конь - идеальный, вакуум - сферический...
                                                  Сообщение отредактировано: Akina -
                                                  Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                  Есть претензии ко мне как к участнику? да ради бога.
                                                  Не нравятся мои ответы? не читайте их.
                                                  В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                    Ну как о чем? О структуре бидэ, о порядке предподготовки данных (я это срезами назвал), о логике построения отчетов .... ;)
                                                    Хотя если окажется что сервер типа укоза а логи это посещение всех сайтов укоза тут все равно все придется перерисовывать ;) :D
                                                      Цитата Павел Калугин @
                                                      Потому что нет. Лог только на инсеррт.


                                                      Не вижу противоречий. Да и "нормализация" тут совсем ненапряжная. Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице. Не думаю, что по ней поиск будет заметным для БД, если нормально отстроить кэширование движка БД.
                                                      Мои программные ништякиhttps://majestio.info
                                                        JoeUser Как верно Акина написал выше - гадание на кофейной гуще.
                                                        если там 100 просмотров страниц в день - любое решение будет работать. Если сотни-тысячи в секунду - ой как думать надо шо с этим делать и как.
                                                        Вопрос в том, что все предложенные решения (в том числе и решение автора) работают в неких границах по нагрузке. И чем больше обработки при записи потока данных в бд тем уже эти пределы. ;)
                                                        Сообщение отредактировано: Павел Калугин -
                                                          Цитата JoeUser @
                                                          Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице.

                                                          Ну как бы это решение вообще необсуждаемое. Таблица посещений - самая в текущем бизнес-процессе высоконагруженная, следовательно, просто обязана быть максимально компактной. Так что только компактные типы (BIGINT, DATETIME), а всякий текстовый хлам - наружу, и по ссылке.
                                                          Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                          Есть претензии ко мне как к участнику? да ради бога.
                                                          Не нравятся мои ответы? не читайте их.
                                                          В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                            ну как бы спорный вопрос. не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции
                                                            Мне кажется или писать во вьюху с инстед тригером тоже не будет оптимальным решением?
                                                              Цитата Павел Калугин @
                                                              не сильно ли замедлит запись подбирать иды под весь этот текстовый хлам и что писать если ида не оказалось? Надо жеж тогда "справочник" обновить - а это еще большее раздувание транзакции

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

                                                              Вьюхи (которые к тому же любят в самый неподходящий момент захотеть материализоваться) или триггеры - это явно лишнее. Ну то есть триггер вроде бы и мог быть по месту - но, как мне кажется, от записи лога трудно ожидать пакетного поступления записей, а в этих условиях триггер только породит ненужные накладные расходы.
                                                              Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                              Есть претензии ко мне как к участнику? да ради бога.
                                                              Не нравятся мои ответы? не читайте их.
                                                              В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                Цитата Akina @
                                                                ошибку дублирования злостно игнорируем

                                                                Вот это не понятно. Как по мне - надо сначала найти урл, если не найден то вставить. Это "тормоз вставки" раз. Потом или повторить поиск или выцепить свежесозданный ид. Это "тормоз вставки" 2
                                                                Если окажутся "неуникальные" урлы то данные тормоза возрастут или будут ошибки. Опять же затормозится сильно последующая обработка так как всю эту "неуникальность" придется отдельно анализировать
                                                                  Цитата Павел Калугин @
                                                                  Вот это не понятно. Как по мне - надо сначала найти урл, если не найден то вставить. Это "тормоз вставки" раз. Потом или повторить поиск или выцепить свежесозданный ид. Это "тормоз вставки" 2

                                                                  Эту проблему решает структура
                                                                  ExpandedWrap disabled
                                                                    CREATE TABLE URLs (
                                                                        ID integer auto_increment primary key,
                                                                        URL varchar(255),
                                                                        INDEX URL (URL) UNIQUE
                                                                    );

                                                                  Соответственно просто делаем тупое
                                                                  ExpandedWrap disabled
                                                                    INSERT INTO URLs (URL) VALUES (:url);

                                                                  Если текущий УРЛ отсутствует - вставка выполняется. Если уже присутствует - при выполнении возникает ошибка нарушения уникального индекса, которую мы проигнорируем. Но вне зависимости от того, какой имеется вариант, после выполнения запроса URL в таблице ЕСТЬ. И можно спокойно выполнять
                                                                  ExpandedWrap disabled
                                                                    INSERT INTO clicks(dt, url_id)
                                                                    SELECT now(), id
                                                                    FROM URLs
                                                                    WHERE URLs.URL = :url;
                                                                  Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                                  Есть претензии ко мне как к участнику? да ради бога.
                                                                  Не нравятся мои ответы? не читайте их.
                                                                  В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                    в таком варианте да.
                                                                    Интересно было бы смоделировтаь нагрузку, при которой такая вставка "захлебнется"
                                                                    И сровнить с просто записью лога без "разбора на иды".. Но, это наверное после майских можно будет поразвлечься.
                                                                      Цитата Akina @
                                                                      Цитата JoeUser @
                                                                      Меня смущает запись в логи URL в сыром виде. Туда не URL писать нужно, IdURL, а сам список возможных URL хранить в отдельной таблице.

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

                                                                      Именно!!!

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

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


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

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

                                                                      Гипотеза

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

                                                                      Эксперимент

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

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

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

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

                                                                      ExpandedWrap disabled
                                                                        #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 (242,19 Кбайт, скачиваний: 69)

                                                                      Добавлено
                                                                      ЗЫ: Хотя, сорь, во втором случае в лог попадают не бинарные индексы, а символьные числа. Переделаю, отпишу скорости.
                                                                      Мои программные ништякиhttps://majestio.info
                                                                        Теперь с нормализацией получилось даже еще красивше)))

                                                                        RAW: 50499 ms
                                                                        ID : 23428 ms

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

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

                                                                        ExpandedWrap disabled
                                                                          #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 (210,5 Кбайт, скачиваний: 58)
                                                                        Мои программные ништякиhttps://majestio.info
                                                                          Цитата JoeUser @
                                                                          Как говорится, получите и распишитесь

                                                                          Всё это никакого отношения к обсуждаемому выше не имеет.
                                                                          Если очень хочется, то проделай всё то же на любом SQL-сервере. MySQL, FB, MS SQL CE... неважно, каком. Сгенери таблицу на, скажем, 1кк URL, а потом замерь время вставки 10к записей, из которых 9к дубли, по первой и по второй технологии. Такой эксперимент будет хоть как-то адекватен, особенно если сможешь организовать многопоточную вставку.
                                                                          Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                                          Есть претензии ко мне как к участнику? да ради бога.
                                                                          Не нравятся мои ответы? не читайте их.
                                                                          В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                            Цитата Akina @
                                                                            Сгенери таблицу на, скажем, 1кк URL


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

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

                                                                            Хотя можно попробовать. Подумаю
                                                                            Мои программные ништякиhttps://majestio.info
                                                                              Цитата JoeUser @
                                                                              На сайте, коль регистрируется доступ к ресурсам, от силы 1к своих URL, и то это черезчур. Нам надо сделать таблицу 1к URL и сымитировать доступ к ним, с записью лога "сырого" и "нормализованного".

                                                                              Я имел в виду 1кк уже существующих записей лога, а не уникальных URL. Впрочем, согласен, что 10к уников - многовато.
                                                                              Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                                              Есть претензии ко мне как к участнику? да ради бога.
                                                                              Не нравятся мои ответы? не читайте их.
                                                                              В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                                Цитата Akina @
                                                                                Такой эксперимент будет хоть как-то адекватен, особенно если сможешь организовать многопоточную вставку.

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

                                                                                Вот код:

                                                                                ExpandedWrap disabled
                                                                                  #!/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];
                                                                                  }


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

                                                                                ExpandedWrap disabled
                                                                                  Поехали тестировать ============================================
                                                                                   
                                                                                   * Генерируем 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 записи/сек. Для таких условий, как проводилось тестирование, я считаю результат вполне приемлем.
                                                                                Мои программные ништякиhttps://majestio.info
                                                                                  У меня с перлом не сложилось... но, насколько я понял, ты замеряешь время загрузки большого массива в пустые таблицы. Если так - это неверно. Надо мерить время загрузки сравнительно небольшой порции (скажем, 10 тыс.) в заполненную большим количеством (скажем, 1 млн.) записей структуру.
                                                                                  Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                                                  Есть претензии ко мне как к участнику? да ради бога.
                                                                                  Не нравятся мои ответы? не читайте их.
                                                                                  В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                                    Цитата Akina @
                                                                                    но, насколько я понял, ты замеряешь время загрузки большого массива в пустые таблицы.

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

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

                                                                                    Попробую вечером посмотреть че можно сделать. Дело в том, что у меня в виртуальных машинах свободного дискового пространства по минимуму - оттого и не могу грузить много. Надо проинспектировать ) Есть еще 4 VM под управлением разных версий линухов, и одна - под Mac OS X. Может быть там есть место, просто не помню.
                                                                                    Мои программные ништякиhttps://majestio.info
                                                                                      Тест крайне простой
                                                                                      - "нагрузка" нужна не с 10 а, хотябы со 100 и не нитей а приложений.
                                                                                      - тест 1 выполняется "инсерт" в таблицу
                                                                                      - тест 2 выполняется "нормализация" с поиском и вставкой если не нашли
                                                                                      - тест 3 выполняется "инсерт" с "подавлением ошибки" и последующим поиском

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

                                                                                      Вот над таким собираюсь на вторые майские посидеть.
                                                                                      Сейчас времени нет, удочки собираю :)
                                                                                        Цитата Павел Калугин @
                                                                                        Вот над таким собираюсь на вторые майские посидеть.

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

                                                                                        Цитата Павел Калугин @
                                                                                        и не нитей а приложений

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

                                                                                        Добавлено
                                                                                        Цитата Павел Калугин @
                                                                                        - тест 2 выполняется "нормализация" с поиском и вставкой если не нашли

                                                                                        И тут момент. ИМХО, вставка URL в свой "каталог URLs" нужно делать в момент создания "материала" (будь то статическая страница, будь то динамическая тема форума, к примеру). А именно поиск+вставка прокатит, если мы хотим собрать статистику по ошибочным обращениям.
                                                                                        Мои программные ништякиhttps://majestio.info
                                                                                          Цитата JoeUser @
                                                                                          Если у тя есть реальный железный сервак БД (выделенный) - имеет смысл заморачиваться, ибо результаты будут более правдивы, чем у меня с ВМ.

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

                                                                                            Кстати, ознакомься с этой темой.
                                                                                            Есть предположение что, если грамотно реализовать сборку единичных логов в "пакеты", то можно будет выжимать чудеса по скоростям :)
                                                                                            Мои программные ништякиhttps://majestio.info
                                                                                              JoeUserНу это уже про шаманство на очень высоких нагрузках ;) Ну и вставку пакетами сервак делать умеет ;)
                                                                                                Цитата JoeUser @
                                                                                                Есть предположение что, если грамотно реализовать сборку единичных логов в "пакеты", то можно будет выжимать чудеса по скоростям

                                                                                                Для чудес по скоростям достаточно создать таблицу для первичного приёма логов на MEMORY-движке, и пачками переливать из неё поступившие сведения в основную таблицу.
                                                                                                Есть претензии ко мне как к модератору? читайте Правила, разделы 5 и 6, и действуйте соответственно.
                                                                                                Есть претензии ко мне как к участнику? да ради бога.
                                                                                                Не нравятся мои ответы? не читайте их.
                                                                                                В общем, берегите себя. Нервные клетки не восстанавливаются.
                                                                                                  Меня тут "осенило" ... сборку логов пакетами можно осуществлять уже имеющимися средствами "а-ля тонких клиентов". Нужно всего лишь скрестить "syslog" или какой-нить его форк типа "syslog-ng" с существующим сервером БД. Тогда первичные запросы на запись логов будет принимать не сам сервак БД, а посредник (syslog*). Времени пока особо нет, так поверхностное гугление дало два интересных линка "kiwisyslog" и "Writing syslog messages to MySQL, PostgreSQL or any other supported Database" ... походу мы упорно изобретаем велосипед из дерева :lol:

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

                                                                                                  Такие дела.
                                                                                                  Мои программные ништякиhttps://majestio.info
                                                                                                    JoeUser и ? топикстартеру от этого уже все ясно эт раз
                                                                                                    что у нас вообще по физике происходит.
                                                                                                    Есть вэбсервер Он , обычно, пишет текстовый лог. посещений
                                                                                                    Если мне не изменяет память, можно перенаправить эту запись в биде.
                                                                                                    Или надо вклиниваться в каждую страницу с кодом который "отгрузит лог".
                                                                                                    Или увы, по расписанию брать серверный лог и пакетом его грузить.
                                                                                                    что дальше?
                                                                                                    Если грузим пакетами нам насрать. Може м и разобрать можем и ....
                                                                                                    Если вклиниваемся в работу вэбсервера любой затык на отгрузке лога будет тормозить сервак при синхронном соединении или приведет к потерям данных при асинхронном
                                                                                                    Тут нужен анализ что нам дороже. Возможно потеря 10-15 данных на результате анализа и не скажется а возможно нужно все "до копейки".
                                                                                                    Если вклиниватся в работу страничек - практически тоже что и с вэбсервером. Или загрузка страницы увеличивается на ожидание отклика или есть риск потерь данных
                                                                                                    Вот как бы и все ;)
                                                                                                    Где тут найден "клиент" и тем более "тонкий клиент" не понимаю.
                                                                                                    Лайт, не лайт тоже не понятно.

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

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

                                                                                                    ну или так.. А на обработке "пачки" скорость уже не настолько критична. ;)
                                                                                                      Цитата Павел Калугин @
                                                                                                      Есть вэбсервер Он , обычно, пишет текстовый лог. посещений

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

                                                                                                      По поводу возможных потерь ... ну если система кривая по настройке и по энергообеспечению. Но это другая композиция из другой оперы.
                                                                                                      Мои программные ништякиhttps://majestio.info
                                                                                                        Цитата JoeUser @
                                                                                                        Осталось только научить syslogd сбрасывать не в системный журнал (как правило это /var/log/messages) или выделенные лог-файлы, а завернуть это пакетными запросами к серверу БД.

                                                                                                        "syslog vs syslog-ng" - походу все уже есть :lol:
                                                                                                        Мои программные ништякиhttps://majestio.info
                                                                                                          Цитата JoeUser @
                                                                                                          По поводу возможных потерь ... ну если система кривая по настройке и по энергообеспечению. Но это другая композиция из другой оперы.

                                                                                                          нет вопрос как раз про потери потому что ыйд сервер не способен принят такое количество запросов. с инсертами в одни и те же таблицы и параллельным поиском по ним ;)
                                                                                                            Цитата Павел Калугин @
                                                                                                            нет вопрос как раз про потери потому что ыйд сервер не способен принят такое количество запросов. с инсертами в одни и те же таблицы и параллельным поиском по ним


                                                                                                            Это вопрос из разряда "как записать, если стоит защита от записи" :lol: Ну если сервак не тянет физически поступаемый объем инфы, или уменьшать поступающую инфу, либо строить/расширять кластер. ИМХО, если сервак работает более чем на 50% загрузки - это уже "звонок" о том, что не все так хорошо в датском королевстве.
                                                                                                            Мои программные ништякиhttps://majestio.info
                                                                                                              Цитата JoeUser @
                                                                                                              или уменьшать поступающую инфу, либо строить/расширять кластер. ИМХО, если сервак работает более чем на 50% загрузки - это уже "звонок" о том, что не все так хорошо в датском королевстве.

                                                                                                              Все же писать софт правильно более бюджетное решение ;)
                                                                                                                Цитата Павел Калугин @
                                                                                                                се же писать софт правильно более бюджетное решение ;)


                                                                                                                Ну таки да :)
                                                                                                                Мои программные ништякиhttps://majestio.info
                                                                                                                0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)
                                                                                                                0 пользователей:


                                                                                                                Рейтинг@Mail.ru
                                                                                                                [ Script Execution time: 0,4408 ]   [ 21 queries used ]   [ Generated: 28.02.20, 08:53 GMT ]