[PERFORM] slow database
my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: # # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". # # # Connection Parameters # #tcpip_socket = false #ssl = false max_connections = 50 superuser_reserved_connections = 2 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # shared_buffers = 5000 # min max_connections*2 or 16, 8KB each max_fsm_relations = 400 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 8 # min 1000, fsm is free space map, ~6 bytes max_locks_per_transaction = 128 # min 10 wal_buffers = 4 # min 4, typically 8KB each # # Non-shared Memory Sizes # sort_mem = 131072 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # # Write-ahead log (WAL) # checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds # commit_delay = 0# range 0-10, in microseconds commit_siblings = 5 # range 1-1000 # fsync = false wal_sync_method = fdatasync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync wal_debug = 0 # range 0-16 # # Optimizer Parameters # enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false effective_cache_size = 17 # typically 8KB each random_page_cost = 10 # units are one sequential page fetch cost cpu_tuple_cost = 0.3# (same) cpu_index_tuple_cost = 0.6 # (same) cpu_operator_cost = 0.7 # (same) default_statistics_target = 1 # range 1-1000 # # GEQO Optimizer Parameters # geqo = true geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_threshold = 2000 geqo_pool_size = 1024 # default based on tables in statement, # range 128-1024 geqo_effort = 1 geqo_generations = 0 geqo_random_seed = -1 # auto-compute seed # # Message display # server_min_messages = fatal # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic client_min_messages = fatal # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error silent_mode = false log_connections = false log_pid = false log_statement = false log_duration = false log_timestamp = false #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #explain_pretty_print = true # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_statement_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_
Re: [PERFORM] slow database
the normal queries do not present problems, but all the ones that join has are very slow. OBS: I am using way ODBC. He will be that they exist some configuration specifies inside of the same bank or in the ODBC? Quoting Rod Taylor <[EMAIL PROTECTED]>: > On Wed, 2004-02-11 at 09:23, [EMAIL PROTECTED] wrote: > > my data base is very slow. The machine is a processor Xeon 2GB with > > 256 MB of RAM DDR. My archive of configuration is this: > > I'm not surprised. New values below old. > > > > sort_mem = 131072 # min 64, size in KB > > sort_mem = 8192. > > > fsync = false > > Are you aware of the potential for data corruption during a hardware, > power or software failure? > > > enable_seqscan = false > > enable_indexscan = false > > enable_tidscan = false > > enable_sort = false > > enable_nestloop = false > > enable_mergejoin = false > > enable_hashjoin = false > > You want all of these set to true, not false. > > > effective_cache_size = 17 # typically 8KB each > > effective_cache_size = 16384. > > > random_page_cost = 10 # units are one sequential page fetch cost > > random_page_cost = 3 > > > cpu_tuple_cost = 0.3# (same) > > cpu_tuple_cost = 0.01 > > > cpu_index_tuple_cost = 0.6 # (same) > > cpu_index_tuple_cost = 0.001 > > > cpu_operator_cost = 0.7 # (same) > > cpu_operator_cost = 0.0025 > > > default_statistics_target = 1 # range 1-1000 > > default_statistics_target = 10 > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(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
Re: [PERFORM] slow database
I already came back the old conditions and I continue slow in the same way! Quoting Tom Lane <[EMAIL PROTECTED]>: > [EMAIL PROTECTED] writes: > > the normal queries do not present problems, but all the ones > > that join has are very slow. > > No surprise, as you've disabled all but the stupidest join algorithm... > > As others already pointed out, you'd be a lot better off with the > default configuration settings than with this set. > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow database
the version is 7.3.2 in a connective 9. the hen foot is without nails at the moment: =) | /|\ this is a principal table of system: CREATE TABLE public.compra_prod_forn ( nu_seq_prod_forn numeric(12) NOT NULL, cd_fabricante numeric(6), cd_moeda numeric(4) NOT NULL, cd_compra numeric(12) NOT NULL, cd_produto numeric(9), cd_fornecedor numeric(6), cd_incotermes numeric(3) NOT NULL, qtde_compra numeric(12,3), perc_comissao_holding numeric(5,2), vl_cotacao_unit_negociacao numeric(20,3), dt_retorno date, cd_status_cv numeric(3) NOT NULL, cd_usuario numeric(6) NOT NULL, tp_comissao varchar(25), vl_pif numeric(9,2), cd_fornecedor_contato numeric(6), cd_contato numeric(6), cd_un_peso varchar(20), vl_currier numeric(9,2), cd_iqf numeric(3), cd_un_peso_vl_unit varchar(10), dt_def_fornecedor date, vl_cotacao_unit_forn numeric(20,3), vl_cotacao_unit_local numeric(20,3), tp_vl_cotacao_unit numeric(1), cd_moeda_forn numeric(4), cd_moeda_local numeric(4), vl_cotacao_unit numeric(20,3), peso_bruto_emb varchar(20), id_fax numeric(1), id_email numeric(1), fob varchar(40), origem varchar(40), tipo_comissao varchar(40), descr_fabricante_select varchar(200), farmacopeia varchar(100), vl_frete numeric(10,3), descr_abandono_representada varchar(2000), descr_abandono_interno varchar(2000), vl_frete_unit numeric(10,3), CONSTRAINT compra_prod_forn_pkey PRIMARY KEY (cd_compra, nu_seq_prod_forn), CONSTRAINT "$1" FOREIGN KEY (cd_moeda_local) REFERENCES public.moeda (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$10" FOREIGN KEY (cd_usuario) REFERENCES public.usuario_sistema (cd_usuario) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$11" FOREIGN KEY (cd_status_cv) REFERENCES public.status_compra_venda (cd_status_cv) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$12" FOREIGN KEY (cd_moeda) REFERENCES public.moeda (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$2" FOREIGN KEY (cd_moeda_forn) REFERENCES public.moeda (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$3" FOREIGN KEY (cd_un_peso_vl_unit) REFERENCES public.unidades_peso (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$4" FOREIGN KEY (cd_un_peso) REFERENCES public.unidades_peso (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$5" FOREIGN KEY (cd_fornecedor_contato, cd_contato) REFERENCES public.fornecedor_contato (cd_fornecedor, cd_contato) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$6" FOREIGN KEY (cd_fabricante) REFERENCES public.fabricante (cd_fabricante) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$7" FOREIGN KEY (cd_produto, cd_fornecedor) REFERENCES public.fornecedor_produto (cd_produto, cd_fornecedor) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$8" FOREIGN KEY (cd_incotermes) REFERENCES public.incotermes (cd_incotermes) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT "$9" FOREIGN KEY (cd_compra) REFERENCES public.compra (cd_compra) ON UPDATE NO ACTION ON DELETE CASCADE ) WITH OIDS; Quoting "scott.marlowe" <[EMAIL PROTECTED]>: > On Wed, 11 Feb 2004 [EMAIL PROTECTED] wrote: > > > I already came back the old conditions and I continue slow in the same > > way! > > OK, we need some things from you to help troubleshoot this problem. > > Postgresql version > schema of your tables > output of "explain analyze your query here" > a chicken foot (haha, just kidding. :-) > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
