Subject: I'm configuraing a new system (Bigish) and need some advice.
From: Jeremiah Jahn <jeremiah@goodinassociates.com>
To: postgres performance <pgsql-performance@postgresql.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-ZiOwI4AYpsu7FZjWOMAk"
Date: Mon, 15 Aug 2005 11:24:57 -0500
Message-Id: <1124123097.27881.59.camel@bluejay.goodinassociates.com>
Mime-Version: 1.0
Received-SPF: pass (penguin.goodinassociates.com: domain of
	goodinassociates.com designates 206.80.71.242 as permitted
	sender) client-ip=206.80.71.242;
	envelope-from=jeremiah@goodinassociates.com;
	helo=[206.80.71.242];


--=-ZiOwI4AYpsu7FZjWOMAk
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

The system is a dual Xenon with 6Gig of ram and 14 73Gig 15K u320 scsi
drives. Plus 2 raid 1 system dives.

RedHat EL ES4 is the OS.=20


Any1 have any suggestions as to the configuration? The database is about
60 Gig's. Should jump to 120 here quite soon. Mus of the searches
involve people's names. Through a website. My current setup just doesn't
seem to have resulted in the performance kick I wanted. I don't know if
it's LVM or what. The strang thing is that My Memory usage stays very
LOW for some reason. While on my current production server it stays very
high. Also looking for ideas on stipe and extent size. The below is run
off of a RAID 10. I have not moved my WAL file yet, but there were no
incoming transactions at the time the query was run. My stats on the
identity table are set to 1000.


> explain analyze select distinct case_category,identity_id,court.name,liti=
gant_details.case_id,case_year,date_of_birth,assigned_case_role,litigant_de=
tails.court_ori,full_name,litigant_details.actor_id,case_data.type_code,cas=
e_data.subtype_code,litigant_details.impound_litigant_data, to_number(trim(=
leading case_data.type_code from trim(leading case_data.case_year from case=
_data.case_id)),'999999') as seq from identity,court,litigant_details,case_=
data where identity.court_ori =3D litigant_details.court_ori and identity.c=
ase_id =3D litigant_details.case_id and identity.actor_id =3D litigant_deta=
ils.actor_id and court.id =3D identity.court_ori and identity.court_ori =3D=
 case_data.court_ori and case_data.case_id =3D identity.case_id  and identi=
ty.court_ori =3D 'IL081025J' and full_name like 'SMITH%' order by  full_nam=
e;
>                                                                          =
                                                                           =
                                                                           =
       QUERY PLAN=20
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------
>  Unique  (cost=3D34042.46..34042.57 rows=3D3 width=3D173) (actual time=3D=
63696.896..63720.193 rows=3D8086 loops=3D1)
>    ->  Sort  (cost=3D34042.46..34042.47 rows=3D3 width=3D173) (actual tim=
e=3D63696.892..63702.239 rows=3D8086 loops=3D1)
>          Sort Key: identity.full_name, case_data.case_category, identity.=
identity_id, court.name, litigant_details.case_id, case_data.case_year, ide=
ntity.date_of_birth, litigant_details.assigned_case_role, litigant_details.=
court_ori, litigant_details.actor_id, case_data.type_code, case_data.subtyp=
e_code, litigant_details.impound_litigant_data, to_number(ltrim(ltrim((case=
_data.case_id)::text, (case_data.case_year)::text), (case_data.type_code)::=
text), '999999'::text)
>          ->  Nested Loop  (cost=3D0.00..34042.43 rows=3D3 width=3D173) (a=
ctual time=3D135.498..63655.542 rows=3D8086 loops=3D1)
>                ->  Nested Loop  (cost=3D0.00..34037.02 rows=3D1 width=3D1=
59) (actual time=3D95.760..34637.611 rows=3D8086 loops=3D1)
>                      ->  Nested Loop  (cost=3D0.00..34033.72 rows=3D1 wid=
th=3D138) (actual time=3D89.222..34095.763 rows=3D8086 loops=3D1)
>                            Join Filter: (("outer".case_id)::text =3D ("in=
ner".case_id)::text)
>                            ->  Index Scan using name_speed on identity  (=
cost=3D0.00..1708.26 rows=3D8152 width=3D82) (actual time=3D42.589..257.818=
 rows=3D8092 loops=3D1)
>                                  Index Cond: (((full_name)::text >=3D 'SM=
ITH'::character varying) AND ((full_name)::text < 'SMITI'::character varyin=
g))
>                                  Filter: (((court_ori)::text =3D 'IL08102=
5J'::text) AND ((full_name)::text ~~ 'SMITH%'::text))
>                            ->  Index Scan using lit_actor_speed on litiga=
nt_details  (cost=3D0.00..3.95 rows=3D1 width=3D81) (actual time=3D4.157..4=
.170 rows=3D1 loops=3D8092)
>                                  Index Cond: (("outer".actor_id)::text =
=3D (litigant_details.actor_id)::text)
>                                  Filter: ('IL081025J'::text =3D (court_or=
i)::text)
>                      ->  Seq Scan on court  (cost=3D0.00..3.29 rows=3D1 w=
idth=3D33) (actual time=3D0.051..0.058 rows=3D1 loops=3D8086)
>                            Filter: ('IL081025J'::text =3D (id)::text)
>                ->  Index Scan using case_data_pkey on case_data  (cost=3D=
0.00..5.36 rows=3D2 width=3D53) (actual time=3D3.569..3.572 rows=3D1 loops=
=3D8086)
>                      Index Cond: (('IL081025J'::text =3D (case_data.court=
_ori)::text) AND ((case_data.case_id)::text =3D ("outer".case_id)::text))
>  Total runtime: 63727.873 ms
>=20
>=20


> tcpip_socket =3D true
> max_connections =3D 100
> shared_buffers =3D 50000          # min 16, at least max_connections*2, 8=
KB each
> sort_mem =3D 2024000              # min 64, size in KB
> vacuum_mem =3D 819200             # min 1024, size in KB
> checkpoint_segments =3D 20        # in logfile segments, min 1, 16MB each
> effective_cache_size =3D 3600000  # typically 8KB each
> random_page_cost =3D 2            # units are one sequential page fetch c=
ost
> log_min_duration_statement =3D 10000 # Log all statements whose
> lc_messages =3D 'C'               # locale for system error message strin=
gs
> lc_monetary =3D 'C'               # locale for monetary formatting
> lc_numeric =3D 'C'                # locale for number formatting
> lc_time =3D 'C'                   # locale for time formatting


Ingrate, n.: A man who bites the hand that feeds him, and then complains
of indigestion.

--=-ZiOwI4AYpsu7FZjWOMAk
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQBDAMHZ6caZY8jbHDkRAsGSAJ4q1QMU9LIsj3aaOQ/0jZFnGRStagCeLEDl
NYMEcN6rFLROJ+MF0riM0MY=
=FjzE
-----END PGP SIGNATURE-----

--=-ZiOwI4AYpsu7FZjWOMAk--