Fw: [PERFORM] Query performance issue with 8.0.0beta1
- Original Message - From: Stefano Bonnin [EMAIL PROTECTED] To: Josh Berkus [EMAIL PROTECTED] Sent: Monday, August 30, 2004 4:13 PM Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1 This is my postgres.conf, I have changed only the work_mem and shared_buffers parameters. DID you configure it for the 8.0 database? What does it mean? Is in 8.0 some important NEW configation parameter ? # pgdata = '/usr/local/pgsql/data' # use data in another directory # hba_conf = '/etc/pgsql/pg_hba.conf' # use hba info in another directory # ident_conf = '/etc/pgsql/pg_ident.conf' # use ident info in another directory # external_pidfile= '/var/run/postgresql.pid' # write an extra pid file #listen_addresses = 'localhost' # what IP interface(s) to listen on; # defaults to localhost, '*' = any #port = 5432 max_connections = 100 #superuser_reserved_connections = 2 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #rendezvous_name = '' # defaults to the computer name #authentication_timeout = 60# 1-600, in seconds #ssl = false #password_encryption = true #krb_server_keyfile = '' #db_user_namespace = false shared_buffers = 2048 # min 16, at least max_connections*2, 8KB each work_mem = 2048 # min 64, size in KB #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each #max_files_per_process = 1000 # min 25 #preload_libraries = '' #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits #bgwriter_delay = 200 # 10-5000 milliseconds #bgwriter_percent = 1 # 1-100% of dirty buffers #bgwriter_maxpages = 100# 1-1000 buffers max at once #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 #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-100 #checkpoint_segments = 3# in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #archive_command = '' # command to use to archive a logfile segment #enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true #enable_seqscan = true #enable_sort = true #enable_tidscan = true #effective_cache_size = 1000# typically 8KB each #random_page_cost = 4 # 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) #geqo = true #geqo_threshold = 12 #geqo_effort = 5# range 1-10 #geqo_pool_size = 0 # selects default based on effort #geqo_generations = 0 # selects default based on effort #geqo_selection_bias = 2.0 # range 1.5-2.0 default_statistics_target = 20 # range 1-1000 #from_collapse_limit = 8 #join_collapse_limit = 8# 1 disables collapsing of explicit JOINs #log_destination = 'stderr' # Valid values are combinations of stderr, # syslog and eventlog, depending on # platform. # This is relevant when logging to stderr: #redirect_stderr = false# Enable capturing of stderr into log files. # These are only relevant if redirect_stderr is true: #log_directory = 'pg_log' # Directory where logfiles are written. # May be specified absolute or relative to PGDATA #log_filename_prefix = 'postgresql_' # Prefix for logfile names. #log_rotation_age = 1440# Automatic rotation of logfiles will happen after # so many minutes. 0 to disable. #log_rotation_size = 10240 # Automatic rotation of logfiles will happen after # so many kilobytes of log output. 0 to disable. # These are relevant when logging to syslog: #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # - When to Log - #client_min_messages = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, notice, warning, error #log_min_messages = notice # Values, in order of decreasing detail:
Re: [PERFORM] Changing the column length
From: Michael Ryan S. Puncia [EMAIL PROTECTED] I am sorry that my question is out of line with this group(performance) but I need -general might be more appropriate an urgent help :-( .pls .. I need to know how to change the length of the column. add a new column, use update to copy values from old column, use alter table to rename columns gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Query performance issue with 8.0.0beta1
Server HP: Intel(R) Pentium(R) 4 CPU 2.26GHz RAM 1GB OS: RedHat 8 And the disk: kernel: megaide: driver version 05.04f (Date: Nov 11, 2002; 18:15 EST) kernel: megaide: bios version 02.06.07221605 kernel: megaide: LD 0 RAID1 status=ONLINE sectors=156297343 capacity=76317 MB drives=2 kernel: scsi0 : LSI Logic MegaIDE RAID BIOS Version 2.6.07221605, 8 targs 1 chans 8 luns kernel: Vendor: LSILOGIC Model: LD 0 IDERAID Rev: kernel: Type: Direct-Access ANSI SCSI revision: 02 Thanks. RedS - Original Message - From: Josh Berkus [EMAIL PROTECTED] To: Stefano Bonnin [EMAIL PROTECTED] Sent: Monday, August 30, 2004 6:54 PM Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1 Stefano, This is my postgres.conf, I have changed only the work_mem and shared_buffers parameters. And not very much, I see. DID you configure it for the 8.0 database? What does it mean? Is in 8.0 some important NEW configation parameter ? Well, there are but they shouldn't affect your case. However, there are a lot of other settings that need to be adjusted. Please post your hardware plaform information: OS, CPU, RAM, and disk setup. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] slower every day
On Wednesday 01 Sep 2004 3:36 pm, G u i d o B a r o s i o wrote: Dear all, I am currently experiencing troubles with the performance of my critical's database. The problem is the time that the postgres takes to perform/return a query. For example, trying the \d tablename command takes between 4 or 5 seconds. This table is very big, but I am not asking for the rows, only asking the table schema, so...why is this so slow?!?!? My last administrative action into this table was a reindex to all the indexes via the BKI in standalone mode. I thought I suceed, but this was las saturday. Today I am in the same situation again. Is this database vacuumed and analyzed recently? I would suggest database-wide vacuum full analyze. If your queries are getting slower, then checking the explain analyze output is a good starting point. To see queries issued by psql, start it as psql -E. HTH Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [ADMIN] slower every day
The solution appeared as something I didn't know On the .conf file Previous situation: #log_something=false log_something=true Worst situation #log_something=false #log_something=true Nice situation log_something=false #log_something=true Ok, the problem was that I assumed that commenting a value on the conf file will set it up to a default (false?). I was wrong. My server was writting tons of log's. Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered? Regards, Guido Am Mittwoch, 1. September 2004 12:06 schrieb G u i d o B a r o s i o: The problem is the time that the postgres takes to perform/return a query. For example, trying the \d tablename command takes between 4 or 5 seconds. This table is very big, but I am not asking for the rows, only asking the table schema, so...why is this so slow?!?!? My last administrative action into this table was a reindex to all the indexes via the BKI in standalone mode. I thought I suceed, but this was las saturday. Do you regularly vacuum and analyze the database? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [ADMIN] slower every day
G u i d o B a r o s i o wrote: Conclusion: If you comment a line on the conf file, and reload it, will remain in the last state. (either wast true or false, while I expected a default) Yes, that's correct. No, you're not the only one to have been caught out by this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [ADMIN] slower every day
This issue was resently discussed on hackers. It is a known issue, not very convinient for the user. Nevertheless it is not fixed in 8.0, but will perhaps be addressed in the next major release. (Remembering, it was a non-trivial thing to change.) Best Regards, Michael Paesold G u i d o B a r o s i o wrote: The solution appeared as something I didn't know On the .conf file Previous situation: #log_something=false log_something=true Worst situation #log_something=false #log_something=true Nice situation log_something=false #log_something=true Ok, the problem was that I assumed that commenting a value on the conf file will set it up to a default (false?). I was wrong. My server was writting tons of log's. Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] [ADMIN] slower every day
Thanks for the reply, Been reading hackers of Aug 2004 and found the threads. It's a common habit to create two lines on the configuration files, in order to maintain the copy of the default conf file. I guess this should be the worst scenery for a freshly incoming DBA trying to put things in order. A temporary patch, will be updating documentation, encouraging administrators to use the SHOW ALL; command in the psql env, to confirm that changes where made. In my case, a 1.2 gig file was written, performance was on the floor. And my previous situation, a reindex force task last saturday, confused me. This is not a trivial problem, but in conjunction with other small problems could become a big one. Good habits when touching conf files using the SHOW ALL to confirm that changes where made will help until this is patched. Thanks for Postgres, Regards, Guido. This issue was resently discussed on hackers. It is a known issue, not very convinient for the user. Nevertheless it is not fixed in 8.0, but will perhaps be addressed in the next major release. (Remembering, it was a non-trivial thing to change.) Best Regards, Michael Paesold G u i d o B a r o s i o wrote: The solution appeared as something I didn't know On the .conf file Previous situation: #log_something=false log_something=true Worst situation #log_something=false #log_something=true Nice situation log_something=false #log_something=true Ok, the problem was that I assumed that commenting a value on the conf file will set it up to a default (false?). I was wrong. My server was writting tons of log's. Is this the normal behavior for pg_ctl reload? It seems that looks for new values, remembering the last state on the ones that actually are commented. Although it's my fault to have 2 (tow) lines for the same issue, and that I should realize that this is MY MISTAKE, the log defaults on a reload, if commented, tend to be the last value entered? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Changing the column length
Michael, I am sorry that my question is out of line with this group(performance) but I need an urgent help :-( .pls .. I need to know how to change the length of the column. In the future, try to provide more detail on your problem.Fortunately, I think I know what it is. PostgreSQL does not support changing the length of VARCHAR columns in-place until version 8.0 (currently in beta). Instead, you need to: 1) Add a new column of the correct length; 2) Copy the data in the old column to the new column; 3) Drop the old column; 4) Rename the new column to the same name as the old column. I realize that this approach can be quite painful if you have dependant views, contstraints, etc.It's why we fixed it for 8.0. You can also: 1) pg_dump the database in text format; 2) edit the table definition in the pg_dump file; 3) re-load the database While it *is* possible to change the column size by updating the system tables, doing so is NOT recommended as it can cause unexpected database errors. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] seqscan instead of index scan
On Mon, 30 Aug 2004, Martin Sarsale wrote: Multicolumn indexes can only be used if the clauses involving the indexed columns are joined with AND. For instance, SELECT name FROM test2 WHERE major = constant OR minor = constant; You can use DeMorgan's Theorem to transform an OR clause to an AND clause. In general: A OR B = NOT ((NOT A) AND (NOT B)) So: But I need something like: select * from t where c0 or d0; select * from t where not (c=0 and d=0); I haven't actually tried to see if postgresql would do anything interesting after such a transformation. That made me really curious. I ran a quick test and it turns out the server used dm's theorem to convert the expression back to 'or' case. Explain output (see below to set up the test case for this stmnt): esp=# explain analyze select * from millions where not (value1 50 and value2 20); QUERY PLAN -- Index Scan using millions_1_idx, millions_2_idx on millions (cost=0.00..12.01 rows=2 width=8) (act ual time=0.000..0.000 rows=2 loops=1) Index Cond: ((value1 = 50) OR (value2 = 20)) Total runtime: 0.000 ms (3 rows) drop table tens; drop table millions; create table tens(value int); create table millions(value1 int, value2 int); insert into tens values (0); insert into tens values (1); insert into tens values (2); insert into tens values (3); insert into tens values (4); insert into tens values (5); insert into tens values (6); insert into tens values (7); insert into tens values (8); insert into tens values (9); insert into millions select ones.value + (tens.value * 10) + (hundreds.value * 100) + (thousands.value * 1000) + (tenthousands.value * 1) + (hundredthousands.value * 10) from tens ones, tens tens, tens hundreds, tens thousands, tens tenthousands, tens hundredthousands; update millions set value2 = value1; create index millions_idx1 on millions(value1); create index millions_idx2 on millions(value2); create index millions_idx12 on millions(value1, value2); vacuum analyze millions; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
