Re: [PERFORM] Attempt at work around of int4 query won't touch int8 index ...
On 10 Sep 2003 at 22:44, Tom Lane wrote: James Robinson [EMAIL PROTECTED] writes: Is this just a dead end, or is there some variation of this that might possibly work, so that ultimately an undoctored literal number, when applied to an int8 column, could find an index? I think it's a dead end. What I was playing with this afternoon was removing the int8-and-int4 comparison operators from pg_operator. It works as far as making int8col = 42 do the right thing, but I'm not sure yet about side-effects. Is it possible to follow data type upgrade model in planner? Something like in C/C++ where data types are promoted upwards to find out better plan? int2-int4-int8-float4-float8 types. That could be a clean solution.. just a thought.. Bye Shridhar -- Hlade's Law:If you have a difficult task, give it to a lazy person -- they will find an easier way to do it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] L|p Treatment that makes your L|ps PLUMP
Title: FINALLY A LIP PLUMPER THAT ACTUALLY WORKS !!! Get Plump, Sexy Lip'sIn Under 30 Days! visit website CITY LIP'S exclusive lip treatment... Stimulates collagen hyaluronic moisture in your lip's resulting in BIGGER, LUSCIOUS, more SENSUOUS Lip's CITY LIP'S is used by men women in 34 countries. Recommended by Plastic Surgeons, Celebrities, Movie Stars CITY LIP'S super-hydrating formula plumps reduces unattractive lip wrinkles fine lines Easy to use, completely pain-free and GUARANTEED to work in 30 days or your MONEY BACK! Be the envy of all your friends! retail $47.95 ONLINE SALE $24.76you save: $23.19 (48% OFF) ~ BUY 2 GET 1 FREE ~ buy now visit website customer ratings: Women love beauty tips, forward this to a friend! Distributors Welcome!
[PERFORM] Inconsistent performance
To whoever can assist, I am working with a decent sized database on an extremely powerful machine. The specs follow: OS: RedHat Linux 9.0 PG Version 7.3 Memory 1 gig CPU Quad Processor - Unsure of exact CPUs Hard Drive 80 gigs Database Size 2 gigs As you can see the server is built for overkill. The problem that I see is as follows. I do a rather simple query: select count (*) from large-table where column = some value; About 80% of the time, the response time is sub-second. However, at 10% of the time, the response time is 5 - 10 seconds. This is nothing readily apparent at the system level that comes close to explaining the performance hits. CPU and memory usage (as measured by top) appear to be fine. Although there are certain tuning issues within the database itself, no documentation I have seen seems to indicate that tuning issues would lead to such inconsistent response time. Any ideas? Regards, Joseph ---(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] Inconsistent performance
On Mon, 15 Sep 2003, Joseph Bove wrote: I am working with a decent sized database on an extremely powerful machine. The specs follow: OS: RedHat Linux 9.0 PG Version 7.3 Memory 1 gig CPU Quad Processor - Unsure of exact CPUs Hard Drive 80 gigs Database Size 2 gigs As you can see the server is built for overkill. The problem that I see is as follows. I do a rather simple query: select count (*) from large-table where column = some value; About 80% of the time, the response time is sub-second. However, at 10% of the time, the response time is 5 - 10 seconds. Is it consistant for various values of some value? If so, it's possible that it's switching plans based on the apparent selectivity of the column for that value. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Inconsistent performance
On Mon, Sep 15, 2003 at 17:34:12 -0400, Joseph Bove [EMAIL PROTECTED] wrote: I do a rather simple query: select count (*) from large-table where column = some value; About 80% of the time, the response time is sub-second. However, at 10% of the time, the response time is 5 - 10 seconds. This is nothing readily apparent at the system level that comes close to explaining the performance hits. CPU and memory usage (as measured by top) appear to be fine. Although there are certain tuning issues within the database itself, no documentation I have seen seems to indicate that tuning issues would lead to such inconsistent response time. Looking at the output from explain analyze for the query would be useful. It may be that there are a lot of rows that have the value in the problem queries. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] restore time: sort_mem vs. checkpoing_segments
Vivek, And the winner is... checkpoint_segments. Restore of a significanly big database (~19.8GB restored) shows nearly no time difference depending on sort_mem when checkpoint_segments is large. There are quite a number of tables and indexes. The restore was done from a pg_dump -Fc dump of one database. Cool! Thank you for posting this. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Inconsistent performance
Joseph, Please see this web page before posting anything else: http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines Currently, you are not posting enough data for anyone to be of meaningful help. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Inconsistent performance
On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, Actually, it's inconsistent with the exact same command. I've now replicated the problem by doing the following command: select count (*) from table; The table in question has 88899 rows. The response time is anywhere from 1 second to 12 seconds. Different response times can occur in the same minute of testing! Well, that's really only got one valid plan right now (seqscan and aggregate). It'd be mildly interesting to see what explain analyze says in slow and fast states, although I'd be willing to bet that it's just going to effectively show that the seqscan is taking more or less time. I think we're going to need to see the configuration settings for the server and possibly some info on how big the table is (say relpages for the pg_class row associated with the table after a vacuum full). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Inconsistent performance
Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN - Aggregate (cost=9993.92..9993.92 rows=1 width=0) (actual time=7575.59..7575.59 rows=1 loops=1) - Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0) (actual time=0.06..7472.20 rows=88910 loops=1) Total runtime: 7575.67 msec (3 rows) The only things changing are the actual time. The costs are constant. The relpages from pg_class for vetapview (the table in question) is 8881. At the end of this message is the exhaustive contents of postgresql.conf. The only settings I have attempted tuning are as follows: tcpip_socket = true max_connections = 100 shared_buffers = 5000 sort_mem = 8192 fsync = false I did have shared_buffers and sort_mem both set higher originally (15000, 32168) but decreased them in case over-utilization of memory was the problem. The kernel setting shmmax is set to 256,000,000 (out of 1 gig) Regards, Joseph postgresql.conf # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 100 #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 = 15000 # min max_connections*2 or 16, 8KB each shared_buffers = 5000 #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8# min 4, typically 8KB each # # Non-shared Memory Sizes # #sort_mem = 32168 # min 64, size in KB sort_mem = 8192 #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 = fsync# the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 # # Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true#enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = 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) #default_statistics_target = 10 # range 1-1000 # # GEQO Optimizer Parameters # #geqo = true #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_threshold = 11 #geqo_pool_size = 0 # 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 = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # 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: #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
Re: [PERFORM] Inconsistent performance
[EMAIL PROTECTED] (Joseph Bove) writes: I do a rather simple query: select count (*) from large-table where column = some value; About 80% of the time, the response time is sub-second. However, at 10% of the time, the response time is 5 - 10 seconds. Does it seem data-dependent? That is, does the time vary for different values of some value? If a particular value is particularly common, the system might well revert to a sequential scan, making the assumption that it is quicker to look at every page in the table rather than to walk through Enormous Numbers of records. I had a case very similar to this where a table had _incredible_ skewing of this sort where there were a small number of column values that occurred hundreds of thousands of times, and other column values only occurred a handful of times. I was able to get Excellent Performance back by setting up two partial indices: - One for WHERE THIS_COLUMN VITAL_VALUE; - One for WHERE THIS_COLUMN VITAL_VALUE; The REALLY COMMON values were in the range VITAL_VALUE. -- output = (cbbrowne @ libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Inconsistent performance
On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN - Aggregate (cost=9993.92..9993.92 rows=1 width=0) (actual time=7575.59..7575.59 rows=1 loops=1) - Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0) (actual time=0.06..7472.20 rows=88910 loops=1) Total runtime: 7575.67 msec (3 rows) The only things changing are the actual time. The costs are constant. The relpages from pg_class for vetapview (the table in question) is 8881. At the end of this message is the exhaustive contents of postgresql.conf. The only settings I have attempted tuning are as follows: tcpip_socket = true max_connections = 100 shared_buffers = 5000 sort_mem = 8192 fsync = false A couple of things. 1: Is there an index on the parts of the query used for the where clause? 2: What is your effect_cache_size set to? It needs to be set right for your postgresql server to be able to take advantage of the kernel's cache (i.e. use an index scan when the kernel is likely to have that data in memory.) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Inconsistent performance
it seems like the difference is probably related to caching. you say you have 1gb of ram, and the database is 2gb.Obviously the entire database isn't cached, but maybe your query runs fast when the table is in memory, and they it gets swapped out of cache because some other piece of information moves into memory. In that circumstance, it has to load the information from disk and is therefor slow. how busy is the system? what other programs are running on the machine? how big (on disk) is the table in question? what kind of load does the system have? is it a single 80gb ide drive? Even though you have 4 CPU's a small amount of memory and bad IO system will kill the database. On Monday, September 15, 2003, at 05:28 PM, Joseph Bove wrote: Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN - Aggregate (cost=9993.92..9993.92 rows=1 width=0) (actual time=7575.59..7575.59 rows=1 loops=1) - Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0) (actual time=0.06..7472.20 rows=88910 loops=1) Total runtime: 7575.67 msec (3 rows) The only things changing are the actual time. The costs are constant. The relpages from pg_class for vetapview (the table in question) is 8881. At the end of this message is the exhaustive contents of postgresql.conf. The only settings I have attempted tuning are as follows: tcpip_socket = true max_connections = 100 shared_buffers = 5000 sort_mem = 8192 fsync = false I did have shared_buffers and sort_mem both set higher originally (15000, 32168) but decreased them in case over-utilization of memory was the problem. The kernel setting shmmax is set to 256,000,000 (out of 1 gig) Regards, Joseph postgresql.conf # # Connection Parameters # tcpip_socket = true #ssl = false max_connections = 100 #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 = 15000 # min max_connections*2 or 16, 8KB each shared_buffers = 5000 #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 1 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 #wal_buffers = 8# min 4, typically 8KB each # # Non-shared Memory Sizes # #sort_mem = 32168 # min 64, size in KB sort_mem = 8192 #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 = fsync# the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync #wal_debug = 0 # range 0-16 # # Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true#enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = 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) #default_statistics_target = 10 # range 1-1000 # # GEQO Optimizer Parameters # #geqo = true #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_threshold = 11 #geqo_pool_size = 0 # 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 = notice # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic #client_min_messages = notice # 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: #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3,
Re: [PERFORM] Inconsistent performance
On Mon, 15 Sep 2003, scott.marlowe wrote: On Mon, 15 Sep 2003, Joseph Bove wrote: Stephan, I've run explain analyze a number of times and have gotten results between 5.5 and 7.5 seconds Attached is a typical output QUERY PLAN - Aggregate (cost=9993.92..9993.92 rows=1 width=0) (actual time=7575.59..7575.59 rows=1 loops=1) - Seq Scan on vetapview (cost=0.00..9771.34 rows=89034 width=0) (actual time=0.06..7472.20 rows=88910 loops=1) Total runtime: 7575.67 msec (3 rows) The only things changing are the actual time. The costs are constant. The relpages from pg_class for vetapview (the table in question) is 8881. At the end of this message is the exhaustive contents of postgresql.conf. The only settings I have attempted tuning are as follows: tcpip_socket = true max_connections = 100 shared_buffers = 5000 sort_mem = 8192 fsync = false A couple of things. 1: Is there an index on the parts of the query used for the where clause? 2: What is your effect_cache_size set to? It needs to be set right for your postgresql server to be able to take advantage of the kernel's cache (i.e. use an index scan when the kernel is likely to have that data in memory.) Sorry, that should be effective_cache_size, not effect_cache_size. It's set in 8k blocks and is usually about how much buffer / cache you have left over after the machines settles after being up and running for a while. Fer instance, on my server, I show 784992K cache, and 42976K buff under top, so, that's 827968k/8k=103496 blocks. Note that if you've recompiled you may have somehow set block size larger, but installations with postgresql block sizes ~=8k are pretty uncommon, and you'd know if you had done that, so it's probably 8k blocks. ---(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] Inconsistent performance
The world rejoiced as [EMAIL PROTECTED] (Joseph Bove) wrote: Actually, it's inconsistent with the exact same command. I've now replicated the problem by doing the following command: select count (*) from table; The table in question has 88899 rows. The response time is anywhere from 1 second to 12 seconds. Different response times can occur in the same minute of testing! The only possible plan for THAT query will involve a seq scan of the whole table. If the postmaster already has the data in cache, it makes sense for it to run in 1 second. If it has to read it from disk, 12 seconds makes a lot of sense. You might want to increase the shared_buffers parameter in postgresql.conf; that should lead to increased stability of times as it should be more likely that the data in table will remain in cache. -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://www3.sympatico.ca/cbbrowne/x.html Signs of a Klingon Programmer - 8. Debugging? Klingons do not debug. Our software does not coddle the weak. Bugs are good for building character in the user. ---(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] restore time: sort_mem vs. checkpoing_segments
Vivek Khera [EMAIL PROTECTED] writes: Restore of a significanly big database (~19.8GB restored) shows nearly no time difference depending on sort_mem when checkpoint_segments is large. There are quite a number of tables and indexes. The restore was done from a pg_dump -Fc dump of one database. I was just bugging Marc for some useful data, so I'll ask you too: could you provide a trace of the pg_restore execution? log_statement plus log_duration output would do it. I am curious to understand exactly which steps in the restore are significant time sinks. I notice during the restore that the disk throughput triples during the checkpoint. Hm, better make sure the log includes some indication of when checkpoints happen. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
