Re: [GENERAL] Tuning queries on large database - Mailing list pgsql-performance
From | Gaetano Mendola |
---|---|
Subject | Re: [GENERAL] Tuning queries on large database |
Date | |
Msg-id | 4112311A.2050808@bigfoot.com Whole thread Raw |
List | pgsql-performance |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Valerie Schneider DSI/DEV wrote: | #--------------------------------------------------------------------------- | # RESOURCE USAGE (except WAL) | #--------------------------------------------------------------------------- | | # - Memory - | | shared_buffers = 30000 # min 16, at least max_connections*2, 8KB each | #sort_mem = 1024 # min 64, size in KB | sort_mem = 5000 # min 64, size in KB | #vacuum_mem = 8192 # min 1024, size in KB | | # - Free Space Map - | | #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each | #max_fsm_relations = 1000 # min 100, ~50 bytes each | | # - Kernel Resource Usage - | | #max_files_per_process = 1000 # min 25 | #preload_libraries = '' | | | #--------------------------------------------------------------------------- | # WRITE AHEAD LOG | #--------------------------------------------------------------------------- | | # - Settings - | | #fsync = true # turns forced synchronization on or off | #wal_sync_method = fsync # the default varies across platforms: | # fsync, fdatasync, open_sync, or open_datasync | #wal_buffers = 8 # min 4, 8KB each | | # - Checkpoints - | | #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each | checkpoint_segments = 30 # in logfile segments, min 1, 16MB each | #checkpoint_timeout = 300 # range 30-3600, in seconds | #checkpoint_warning = 30 # 0 is off, in seconds | #commit_delay = 0 # range 0-100000, in microseconds | #commit_siblings = 5 # range 1-1000 | | | #--------------------------------------------------------------------------- | # QUERY TUNING | #--------------------------------------------------------------------------- | | # - Planner Method Enabling - | | #enable_hashagg = true | #enable_hashjoin = true | #enable_indexscan = true | #enable_mergejoin = true | #enable_nestloop = true | enable_seqscan = false | #enable_sort = true | #enable_tidscan = true | | # - Planner Cost Constants - | | #effective_cache_size = 1000 # typically 8KB each | effective_cache_size = 200000 # typically 8KB each | #random_page_cost = 4 # units are one sequential page fetch cost | random_page_cost = 2 # units are one sequential page fetch cost | #cpu_tuple_cost = 0.01 # (same) | #cpu_index_tuple_cost = 0.001 # (same) | #cpu_operator_cost = 0.0025 # (same) | | # - Genetic Query Optimizer - | | #geqo = true | #geqo_threshold = 11 | #geqo_effort = 1 | #geqo_generations = 0 | #geqo_pool_size = 0 # default based on tables in statement, | # range 128-1024 | #geqo_selection_bias = 2.0 # range 1.5-2.0 Your wal_buffers is too small try do bump up your wal_buffers to ~3000, and see the effects. why did you disable the sequential_scan (see later) ? Try also to lower the cpu_costs: cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 # (same) cpu_operator_cost = 0.0005 # (same) this will push the optimizer to choose the index scans. If not show us the explain with enable_seqscan = false and with enable_seqscan = true *Mount also your partition with the noatime parameter* Regards Gaeatano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBEjEY7UpzwH2SGd4RAtxnAKDuTtYZvWMXL7zjHWU20VFtm2V1OACg/Y1l GZuQ5RviMB2nB4M8G6PW17U= =HxGz -----END PGP SIGNATURE-----
pgsql-performance by date: