pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: 'Sean Shanny' <shannyconsulting@earthlink.net>
To: pgsql-performance@postgresql.org
Subject: Re: General performance questions about postgres on Apple hardware...
Date: Tue, 24 Feb 2004 00:17:28 -0000
Message-ID: <002101c3fa6b$96da7610$ec9387d9@LaptopDellXP> (raw)
In-Reply-To: <40365D36.5090708@earthlink.net>

>Sean Shanny
> Hardware:  Apple G5 dual 2.0 with 8GB memory attached via dual fibre
> channel to a fully loaded 3.5TB XRaid.  The XRaid is configured as two
7
> disk hardware based RAID5 sets software striped to form a RAID50 set.
> The DB, WALS, etc are all on that file set.  Running OSX journaled
file
> system  Running postgres 7.4.1.  OSX Server 10.3.2  Postgres is
compiled
> locally with  '--enable-recode' '--enable-multibyte=UNICODE'
> 'CFLAGS=-mcpu=970 -mtune=970 -mpowerpc64 -O3'

Have you tried altering the blocksize to a higher value? Say 32K?

> max_connections = 100

Why have you set this to 100 when you have typically 1-3 users?

> sort_mem = 256000               # min 64, size in KB

If you have only 1-3 users, then that value seems reasonable.

> The query is
> 
> SELECT t1.id, t2.md5, t2.url FROM referral_temp t2 LEFT OUTER JOIN
> d_referral t1 ON t2.md5 = t1.referral_md5;
> 
> 
> \d d_referral
>  id                 | integer | not null
>  referral_md5       | text    | not null
>  referral_raw_url   | text    | not null
>  referral_host      | text    |
>  referral_path      | text    |
>  referral_query     | text    |
>  job_control_number | integer | not null
> 
> 
> \d referral_temp
>  md5    | text |
>  url    | text |

Have you looked at using reversed indexes, as per recent postings in
[performance]? These seemed to help considerably with lookup speed when
using a large URL database, which seems to be your situation here.

...
>Jeff Boes writes
> We have a large (several million row) table with a field containing
> URLs. Now, funny thing about URLs: they mostly start with a common
> substring ("http://www.";). But not all the rows start with this, so we
> can't just lop off the first N characters. However, we noticed some
time
> ago that an index on this field wasn't as effective as an index on the
> REVERSE of the field. So ...
> 
> CREATE OR REPLACE FUNCTION fn_urlrev(text) returns text as '
> return reverse(lc($_[0]))
> ' language 'plperl' with (iscachable,isstrict);
> 
> and then
> 
> CREATE UNIQUE INDEX ix_links_3 ON links
> (fn_urlrev(path_base));

You have 2 CPUs: have you tried splitting your input data file into two
tables, then executing the same query simultaneously, to split the
processing? If you get the correct plan, you should use roughly the same
I/O but use all of the available CPU power.

I'm sure we'd all be interested in your further results!

Best Regards, Simon Riggs




view thread (12+ messages)  latest in thread

Message-ID: <002101c3fa6b$96da7610$ec9387d9@LaptopDellXP>
Permalink:  ../002101c3fa6b$96da7610$ec9387d9@LaptopDellXP/
Also on:    postgresql.org/message-id/002101c3fa6b$96da7610$ec9387d9@LaptopDellXP

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: simon@2ndquadrant.com, shannyconsulting@earthlink.net
  Subject: Re: General performance questions about postgres on Apple hardware...
  In-Reply-To: <002101c3fa6b$96da7610$ec9387d9@LaptopDellXP>

* 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