PostgreSQL: CPU utilization creeping to 100%

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL: CPU utilization creeping to 100%

David I Noel
I'm running into a strange issue whereby my postgres processes are
slowly creeping to 100% CPU utilization. I'm running
postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
postgresql-9.2-1002.jdbc4 driver.

I'm not sure what information here is relevant, so I'll give
everything I can as concisely as I can.

The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz,
16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a
HighPoint RocketRAID 2721 controller, ZFS, RAID10.

There are 2 databases, one a queue, and one containing tables storing
crawled data.

The application is a webcrawler. The application pulls URLs from the
queue and marks them active in a single transaction. It then feeds the
URLs to the crawler threads who crawl the URL, populate the tables
with data, and signal the main thread to update the queue database,
marking the item as inactive and rescheduling it with a new
"NextCrawlDate".

The processes that hang are the postgres processes that interact with
the queue database.

'select * from pg_stat_activity' shows that the queries are not
waiting, and are in the idle state.

Essentially, the query in question is responsible for returning 1 URL
from the union of the list of URL's whose crawl session has timed out
and the list of URL's next in line to be crawled according to the
schedule (NextCrawlDate). The query is: "select * from ((select * from
"crawlq" where "Active" = 'true' AND "TimeoutDate" <= now()) UNION
(select * from "crawlq" where "Active" = 'false')) as RS order by
"NextCrawlDate" asc limit 1"

Beyond this I don't know what useful debugging information to include.
I'll take a guess and start with some vmstat output.

Under normal conditions (with the crawler running) vmstat shows the following:

 procs      memory      page                    disks     faults         cpu
 r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy
cs us sy id
 2 0 0    119G  8450M  1143   0   1   0   900   0   0   0  128 6700
8632 32  4 65
 2 0 0    119G  8444M  1937   0   0   0   100   0   4   4  280 112206
7683 36  5 59
 1 0 0    119G  8443M   427   0   0   0  1377   0  90  90  222 115889
9020 35  7 58
 1 0 0    119G  8442M  1798   0   0   0    18   0   1   1  153 104954
7071 30  4 66
 3 0 0    119G  8443M   528   0   0   0   681   0  10  10  293 125170
14523 40 15 46
 1 0 0    119G  8432M 15227   0   0   4  2850   0   3   3  205 83830
6663 55 12 33
 8 0 0    119G  8433M  3306   0   0   0   445   0   2   2  249 113792
7068 34  5 61
10 0 0    118G  8461M  1190   0   0   0  9909   0  72  73  451 120263
10036 56  9 35
14 0 0    118G  8456M  5887   0   0   0  1202   0   2   2  272 130795
9506 44 12 44
 9 0 0    118G  8444M  7750   0   0   0  1070   0   9   9  298 87643
9584 80 13  7
 3 0 0    118G  8442M  1335   0   0   0   648   0   5   5  189 143691
9234 36  6 58
 1 0 0    118G  8442M   689   0   1   1   472   0   2   2  206 153868
8635 32  7 61
 1 0 0    118G  8441M   203   0   0   0  1124   0  75  75  191 142598
8909 31 10 60
 2 0 0    118G  8440M  9508   0   0   0   684   0   8   8  231 132785
10247 47 13 41
 4 0 0    118G  8456M  4046   0   0   0  5469   0  11  11  299 143119
12475 54 22 24
 4 0 0    117G  8490M  1076   0   0   0  9858   0  16  16  291 140701
14849 58 25 17
 1 0 0    116G  8524M   344   0   0   0  8936   0   4   4  234 149103
12137 45 15 40
 2 0 0    114G  8586M   715   0   0   5 17719   0  73  75  322 151002
11430 34 10 56
 5 0 0    112G  8648M  2773   0   0   0 16997   0   6   6  225 118339
8700 30 10 61
 1 0 0    110G  8705M  4429   0   0   0 15763   0   7   7  423 139590
10354 40 11 49
 1 0 0    108G  8760M  1443   0   0   0 14519   0   7   7  405 139806
10214 37  5 58
 1 0 0    104G  8863M   333   0   0   0 26537   0   5   5  284 107770
9947 34  6 60
 1 0 0    104G  8859M  1331   0   0   0  1700   0 114 114  464 103248
12113 40  9 51
 1 0 0    104G  8854M  1708   0   0   0   272   0   6   6  279 99817
9470 40  5 55
 9 0 0    104G  8850M  3653   0   0   0  4809   0  28  28  346 160041
54071 42 32 26
12 3 0    105G  8845M 20576   0   0   0 18344   0   7   7  383 95019
32533 46 53  1
20 0 0    114G  8721M 46913   0   0   0  2941   0  11  11  461 77480
9794 72 28  0
12 1 0    110G  8759M 25109   0   0   0 35881   0  70  70  413 72631
10161 76 24  0
 2 0 0    110G  8716M 12993   0   1   1   265   0   8   8  292 83085
10073 61 30  9
 3 0 0    110G  8716M  2144   0   0   0    45   0   3   3  183 100994
7410 39 20 41


...and when postgres goes bonkers:

 procs      memory      page                    disks     faults         cpu
 r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy
cs us sy id
 2 0 0     98G    10G  1091   0   1   0   849   0   0   0  114 2641
8582 30  4 66
 2 0 0     98G    10G    20   0   0   0     0   0   0   0  197 20500
10454 46  2 53
 2 0 0     98G    10G    59   0   0   0     0   0   0   0  284 23715
11180 46  3 51
 3 0 0     98G    10G    17   0   0   0   652   0  69  70  288 21968
11571 46  4 50
 2 0 0     98G    10G    56   0   0   0     0   0   0   0  242 22120
10251 47  2 50
 2 0 0     98G    10G    16   0   0   0     0   0   0   0  207 20750
9982 48  1 51
 4 0 0     98G    10G    61   0   0   0    97   0   0   0  205 21024
10395 49  2 50
 2 0 0     98G    10G    24   0   0   0     0   0   0   0  215 21167
10378 48  1 50
 4 0 0     98G    10G    57   0   0   0   346   0  46  46  272 22766
11314 47  3 50
 2 0 0     98G    10G    19   0   0   0     0   0   0   0  205 20594
10340 48  1 50
 2 0 0     98G    10G    53   0   0   0     0   0   0   0  250 22282
10526 48  2 50
 2 0 0     98G    10G    27   0   0   0     0   0   0   0  260 27824
12090 51  4 44
 2 0 0     98G    10G   543   0   0   0   429   0   0   0  267 28757
11817 46  4 50
 2 0 0     98G    10G    23   0   0   0   260   0  44  44  278 33147
12624 51  5 43
 2 0 0     98G    10G    55   0   0   0     0   0   0   0  243 26885
11081 47  3 50
 2 0 0     98G    10G    16   0   0   0     0   0   0   0  207 20854
10348 48  1 51
 2 0 0     98G    10G    55   0   0   0     9   0   1   1  222 22714
10766 48  1 51
 2 0 0     98G    10G    23   0   0   0     0   0   0   0  200 20919
10392 47  2 51
 2 0 0     98G    10G    55   0   0   0   517   0  88  87  247 20874
11032 46  3 51
 4 0 0     98G    10G    20   0   0   0     0   0   0   0  253 30263
11705 50  3 47
 2 0 0     98G    10G  1133   0   0   0     4   0   0   0  212 26791
11186 51  2 47
 2 0 0     98G    10G    19   0   0   0     4   0   0   0  202 22043
10752 48  1 51
 2 0 0     98G    10G    59   0   0   0     0   0   0   0  208 21416
10506 48  1 51
 4 0 0     98G    10G    18   0   0   0     4   0   0   0  214 22207
10598 48  2 50
 1 0 0     98G    10G    57   0   0   0     9   0   0   0  203 21102
10245 50  1 49
 2 0 0     98G    10G    25   0   0   0     2   0   0   0  206 21531
10225 47  3 50
 2 0 0     98G    10G    53   0   0   0     0   0   0   0  217 21083
10519 46  1 53
 2 0 0     98G    10G    24   0   0   0    15   0   1   1  199 22009
10620 49  3 48
 2 0 0     98G    10G    55   0   0   0   138   0  44  44  233 21647
10862 48  2 50
 1 0 0     98G    10G    55   0   0   0     6   0   0   0  203 23002
10653 49  2 49

/etc/rc.conf:
postgresql_enable="YES"
postgresql_data="/zdb/pgsql/data"
postgresql_flags="-s -m smart"

/boot/loader.conf:
kern.ipc.semmns="1024"
kern.ipc.semmni="128"
kern.ipc.shmall="1048576"
kern.ipc.shmseg="2048"
kern.ipc.shmmax="2147483647"
kern.ipc.shmmni="2048"
kern.maxusers="1024"
kern.maxswzone="335544320"

postgresql.conf, all standard/default except for:
max_connections = 256

Any thoughts? What other information can I provide?

Regards,

-David
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

Ian Lawrence Barwick
2013/4/3 David Noel <[hidden email]>:
> I'm running into a strange issue whereby my postgres processes are
> slowly creeping to 100% CPU utilization. I'm running
> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
> postgresql-9.2-1002.jdbc4 driver.

(...)
> postgresql.conf, all standard/default except for:
> max_connections = 256

It's very likely the default settings are woefully inadequate for your
server; some basic
tuning (especially the value of shared_buffers and other
memory-related parameters)
should help.

> Any thoughts? What other information can I provide?

Sample EXPLAIN/EXPLAIN ANALYZE output from the query in question, and
if possible relevant table definitions etc. would certainly be useful.

Regards

Ian Barwick
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL: CPU utilization creeping to 100%

David I Noel
In reply to this post by David I Noel
What's strange is that the crawler will run just fine for up to
several hours. At some point though the CPU utilization slowly begins
to creep higher. Eventually everything locks and the program hangs.
'top' shows the processes connected to the queue database at or near
%100, and the program ceases output (I have debugging messages built
in to show current activity "[timestamp] : crawling [URL]"). At some
point--anywhere from 30 minutes to several hours later--CPU
utilization drops to normal and the program resumes operation as if
everything were fine. This goes on for up to several hours, then the
utilization issue repeats. So it's a very odd issue I've run into.

On 4/2/13, David Noel <[hidden email]> wrote:

> I'm running into a strange issue whereby my postgres processes are
> slowly creeping to 100% CPU utilization. I'm running
> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
> postgresql-9.2-1002.jdbc4 driver.
>
> I'm not sure what information here is relevant, so I'll give
> everything I can as concisely as I can.
>
> The hardware is a Dell PowerEdge 1420, dual Xeon Nocona's, 3.2ghz,
> 16gb ram. The disks are 4 Kingston HyperX SATA3's attached to a
> HighPoint RocketRAID 2721 controller, ZFS, RAID10.
>
> There are 2 databases, one a queue, and one containing tables storing
> crawled data.
>
> The application is a webcrawler. The application pulls URLs from the
> queue and marks them active in a single transaction. It then feeds the
> URLs to the crawler threads who crawl the URL, populate the tables
> with data, and signal the main thread to update the queue database,
> marking the item as inactive and rescheduling it with a new
> "NextCrawlDate".
>
> The processes that hang are the postgres processes that interact with
> the queue database.
>
> 'select * from pg_stat_activity' shows that the queries are not
> waiting, and are in the idle state.
>
> Essentially, the query in question is responsible for returning 1 URL
> from the union of the list of URL's whose crawl session has timed out
> and the list of URL's next in line to be crawled according to the
> schedule (NextCrawlDate). The query is: "select * from ((select * from
> "crawlq" where "Active" = 'true' AND "TimeoutDate" <= now()) UNION
> (select * from "crawlq" where "Active" = 'false')) as RS order by
> "NextCrawlDate" asc limit 1"
>
> Beyond this I don't know what useful debugging information to include.
> I'll take a guess and start with some vmstat output.
>
> Under normal conditions (with the crawler running) vmstat shows the
> following:
>
>  procs      memory      page                    disks     faults
> cpu
>  r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy
> cs us sy id
>  2 0 0    119G  8450M  1143   0   1   0   900   0   0   0  128 6700
> 8632 32  4 65
>  2 0 0    119G  8444M  1937   0   0   0   100   0   4   4  280 112206
> 7683 36  5 59
>  1 0 0    119G  8443M   427   0   0   0  1377   0  90  90  222 115889
> 9020 35  7 58
>  1 0 0    119G  8442M  1798   0   0   0    18   0   1   1  153 104954
> 7071 30  4 66
>  3 0 0    119G  8443M   528   0   0   0   681   0  10  10  293 125170
> 14523 40 15 46
>  1 0 0    119G  8432M 15227   0   0   4  2850   0   3   3  205 83830
> 6663 55 12 33
>  8 0 0    119G  8433M  3306   0   0   0   445   0   2   2  249 113792
> 7068 34  5 61
> 10 0 0    118G  8461M  1190   0   0   0  9909   0  72  73  451 120263
> 10036 56  9 35
> 14 0 0    118G  8456M  5887   0   0   0  1202   0   2   2  272 130795
> 9506 44 12 44
>  9 0 0    118G  8444M  7750   0   0   0  1070   0   9   9  298 87643
> 9584 80 13  7
>  3 0 0    118G  8442M  1335   0   0   0   648   0   5   5  189 143691
> 9234 36  6 58
>  1 0 0    118G  8442M   689   0   1   1   472   0   2   2  206 153868
> 8635 32  7 61
>  1 0 0    118G  8441M   203   0   0   0  1124   0  75  75  191 142598
> 8909 31 10 60
>  2 0 0    118G  8440M  9508   0   0   0   684   0   8   8  231 132785
> 10247 47 13 41
>  4 0 0    118G  8456M  4046   0   0   0  5469   0  11  11  299 143119
> 12475 54 22 24
>  4 0 0    117G  8490M  1076   0   0   0  9858   0  16  16  291 140701
> 14849 58 25 17
>  1 0 0    116G  8524M   344   0   0   0  8936   0   4   4  234 149103
> 12137 45 15 40
>  2 0 0    114G  8586M   715   0   0   5 17719   0  73  75  322 151002
> 11430 34 10 56
>  5 0 0    112G  8648M  2773   0   0   0 16997   0   6   6  225 118339
> 8700 30 10 61
>  1 0 0    110G  8705M  4429   0   0   0 15763   0   7   7  423 139590
> 10354 40 11 49
>  1 0 0    108G  8760M  1443   0   0   0 14519   0   7   7  405 139806
> 10214 37  5 58
>  1 0 0    104G  8863M   333   0   0   0 26537   0   5   5  284 107770
> 9947 34  6 60
>  1 0 0    104G  8859M  1331   0   0   0  1700   0 114 114  464 103248
> 12113 40  9 51
>  1 0 0    104G  8854M  1708   0   0   0   272   0   6   6  279 99817
> 9470 40  5 55
>  9 0 0    104G  8850M  3653   0   0   0  4809   0  28  28  346 160041
> 54071 42 32 26
> 12 3 0    105G  8845M 20576   0   0   0 18344   0   7   7  383 95019
> 32533 46 53  1
> 20 0 0    114G  8721M 46913   0   0   0  2941   0  11  11  461 77480
> 9794 72 28  0
> 12 1 0    110G  8759M 25109   0   0   0 35881   0  70  70  413 72631
> 10161 76 24  0
>  2 0 0    110G  8716M 12993   0   1   1   265   0   8   8  292 83085
> 10073 61 30  9
>  3 0 0    110G  8716M  2144   0   0   0    45   0   3   3  183 100994
> 7410 39 20 41
>
>
> ...and when postgres goes bonkers:
>
>  procs      memory      page                    disks     faults
> cpu
>  r b w     avm    fre   flt  re  pi  po    fr  sr da0 da1   in   sy
> cs us sy id
>  2 0 0     98G    10G  1091   0   1   0   849   0   0   0  114 2641
> 8582 30  4 66
>  2 0 0     98G    10G    20   0   0   0     0   0   0   0  197 20500
> 10454 46  2 53
>  2 0 0     98G    10G    59   0   0   0     0   0   0   0  284 23715
> 11180 46  3 51
>  3 0 0     98G    10G    17   0   0   0   652   0  69  70  288 21968
> 11571 46  4 50
>  2 0 0     98G    10G    56   0   0   0     0   0   0   0  242 22120
> 10251 47  2 50
>  2 0 0     98G    10G    16   0   0   0     0   0   0   0  207 20750
> 9982 48  1 51
>  4 0 0     98G    10G    61   0   0   0    97   0   0   0  205 21024
> 10395 49  2 50
>  2 0 0     98G    10G    24   0   0   0     0   0   0   0  215 21167
> 10378 48  1 50
>  4 0 0     98G    10G    57   0   0   0   346   0  46  46  272 22766
> 11314 47  3 50
>  2 0 0     98G    10G    19   0   0   0     0   0   0   0  205 20594
> 10340 48  1 50
>  2 0 0     98G    10G    53   0   0   0     0   0   0   0  250 22282
> 10526 48  2 50
>  2 0 0     98G    10G    27   0   0   0     0   0   0   0  260 27824
> 12090 51  4 44
>  2 0 0     98G    10G   543   0   0   0   429   0   0   0  267 28757
> 11817 46  4 50
>  2 0 0     98G    10G    23   0   0   0   260   0  44  44  278 33147
> 12624 51  5 43
>  2 0 0     98G    10G    55   0   0   0     0   0   0   0  243 26885
> 11081 47  3 50
>  2 0 0     98G    10G    16   0   0   0     0   0   0   0  207 20854
> 10348 48  1 51
>  2 0 0     98G    10G    55   0   0   0     9   0   1   1  222 22714
> 10766 48  1 51
>  2 0 0     98G    10G    23   0   0   0     0   0   0   0  200 20919
> 10392 47  2 51
>  2 0 0     98G    10G    55   0   0   0   517   0  88  87  247 20874
> 11032 46  3 51
>  4 0 0     98G    10G    20   0   0   0     0   0   0   0  253 30263
> 11705 50  3 47
>  2 0 0     98G    10G  1133   0   0   0     4   0   0   0  212 26791
> 11186 51  2 47
>  2 0 0     98G    10G    19   0   0   0     4   0   0   0  202 22043
> 10752 48  1 51
>  2 0 0     98G    10G    59   0   0   0     0   0   0   0  208 21416
> 10506 48  1 51
>  4 0 0     98G    10G    18   0   0   0     4   0   0   0  214 22207
> 10598 48  2 50
>  1 0 0     98G    10G    57   0   0   0     9   0   0   0  203 21102
> 10245 50  1 49
>  2 0 0     98G    10G    25   0   0   0     2   0   0   0  206 21531
> 10225 47  3 50
>  2 0 0     98G    10G    53   0   0   0     0   0   0   0  217 21083
> 10519 46  1 53
>  2 0 0     98G    10G    24   0   0   0    15   0   1   1  199 22009
> 10620 49  3 48
>  2 0 0     98G    10G    55   0   0   0   138   0  44  44  233 21647
> 10862 48  2 50
>  1 0 0     98G    10G    55   0   0   0     6   0   0   0  203 23002
> 10653 49  2 49
>
> /etc/rc.conf:
> postgresql_enable="YES"
> postgresql_data="/zdb/pgsql/data"
> postgresql_flags="-s -m smart"
>
> /boot/loader.conf:
> kern.ipc.semmns="1024"
> kern.ipc.semmni="128"
> kern.ipc.shmall="1048576"
> kern.ipc.shmseg="2048"
> kern.ipc.shmmax="2147483647"
> kern.ipc.shmmni="2048"
> kern.maxusers="1024"
> kern.maxswzone="335544320"
>
> postgresql.conf, all standard/default except for:
> max_connections = 256
>
> Any thoughts? What other information can I provide?
>
> Regards,
>
> -David
>
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

Kevin Grittner
In reply to this post by David I Noel
David Noel <[hidden email]> wrote:

> 'select * from pg_stat_activity' shows that the queries are not
> waiting, and are in the idle state.

The process is idle or the process is running the query?  If the
latter, what do you mean when you say "the queries ... are in the
idle state"?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

David I Noel
In reply to this post by Ian Lawrence Barwick
On 4/2/13, Ian Lawrence Barwick <[hidden email]> wrote:

> 2013/4/3 David Noel <[hidden email]>:
>> I'm running into a strange issue whereby my postgres processes are
>> slowly creeping to 100% CPU utilization. I'm running
>> postgresql-server-9.2.3, FreeBSD 8.3-RELEASE-p6, and using the
>> postgresql-9.2-1002.jdbc4 driver.
>
> (...)
>> postgresql.conf, all standard/default except for:
>> max_connections = 256
>
> It's very likely the default settings are woefully inadequate for your
> server; some basic
> tuning (especially the value of shared_buffers and other
> memory-related parameters)
> should help.
>
>> Any thoughts? What other information can I provide?
>
> Sample EXPLAIN/EXPLAIN ANALYZE output from the query in question, and
> if possible relevant table definitions etc. would certainly be useful.
>
> Regards
>
> Ian Barwick

Thanks for the feedback. I'll look into pg tunings. Hopefully the
problem's there somewhere.

explain analyze select * from ((select * from "crawlq" where "Active"
= 'true' AND "TimeoutDate" <= now()) UNION (select * from "crawlq"
where "Active" = 'false')) as RS order by "NextCrawlDate" asc limit 1

"Limit  (cost=4092.39..4092.39 rows=1 width=203) (actual
time=23.447..23.450 rows=1 loops=1)"
"  ->  Sort  (cost=4092.39..4096.34 rows=1583 width=203) (actual
time=23.442..23.442 rows=1 loops=1)"
"        Sort Key: public.crawlq."NextCrawlDate""
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  HashAggregate  (cost=4052.81..4068.64 rows=1583
width=236) (actual time=18.195..20.486 rows=877 loops=1)"
"              ->  Append  (cost=0.00..3997.41 rows=1583 width=236)
(actual time=0.015..13.423 rows=877 loops=1)"
"                    ->  Seq Scan on crawlq  (cost=0.00..1995.14
rows=18 width=236) (actual time=0.011..3.397 rows=49 loops=1)"
"                          Filter: ("Active" AND ("TimeoutDate" <= now()))"
"                          Rows Removed by Filter: 828"
"                    ->  Seq Scan on crawlq  (cost=0.00..1986.43
rows=1565 width=236) (actual time=0.013..7.152 rows=828 loops=1)"
"                          Filter: (NOT "Active")"
"                          Rows Removed by Filter: 49"
"Total runtime: 23.633 ms"

Relevant rows from table crawlq:

CREATE TABLE crawlq
(
  "URL" text NOT NULL,
  "LastCrawlDate" timestamp with time zone DEFAULT now(),
  "NextCrawlDate" timestamp with time zone,
  "Active" boolean DEFAULT false,
  "TimeoutDate" timestamp with time zone,
  CONSTRAINT crawlq_pkey PRIMARY KEY ("URL")
)
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

David I Noel
In reply to this post by Kevin Grittner
On 4/2/13, Kevin Grittner <[hidden email]> wrote:
> David Noel <[hidden email]> wrote:
>
>> 'select * from pg_stat_activity' shows that the queries are not
>> waiting, and are in the idle state.
>
> The process is idle or the process is running the query?  If the
> latter, what do you mean when you say "the queries ... are in the
> idle state"?

select * from pg_stat_activity returns a table containing a column
labeled "state". When the postgres process is at 100% utilization and
the application has hung, this query returns the value "idle" in that
field. When things are running properly, as they are for the moment
now, the value is "active".
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

Kevin Grittner
David Noel <[hidden email]> wrote:

> On 4/2/13, Kevin Grittner <[hidden email]> wrote:
>> David Noel <[hidden email]> wrote:
>>
>>> 'select * from pg_stat_activity' shows that the queries are not
>>> waiting, and are in the idle state.
>>
>> The process is idle or the process is running the query?  If the
>> latter, what do you mean when you say "the queries ... are in the
>> idle state"?
>
> select * from pg_stat_activity returns a table containing a column
> labeled "state". When the postgres process is at 100% utilization and
> the application has hung, this query returns the value "idle" in that
> field. When things are running properly, as they are for the moment
> now, the value is "active".

When a connection shows "idle", the related process should be
showing zero CPU usage.  Are you seeing something different?  If
so, is the transaction or query start time changing from one sample
to the next?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100%

David I Noel
On 4/3/13, Kevin Grittner <[hidden email]> wrote:

> David Noel <[hidden email]> wrote:
>> On 4/2/13, Kevin Grittner <[hidden email]> wrote:
>>> David Noel <[hidden email]> wrote:
>>>
>>>> 'select * from pg_stat_activity' shows that the queries are not
>>>> waiting, and are in the idle state.
>>>
>>> The process is idle or the process is running the query?  If the
>>> latter, what do you mean when you say "the queries ... are in the
>>> idle state"?
>>
>> select * from pg_stat_activity returns a table containing a column
>> labeled "state". When the postgres process is at 100% utilization and
>> the application has hung, this query returns the value "idle" in that
>> field. When things are running properly, as they are for the moment
>> now, the value is "active".
>
> When a connection shows "idle", the related process should be
> showing zero CPU usage.  Are you seeing something different?  If
> so, is the transaction or query start time changing from one sample
> to the next?

The related process was maxed out at or near 100%. I've scanned
chapter 18.4 of the documentation and along with some suggested values
from a reply earlier have brought things to the point where it *seems*
to be stable. I'm continuing to test though, so if the issue pops up
again I'll check to see whether the query start time is changing or
not.

Thanks for the help,

-David
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"