Re: Looking for ideas on how to speed up warehouse loading - Mailing list pgsql-performance

One other thing: we are running with a block size of 32K.


Nick Shanny
(Brother of above person)

On Apr 22, 2004, at 7:30 PM, Sean Shanny wrote:

> I should have included this as well:
>
> show all;
>              name              |    setting
> --------------------------------+----------------
> add_missing_from               | on
> australian_timezones           | off
> authentication_timeout         | 60
> check_function_bodies          | on
> checkpoint_segments            | 64
> checkpoint_timeout             | 30
> checkpoint_warning             | 30
> client_encoding                | UNICODE
> client_min_messages            | notice
> commit_delay                   | 0
> commit_siblings                | 5
> cpu_index_tuple_cost           | 0.001
> cpu_operator_cost              | 0.0025
> cpu_tuple_cost                 | 0.01
> DateStyle                      | ISO, MDY
> db_user_namespace              | off
> deadlock_timeout               | 1000
> debug_pretty_print             | off
> debug_print_parse              | off
> debug_print_plan               | off
> debug_print_rewritten          | off
> default_statistics_target      | 1000
> default_transaction_isolation  | read committed
> default_transaction_read_only  | off
> dynamic_library_path           | $libdir
> effective_cache_size           | 400000
> enable_hashagg                 | on
> enable_hashjoin                | on
> enable_indexscan               | on
> enable_mergejoin               | on
> enable_nestloop                | on
> enable_seqscan                 | on
> enable_sort                    | on
> enable_tidscan                 | on
> explain_pretty_print           | on
> extra_float_digits             | 0
> from_collapse_limit            | 8
> fsync                          | on
> geqo                           | on
> geqo_effort                    | 1
> geqo_generations               | 0
> geqo_pool_size                 | 0
> geqo_selection_bias            | 2
> geqo_threshold                 | 11
> join_collapse_limit            | 8
> krb_server_keyfile             | unset
> lc_collate                     | C
> lc_ctype                       | C
> lc_messages                    | C
> lc_monetary                    | C
> lc_numeric                     | C
> lc_time                        | C
> log_connections                | off
> log_duration                   | off
> log_error_verbosity            | default
> log_executor_stats             | off
> log_hostname                   | off
> log_min_duration_statement     | -1
> log_min_error_statement        | panic
> log_min_messages               | notice
> log_parser_stats               | off
> log_pid                        | off
> log_planner_stats              | off
> log_source_port                | off
> log_statement                  | off
> log_statement_stats            | off
> log_timestamp                  | on
> max_connections                | 100
> max_expr_depth                 | 10000
> max_files_per_process          | 1000
> max_fsm_pages                  | 20000
> max_fsm_relations              | 1000
> max_locks_per_transaction      | 64
> password_encryption            | on
> port                           | 5432
> pre_auth_delay                 | 0
> preload_libraries              | unset
> random_page_cost               | 4
> regex_flavor                   | advanced
> rendezvous_name                | unset
> search_path                    | $user,public
> server_encoding                | UNICODE
> server_version                 | 7.4.1
> shared_buffers                 | 4000
> silent_mode                    | off
> sort_mem                       | 64000
> sql_inheritance                | on
> ssl                            | off
> statement_timeout              | 0
> stats_block_level              | on
> stats_command_string           | on
> stats_reset_on_server_start    | off
> stats_row_level                | on
> stats_start_collector          | on
> superuser_reserved_connections | 2
> syslog                         | 0
> syslog_facility                | LOCAL0
> syslog_ident                   | postgres
> tcpip_socket                   | on
> TimeZone                       | unknown
> trace_notify                   | off
> transaction_isolation          | read committed
> transaction_read_only          | off
> transform_null_equals          | off
> unix_socket_directory          | unset
> unix_socket_group              | unset
> unix_socket_permissions        | 511
> vacuum_mem                     | 64000
> virtual_host                   | unset
> wal_buffers                    | 1024
> wal_debug                      | 0
> wal_sync_method                | open_sync
> zero_damaged_pages             | off
>
>
> Sean Shanny wrote:
>
>> To all,
>>
>> Essentials:  Running 7.4.1 on OSX on a loaded G5 with dual procs, 8GB
>> memory, direct attached via fibre channel to a fully optioned 3.5TB
>> XRaid (14 spindles, 2 sets of 7 in RAID 5) box running RAID 50.
>>
>> Background:  We are loading what are essentially xml based access
>> logs from about 20+ webservers daily, about 6GB of raw data.  We have
>> a classic star schema.  All the ETL tools are custom java code or
>> standard *nix tools like sort, uniq etc...
>>
>> The problem:  We have about 46 million rows in a table with the
>> following schema:
>>
>> Table "public.d_referral"
>>       Column       |  Type   | Modifiers
>> --------------------+---------+-----------
>> id                 | integer | not null
>> referral_raw_url   | text    | not null
>> job_control_number | integer | not null
>> Indexes:
>>    "d_referral_pkey" primary key, btree (id)
>>    "idx_referral_url" btree (referral_raw_url)
>>
>> This is one of our dimension tables.  Part of the daily ETL process
>> is to match all the new referral URL's against existing data in the
>> d_referral table.  Some of the values in referral_raw_url can be 5000
>> characters long :-( .  The avg length is :  109.57 characters.
>>
>> I sort and uniq all the incoming referrals and load them into a temp
>> table.
>>
>> Table "public.referral_temp"
>> Column | Type | Modifiers
>> --------+------+-----------
>> url    | text | not null
>> Indexes:
>>    "referral_temp_pkey" primary key, btree (url)
>>
>> I then do a left join
>>
>> SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER JOIN d_referral
>> t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id
>>
>> This is the output from an explain analyze (Please note that I do a
>> set enable_index_scan = false prior to issuing this because it takes
>> forever using indexes.):
>>
>> explain analyze SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER
>> JOIN d_referral t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id;
>>
>> QUERY PLAN
>> ----------------------------------------------------------------------
>> ----------------------------------------------------------------------
>> ----------
>> Sort  (cost=4012064.81..4013194.45 rows=451856 width=115) (actual
>> time=1297320.823..1297739.813 rows=476176 loops=1)
>>   Sort Key: t1.id
>>   ->  Hash Left Join  (cost=1052345.95..3969623.10 rows=451856
>> width=115) (actual time=1146650.487..1290230.590 rows=476176 loops=1)
>>         Hash Cond: ("outer".url = "inner".referral_raw_url)
>>         ->  Seq Scan on referral_temp t2  (cost=0.00..6645.56
>> rows=451856 width=111) (actual time=20.285..1449.634 rows=476176
>> loops=1)
>>         ->  Hash  (cost=729338.16..729338.16 rows=46034716 width=124)
>> (actual time=1146440.710..1146440.710 rows=0 loops=1)
>>               ->  Seq Scan on d_referral t1  (cost=0.00..729338.16
>> rows=46034716 width=124) (actual time=14.502..-1064277.123
>> rows=46034715 loops=1)
>> Total runtime: 1298153.193 ms
>> (8 rows)
>>
>>
>>
>> What I would like to know is if there are better ways to do the join?
>>  I need to get all the rows back from the referral_temp table as they
>> are used for assigning FK's for the fact table later in processing.
>> When I iterate over the values that I get back those with t1.id =
>> null I assign a new FK and push both into the d_referral table as new
>> entries as well as a text file for later use.  The matching records
>> are written to a text file for later use.
>> If we cannot improve the join performance my question becomes are
>> there better tools to match up the 46 million and growing at the rate
>> of 1 million every 3 days, strings outside of postgresql?  We don't
>> want to have to invest in zillions of dollars worth of hardware but
>> if we have to we will.  I just want to make sure we have all the non
>> hardware possibilities for improvement covered before we start
>> investing in large disk arrays.
>> Thanks.
>>
>> --sean
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if
>> your
>>      joining column's datatypes do not match
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>