-
Notifications
You must be signed in to change notification settings - Fork 69
Description
Problem description
Падает производительность на INSERT+SELECT и SELECT тестах pgbench после секционирования.
Создаю начальные объекты:
pgbench -i -s 60 postgres
create index idx_pgbench_history_000 on pgbench_history (mtime);
alter table pgbench_history alter mtime set not null;
Запускаю кастомный скрипт:
pgbench -c 48 -T 60 -j 16 -R 60000 -M prepared -r -s 60 -f "/opt/postgres/pgbench/test-insert_select.pgbench" postgres
Содержимое insert_select.pgbench:
\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
\setrandom tsdelta 0 86400
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
SELECT * FROM pgbench_history WHERE mtime = (To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
Для тестов с pathman делаю:
SELECT create_range_partitions('pgbench_history', 'mtime', To_timestamp('20180209000000', 'YYYYMMDDHH24MISS'), INTERVAL '1 day', NULL, true);
Результат без pathman:
duration: 60 s
number of transactions actually processed: 750973
latency average: 22571.670 ms
latency stddev: -nan ms
rate limit schedule lag: avg 22567.843 (max 48233.517) ms
tps = 12510.076447 (excluding connections establishing)
statement latencies in milliseconds:
2.989117 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
0.806437 SELECT * FROM pgbench_history WHERE mtime = (To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
Результат c pathman:
duration: 60 s
number of transactions actually processed: 92103
latency average: 28891.668 ms
latency stddev: -nan ms
rate limit schedule lag: avg 28860.452 (max 58540.388) ms
tps = 1534.289842 (excluding connections establishing)
statement latencies in milliseconds:
18.331416 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
12.748327 SELECT * FROM pgbench_history WHERE mtime = (To_timestamp('20180209000000', 'YYYYMMDDHH24MISS') + (interval '1 MINUTE' * :tsdelta));
Такая же печальная ситуация, если запускать только SELECT (без INSERT).
Но если оставить только INSERT, то результаты с и без pathman одинаковые.
Подскажите, в чём может быть проблема? Очень хотим использовать pg_pathman, но у нас большие нагрузки и хочется быть уверенными, что не возникнет проблем с производительностью.
Environment
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------------------+----------+--------------+----------------+------------+---------------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
pg_stat_statements | 10 | 2200 | t | 1.3 | |
pg_pathman | 10 | 2200 | f | 1.4 | {58035,58046} | {"",""}
(3 rows)
PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
get_pathman_lib_version
10409