X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id BCA2AB430D6 for ; Mon, 7 Jul 2003 23:30:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 63324-08 for ; Mon, 7 Jul 2003 20:30:21 -0300 (ADT) Received: from ethereal-realms.org (thebe.ethereal-realms.org [142.179.222.205]) by svr1.postgresql.org (Postfix) with ESMTP id 9ADA0B43183 for ; Mon, 7 Jul 2003 20:30:20 -0300 (ADT) Received: from ethereal-realms.org (ip14.ethereal-realms.COM [192.168.1.14]) by ethereal-realms.org (8.12.6/8.12.6) with ESMTP id h67NTrPu010885; Mon, 7 Jul 2003 17:29:53 -0600 (MDT) Message-ID: <3F0A026E.8090907@ethereal-realms.org> Date: Mon, 07 Jul 2003 17:29:50 -0600 From: Martin Foster Organization: Ethereal Realms User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Scott Marlowe Cc: Shridhar Daithankar , Postgresql Performance Subject: Re: Extreme high load averages References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/140 X-Sequence-Number: 2433 scott.marlowe wrote: > > > I would try a few things. First off, effective_cache_size is the size > measured in 8k blocks, so 512 would be a setting of 4 Megs. Probably a > little low. If you average 512Meg free, that would be a setting of 65536. > > Note that the higer the effective_cache_size, the more the planner will > favor index scans, and the lower, the more it will favor sequential scans. > > Generally speaking, index scans cost in CPU terms, while seq scans cost in > I/O time. > > Since you're reporting low CPU usage, I'm guessing you're getting a lot of > seq scans. > > Do you have any type mismatches anywhere that could be the culprit? > running vacuum and analyze regurlarly? Any tables that are good > candidates for clustering? > > A common problem is a table like this: > > create table test (info text, id int8 primary key); > insert into test values ('ted',1); > .. a few thousand more inserts; > vacuum full; > analyze; > select * from test where id=1; > > will result in a seq scan, always, because the 1 by itself is > autoconverted to int4, which doesn't match int8 automatically. This > query: > > select * from test where id=1::int8 > > will cast the 1 to an int8 so the index can be used. > That last trick actually listed seemed to have solved on the larger slowdowns I had. It would seem that a view was making use of INTERVAL and CURRENT_TIMESTAMP. However, the datatype did not make use of timezones and that caused significant slowdowns. By using ::TIMESTAMP, it essentially dropped the access time from 4.98+ to 0.98 seconds. This alone makes my day, as it shows that Postgres is performing well, but is just a bit more picky about the queries. I changed the settings as you recommended, locked the memory to 768 megs so that PostgreSQL cannot go beyond that and made the database priority higher. All of those changes seems to have increase overall performance. I do have a site question: ENABLE_HASHJOIN (boolean) ENABLE_INDEXSCAN (boolean) ENABLE_MERGEJOIN (boolean) ENABLE_TIDSCAN (boolean) All of the above, state that they are for debugging the query planner. Does this mean that disabling these reduces debugging overhead and streamlines things? The documentation is rather lacking for information on these. Martin Foster Creator/Designer Ethereal Realms martin@ethereal-realms.org