Обработка огромных данных на Postgres

ОК, как только вы здесь, вы уже знаете о Postgres! Итак, давайте погрузимся прямо в тему, в которую мы смотрим, как обрабатывать определенную таблицу, когда она поражает ваше лицо. 🥴

Позвольте мне просто кратко о моем опыте с огромной нагрузкой данных и о том, как мы его оптимизировали. Во время окончательного проекта семестра, который мы сделали, у нас было почти около 20 миллионов записей WiFi Restords и подсчета в определенной таблице, которую мы решили назвать «WiFis» (Sequelize сгенерировал его!).

Проблема 1.

Первый вопрос начался, когда мы просто пытались подсчитать количество запросов зонда, которые мы получаем в режиме реального времени, и отображаем общий счет на панели инструментов Grafana (которая выглядит относительно простой), пока мы не обнаружили, что запросы на 20 миллионов записей стол с Выберите Count (*) из «WiFis»; Был работать, но медленное решение проблемы с тех пор, как он почти 5 секунд почти 5 секунд! вернуть счет.

Ну, после нескольких поисков обнаружил, почему это было то, что было встроено в Postgres. PostgreSQL использует контроль параллелизма Multiverionsion (MVCC) для обеспечения согласованности между одновременными транзакциями. Это означает, что каждая транзакция может видеть разные строки и разное количество строк в таблице. Нет единой универсальной подряд, который база данных может кэш, поэтому он должен просматривать все строки, считая, сколько видно. Производительность для точного количества растет линейно со размером стола.

Итак, чтобы преодолеть это, мы на самом деле настроим вторую новую таблицу, которая просто удержала бы количество всех необходимых вещей, которые необходимо было отображать на приборной панели, и продолжает обновлять после каждой транзакции на таблице «WiFis». Как мы достигаем этого?

Введите триггеры ….

Так что план был простым

Создать вторую таблицу под названием count_table.

CREATE TABLE row_counts (
    relationname text PRIMARY KEY,
    relationtuples bigint
);

Инициализируйте эту таблицу с текущим состоянием/количеством записей в таблице «WiFis».

INSERT INTO row_counts (relationname, relationtuples)
VALUES (
        'wifiProbes',
        (
            SELECT count(*)
            from "Wifis"
        )
    );

Создайте функцию триггера, которая регулирует количество таблиц ROW_COUNTS, когда есть действие на таблице «WiFis».

CREATE OR REPLACE FUNCTION adjust_count()
RETURNS TRIGGER AS
$$
   DECLARE
   BEGIN
   IF TG_OP = 'INSERT' THEN
      EXECUTE 'UPDATE row_counts set relationtuples=relationtuples +1 where relationname = ''' || TG_RELNAME || '''';
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      EXECUTE 'UPDATE row_counts set relationtuples=relationtuples -1 where relationname = ''' || TG_RELNAME || '''';
      RETURN OLD;
   END IF;
   END;
$$
LANGUAGE 'plpgsql';

Далее Создайте триггер на таблице «WiFis»

CREATE TRIGGER probes_count BEFORE
INSERT
    OR DELETE ON "Wifis" FOR EACH ROW EXECUTE PROCEDURE adjust_count();

Таким образом, в следующий раз вы просто хотите получить количество объектов на вашем огромном столе, просто запрашивайте относительно меньшую таблицу row_counts который обновляется каждый раз, когда происходит операция на главной таблице, отсюда, отсюдат Считать Запросы от 5 секунд! к простым миллисекундам. 🚀

Проблема 2.

Другие вопросы, которые мы нашли в ходе этого упражнения, заключается в том, что мы запрашивали, чтобы одинаковую таблицу «WiFis» для отображения некоторых данных на Frestend мы получили необходимость Заказать Их по временной метку.

Первый запрос, который мы побежали почти 25 секунд! Чтобы ответить и к тому времени, что Frontend HTTP-запрос уже имел время.

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

pi_production=# select * from "Wifis" where "timestamp" >= timestamp '2020-06-01T18:30:00.000Z'  and "timestamp" <= timestamp '2020-06-03T15:11:06.276Z' order BY "timestamp" limit 50
                               QUERY PLAN
----------------------------------------------------------------------------------
 Limit (cost=185076.20..185076.23 rows=50 width=11)
       (actual time=896.739..896.740 rows=50 loops=1)
        -> Sort (cost=185076.20..197576.20 rows=20789184 width=11)
                (actual time=896.737..896.738 rows=10 loops=1)
           Sort Key: x
           Sort Method: top-N heapsort Memory: 25kB
           -> Seq Scan on t_test (cost=0.00..77028.00 rows=20789184 width=11) 
                                 (actual time=1.154..282.408 rows=20789184 loops=1)
Planning time: 0.087 ms
Execution time: 24.768 s
(7 rows)

Как видите, проходите/сканирование через термины Лэймана заняли много времени.

Чтение больше об этом, казалось, указывало на вопрос. Поскольку эти строки не в памяти для Postgres для их обработки, он на самом деле должен получить их с диска, загружать их на память, а затем обработать их. Итак, на самом деле ускорить его, все, что нам нужно сделать, это просто держать их в памяти. Так что индексируйте их в Postgres. I.E Указатель столбец, который вы хотели бы использовать чаще, в моем случае это было Timestamp Так как мы бы чаще заказываем предметы в таблице. В случае, если вы будете искать предмет чаще, индекс ID Отказ

pi_production=# create index timestamp_index on "Wifis"(timestamp);
CREATE INDEX
Time: 40252.817 ms

После создания индекса тот же запрос Ордер по временной метку Взял всего 6,808 мс от ~ 25 секунд. 🚀

pi_production=# explain analyze select * from "Wifis" where "timestamp" >= timestamp '2020-06-01T18:30:00.000Z'  and "timestamp" <= timestamp '2020-06-03T15:11:06.276Z' order BY "timestamp" limit 50;
                                                                              QUERY PLAN                                               

---------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
 Limit  (cost=0.56..169.98 rows=50 width=268) (actual time=0.240..0.316 rows=50 loops=1)
   ->  Index Scan using timestamp_index on "Wifis"  (cost=0.56..955416.29 rows=281963 width=268) (actual time=0.227..0.294 rows=50 loop
s=1)
         Index Cond: (("timestamp" >= '2020-06-01 18:30:00'::timestamp without time zone) AND ("timestamp" <= '2020-06-03 15:11:06.276'
::timestamp without time zone))
 Planning Time: 2.482 ms
 Execution Time: 0.475 ms
(5 rows)

Феве !! Это было много поисков и реализации, помните, когда у нас есть огромный стол, который часто имеет операции на нем И если вы хотите, чтобы это было быстро (Duh! ) Всегда индексируйте их. 🤗

Если есть проблемы с обработкой огромных данных на Postgres, пожалуйста, будьте звездой и бросьте их в дискуссии, поэтому я готов справиться с ними в следующий раз. (Благодаря вам)

Оригинал: «https://dev.to/itspavan/handling-huge-data-on-postgres-dng»

Проблема 1.

Проблема 2.

Читайте ещё по теме: