Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id AE9CE9FBF11 for ; Tue, 8 May 2007 17:45:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 28737-05 for ; Tue, 8 May 2007 17:44:58 -0300 (ADT) X-Greylist: delayed 00:17:46.168574 by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id F334E9FBF0B for ; Tue, 8 May 2007 17:45:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from orthanc.site5.com (orthanc.site5.com [64.21.68.2]) by svr4.postgresql.org (Postfix) with ESMTP id F2F4F5AF03A for ; Tue, 8 May 2007 20:27:13 +0000 (GMT) Received: from pool-71-124-169-145.bstnma.east.verizon.net ([71.124.169.145] helo=[192.168.1.8]) by orthanc.site5.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.52) id 1HlWGy-0005HC-Oh for pgsql-performance@postgresql.org; Tue, 08 May 2007 16:27:00 -0400 Mime-Version: 1.0 Message-Id: Date: Tue, 8 May 2007 16:27:10 -0400 To: pgsql-performance@postgresql.org From: Daniel Griscom Subject: Throttling PostgreSQL's CPU usage Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-Antivirus-Scanner: This message has been scanned by ClamAV. X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - orthanc.site5.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - suitable.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200705/138 X-Sequence-Number: 24528 I'm building a kiosk with a 3D front end accessing PostGIS/PostgreSQL via Apache/PHP. The 3D display is supposed to show smooth motion from location to location, with PostGIS giving dynamically updated information on the locations. Everything runs on the same machine, and it all works, but when I start a query the 3D display stutters horribly. It looks like PostgreSQL grabs hold of the CPU and doesn't let go until it's completed the query. I don't need the PostgreSQL query to return quickly, but I must retain smooth animation while the query is being processed. In other words, I need PostgreSQL to spread out its CPU usage so that it doesn't monopolize the CPU for any significant time (more than 50ms or so). Possible solutions: 1: Set the PostgreSQL task priority lower than the 3D renderer task, and to make sure that the 3D renderer sleep()s enough to let PostgreSQL get its work done. The obvious objection to this obvious solution is "Priority inversion!", but I see that as an additional challenge to be surmounted rather than an absolute prohibition. So, any thoughts on setting the PostgreSQL task priority (including by the much-maligned tool shown at )? 2: Some variation of the Cost-Based Vacuum Delay. Hypothetically, this would have the PostgreSQL task sleep() periodically while processing the query, allowing the 3D renderer to continue working at a reduced frame rate. My understanding, however, is that this only works during VACUUM and ANALYZE commands, so it won't help during my SELECT commands. So, any thoughts on using Cost-Based Vacuum Delay as a Cost-Based Select Delay? 3: ... some other solution I haven't thought of. Any thoughts, suggestions, ideas? Thanks, Dan -- Daniel T. Griscom griscom@suitable.com Suitable Systems http://www.suitable.com/ 1 Centre Street, Suite 204 (781) 665-0053 Wakefield, MA 01880-2400