Re: [PERFORM] Possible to improve query plan?
2011/1/25 Kevin Grittner : > Jeremy Palmer wrote: > >> Kevin I've now clustered the table. And the performance did >> increase quite a bit. > > Yeah, that's enough to notice the difference. > >> My only question is how often will I need to re-cluster the table, >> because it comes at quite a cost. The setup I'm running will mean >> that 10,000 new rows will be inserted, and 2,500 rows will be >> updated on this table each day. > > You're going to see performance drop off as the data fragments. > You'll need to balance the performance against maintenance > down-time. I would guess, though, that if you have a weekly > maintenance window big enough to handle the CLUSTER, it might be > worth doing it that often. Was FILLFACTOR already suggested regarding the INSERT vs UPDATE per day ? http://www.postgresql.org/docs/9.0/static/sql-altertable.html (and index too, but they already have a default at 90% for btree) > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fun little performance IMPROVEMENT...
On 21/01/2011 19:12, gr...@amadensor.com wrote: I was doing a little testing to see how machine load affected the performance of different types of queries, index range scans, hash joins, full scans, a mix, etc. In order to do this, I isolated different performance hits, spinning only CPU, loading the disk to create high I/O wait states, and using most of the physical memory. This was on a 4 CPU Xen virtual machine running 8.1.22 on CENTOS. Here is the fun part. When running 8 threads spinning calculating square roots (using the stress package), the full scan returned consistently 60% faster than the machine with no load. It was returning 44,000 out of 5,000,000 rows. Here is the explain analyze. I am hoping that this triggers something (I can run more tests as needed) that can help us make it always better. Looks like a virtualization artifact. Here's a list of some such noticed artifacts: http://wiki.freebsd.org/WhyNotBenchmarkUnderVMWare Idling: QUERY PLAN Seq Scan on schedule_details (cost=0.00..219437.90 rows=81386 width=187) (actual time=0.053..2915.966 rows=44320 loops=1) Filter: (schedule_type = '5X'::bpchar) Total runtime: 2986.764 ms Loaded: QUERY PLAN Seq Scan on schedule_details (cost=0.00..219437.90 rows=81386 width=187) (actual time=0.034..1698.068 rows=44320 loops=1) Filter: (schedule_type = '5X'::bpchar) Total runtime: 1733.084 ms In this case it looks like the IO generated by the VM is causing the Hypervisor to frequently "sleep" the machine while waiting for the IO, but if the machine is also generating CPU load, it is not put to sleep as often. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT page splits?
On Mon, Jan 24, 2011 at 9:26 PM, Josh Berkus wrote: > It looks to me like autovacuum doesn't ever consider when HOT updates > lead to page splits, and so require vacuuming. Or am I diagnosing it wrong? I'm not sure what you mean by a page split. An update wherein the new heap tuple won't fit on the same page as the existing heap tuple should be treated as non-HOT. But nothing gets split in that case. I think of a page split as an index event, and if these are HOT updates there shouldn't be any index changes at all. Can we see those stats again with n_tup_ins/upd/del? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT page splits?
> Can we see those stats again with n_tup_ins/upd/del? Sure: -[ RECORD 2 ]--+-- schemaname | public relname| general_info n_dead_tup | 12 n_live_tup | 1 n_tup_upd | 8817 n_tup_del | 0 n_tup_ins | 0 n_tup_hot_upd | 8817 pg_relation_size | 155648 pg_total_relation_size | 172032 -[ RECORD 4 ]--+-- schemaname | public relname| current_info n_dead_tup | 27 n_live_tup | 1 n_tup_upd | 3296 n_tup_del | 0 n_tup_ins | 0 n_tup_hot_upd | 3296 pg_relation_size | 385024 pg_total_relation_size | 409600 One question: in 8.3 and earlier, is the FSM used to track dead_rows for pg_stat_user_tables? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bloat issue on 8.3; autovac ignores HOT page splits?
On 26/01/11 07:28, Josh Berkus wrote: One question: in 8.3 and earlier, is the FSM used to track dead_rows for pg_stat_user_tables? If I'm understanding you correctly, ANALYZE is the main guy tracking/updating the dead row count. regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Queries becoming slow under heavy load
Hi, We are running some performances tests. With a lot of concurrent access, queries get very slow. When there is no load, those queries run fast. We kind of see a trend about these queries: it seems like the ones that become very slow have an ORDER BY or MAX in them. Here are our config settings: name | setting | description -+--+--- add_missing_from| off | Automatically adds missing table references to FROM clauses. allow_system_table_mods | off | Allows modifications of the structure of system tables. archive_command | (disabled) | Sets the shell command that will be called to archive a WAL file. archive_mode| off | Allows archiving of WAL files using archive_command. archive_timeout | 0| Forces a switch to the next xlog file if a new file has not been started within N seconds. array_nulls | on | Enable input of NULL elements in arrays. authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication. autovacuum | on | Starts the autovacuum subprocess. autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples. autovacuum_analyze_threshold| 250 | Minimum number of tuple inserts, updates or deletes prior to analyze. autovacuum_freeze_max_age | 2| Age at which to autovacuum a table to prevent transaction ID wraparound. autovacuum_max_workers | 3| Sets the maximum number of simultaneously running autovacuum worker processes. autovacuum_naptime | 5min | Time to sleep between autovacuum runs. autovacuum_vacuum_cost_delay| 20ms | Vacuum cost delay in milliseconds, for autovacuum. autovacuum_vacuum_cost_limit| -1 | Vacuum cost amount available before napping, for autovacuum. autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. autovacuum_vacuum_threshold | 500 | Minimum number of tuple updates or deletes prior to vacuum. backslash_quote | safe_encoding| Sets whether "\'" is allowed in string literals. bgwriter_delay | 200ms| Background writer sleep time between rounds. bgwriter_lru_maxpages | 100 | Background writer maximum number of LRU pages to flush per round. bgwriter_lru_multiplier | 2| Background writer multiplier on average buffers to scan per round. block_size | 8192 | Shows the size of a disk block. bonjour_name| | Sets the Bonjour broadcast service name. check_function_bodies | on | Check function bodies during CREATE FUNCTION. checkpoint_completion_target| 0.5 | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval. checkpoint_segments | 3| Sets the maximum distance in log segments between automatic WAL checkpoints. checkpoint_timeout | 5min | Sets the maximum time between automatic WAL checkpoints. checkpoint_warning | 30s | Enables warnings if checkpoint segments are filled more frequently than this. client_encoding | UTF8 | Sets the client's character set encoding. client_min_messages | notice | Sets the message levels that are sent to the client. commit_delay| 250 | Sets the delay in microseconds between transaction commit and flushing WAL to disk. commit_siblings | 10 | Sets the minimum concurrent open transactions before performing commit_delay. constraint_exclusion| off | Enables the planner to use constraints to optimize queries. cpu_index_tuple_cost| 0.005| Sets the planner's estimate of the cost of processing each index entry during an index scan. cpu_operator_cost | 0.0025 | Sets the planner's estimate of the cost of processing each operator or function call. cpu_tuple_cost
Re: [PERFORM] Queries becoming slow under heavy load
On 1/25/2011 3:37 PM, Anne Rosset wrote: Hi, We are running some performances tests. With a lot of concurrent access, queries get very slow. When there is no load, those queries run fast. We kind of see a trend about these queries: it seems like the ones that become very slow have an ORDER BY or MAX in them. Here are our config settings: It seems to me that we should try increasing shared_buffers. But do you have any other suggestions? Or do you see anything wrong in our config? Thanks, Anne While I applaud your attempt to get us lots of information, unfortunately the the one property you ask about (shared_buffers), I can't seem to find. So, maybe you could post a bit more: 1) how many concurrent clients? 2) can we see an explain analyze for a query when its fast, and then again when its slow? 3) Is this box dedicated to PG or are there other services running? 4) Looks like you have 8 Gig of ram, so I assume this is a 64 bit OS, can you tell us what you have for: shared_buffers effective_cahce_size work_mem 5) Once many clients start hitting the db, it might not all fit into ram and start hitting the HD, can you tell us what sort of IO you have (sata, scsi, raid, # of disks, etc). The stats from /proc/meminfo: SwapTotal: 2097112 kB SwapFree: 2096612 kB Was this run when the system was busy? Looks like you are not using any swap, so thats good at least. Oh, wait, there are two cat /proc/meminfo's. Is one when its fast and one when its slow? Looks to me, in both cases, you are not using much memory at all. (if you happen to have 'free', its output is a little more readable, if you wouldn't mind posting it (only really need it for when the box is slow) -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Queries becoming slow under heavy load
"Anne Rosset" wrote: > We are running some performances tests. With a lot of concurrent > access, queries get very slow. When there is no load, those > queries run fast. What's "a lot"? > We kind of see a trend about these queries: it seems like the > ones that become very slow have an ORDER BY or MAX in them. Without knowing the PostgreSQL version or more details about the queries, I would only be guessing at the cause. > It seems to me that we should try increasing shared_buffers. But > do you have any other suggestions? Or do you see anything wrong in > our config? I don't think you showed us your whole PostgreSQL configuration, and the format was hard to read -- it's best to show the contents of your postgresql.conf file, minus comments. If you read this page and re-post we can probably be more helpful: http://wiki.postgresql.org/wiki/SlowQueryQuestions -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Queries becoming slow under heavy load
When you say that with a lot of concurrent access, queries get very slow, How many concurrent connections to your server have you had? more that max_connections´value? If you want to have many concurrent connections, you should have consider to use a pooling connection system like pgbouncer or pgpool. Which are the values for: - work_mem - shared_buffers - maintenance_work_mem - effective_cache_size - effective_io_concurrency - server_version Which are your platform? Regards -- Ing. Marcos Luís Ortíz Valmaseda System Engineer -- Database Administrator Centro de Tecnologías de Gestión de Datos (DATEC) Universidad de las Ciencias Informáticas http://postgresql.uci.cu -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences
Robert Haas wrote: > On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian wrote: > > Chris Browne wrote: > >> gentosa...@gmail.com (A B) writes: > >> > If you just wanted PostgreSQL to go as fast as possible WITHOUT any > >> > care for your data (you accept 100% dataloss and datacorruption if any > >> > error should occur), what settings should you use then? > >> > >> Use /dev/null. ?It is web scale, and there are good tutorials. > >> > >> But seriously, there *are* cases where "blind speed" is of use. ?When > >> loading data into a fresh database is a good time for this; if things > >> fall over, it may be pretty acceptable to start "from scratch" with > >> mkfs/initdb. > >> > >> I'd: > >> - turn off fsync > >> - turn off synchronous commit > >> - put as much as possible onto Ramdisk/tmpfs/similar as possible > > > > FYI, we do have a documentation section about how to configure Postgres > > for improved performance if you don't care about durability: > > > > ? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html > > This sentence looks to me like it should be removed, or perhaps clarified: > > This does affect database crash transaction durability. Uh, doesn't it affect database crash transaction durability? I have applied the attached patch to clarify things. Thanks. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml index 2699828..fb55598 100644 *** a/doc/src/sgml/perform.sgml --- b/doc/src/sgml/perform.sgml *** SELECT * FROM x, y, a, b, c WHERE someth *** 1159,1165 Turn off ; there might be no need to write the WAL to disk on every !commit. This does affect database crash transaction durability. --- 1159,1165 Turn off ; there might be no need to write the WAL to disk on every !commit. This can cause transaction loss after a server crash. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
