From: Martin Foster <martin@ethereal-realms.org>
To: Scott Marlowe <scott.marlowe@ihs.com>
Cc: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Extreme high load averages
Date: Mon, 07 Jul 2003 17:29:50 -0600
Message-ID: <3F0A026E.8090907@ethereal-realms.org> (raw)
In-Reply-To: <Pine.LNX.4.33.0307071330560.4823-100000@css120.ihs.com>
References: <Pine.LNX.4.33.0307071330560.4823-100000@css120.ihs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: martin@ethereal-realms.org, scott.marlowe@ihs.com, shridhar_daithankar@persistent.co.in
Subject: Re: Extreme high load averages
In-Reply-To: <3F0A026E.8090907@ethereal-realms.org>
References: <Pine.LNX.4.33.0307071330560.4823-100000@css120.ihs.com> <3F0A026E.8090907@ethereal-realms.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox