Обсуждение: 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 = 80000 # 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-100000, 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 = 170000 # typically 8KB each
random_page_cost = 1000000000 # 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_lock_oidmin = 16384
#trace_lock_table = 0
#
# Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
datestyle = 'iso, us'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
client_encoding = sql_ascii # actually, defaults to database encoding
authentication_timeout = 1 # 1-600, in seconds
deadlock_timeout = 100 # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 1000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
db_user_namespace = false
#
# Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'
================================================================
somebody please knows to give tips to me to increase the
performance
On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote: > somebody please knows to give tips to me to increase the > performance Run VACUUM ANALYZE. Find one query that is slow. Run EXPLAIN ANALYZE on that query. Read the plan and figure out why it is slow. Fix it. -- /Dennis Björklund
On Feb 11, 2004, at 7:23 AM, alemon@tiago.hazor.com.br wrote: > # > # Optimizer Parameters > # > enable_seqscan = false > enable_indexscan = false > enable_tidscan = false > enable_sort = false > enable_nestloop = false > enable_mergejoin = false > enable_hashjoin = false > Why did you disable *every* type of query method? Try commenting all of these out or changing them to "true" instead of "false". -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: drewpc@ibsncentral.com
On Feb 11, 2004, at 7:23 AM, alemon@tiago.hazor.com.br 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: > After looking through the configuration some more, I would definitely recommend getting rid of your current postgresql.conf file and replacing it with the default. You have some very very odd settings, namely: This is dangerous, but maybe you need it: fsync = false You've essentially disabled the optimizer: enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false WOAH, this is huge: random_page_cost = 1000000000 Take a look at this page which goes through each option in the configuration file: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: drewpc@ibsncentral.com
On Wed, 2004-02-11 at 09:23, alemon@tiago.hazor.com.br 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 = 170000 # typically 8KB each effective_cache_size = 16384. > random_page_cost = 1000000000 # 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
alemon@tiago.hazor.com.br 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: > > This is a joke, right? chris
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 <pg@rbt.ca>: > On Wed, 2004-02-11 at 09:23, alemon@tiago.hazor.com.br 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 = 170000 # typically 8KB each > > effective_cache_size = 16384. > > > random_page_cost = 1000000000 # 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 >
alemon@tiago.hazor.com.br writes: > 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: > sort_mem = 131072 # min 64, size in KB > #vacuum_mem = 8192 # min 1024, size in KB Change it back to 8192, or perhaps even less. This large value is probably causing swapping, because it leads to every sort trying to use 1073741824 bytes of memory, which is considerably more than you have. > fsync = false > wal_sync_method = fdatasync # the default varies across platforms: I presume that you are aware that you have chosen the value that leaves your data vulnerable to corruption? I wouldn't set this to false... > enable_seqscan = false > enable_indexscan = false > enable_tidscan = false > enable_sort = false > enable_nestloop = false > enable_mergejoin = false > enable_hashjoin = false Was there some reason why you wanted to disable every query optimization strategy that can be disabled? If you're looking to get slow queries, this would accomplish that nicely. > effective_cache_size = 170000 # typically 8KB each > random_page_cost = 1000000000 # 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) Where did you get those numbers? The random_page_cost alone will probably force every query to do seq scans, ignoring indexes, and is _really_ nonsensical. The other values seem way off. > default_statistics_target = 1 # range 1-1000 ... Apparently it didn't suffice to try to disable query optimization, and modify the cost parameters into nonsense; it was also "needful" to tell the statistics analyzer to virtually eliminate statistics collection. If you want a value other than 10, then pick a value slightly LARGER than 10. > somebody please knows to give tips to me to increase the performance Delete the postgresql.conf file, create a new database using initdb, and take the file produced by _that_, and replace with that one. The default values, while not necessarily perfect, are likely to be 100x better than what you have got. Was this the result of someone trying to tune the database for some sort of anti-benchmark? -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/rdbms.html Rules of the Evil Overlord #179. "I will not outsource core functions." <http://www.eviloverlord.com/>
alemon@tiago.hazor.com.br 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
If my boss came to me and asked me to make my database server run as slowly as possible, I might come up with the exact same postgresql.conf file as what you posted. Just installing the default postgresql.conf that came with postgresql should make this machine run faster. Read this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
I already came back the old conditions and I continue slow in the same way! Quoting Tom Lane <tgl@sss.pgh.pa.us>: > alemon@tiago.hazor.com.br 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 > >
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" <scott.marlowe@ihs.com>: > On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br 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. :-) > >
On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br 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. :-)
On Wed, 2004-02-11 at 12:15, alemon@tiago.hazor.com.br wrote: > I already came back the old conditions and I continue slow in the same > way! Dumb question, but did you restart the database after changing the config file?
First thing I would check is to make sure all those foreign keys are the same type. Second, make sure you've got indexes to go with them. I.e. on a multi-key fk, have a multi-key index. On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote: > 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" <scott.marlowe@ihs.com>: > > > On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br 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. :-) > > > > > > > >
If things are still slow after you have checked your keys as indicated, then pick one query and post the output from EXPLAIN ANALYZE for the list to examine. oh - and ensure you are *not* still using your original postgresql.conf :-) best wishes Mark scott.marlowe wrote: >First thing I would check is to make sure all those foreign keys are the >same type. > >Second, make sure you've got indexes to go with them. I.e. on a multi-key >fk, have a multi-key index. > > > > >