Hi,
Many thanks for all your thoughts and advice. With just 2GB or RAM, no
change to the harddisc (still SATA) but proper tuning of Postgresql
(still 7.4) and aggressive normalization to shrink row width, I have
managed to get suitable performance, with, when fully cached, queries on
a 5 million
Networks, Inc. (www.airtightnetworks.net)
O: +91 20 2588 1555 ext 205
F: +91 20 2588 1445
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron
Sent: Friday, September 02, 2005 2:36 AM
To: Tom Lane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Massive
Matthew,
Well, this is a development box. But the live box wouldn't be much more
than RAID 1 on SCSI 10ks so that should only be a halving of seek time,
not the 1000 times reduction I'm after!
If you're looking for 1000 times reduction, I think you're going to need
*considerably* beefier
Akshay Mathur wrote:
Ron,
Can you give me some pointers to make the tables RAM resident. If one
does so, is the program accessing the data need to change. Does pgsql
take care to write the data to disk?
PostgreSQL tried to intelligently cache information and then will also
use the OS disk
Mark Kirkwood wrote:
Matthew Sackman wrote:
I need to get to the stage where I can run queries such as:
select street, locality_1, locality_2, city from address where (city =
'Nottingham' or locality_2 = 'Nottingham'
or locality_1 = 'Nottingham')
and upper(substring(street from
Hi,
I'm having performance issues with a table consisting of 2,043,133 rows. The
schema is:
\d address
Table public.address
Column| Type | Modifiers
--++---
postcode_top | character
I'm having performance issues with a table consisting of 2,043,133
rows.
The
schema is:
locality_1 has 16650 distinct values and locality_2 has 1156 distinct
values.
Just so you know I have a 2GHz p4 workstation with similar size (2M
rows), several keys, and can find and fetch 2k rows based
Table public.address
Column| Type | Modifiers
--++---
postcode_top | character varying(2) | not null
postcode_middle | character varying(4) | not null
postcode_bottom
On Thu, Sep 01, 2005 at 02:47:06PM -0400, Tom Lane wrote:
Matthew Sackman [EMAIL PROTECTED] writes:
Obviously, to me, this is a problem, I need these queries to be under a
second to complete. Is this unreasonable?
Yes. Pulling twenty thousand rows at random from a table isn't free.
I
On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
Any help most gratefully received (even if it's to say that I should
be
posting to a different mailing list!).
this is correct list. did you run vacuum/analyze, etc?
Please post vacuum analyze times.
2005-09-01 19:47:08
Matthew Sackman [EMAIL PROTECTED] writes:
Obviously, to me, this is a problem, I need these queries to be under a
second to complete. Is this unreasonable?
Yes. Pulling twenty thousand rows at random from a table isn't free.
You were pretty vague about your disk hardware, which makes me think
@postgresql.org
Subject: Re: [PERFORM] Massive performance issues
I'm having performance issues with a table consisting of 2,043,133
rows.
The
schema is:
locality_1 has 16650 distinct values and locality_2 has 1156 distinct
values.
Just so you know I have a 2GHz p4 workstation with similar size
Matthew Sackman schrieb:
Hi,
I'm having performance issues with a table consisting of 2,043,133 rows. The
schema is:
\d address
Table public.address
Column| Type | Modifiers
--++---
On Thu, Sep 01, 2005 at 02:04:54PM -0400, Merlin Moncure wrote:
Table public.address
Column| Type | Modifiers
--++---
postcode_top | character varying(2) | not null
On 1-9-2005 19:42, Matthew Sackman wrote:
Obviously, to me, this is a problem, I need these queries to be under a
second to complete. Is this unreasonable? What can I do to make this go
faster? I've considered normalising the table but I can't work out
whether the slowness is in dereferencing
Ron [EMAIL PROTECTED] writes:
... Your target is to have each row take = 512B.
Ron, are you assuming that the varchar fields are blank-padded or
something? I think it's highly unlikely that he's got more than a
couple hundred bytes per row right now --- at least if the data is
what it sounds
This should be able to run _very_ fast.
At 01:42 PM 9/1/2005, Matthew Sackman wrote:
Hi,
I'm having performance issues with a table consisting of 2,043,133 rows. The
schema is:
\d address
Table public.address
Column| Type | Modifiers
At 04:25 PM 9/1/2005, Tom Lane wrote:
Ron [EMAIL PROTECTED] writes:
... Your target is to have each row take = 512B.
Ron, are you assuming that the varchar fields are blank-padded or
something? I think it's highly unlikely that he's got more than a
couple hundred bytes per row right now
On Thu, Sep 01, 2005 at 10:09:30PM +0200, Steinar H. Gunderson wrote:
address_city_index btree (city)
address_county_index btree (county)
address_locality_1_index btree (locality_1)
address_locality_2_index btree (locality_2)
address_pc_bottom_index btree
On Thu, Sep 01, 2005 at 10:54:45PM +0200, Arjen van der Meijden wrote:
On 1-9-2005 19:42, Matthew Sackman wrote:
Obviously, to me, this is a problem, I need these queries to be under a
second to complete. Is this unreasonable? What can I do to make this go
faster? I've considered normalising
Well I've got 1GB of RAM, but from analysis of its use, a fair amount
isn't being used. About 50% is actually in use by applications and about
half of the rest is cache and the rest isn't being used. Has this to do
with the max_fsm_pages and max_fsm_relations settings? I've pretty much
not
On Thu, Sep 01, 2005 at 10:13:59PM +0100, Matthew Sackman wrote:
Well that's the thing - on the queries where it decides to use the index
it only reads at around 3MB/s and the CPU is maxed out, whereas when it
doesn't use the index, the disk is being read at 60MB/s. So when it
decides to use
On Thu, Sep 01, 2005 at 02:26:47PM -0700, Jeff Frost wrote:
Well I've got 1GB of RAM, but from analysis of its use, a fair amount
isn't being used. About 50% is actually in use by applications and about
half of the rest is cache and the rest isn't being used. Has this to do
with the
At 05:06 PM 9/1/2005, Matthew Sackman wrote:
On Thu, Sep 01, 2005 at 10:09:30PM +0200, Steinar H. Gunderson wrote:
address_city_index btree (city)
address_county_index btree (county)
address_locality_1_index btree (locality_1)
address_locality_2_index btree (locality_2)
On Thu, Sep 01, 2005 at 11:52:45PM +0200, Steinar H. Gunderson wrote:
On Thu, Sep 01, 2005 at 10:13:59PM +0100, Matthew Sackman wrote:
Well that's the thing - on the queries where it decides to use the index
it only reads at around 3MB/s and the CPU is maxed out, whereas when it
doesn't use
On Thu, Sep 01, 2005 at 06:05:43PM -0400, Ron wrote:
Selection from the database is, hence the indexes.
A DB _without_ indexes that fits into RAM during ordinary operation
may actually be faster than a DB _with_ indexes that does
not. Fitting the entire DB into RAM during ordinary
On Thu, Sep 01, 2005 at 06:42:31PM +0100, Matthew Sackman wrote:
address_pc_top_index btree (postcode_top)
address_pc_top_middle_bottom_index btree (postcode_top,
postcode_middle, postcode_bottom)
address_pc_top_middle_index btree (postcode_top,
At 06:22 PM 9/1/2005, Matthew Sackman wrote:
On Thu, Sep 01, 2005 at 06:05:43PM -0400, Ron wrote:
Since I assume you are not going to run anything with the string
unstable in its name in production (?!), why not try a decent
production ready distro like SUSE 9.x and see how pg 8.0.3 runs on
On Thu, Sep 01, 2005 at 06:42:31PM +0100, Matthew Sackman wrote:
flat_extra | character varying(100) | not null
number | character varying(100) | not null
street | character varying(100) | not null
locality_1 | character varying(100) | not
It would be good to see EXPLAIN ANALYZE output for the three queries
below (the real vs. estimated row counts being of interest).
The number of pages in your address table might be interesting to know too.
regards
Mark
Matthew Sackman wrote (with a fair bit of snippage):
explain select
Matthew Sackman wrote:
I need to get to the stage where I can run queries such as:
select street, locality_1, locality_2, city from address
where (city = 'Nottingham' or locality_2 = 'Nottingham'
or locality_1 = 'Nottingham')
and upper(substring(street from 1 for 1)) = 'A'
group
31 matches
Mail list logo
