10.2: high cpu usage on update statement

From: Kevin Wilkinson <w(dot)kevin(dot)wilkinson(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 10.2: high cpu usage on update statement
Date: 2019-04-05 22:45:26
Message-ID: 214e8730-17b5-16be-afd6-8b6175dd15ab@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ---

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andres Freund 2019-04-06 01:16:38 Re: query logging of prepared statements
Previous Message Peter J. Holzer 2019-04-05 19:56:49 Re: Recommendation to run vacuum FULL in parallel