ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / ускорение операций update
13 сообщений из 13, страница 1 из 1
ускорение операций update
    #40025954
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
Добрый день.
Помогите советом/рекоммендациями как выжать из PG-13 максимум на операциях обновления(update).
Есть прототип приложения(Proc1) работающий с базой через libpq. Сама база небольшая(~20мб) и содержит одну плоскую таблицу (с полями x1,x2,x3,x4,y1,y2,y3,y4,result), все записи уникальные (по x1,x2,x3,x4,y1,y2,y3,y4 построен уникальный индекс)в количестве 1млн(по ТЗ их может быть в 2-2.5 раз больше). К этой базе/таблице раз в 5 минут обращается другое приложение(Proс2), которое выберет из всех строк только одну.
"Proc1" получает часть данных из другого источника, загоняет их в массив М1, считывает нашу плоскую таблицу целиком в другой массив М2,обсчитывает одно поле и обновляет его в массиве, потом содержимым массива в цикле делается update только поля result. все обновления выполняется одной транзакцией.
Когда дошло дело до "натурных испытаний" столкнулись с тем что не укладываемся в "окно" 5мин, среднее значение 2500-3000 обновлений в секунду. Стендовая машина старый сервер, 4core-3МГц, 8Гб RAM, RAID-контроллер с "сдохшей" батарейкой(но write back включен) и RAID-0 из двух SATA-II 5400rpm.
Решили, раз бд таких скромных размеров, то давайте ее полностью загоним в RAM. Я понимаю что это опасно, но это прототипирование и сама база небольшая+можно физический стендбай на хард-драйве держать. И в этом упражнении главное научится правильно готовить PG.
mount -t ramfs none /mnt/ram, скопировали кластер в /mnt/ram, сгенерировали с помощью сайта-"калькулятора" pgtune параметры для конфига(как для SSD-стороджа), cохранили их в postgresql.auto.conf, postgresql.conf дефолтный

max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Запустили базу, кардинально лучше не стало прирост в скорости примерно 10%, и все также не укладываемся в рамки 5 минут на обновление. Сделали таблице unlogged, FillFactor=50 и тоже значимых улучшений не принесло.

Все команды update динамически строятся приложением и посылаются на сервер через вызов PQexec(я так понимаю что лучше update обернуть в функцию plpgsql + может быть переделают на вариант 1- PQsendQuery или вариант2 - очистка таблицы+PQputCopyData)

Сам запрос и его план.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
[/SRC]
explain analyze update table1 set result=10 where x1=10::smallint and  x2=100::smallint and  x3=14::smallint and  x4=28::smallint and y1=33::smallint and y2=87::smallint and y3=170::smallint and y4=2::smallint;

 Update on table1  (cost=0.42..2.65 rows=1 width=62) (actual time=0.013..0.013 rows=0 loops=1)
   ->  Index Scan using ix_w on table1  (cost=0.42..2.65 rows=1 width=62) (actual time=0.012..0.012 rows=0 loops=1)
         Index Cond: ((x1 = '10'::smallint) AND (x2 = '100'::smallint) AND (x3 = '14'::smallint) AND (x4 = '28'::smallint) AND (y1 = '33'::smallint) AND (y2 = '87'::smallint) AND (y3 = '170'::smallint) AND (y4 = '2'::smallint))
 Planning Time: 0.269 ms
[src]


Execution Time: 0.039 ms
...
Рейтинг: 0 / 0
09.12.2020, 15:18
| Ответить | Цитировать | Написать  
ускорение операций update
    #40025994
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Я бы сказал что упираетесь вы в латентность операций над множеством очень маленьких запросов. 3000rps по 0,25мс - это уже 0.8с и есть.

wot-64
"Proc1" получает часть данных из другого источника, загоняет их в массив М1, считывает нашу плоскую таблицу целиком в другой массив М2,обсчитывает одно поле и обновляет его в массиве, потом содержимым массива в цикле делается update только поля result. все обновления выполняется одной транзакцией.

Загоните через copy в другую табличку (времянку можно), затем одним update обновите что различается в result.
...
Рейтинг: 0 / 0
09.12.2020, 16:03
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026006
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
Интересный вариант, спасибо!!!
тут кстати и пример нашел по этой теме.


https://stackoverflow.com/questions/962361/how-can-i-speed-up-update-replace-operations-in-postgresql
...
Рейтинг: 0 / 0
09.12.2020, 16:23
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026016
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
wot-64
Интересный вариант, спасибо!!!
тут кстати и пример нашел по этой теме.


https://stackoverflow.com/questions/962361/how-can-i-speed-up-update-replace-operations-in-postgresql


что смешно - самого эффективного метода через copy во временную таблицу в этом списке и нет...
а ведь именно COPY API специально именно для batch операций специально придуман.
multi-inserts хуже по скорости будет заметно но лучше чем построчно делать.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
09.12.2020, 16:38
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026025
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
Насчет COPY тоже нашлись примеры.

https://cpp.hotexamples.com/ru/examples/-/-/PQputCopyData/cpp-pqputcopydata-function-examples.html
https://gist.github.com/ictlyh/12fe787ec265b33fd7e4b0bd08bc27cb

Я так понимаю дальше заниматься конфигурированием сервера не имеет смысла? Если "тормоза" в логике/коде клиентского приложения.

Меня еще смущают скромные цифры выдаваемые бенчмарком, pg_wal тоже в ram лежит.

pgbench -c 20 -C -j 50 -t 100 -r pgench
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 20
number of transactions per client: 100
number of transactions actually processed: 2000/2000
latency average = 54.891 ms
tps = 364.356308 (including connections establishing)
tps = 859.271715 (excluding connections establishing)
statement latencies in milliseconds:
0.006 \set aid random(1, 100000 * :scale)
0.002 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
3.592 BEGIN;
4.269 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
3.111 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
3.816 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
5.742 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
1.107 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.777 END;

А pg_test_fsync выдает замечательные результаты

/mnt/ram/postgres/main$ pg_test_fsync -f test
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync n/a*
fdatasync 427902.029 ops/sec 2 usecs/op
fsync 439544.681 ops/sec 2 usecs/op
fsync_writethrough n/a
open_sync n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync n/a*
fdatasync 246335.858 ops/sec 4 usecs/op
fsync 250405.649 ops/sec 4 usecs/op
fsync_writethrough n/a
open_sync n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write n/a*
2 * 8kB open_sync writes n/a*
4 * 4kB open_sync writes n/a*
8 * 2kB open_sync writes n/a*
16 * 1kB open_sync writes n/a*

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 146658.531 ops/sec 7 usecs/op
write, close, fsync 145677.076 ops/sec 7 usecs/op

Non-sync'ed 8kB writes:
write 234574.849 ops/sec 4 usecs/op
...
Рейтинг: 0 / 0
09.12.2020, 17:05
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026057
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
wot-64,

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

Вопросы
1)вы pgbench пускали локально или с удаленного сервера?
2)попробуйте его запустить минут на 10 хотя бы и посмотреть что top на сервере с базой показывает
и что там же показывает iostat -xmd 10 в это время
тогда можно будет подумать (но вообще у вас не сервер а издевательство над здравым смыслом).

Но copy и batch update помогут вне зависимости от итогов теста.


--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
09.12.2020, 17:57
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026093
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
Maxim Boguk,

На нормальное железо еще не заработали :(, поэтому мучаем то что не жалко.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
x CPU Utilisation Stats qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqx
xALL   129.4    0.0  268.4    1.0    0.0    0.0    2.0    0.0    0.0    0.0                                                          x
xCPU   User%  Nice%   Sys%  Idle%  Wait% HWirq% SWirq% Steal% Guest% GuestNice%                                                      x
x  1    35.0    0.0   64.5    0.0    0.0    0.0    0.5    0.0    0.0    0.0                                                          x
x  2    34.5    0.0   65.0    0.0    0.0    0.0    0.5    0.0    0.0    0.0                                                          x
x  3    30.5    0.0   68.5    0.0    0.0    0.0    0.5    0.0    0.0    0.0                                                          x
x  4    29.5    0.0   70.5    0.0    0.0    0.0    0.5    0.0    0.0    0.0                                                          x
xqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqx
x                                                                                                                                    x
x

Sys очень высокий, хотя когда запускаем наше приложение kernel забирает на себя 1-3.5%(LA 1.0-1.5). iostat&iotop не видят ram как блочное устройство, они во время запуска pgbench показывают нулевую дисковую активность.
И если базу сбросить на диск и запустить бенчмарк LA остается высоким. Вообщем направлений для экспериментов много.

Всем спасибо.
...
Рейтинг: 0 / 0
09.12.2020, 19:10
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026108
Фотография mefman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mefman
Участник
wot-64,
Если большой sys - посмотрите что perf top скажет.
...
Рейтинг: 0 / 0
09.12.2020, 19:35
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026110
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
wot-64,

А скажите ну вот нафига вы -С ключ добавили??? (-C, --connect establish new connection for each transaction)
Вы протестировали не скорость работы базы а скорость с какой OS форки отрабатывает на вашем железе.
Оттого и system time высокий.

-с 20 на 4х ядрах тоже один вред


Вы вот так вот попробуйте если хотите на вашей кофеварке какие то разумные цифры увидеть:
pgbench --client=8 --time=100 --protocol=prepared --vacuum-all --report-latencies pgench

--client может и даже 4 будет лучше в вашем случае но тут уже проверять надо.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
09.12.2020, 19:46
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026159
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
вывод perf top
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
   2.47%  [kernel]                       [k] __entry_trampoline_start
   2.24%  [kernel]                       [k] _raw_spin_lock_irqsave
   1.90%  postgres                       [.] hash_search_with_hash_value
   1.19%  [kernel]                       [k] syscall_return_via_sysret
   1.12%  [kernel]                       [k] _raw_spin_lock
   1.01%  postgres                       [.] PostgresMain
   0.93%  [kernel]                       [k] __schedule
   0.87%  [kernel]                       [k] __fget_light
   0.80%  [kernel]                       [k] unix_stream_read_generic
   0.79%  postgres                       [.] LWLockRelease
   0.77%  postgres                       [.] HeapTupleSatisfiesVacuum
   0.76%  [kernel]                       [k] update_load_avg
   0.75%  postgres                       [.] heap_hot_search_buffer
   0.72%  [kernel]                       [k] do_syscall_64
   0.67%  postgres                       [.] 0x00000000003dfb66
   0.65%  postgres                       [.] HeapTupleSatisfiesVisibility
   0.61%  [kernel]                       [k] _raw_spin_unlock_irqrestore
   0.60%  [kernel]                       [k] copy_user_generic_string
   0.57%  [kernel]                       [k] update_curr
   0.57%  [kernel]                       [k] unix_poll
   0.57%  [kernel]                       [k] try_to_wake_up
   0.55%  [kernel]                       [k] __switch_to
   0.54%  postgres                       [.] hash_bytes
   0.53%  [kernel]                       [k] sock_poll



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
  pgbench --client=4  --time=100 --protocol=prepared --vacuum-all --report-latencies pgench
starting vacuum...end.
starting vacuum pgbench_accounts...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: prepared
number of clients: 4
number of threads: 1
duration: 100 s
number of transactions actually processed: 602233
latency average = 0.664 ms
tps = 6022.062237 (including connections establishing)
tps = 6022.268937 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  \set aid random(1, 100000 * :scale)
         0.000  \set bid random(1, 1 * :scale)
         0.000  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.045  BEGIN;
         0.094  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.065  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.112  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.214  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.059  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         0.070  END;
...
Рейтинг: 0 / 0
09.12.2020, 22:49
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026160
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
wot-64,

Ну вот 6000tps pgbench это похоже на правду на вашем оборудовании.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
09.12.2020, 22:51
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026175
Фотография mefman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mefman
Участник
wot-64, это на 4 или на 20 потоках? на 4х его смотреть излишне?
...
Рейтинг: 0 / 0
09.12.2020, 23:34
| Ответить | Цитировать | Написать  
ускорение операций update
    #40026193
wot-64
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wot-64
Гость
mefman,

на 4-х потоках.

Уже добавили формирование и отправку в PQexec пачек из 2000 update-ов , 247k строк обновляются за 1мин 30 сек.
В логах правда иногда проскакивает "WARNING: there is already a transaction in progress", хотя в начале пачки есть "begin transaction;" и в конце "commit;". Хотя наверное они лишние, вот выдержка из описания PQexec - "Несколько запросов, отправленных с помощью одного вызова PQexec, обрабатываются в рамках одной транзакции, если только команды BEGIN/COMMIT не включены явно в строку запроса, чтобы разделить его на несколько транзакций."
Но этой ошибкой займемся завтра.

Спасибо за уделенное время и помощь.
...
Рейтинг: 0 / 0
10.12.2020, 00:48
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / ускорение операций update
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=19&tid=1994327]:
 0ms
get settings:
 9ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 301ms
total:  430ms
созд. / загр.: 430ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]