Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes

Поиск
Список
Период
Сортировка
От Saul, Jean Paolo
Тема Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes
Дата
Msg-id CA+73ANdXO+wDj+SmyXpDbCNOeZziSD0J889CaJZT6-FTeOOrRQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes  (Peter Geoghegan <pg@bowt.ie>)
Ответы Re: BUG #15609: synchronous_commit=off insert performance regressionwith secondary indexes  ("Saul, Jean Paolo" <paolo.saul@verizonconnect.com>)
Список pgsql-bugs
Дерево обсуждения
Hi Peter,

That is unfortunate (both your results and the hash performance info).
We are still testing hash indexing and select/update/delete performance and will keep that warning in mind.

I have replicated my setup by running the script by hand (see below).
Can you see anything in the test setup that seems to be wrong?

Thanks for all your feedback.

Cheers,

Paolo

------------------------------------
Notes:
Aside from the port number, all other settings are using default values, and is only overwritten when we pass in -o on pg_ctl at startup.
The pgbench outputs are grep'ed and awk'ed to only show tps including connections establishing.
Changed timing run for pgbench to only 10 secs. This was enough to show the pattern and I didn't want to run the pgbenches again @ 20 mins per data point.
Note how I am changing the insert sql and watch the pgbench patterns change when sync commit is off/on and when the indexes are applied to bool or text columns.
The data is still consistent with my initial results :(. If anyone can spot where my tests could have gone wrong that would be very much appreciated.



Raw commandline output below.

------------------------------------

demo_server $ cat /etc/*release
CentOS Linux release 7.6.1810 (Core)
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"

CentOS Linux release 7.6.1810 (Core)
CentOS Linux release 7.6.1810 (Core)
demo_server $ free -m
              total        used        free      shared  buff/cache   available
Mem:         257773        1941      248149        1352        7683      253379
Swap:             0           0           0
demo_server $ grep cores /proc/cpuinfo |wc -l
64
demo_server $ head /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 79
model name      : Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz
stepping        : 1
microcode       : 0xb000031
cpu MHz         : 1820.739
cache size      : 46080 KB
physical id     : 0
demo_server $ df -h /var/data/
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvdn1       50G   14G   37G  28% /var/data
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D /var/data/test95_data
server starting
demo_server $ < 2019-02-01 00:48:02.079 UTC >LOG:  redirecting log output to logging collector process
< 2019-02-01 00:48:02.079 UTC >HINT:  Future log output will appear in directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data
waiting for server to start....2019-02-01 00:50:30.745 UTC [47358] LOG:  listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 00:50:30.745 UTC [47358] LOG:  listening on IPv6 address "::", port 11000
2019-02-01 00:50:30.747 UTC [47358] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 00:50:30.750 UTC [47358] LOG:  listening on Unix socket "/tmp/.s.PGSQL.11000"
2019-02-01 00:50:30.765 UTC [47358] LOG:  redirecting log output to logging collector process
2019-02-01 00:50:30.765 UTC [47358] HINT:  Future log output will appear in directory "log".
 done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $
demo_server $
demo_server $ echo 'INSERT INTO test_indexes (bool_data , int_data , text_data ) VALUES ( (RANDOM()*10)::INT % 2 = 0, RANDOM()*10000, MD5((RANDOM()*1000)::TEXT) );' > /home/postgres/insert_test.sql
demo_server $
demo_server $
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       43266.931198
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       45366.444002
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D /var/data/test95_data -o "-c synchronous_commit=0"
server starting
demo_server $ < 2019-02-01 02:48:01.396 UTC >LOG:  redirecting log output to logging collector process
< 2019-02-01 02:48:01.396 UTC >HINT:  Future log output will appear in directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data -o "-c synchronous_commit=0"
waiting for server to start....2019-02-01 02:48:33.186 UTC [64491] LOG:  listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 02:48:33.186 UTC [64491] LOG:  listening on IPv6 address "::", port 11000
2019-02-01 02:48:33.187 UTC [64491] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 02:48:33.190 UTC [64491] LOG:  listening on Unix socket "/tmp/.s.PGSQL.11000"
2019-02-01 02:48:33.204 UTC [64491] LOG:  redirecting log output to logging collector process
2019-02-01 02:48:33.204 UTC [64491] HINT:  Future log output will appear in directory "log".
 done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       104986.476772
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       117705.555724
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       66106.908670
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       50950.234033
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       102645.086816
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       112472.667768
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       103298.461287
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       113698.041576
demo_server $
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $
demo_server $
demo_server $ echo 'INSERT INTO test_indexes (bool_data , int_data , text_data ) VALUES ( (RANDOM()*10)::INT % 2 = 0, RANDOM()*10000, MD5(((RANDOM()*1000)::BIGINT % 2)::TEXT) );' > /home/postgres/insert_test.sql
demo_server $
demo_server $
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D /var/data/test95_data
server starting
demo_server $ < 2019-02-01 03:26:10.233 UTC >LOG:  redirecting log output to logging collector process
< 2019-02-01 03:26:10.233 UTC >HINT:  Future log output will appear in directory "pg_log".

demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data
waiting for server to start....2019-02-01 03:26:31.248 UTC [71705] LOG:  listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 03:26:31.248 UTC [71705] LOG:  listening on IPv6 address "::", port 11000
2019-02-01 03:26:31.249 UTC [71705] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 03:26:31.252 UTC [71705] LOG:  listening on Unix socket "/tmp/.s.PGSQL.11000"
2019-02-01 03:26:31.267 UTC [71705] LOG:  redirecting log output to logging collector process
2019-02-01 03:26:31.267 UTC [71705] HINT:  Future log output will appear in directory "log".
 done
server started
demo_server $
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
/usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       47055.402951
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       47844.925367
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       44519.824705
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       43483.982157
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       43995.744128
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       44881.281223
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       43505.532207
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       43480.194225
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test95_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl -D /var/data/test11_data stop
waiting for server to shut down.... done
server stopped
demo_server $ sudo -u postgres /usr/pgsql-9.5/bin/pg_ctl start -D /var/data/test95_data -o "-c synchronous_commit=0"
sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data -o "-c synchronous_commit=0"
server starting
demo_server $ sudo -u postgres /usr/pgsql-11/bin/pg_ctl start -D /var/data/test11_data -o "-c synchronous_commit=0"
waiting for server to start....2019-02-01 03:38:13.884 UTC [75407] LOG:  listening on IPv4 address "0.0.0.0", port 11000
2019-02-01 03:38:13.884 UTC [75407] LOG:  listening on IPv6 address "::", port 11000
2019-02-01 03:38:13.885 UTC [75407] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.11000"
2019-02-01 03:38:13.888 UTC [75407] LOG:  listening on Unix socket "/tmp/.s.PGSQL.11000"
2019-02-01 03:38:13.902 UTC [75407] LOG:  redirecting log output to logging collector process
2019-02-01 03:38:13.902 UTC [75407] HINT:  Future log output will appear in directory "log".
 done
server started
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       105304.025276
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       117373.057477
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (bool_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       67686.991879
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       52750.704496
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (int_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       103595.827322
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       112841.729898
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"DROP TABLE IF EXISTS test_indexes; CREATE TABLE test_indexes (test_indexes_id BIGSERIAL, bool_data BOOLEAN, int_data INTEGER, text_data TEXT, PRIMARY KEY (test_indexes_id));"
CREATE TABLE
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p9500 -c"create index on test_indexes using btree (text_data);"
/usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/psql -Upostgres -p11000 -c"create index on test_indexes using btree (text_data);"
CREATE INDEX
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 9500 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       62094.775577
demo_server $ /usr/pgsql-11/bin/pgbench -Upostgres -f /home/postgres/insert_test.sql -n -c 60 -j 60 -T10 -p 11000 postgres |grep tps|head -n1|awk '{print "      ",$3}'
       46815.366392
demo_server $
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: BUG #15548: Unaccent does not remove combining diacriticalcharacters
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #15616: extension functionality issues