On Fri, Sep 11, 2020 at 3:49 AM Greg Nancarrow <gregn4...@gmail.com> wrote:
>
> I couldn't use the original machine from which I obtained the previous
> results, but ended up using a 4-core CentOS7 VM, which showed a
> similar pattern in the performance results for this test case.
> I obtained the following results from loading a 2GB CSV file (1000000
> rows, 4 indexes):
>
> Copy Type Duration (s) Load factor
> ===============================================
> Normal Copy 190.891 -
>
> Parallel Copy
> (#workers)
> 1 210.947 0.90
>
Hi Greg,
I tried to recreate the test case(attached) and I didn't find much
difference with the custom postgresql.config file.
Test case: 250000 tuples, 4 indexes(composite indexes with 10
columns), 3.7GB, 100 columns(as suggested by you and all the
varchar(255) columns are having 255 characters), exec time in sec.
With custom postgresql.conf[1], removed and recreated the data
directory after every run(I couldn't perform the OS page cache flush
due to some reasons. So, chose this recreation of data dir way, for
testing purpose):
HEAD: 129.547, 128.624, 128.890
Patch: 0 workers - 130.213, 131.298, 130.555
Patch: 1 worker - 127.757, 125.560, 128.275
With default postgresql.conf, removed and recreated the data directory
after every run:
HEAD: 138.276, 150.472, 153.304
Patch: 0 workers - 162.468, 149.423, 159.137
Patch: 1 worker - 136.055, 144.250, 137.916
Few questions:
1. Was the run performed with default postgresql.conf file? If not,
what are the changed configurations?
2. Are the readings for normal copy(190.891sec, mentioned by you
above) taken on HEAD or with patch, 0 workers? How much is the runtime
with your test case on HEAD(Without patch) and 0 workers(With patch)?
3. Was the run performed on release build?
4. Were the readings taken on multiple runs(say 3 or 4 times)?
[1] - Postgres configuration used for above testing:
shared_buffers = 40GB
max_worker_processes = 32
max_parallel_maintenance_workers = 24
max_parallel_workers = 32
synchronous_commit = off
checkpoint_timeout = 1d
max_wal_size = 24GB
min_wal_size = 15GB
autovacuum = off
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com
{\rtf1\ansi\ansicpg1252\cocoartf2513
\cocoatextscaling0\cocoaplatform0{\fonttbl\f0\fswiss\fcharset0 Helvetica;}
{\colortbl;\red255\green255\blue255;}
{\*\expandedcolortbl;;}
\paperw11900\paperh16840\margl1440\margr1440\vieww30560\viewh11000\viewkind0
\pard\tx566\tx1133\tx1700\tx2267\tx2834\tx3401\tx3968\tx4535\tx5102\tx5669\tx6236\tx6803\pardirnatural\partightenfactor0
\f0\fs24 \cf0 DROP TABLE IF EXISTS t100_1;\
CREATE TABLE t100_1 (\
c1_s_1 bigserial,\
c2_vc_1 varchar(255),\
c3_d_1 date,\
c4_n_1 numeric,\
c5_vc_2 varchar(255),\
c6_d_2 date,\
c7_vc_3 varchar(255),\
c8_t_1 time without time zone,\
c9_vc_4 varchar(255),\
c10_vc_5 varchar(255),\
c11_t_2 time without time zone,\
c12_vc_6 varchar(255),\
c13_vc_7 varchar(255),\
c14_n_2 numeric,\
c15_d_3 date,\
c16_t_3 time without time zone,\
c17_n_3 numeric,\
c18_vc_8 varchar(255),\
c19_d_4 date,\
c20_vc_9 varchar(255),\
c21_t_4 time without time zone,\
c22_vc_10 varchar(255),\
c23_d_5 date,\
c24_vc_11 varchar(255),\
c25_t_5 time without time zone,\
c26_t_6 time without time zone,\
c27_vc_12 varchar(255),\
c28_vc_13 varchar(255),\
c29_d_6 date,\
c30_vc_14 varchar(255),\
c31_vc_15 varchar(255),\
c32_t_7 time without time zone,\
c33_t_8 time without time zone,\
c34_vc_16 varchar(255),\
c35_vc_17 varchar(255),\
c36_d_7 date,\
c37_d_8 date,\
c38_vc_18 varchar(255),\
c39_t_9 time without time zone,\
c40_vc_19 varchar(255),\
c41_vc_20 varchar(255),\
c42_vc_21 varchar(255),\
c43_vc_22 varchar(255),\
c44_t_10 time without time zone,\
c45_d_9 date,\
c46_vc_23 varchar(255),\
c47_t_11 time without time zone,\
c48_vc_24 varchar(255),\
c49_vc_25 varchar(255),\
c50_vc_26 varchar(255),\
c51_d_10 date,\
c52_vc_27 varchar(255),\
c53_vc_28 varchar(255),\
c54_vc_29 varchar(255),\
c55_vc_30 varchar(255),\
c56_d_11 date,\
c57_vc_31 varchar(255),\
c58_vc_32 varchar(255),\
c59_vc_33 varchar(255),\
c60_vc_34 varchar(255),\
c61_vc_35 varchar(255),\
c62_vc_36 varchar(255),\
c63_vc_37 varchar(255),\
c64_vc_38 varchar(255),\
c65_vc_39 varchar(255),\
c66_n_4 numeric,\
c67_vc_40 varchar(255),\
c68_vc_41 varchar(255),\
c69_vc_42 varchar(255),\
c70_vc_43 varchar(255),\
c71_n_5 numeric,\
c72_vc_44 varchar(255),\
c73_n_6 numeric,\
c74_vc_45 varchar(255),\
c75_vc_46 varchar(255),\
c76_vc_47 varchar(255),\
c77_n_7 numeric,\
c78_n_8 numeric,\
c79_d_12 date,\
c80_n_9 numeric,\
c81_vc_48 varchar(255),\
c82_vc_49 varchar(255),\
c83_vc_50 varchar(255),\
c84_n_10 numeric,\
c85_vc_51 varchar(255),\
c86_vc_52 varchar(255),\
c87_vc_53 varchar(255),\
c88_d_13 date,\
c89_n_13 numeric,\
c90_vc_54 varchar(255),\
c91_vc_55 varchar(255),\
c92_n_11 numeric,\
c93_n_12 numeric,\
c94_vc_56 varchar(255),\
c95_t_12 time without time zone,\
c96_vc_57 varchar(255),\
c97_vc_58 varchar(255),\
c98_vc_59 varchar(255),\
c99_vc_60 varchar(255),\
c100_t_13 time without time zone\
);\
\
\
INSERT INTO t100_1\
SELECT i,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
current_date, \
clock_timestamp()::time without time zone, \
log(i), \
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone, \
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone, \
clock_timestamp()::time without time zone, \
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date, \
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
clock_timestamp()::time without time zone,\
clock_timestamp()::time without time zone,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone,\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
log(i),\
current_date,\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
current_date,\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
log(i),\
log(i),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone, \
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||md5(i::text)||left(md5(i::text), 31),\
clock_timestamp()::time without time zone \
FROM generate_series(1, 250000, 1) AS i;\
\
\
CREATE INDEX t100_1_idx_1 ON t100_2(c1_s_1, c5_vc_2, c25_t_5, c35_vc_17, c37_d_8, c66_n_4, c70_vc_43, c87_vc_53, c88_d_13, c90_vc_54);\
CREATE INDEX t100_1_idx_2 ON t100_2(c1_s_1, c95_t_12, c91_vc_55, c85_vc_51, c78_n_8, c77_n_7, c70_vc_43, c36_d_7, c16_t_3, c13_vc_7);\
CREATE INDEX t100_1_idx_3 ON t100_2(c1_s_1, c25_t_5, c9_vc_4, c3_d_1, c13_vc_7, c57_vc_31, c58_vc_32, c61_vc_35, c69_vc_42, c72_vc_44);\
CREATE INDEX t100_1_idx_4 ON t100_2(c1_s_1, c45_d_9, c71_n_5, c63_vc_37, c66_n_4, c44_t_10, c20_vc_9, c65_vc_39, c61_vc_35, c75_vc_46);\
\
COPY t100_2 FROM '/mnt/ssd/bharath.rupireddy/pc0.25mn.csv' WITH(FORMAT CSV);\
\
COPY t100_2 FROM '/mnt/ssd/bharath.rupireddy/pc0.25mn.csv' WITH(FORMAT CSV, PARALLEL 1);\
}
