From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 22:35:42 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id 986929D6 for ; Tue, 2 Apr 2013 22:35:42 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pa0-f51.google.com (mail-pa0-f51.google.com [209.85.220.51]) by mx1.freebsd.org (Postfix) with ESMTP id 79F49F9A for ; Tue, 2 Apr 2013 22:35:42 +0000 (UTC) Received: by mail-pa0-f51.google.com with SMTP id jh10so539180pab.10 for ; Tue, 02 Apr 2013 15:35:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:date:message-id:subject:from:to :content-type; bh=idFiAYF4FzCxusOXvecb6ILRJ5bSvtgPjD2RtnF3y7s=; b=u4gF7QxhlqmsteEAWMFRr0r2qzcBk+NY9YuDxA4QXsxyRba5jcY3CGK5GnimQEZZVF oUhOw0wsV+D1pdlidygspXSZULtdDq5IXCAvsBHo2/747M3993kWhvqqFtmXLmOQrfIj 89lwAN3J4oZfIsrhi2mrwNtpO4aoUDOOhrnSamN0B7p3gsEoL2aw2nwF75RqUYrPXl4p qb/FUC6pErFPnn1NtXG36Ur1Q1cM8+ZeXmgziTbC5IikmqCzEc3BVulRJJDxdVBf4Az8 6QYH01JyZwmoJt3zmiwu7XeX4bTEFTVrP63O0DFE/Ya0ygYrPiaSlTvC+7bm3MvH30U/ zTUw== MIME-Version: 1.0 X-Received: by 10.68.64.136 with SMTP id o8mr27004796pbs.59.1364942135898; Tue, 02 Apr 2013 15:35:35 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Tue, 2 Apr 2013 15:35:35 -0700 (PDT) Date: Tue, 2 Apr 2013 17:35:35 -0500 Message-ID: Subject: PostgreSQL: CPU utilization creeping to 100% From: David Noel To: PGSQL Mailing List , freebsd-database@freebsd.org Content-Type: text/plain; charset=ISO-8859-1 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 22:35:42 -0000 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 From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 22:44:57 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 289ECD7E for ; Tue, 2 Apr 2013 22:44:57 +0000 (UTC) (envelope-from barwick@gmail.com) Received: from mail-ie0-x236.google.com (mail-ie0-x236.google.com [IPv6:2607:f8b0:4001:c03::236]) by mx1.freebsd.org (Postfix) with ESMTP id 0075DE4 for ; Tue, 2 Apr 2013 22:44:56 +0000 (UTC) Received: by mail-ie0-f182.google.com with SMTP id at1so1036321iec.27 for ; Tue, 02 Apr 2013 15:44:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:in-reply-to:references:date:message-id :subject:from:to:cc:content-type; bh=IpSZxtPEcH0APZzLst2MtkIsUzsBDQ+tDDPRARDTWPA=; b=EhMesYp+Ikf/IyOvG5cyKu/Uoxtvs3Zgg49UFoDZynXbozF5Q2PhkbiWP+Nby/qBIs uoCUgxIkHWG/4MZbbgb6gX9zgRYNNYssJ+1BeFAloAaBcAdnBqbeAhYtlqyJiqMQoM41 nTdA74gNGrSsIl5DTqfkRhFbtd/30PWsX7DkQm7K29VdU5VCSyfsc09LDbH0jPDkLPmF gh1rmyJYa69DWuTOunDbL+UM6loIZ8kPAd1ie+tSyOU0hWEd43uOrbPdMV9Cx7r/Y3/f dYhRKHxqqEOdK2SmqmftjjwG9RYn6mxp/aPzuW7ywy/y7Jyuw4D1G3T7Zyb9eNSuqQUf PGvw== MIME-Version: 1.0 X-Received: by 10.50.236.100 with SMTP id ut4mr5635129igc.86.1364942696599; Tue, 02 Apr 2013 15:44:56 -0700 (PDT) Received: by 10.50.106.166 with HTTP; Tue, 2 Apr 2013 15:44:56 -0700 (PDT) In-Reply-To: References: Date: Wed, 3 Apr 2013 07:44:56 +0900 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: Ian Lawrence Barwick To: David.I.Noel@gmail.com Content-Type: text/plain; charset=UTF-8 Cc: freebsd-database@freebsd.org, PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 22:44:57 -0000 2013/4/3 David 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. (...) > 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 From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 22:46:54 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id 5C6ABE0B for ; Tue, 2 Apr 2013 22:46:54 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pa0-f45.google.com (mail-pa0-f45.google.com [209.85.220.45]) by mx1.freebsd.org (Postfix) with ESMTP id 3D055116 for ; Tue, 2 Apr 2013 22:46:54 +0000 (UTC) Received: by mail-pa0-f45.google.com with SMTP id kl13so542331pab.18 for ; Tue, 02 Apr 2013 15:46:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=/CZ0NQs3oCiCHziFcxiEfCGqVnLFU6YaDFPUq0pTO00=; b=cxBtvWd/P8n6HJ5TpD20v8lNuvtawtPuyGWCxDl0RHJxkHzZERKUO+Rx64GQOvP8Dw l+mVb4mMcSEnRgwdnREze1fKqGEL567IBVnCMppZjEi+fI2vT0fGEdwfAZX6VFlRxMxy Tqe9/s9Bd9laGc0rxD8+dYUee4CE+29GhpubpA296W5Yj7ivtqofboA3TSbKD0eSSaZh 9qA+Pvf2fNO8BDFFpTj6tIfZYtNyXTqsJoXPrRH+gzv7/hAe6mUB77oxGCDNqCKan0Pw CHNHxzk9jU4F8TjbO4UBbpC8A84HzwO2Bys5970NLxCbHL8pZAIbfwbaoj2gKp2JmZwk qoKg== MIME-Version: 1.0 X-Received: by 10.66.6.42 with SMTP id x10mr27911206pax.218.1364942808219; Tue, 02 Apr 2013 15:46:48 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Tue, 2 Apr 2013 15:46:48 -0700 (PDT) In-Reply-To: References: Date: Tue, 2 Apr 2013 17:46:48 -0500 Message-ID: Subject: Re: PostgreSQL: CPU utilization creeping to 100% From: David Noel To: PGSQL Mailing List , freebsd-database@freebsd.org Content-Type: text/plain; charset=ISO-8859-1 X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 22:46:54 -0000 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 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 > From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 22:52:29 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 19196EFA for ; Tue, 2 Apr 2013 22:52:29 +0000 (UTC) (envelope-from kgrittn@ymail.com) Received: from nm3-vm0.bullet.mail.bf1.yahoo.com (nm3-vm0.bullet.mail.bf1.yahoo.com [98.139.212.154]) by mx1.freebsd.org (Postfix) with SMTP id 93035179 for ; Tue, 2 Apr 2013 22:52:28 +0000 (UTC) Received: from [98.139.212.151] by nm3.bullet.mail.bf1.yahoo.com with NNFMP; 02 Apr 2013 22:52:27 -0000 Received: from [98.139.215.249] by tm8.bullet.mail.bf1.yahoo.com with NNFMP; 02 Apr 2013 22:52:27 -0000 Received: from [127.0.0.1] by omp1062.mail.bf1.yahoo.com with NNFMP; 02 Apr 2013 22:52:27 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 681231.22617.bm@omp1062.mail.bf1.yahoo.com Received: (qmail 58518 invoked by uid 60001); 2 Apr 2013 22:52:27 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s1024; t=1364943147; bh=BhDCiVK0q471b6vDd2T4gkCAQEV7FeQPACeatY+8d1E=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=kVGWptOxW0O2F92aVenqy6p21n7dxpobZfkpUI0XIAW3Od4xfeDDf3mV9X/axGPepiXv9UiNFIin8XLzXz1v4pFBnpPO42/Hopu/V9wIF2ciJLfgW4PEjIhS5IHsOTY8QwK3P2anOE/67iBuUz7OSMNqdFAncq5mEfr8jmK3jWo= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=hVKdWPUGbjcl/Yq52HIXmAhFkzv02ByikcPajVwuxmr76ooL6lSmJXFrN0Ldb2QHREmursY5+QwjoXQJHEGiquJ5/nbktTgppar2bv+iNSd7Xe/J01zurthLGZjw/qCOTXaqpOd8FAgFb4iNYK7vQzfpf1TBYflpdpfalDQb0N8=; X-YMail-OSG: WuhIPpsVM1lrexzIqlSOjwGc0KTkZ1iZIX1fbP19yl8lqdA 8tRvTR7UnKwy3yA7YPnCRUQEGe58DLam9lh0qULj4LsbHNvGmL_Wt6km.Jw. N13gDdd4BMPH62rgFqMXq7sEMjWhtpYCAx4nutKc5s3mRy2DLeWMAimbsz6I 4GGZNbe9h2f8vS6X7_l8hUDXFDM3rMnmI1_nlSmvqF5bxy0VN85J9TJ7wtu6 eO23IorwTcZZQJUOVQxYs67ipnwSaLtY0qMeHPQJtWq3p7VcGGLvOGc9GQEL nFkDqKneHPTodNiIEShMW9mtERRc4Lnl5U0WTLW1pIbo83_gC0gTBYKBsxLQ NVyf8Usp92BnZfTx5YtdyXSR70fH3WGQBi9D5PvMun0CGA9dfpL79RdgEtMw HTawz_Hbbqae105NwZWFzcMwbzG3x4D1._t.VB_x7VNXaFCqB3nC_r8w9NG_ YEpOOTSsAH_uRK82Doy9RAl7vYhSrAP0CwvAHoapjWebHBYUgE1DfLnj4D_Q DwMM6XtTWpHO9Yf3Q7jMo8WVY_heMMwTUqnjNk38- Received: from [76.255.18.237] by web162902.mail.bf1.yahoo.com via HTTP; Tue, 02 Apr 2013 15:52:27 PDT X-Rocket-MIMEInfo: 002.001, RGF2aWQgTm9lbCA8ZGF2aWQuaS5ub2VsQGdtYWlsLmNvbT4gd3JvdGU6Cgo.ICdzZWxlY3QgKiBmcm9tIHBnX3N0YXRfYWN0aXZpdHknIHNob3dzIHRoYXQgdGhlIHF1ZXJpZXMgYXJlIG5vdAo.IHdhaXRpbmcsIGFuZCBhcmUgaW4gdGhlIGlkbGUgc3RhdGUuCgpUaGUgcHJvY2VzcyBpcyBpZGxlIG9yIHRoZSBwcm9jZXNzIGlzIHJ1bm5pbmcgdGhlIHF1ZXJ5P8KgIElmIHRoZQpsYXR0ZXIsIHdoYXQgZG8geW91IG1lYW4gd2hlbiB5b3Ugc2F5ICJ0aGUgcXVlcmllcyAuLi4gYXJlIGluIHRoZQppZGxlIHN0YXQBMAEBAQE- X-Mailer: YahooMailWebService/0.8.140.532 References: Message-ID: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> Date: Tue, 2 Apr 2013 15:52:27 -0700 (PDT) From: Kevin Grittner Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% To: "David.I.Noel@gmail.com" , PGSQL Mailing List , "freebsd-database@freebsd.org" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: Kevin Grittner List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 22:52:29 -0000 David Noel wrote:=0A=0A> 'select * from pg_stat_ac= tivity' shows that the queries are not=0A> waiting, and are in the idle sta= te.=0A=0AThe process is idle or the process is running the query?=A0 If the= =0Alatter, what do you mean when you say "the queries ... are in the=0Aidle= state"?=0A=0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterprisedb.c= om=0AThe Enterprise PostgreSQL Company From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 23:03:58 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id A68B325F for ; Tue, 2 Apr 2013 23:03:58 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pa0-f43.google.com (mail-pa0-f43.google.com [209.85.220.43]) by mx1.freebsd.org (Postfix) with ESMTP id 8518021A for ; Tue, 2 Apr 2013 23:03:58 +0000 (UTC) Received: by mail-pa0-f43.google.com with SMTP id hz11so552473pad.30 for ; Tue, 02 Apr 2013 16:03:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=nT3HenehaBJm4mM1id2amaEMONruv9w4uVDhkWmxVY0=; b=FOOxIixa4/OlzFQEe+ir1IRH4sgJXjwpIERASsbWdod6+kUID9r9m95PiPDC8XMOEP kgwQHONUDRqbHnPP5V/NEt8kD4rRWqqKD8kPh21Rn5sp5MWqu2tEyk+IVQCS9oyKm6vT 3O2YIGv9NsFO739ZbiIkiDeAnZK8xKNStDDueDO/GDzbvisj3Gb0qrT+0gCiyl+2DAnc gXHJuVoNZfIjPmKuCEOtRNUuG0lkSKzEQOccu2SiXyfifgDeV/t3UorMmN5hlRt55FWF RakRImg2/B26USAPyIrwcKd84ZygXaZGTCAUAKWguxZCDMjeHbWW6AUv5Mmt/xDN10/+ oMuw== MIME-Version: 1.0 X-Received: by 10.68.40.165 with SMTP id y5mr27237368pbk.218.1364943832626; Tue, 02 Apr 2013 16:03:52 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Tue, 2 Apr 2013 16:03:52 -0700 (PDT) In-Reply-To: References: Date: Tue, 2 Apr 2013 18:03:52 -0500 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: David Noel To: Ian Lawrence Barwick Content-Type: text/plain; charset=ISO-8859-1 Cc: freebsd-database@freebsd.org, PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 23:03:58 -0000 On 4/2/13, Ian Lawrence Barwick wrote: > 2013/4/3 David 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. > > (...) >> 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") ) From owner-freebsd-database@FreeBSD.ORG Tue Apr 2 23:08:36 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id D4317316 for ; Tue, 2 Apr 2013 23:08:36 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pa0-f43.google.com (mail-pa0-f43.google.com [209.85.220.43]) by mx1.freebsd.org (Postfix) with ESMTP id B3E20263 for ; Tue, 2 Apr 2013 23:08:36 +0000 (UTC) Received: by mail-pa0-f43.google.com with SMTP id hz11so552178pad.2 for ; Tue, 02 Apr 2013 16:08:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=wkWmoJgjbG0KUnQyfGj3/b23NC66QCIqALCcfaem6mo=; b=Fn5qOzlyyDc5XfhQLXuOHj1Ld3hklYyq57yCO2dQDXxzjbRTa1+U0i0Mp0Z/hNKtsl NFFF2MBMmr9dWvzYJa3QyKjZnHozGUL0rxxRWMUi+ChvOropa7yTsbTSICttjzJxbT0a 2uCuTf20JOttJKYgdW5MuMAmFR9+8CVFYizYozSb4D5AJQssbijJ/8aPpLbIAwRx9YkK TLJEe3MdaDIE1qpLrZroDXTv5bIjqf6leVixs4zfg+/6Rnkrip8NahT89IIStINeKYch 9ewG3FADw5OwQjtZzVTopD5iSQMD+giyscEVZE/BVEmYah3Y/XfWlHUIMlASwXJxP0Fe cmlg== MIME-Version: 1.0 X-Received: by 10.68.228.164 with SMTP id sj4mr26437560pbc.180.1364944116479; Tue, 02 Apr 2013 16:08:36 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Tue, 2 Apr 2013 16:08:36 -0700 (PDT) In-Reply-To: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> References: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> Date: Tue, 2 Apr 2013 18:08:36 -0500 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: David Noel To: Kevin Grittner Content-Type: text/plain; charset=ISO-8859-1 Cc: "freebsd-database@freebsd.org" , PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 02 Apr 2013 23:08:36 -0000 On 4/2/13, Kevin Grittner wrote: > David Noel 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". From owner-freebsd-database@FreeBSD.ORG Wed Apr 3 15:25:54 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.FreeBSD.org [8.8.178.115]) by hub.freebsd.org (Postfix) with ESMTP id 5D409B41 for ; Wed, 3 Apr 2013 15:25:54 +0000 (UTC) (envelope-from kgrittn@ymail.com) Received: from nm17-vm0.bullet.mail.bf1.yahoo.com (nm17-vm0.bullet.mail.bf1.yahoo.com [98.139.213.157]) by mx1.freebsd.org (Postfix) with SMTP id E7561954 for ; Wed, 3 Apr 2013 15:25:53 +0000 (UTC) Received: from [98.139.212.144] by nm17.bullet.mail.bf1.yahoo.com with NNFMP; 03 Apr 2013 15:25:53 -0000 Received: from [98.139.212.247] by tm1.bullet.mail.bf1.yahoo.com with NNFMP; 03 Apr 2013 15:25:53 -0000 Received: from [127.0.0.1] by omp1056.mail.bf1.yahoo.com with NNFMP; 03 Apr 2013 15:25:53 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 107608.5824.bm@omp1056.mail.bf1.yahoo.com Received: (qmail 46056 invoked by uid 60001); 3 Apr 2013 15:25:53 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s1024; t=1365002753; bh=k2gVhcVf1fZoplmFfgnhKDdCbZGq/gsPcUgcE7UINQc=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=JFFgkoilwz0PWhtGCdVCwsjNotam6oOlaGzwy/b8/xhBM5ElEvTMj83yeKRi6FTTnqolYzxy+NVIhZWSrjEr4rxBZvcy0ptX+phj3C1pmMmNB7kZuR9cPkYVFGQATr5sibOzrUmHxzNxwKv6gg/6d+Z+fdoubeylGj/UXcSGESE= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=bjpkAORbMYU8TqyJQTwwlWpcEZFEq0jWFjUp9tMNvasB2T08iX8Yqg4J1nLUoRqiUGWVY51xVhWwfg6wk4wCzpqtyAR9jS5NNoPQJwPsFNIk3SaXDJCW9mY0B5CvjaPq98tQmO3PE0rGDnTVeV6bU1EwQc0s23Brg4Gd/sE1YIM=; X-YMail-OSG: pZL_YfsVM1kO0D6Oq57nzkxampeWC062rB3xlXU6Jnhfs65 _JEOn4j2AH9ToGt3ZmzVAIBp2CLXSrojaa45TEGP4ht96TgTji_aCximdshS 5CmY24TbWKbQowtnmxy4OlctatudqHTnkfNMRYVVCJtl6lVgqx6kkKTWTAuR ZPsniyxtCzo_az453F3xJPKdAL69X4gjfOkCQezO.d8McLf38evr7gMPv2lM XF8IN7ngUZnd_mivyrqBvAYI6CXUx8PlwjCKYYqJzihcZry1bq0zrWRptdwi Iknuo3sV5cwnP4RZFeW6PiJusKwtqtUvcZlYdOfYhvOBU5wLxq58oBzmONno Et77G4Iu0GzdDzvcn0sTfFT5cyU7sjsO08dQ5wXxKpv4Ostwy9hmU1tN88cu n11NpO7ZThP2p05I8H3O_ApvE__xF3nnPtuPUf7aQKNyGXe7Hj8QgJ3z6E3y C6G4VedqEP5PcXK3WUgWLpRM0sm0UtZMdTZzcEgk6BT82nNKicmdQN1AUf48 9e.q3_JM2XegO7v86oyEvix_bfioB.o88kVAzyhs- Received: from [76.255.18.237] by web162905.mail.bf1.yahoo.com via HTTP; Wed, 03 Apr 2013 08:25:52 PDT X-Rocket-MIMEInfo: 002.001, RGF2aWQgTm9lbCA8ZGF2aWQuaS5ub2VsQGdtYWlsLmNvbT4gd3JvdGU6Cj4gT24gNC8yLzEzLCBLZXZpbiBHcml0dG5lciA8a2dyaXR0bkB5bWFpbC5jb20.IHdyb3RlOgo.PiBEYXZpZCBOb2VsIDxkYXZpZC5pLm5vZWxAZ21haWwuY29tPiB3cm90ZToKPj4KPj4.ICdzZWxlY3QgKiBmcm9tIHBnX3N0YXRfYWN0aXZpdHknIHNob3dzIHRoYXQgdGhlIHF1ZXJpZXMgYXJlIG5vdAo.Pj4gd2FpdGluZywgYW5kIGFyZSBpbiB0aGUgaWRsZSBzdGF0ZS4KPj4KPj4gVGhlIHByb2Nlc3MgaXMgaWRsZSBvciB0aGUgcHIBMAEBAQE- X-Mailer: YahooMailWebService/0.8.140.532 References: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> Message-ID: <1365002752.81518.YahooMailNeo@web162905.mail.bf1.yahoo.com> Date: Wed, 3 Apr 2013 08:25:52 -0700 (PDT) From: Kevin Grittner Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% To: "David.I.Noel@gmail.com" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Cc: "freebsd-database@freebsd.org" , PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: Kevin Grittner List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 03 Apr 2013 15:25:54 -0000 David Noel wrote:=0A> On 4/2/13, Kevin Grittner wrote:=0A>> David Noel wrote:=0A= >>=0A>>> 'select * from pg_stat_activity' shows that the queries are not=0A= >>> waiting, and are in the idle state.=0A>>=0A>> The process is idle or th= e process is running the query?=A0 If the=0A>> latter, what do you mean whe= n you say "the queries ... are in the=0A>> idle state"?=0A>=0A> select * fr= om pg_stat_activity returns a table containing a column=0A> labeled "state"= . When the postgres process is at 100% utilization and=0A> the application = has hung, this query returns the value "idle" in that=0A> field. When thing= s are running properly, as they are for the moment=0A> now, the value is "a= ctive".=0A=0AWhen a connection shows "idle", the related process should be= =0Ashowing zero CPU usage.=A0 Are you seeing something different?=A0 If=0As= o, is the transaction or query start time changing from one sample=0Ato the= next?=0A=0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterprisedb.com= =0AThe Enterprise PostgreSQL Company From owner-freebsd-database@FreeBSD.ORG Thu Apr 4 23:17:37 2013 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id D40C1B06 for ; Thu, 4 Apr 2013 23:17:37 +0000 (UTC) (envelope-from david.i.noel@gmail.com) Received: from mail-pd0-f177.google.com (mail-pd0-f177.google.com [209.85.192.177]) by mx1.freebsd.org (Postfix) with ESMTP id B1D0127B for ; Thu, 4 Apr 2013 23:17:37 +0000 (UTC) Received: by mail-pd0-f177.google.com with SMTP id u11so1679181pdi.22 for ; Thu, 04 Apr 2013 16:17:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:x-received:reply-to:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=pDQMOrAIjcidox6m9kv4awASMxp2+UGxJZobUGf8jfQ=; b=I1Islv6Ebf2kkaWoMp8LhAI6Ay+dGLqwv4jQYogBf+546qhpc0BEhewGf2KJ4xAtd1 ZupecdVHRbxTDxb/a4tJztt0VBW/f5yp8XVc/ZTRyHM5LfbXisxTuhmwW2VIimVfw7L5 6ErkKeiuSq3JIVjVbFBj9qrPRPET6hodj/LuusqVFscHuTIT0NMkA4sEqYA5Ack8UaW6 WOp8AbXhcKc0zFc1pK072KD4kpzDX16M493qlrgUgRbwy8b2ltiQ14tXJtV7rObIOFv0 +3Jwr4iDBBbDHNbxpTUmTAGnhCC4+V6uyav6gEvRJ6FoTc0ka+Bfp8HvsAcAEO4OjWf6 65rQ== MIME-Version: 1.0 X-Received: by 10.68.201.227 with SMTP id kd3mr11275562pbc.65.1365117457186; Thu, 04 Apr 2013 16:17:37 -0700 (PDT) Received: by 10.68.90.100 with HTTP; Thu, 4 Apr 2013 16:17:37 -0700 (PDT) In-Reply-To: <1365002752.81518.YahooMailNeo@web162905.mail.bf1.yahoo.com> References: <1364943147.84745.YahooMailNeo@web162902.mail.bf1.yahoo.com> <1365002752.81518.YahooMailNeo@web162905.mail.bf1.yahoo.com> Date: Thu, 4 Apr 2013 18:17:37 -0500 Message-ID: Subject: Re: [GENERAL] PostgreSQL: CPU utilization creeping to 100% From: David Noel To: Kevin Grittner Content-Type: text/plain; charset=ISO-8859-1 Cc: "freebsd-database@freebsd.org" , PGSQL Mailing List X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list Reply-To: David.I.Noel@gmail.com List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 04 Apr 2013 23:17:37 -0000 On 4/3/13, Kevin Grittner wrote: > David Noel wrote: >> On 4/2/13, Kevin Grittner wrote: >>> David Noel 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