I'm configuraing a new system (Bigish) and need some advice. - Mailing list pgsql-performance

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.


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,litigant_details.case_id,case_year,date_of_birth,assigned_case_role,litigant_details.court_ori,full_name,litigant_details.actor_id,case_data.type_code,case_data.subtype_code,litigant_details.impound_litigant_data,
to_number(trim(leadingcase_data.type_code from trim(leading case_data.case_year from case_data.case_id)),'999999') as
seqfrom identity,court,litigant_details,case_data where identity.court_ori = litigant_details.court_ori and
identity.case_id= litigant_details.case_id and identity.actor_id = litigant_details.actor_id and court.id =
identity.court_oriand identity.court_ori = case_data.court_ori and case_data.case_id = identity.case_id  and
identity.court_ori= 'IL081025J' and full_name like 'SMITH%' order by  full_name; 
>
                                                                                                               QUERY
PLAN 
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=34042.46..34042.57 rows=3 width=173) (actual time=63696.896..63720.193 rows=8086 loops=1)
>    ->  Sort  (cost=34042.46..34042.47 rows=3 width=173) (actual time=63696.892..63702.239 rows=8086 loops=1)
>          Sort Key: identity.full_name, case_data.case_category, identity.identity_id, court.name,
litigant_details.case_id,case_data.case_year, identity.date_of_birth, litigant_details.assigned_case_role,
litigant_details.court_ori,litigant_details.actor_id, case_data.type_code, case_data.subtype_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=0.00..34042.43 rows=3 width=173) (actual time=135.498..63655.542 rows=8086 loops=1)
>                ->  Nested Loop  (cost=0.00..34037.02 rows=1 width=159) (actual time=95.760..34637.611 rows=8086
loops=1)
>                      ->  Nested Loop  (cost=0.00..34033.72 rows=1 width=138) (actual time=89.222..34095.763 rows=8086
loops=1)
>                            Join Filter: (("outer".case_id)::text = ("inner".case_id)::text)
>                            ->  Index Scan using name_speed on identity  (cost=0.00..1708.26 rows=8152 width=82)
(actualtime=42.589..257.818 rows=8092 loops=1) 
>                                  Index Cond: (((full_name)::text >= 'SMITH'::character varying) AND
((full_name)::text< 'SMITI'::character varying)) 
>                                  Filter: (((court_ori)::text = 'IL081025J'::text) AND ((full_name)::text ~~
'SMITH%'::text))
>                            ->  Index Scan using lit_actor_speed on litigant_details  (cost=0.00..3.95 rows=1
width=81)(actual time=4.157..4.170 rows=1 loops=8092) 
>                                  Index Cond: (("outer".actor_id)::text = (litigant_details.actor_id)::text)
>                                  Filter: ('IL081025J'::text = (court_ori)::text)
>                      ->  Seq Scan on court  (cost=0.00..3.29 rows=1 width=33) (actual time=0.051..0.058 rows=1
loops=8086)
>                            Filter: ('IL081025J'::text = (id)::text)
>                ->  Index Scan using case_data_pkey on case_data  (cost=0.00..5.36 rows=2 width=53) (actual
time=3.569..3.572rows=1 loops=8086) 
>                      Index Cond: (('IL081025J'::text = (case_data.court_ori)::text) AND ((case_data.case_id)::text =
("outer".case_id)::text))
>  Total runtime: 63727.873 ms
>
>



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





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