10.2: high cpu usage on update statement
on 10.2, we're seeing very high cpu usage when doing an update statement
on a relatively small table (1GB). one of the updated columns is text,
about 1k bytes. there are four threads doing similar updates
concurrently to the same table (but different rows). each thread does an
update about every two seconds, i.e., the tables gets updated every 1/2
second. the stack trace below shows the process stuck in reading the
update results. this seems very odd. has anyone seen something similar?
this is a modest server of 8 cores, all of which are 90% busy.
thanks,
kevin
---- stack trace ---
"pool-2-thread-1" #61 prio=5 os_prio=0 tid=0x00007efd0811f000
nid=0x1fc85a runnable [0x00007efcdcfcc000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
at
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
at
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:293)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1938)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
- locked <0x00000006406504c0> (a
org.postgresql.core.v3.QueryExecutorImpl)
at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
at
org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
at ...
--- end stack trace ---
On 4/5/19 5:45 PM, Kevin Wilkinson wrote:
on 10.2, we're seeing very high cpu usage when doing an update statement
on a relatively small table (1GB). one of the updated columns is text,
about 1k bytes. there are four threads doing similar updates concurrently
to the same table (but different rows). each thread does an update about
every two seconds, i.e., the tables gets updated every 1/2 second. the
stack trace below shows the process *stuck in reading the update results*.
this seems very odd. has anyone seen something similar? this is a modest
server of 8 cores, all of which are 90% busy.
Does the table have supporting indexes?
--
Angular momentum makes the world go 'round.
Kevin Wilkinson wrote:
on 10.2, we're seeing very high cpu usage when doing an update statement
on a relatively small table (1GB). one of the updated columns is text,
about 1k bytes. there are four threads doing similar updates
concurrently to the same table (but different rows). each thread does an
update about every two seconds, i.e., the tables gets updated every 1/2
second. the stack trace below shows the process stuck in reading the
update results. this seems very odd. has anyone seen something similar?
this is a modest server of 8 cores, all of which are 90% busy.
Try to profile the server ("perf" on Linux) to see where the time is spent.
Are there any foreign key constraints pointing to the table being updated?
Then make sure that either no key column is updates or that the foreign
keys are indexed.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
resolved. sorry for not posting the resolution earlier.
it was a good puzzler. turns out the postgresql server used
network-attached disks. and the updated table had no index for the
updated columns. so, the update required a serial scan of the table over
the network. thus, the high cpu usage for updating a single row.
kevin
Show quoted text
On 4/7/2019 11:41 PM, Laurenz Albe wrote:
Kevin Wilkinson wrote:
on 10.2, we're seeing very high cpu usage when doing an update statement
on a relatively small table (1GB). one of the updated columns is text,
about 1k bytes. there are four threads doing similar updates
concurrently to the same table (but different rows). each thread does an
update about every two seconds, i.e., the tables gets updated every 1/2
second. the stack trace below shows the process stuck in reading the
update results. this seems very odd. has anyone seen something similar?
this is a modest server of 8 cores, all of which are 90% busy.Try to profile the server ("perf" on Linux) to see where the time is spent.
Are there any foreign key constraints pointing to the table being updated?
Then make sure that either no key column is updates or that the foreign
keys are indexed.Yours,
Laurenz Albe