pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to avoid the XLog Write to be the performance bottleneck
2+ messages / 2 participants
[nested] [flat]
* How to avoid the XLog Write to be the performance bottleneck
@ 2018-09-21 08:16 范国腾 <fanguoteng@highgo.com>
0 siblings, 1 reply; 2+ messages in thread
From: 范国腾 @ 2018-09-21 08:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi,
We have two postgres nodes（active/standby). The active node and the standby node use the same share disk（GFS2 file system）.
We are doing the performance test in active side:
(1)Now there is no SQL request sending to the standby side.
(2)The active node has 20 sessions and the test tool sends INSERT/UPDATE/SELECT request to them. The call load is very high.
In active node, we find that the disk IO is very high but the CPU of each postgres process is about 20%. The pstack result shows that most of the postgres process is waiting for the XLOG Write Lock. It seems that the XLog write become the bottleneck of the postgres database.
Could you please give any suggestion how to improve it?
=======================================================
[root@highgo1 ~]# pstack 9434
#0 0x00007fd79545b3a7 in semop () from /usr/lib64/libc.so.6
#1 0x0000000000652d01 in PGSemaphoreLock ()
#2 0x00000000006ab314 in LWLockAcquireOrWait ()
#3 0x00000000004e1f9d in XLogFlush ()
#4 0x00000000004d9167 in CommitTransaction ()
#5 0x00000000004d9bd5 in CommitTransactionCommand ()
#6 0x00000000006b9ff5 in finish_xact_command.part.4 ()
#7 0x00000000006bdd6a in PostgresMain ()
#8 0x0000000000477276 in ServerLoop ()
#9 0x0000000000664186 in PostmasterMain ()
#10 0x0000000000478172 in main ()
The recovery.conf in standby side is :
standby_mode = on
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.100.104 port=5866 user=repuser password=repuser1 application_name=node1'
primary_slot_name = 'slot1'
Thanks
Steven
Attachments:
postgresql.conf (application/octet-stream, 21.9K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to avoid the XLog Write to be the performance bottleneck
@ 2018-09-21 16:26 Andres Freund <andres@anarazel.de>
parent: 范国腾 <fanguoteng@highgo.com>
0 siblings, 0 replies; 2+ messages in thread
From: Andres Freund @ 2018-09-21 16:26 UTC (permalink / raw)
To: 范国腾 <fanguoteng@highgo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 2018-09-21 08:16:42 +0000, 范国腾 wrote:
> Hi,
>
> We have two postgres nodes（active/standby). The active node and the standby node use the same share disk（GFS2 file system）.
>
> We are doing the performance test in active side:
> (1)Now there is no SQL request sending to the standby side.
> (2)The active node has 20 sessions and the test tool sends INSERT/UPDATE/SELECT request to them. The call load is very high.
>
> In active node, we find that the disk IO is very high but the CPU of each postgres process is about 20%. The pstack result shows that most of the postgres process is waiting for the XLOG Write Lock. It seems that the XLog write become the bottleneck of the postgres database.
Usually that doesn't really mean there's lock contention, but that
your IO isn't fast enough. What you can do:
a) check whether some/most/all of your transactions can use
synchronous_commit = off - that can drastically reduce the amount of
IO.
b) Consider putting your WAL onto a separate disk (or even partition),
that can reduce overhead by disentangling synchronous writes (for the
WAL) from asynchronous writes (the data being written back), and
synchronous reads (queries).
c) Get faster storage.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-09-21 16:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-21 08:16 How to avoid the XLog Write to be the performance bottleneck 范国腾 <fanguoteng@highgo.com>
2018-09-21 16:26 ` Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox