Hello,
We currently use psotgres 9.3 in our products. Recently we upgraded to
postgres 9.6. But with 9.6 we have seen a drastic reduction in throughput.
After analyzing carefully I found that "planner time" in 9.6 is very high.
Below are the details:
Scenario:
1 Create a table with 100000 rows.
2 Execute simple query: select * from subscriber where s_id = 100;
3 No update/delete/insert; tried vacuum, full vacuum; by default we enable
auto-vacuum
9.3: Avg of "Total runtime" : *0.24ms* [actual throughput: *650 TPS*]
9.6: Avg of Total time: *0.56ms* (Avg of "Planning time" : 0.38ms + Avg of
"Execution time" : 0.18ms) [actual throughput: *80 TPS*]
Check the attachments for more details.
Below is the configuration setting. Full configuration can be found in
attachment.
shared_buffers = 128MB
effective_cache_size = 256MB
Note that we use master-slave (one master - one slave) setup. I could see
no difference even when I take out slave.
I tried all possibilities of increasing shared memory, maitenance_work,
asynchronous commit etc. but, nothing showed any major improvements. Kindly
help to identify what is missing!
PS: We use postgres for small scale so the values are less. The size of the
DB is also just around 180MB.
--
Cheers,
Prakash
psql (9.3.14)
Type "help" for help.
perftestdb=# select count(*) from subscriber ;
count
--------
100000
(1 row)
perftestdb=# \d subscriber
Table "public.subscriber"
Column | Type | Modifiers
--------------+-----------------------+-----------
s_id | integer | not null
sub_nbr | character varying(15) | not null
bit_1 | smallint |
bit_2 | smallint |
bit_3 | smallint |
bit_4 | smallint |
bit_5 | smallint |
bit_6 | smallint |
bit_7 | smallint |
bit_8 | smallint |
bit_9 | smallint |
bit_10 | smallint |
hex_1 | smallint |
hex_2 | smallint |
hex_3 | smallint |
hex_4 | smallint |
hex_5 | smallint |
hex_6 | smallint |
hex_7 | smallint |
hex_8 | smallint |
hex_9 | smallint |
hex_10 | smallint |
byte2_1 | smallint |
byte2_2 | smallint |
byte2_3 | smallint |
byte2_4 | smallint |
byte2_5 | smallint |
byte2_6 | smallint |
byte2_7 | smallint |
byte2_8 | smallint |
byte2_9 | smallint |
byte2_10 | smallint |
msc_location | integer |
vlr_location | integer |
Indexes:
"subscriber_pkey" PRIMARY KEY, btree (s_id)
"subscriber_by_sub_nbr" UNIQUE, btree (sub_nbr)
Referenced by:
TABLE "access_info" CONSTRAINT "access_info_s_id_fkey" FOREIGN KEY (s_id)
REFERENCES subscriber(s_id)
TABLE "special_facility" CONSTRAINT "special_facility_s_id_fkey" FOREIGN
KEY (s_id) REFERENCES subscriber(s_id)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.049..0.055 rows=1 loops=1)
Index Cond: (s_id = 100)
Total runtime: 0.231 ms
(3 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.059..0.066 rows=1 loops=1)
Index Cond: (s_id = 100)
Total runtime: 0.246 ms
(3 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.059..0.066 rows=1 loops=1)
Index Cond: (s_id = 100)
Total runtime: 0.249 ms
(3 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.056..0.062 rows=1 loops=1)
Index Cond: (s_id = 100)
Total runtime: 0.245 ms
(3 rows)
Configuration:
max_connections = 100
shared_buffers = 128MB
effective_cache_size = 256MB
checkpoint_segments = 10
checkpoint_timeout = 180s
wal_keep_segments = 10
archive_command = 'rm ./Archive_*; touch ./Archive_"%f"; exit 0'
archive_timeout = 0
#-- Some helpful logging to detect deadlock and IO load
log_lock_waits = on
log_checkpoints = on
archive_mode = on
tcp_keepalives_idle = 15
tcp_keepalives_interval = 5
tcp_keepalives_count = 3
max_wal_senders = 5
wal_level = hot_standby
hot_standby = on
# REST ALL PARAMETERS ARE DEFAULT
psql (9.6.0)
Type "help" for help.
perftestdb=# select count(*) from subscriber;
count
--------
100000
(1 row)
perftestdb=# \d subscriber
Table "public.subscriber"
Column | Type | Modifiers
--------------+-----------------------+-----------
s_id | integer | not null
sub_nbr | character varying(15) | not null
bit_1 | smallint |
bit_2 | smallint |
bit_3 | smallint |
bit_4 | smallint |
bit_5 | smallint |
bit_6 | smallint |
bit_7 | smallint |
bit_8 | smallint |
bit_9 | smallint |
bit_10 | smallint |
hex_1 | smallint |
hex_2 | smallint |
hex_3 | smallint |
hex_4 | smallint |
hex_5 | smallint |
hex_6 | smallint |
hex_7 | smallint |
hex_8 | smallint |
hex_9 | smallint |
hex_10 | smallint |
byte2_1 | smallint |
byte2_2 | smallint |
byte2_3 | smallint |
byte2_4 | smallint |
byte2_5 | smallint |
byte2_6 | smallint |
byte2_7 | smallint |
byte2_8 | smallint |
byte2_9 | smallint |
byte2_10 | smallint |
msc_location | integer |
vlr_location | integer |
Indexes:
"subscriber_pkey" PRIMARY KEY, btree (s_id)
"subscriber_by_sub_nbr" UNIQUE, btree (sub_nbr)
Referenced by:
TABLE "access_info" CONSTRAINT "access_info_s_id_fkey" FOREIGN KEY (s_id)
REFERENCES subscriber(s_id)
TABLE "special_facility" CONSTRAINT "special_facility_s_id_fkey" FOREIGN
KEY (s_id) REFERENCES subscriber(s_id)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.046..0.049 rows=1 loops=1)
Index Cond: (s_id = 100)
Planning time: 0.396 ms
Execution time: 0.181 ms
(4 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.036..0.038 rows=1 loops=1)
Index Cond: (s_id = 100)
Planning time: 0.354 ms
Execution time: 0.160 ms
(4 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.045..0.047 rows=1 loops=1)
Index Cond: (s_id = 100)
Planning time: 0.395 ms
Execution time: 0.180 ms
(4 rows)
perftestdb=# explain analyze select * from subscriber where s_id = 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using subscriber_pkey on subscriber (cost=0.29..8.31 rows=1
width=88) (actual time=0.045..0.048 rows=1 loops=1)
Index Cond: (s_id = 100)
Planning time: 0.392 ms
Execution time: 0.180 ms
(4 rows)
Configuration:
max_connections = 100
shared_buffers = 128MB
effective_cache_size = 256MB
checkpoint_timeout = 180s
wal_keep_segments = 10
archive_command = 'rm ./Archive_*; touch ./Archive_"%f"; exit 0'
archive_timeout = 0
max_wal_size = 40MB
min_wal_size = 40MB
#-- Some helpful logging to detect deadlock and IO load
log_lock_waits = on
log_checkpoints = on
archive_mode = on
tcp_keepalives_idle = 15
tcp_keepalives_interval = 5
tcp_keepalives_count = 3
max_wal_senders = 5
wal_level = hot_standby
hot_standby = on
wal_retrieve_retry_interval = 2s
# REST ALL PARAMETERS ARE DEFAULT
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
