Re: [PERFORM] Merge Join vs Nested Loop
[Tom Lane - Tue at 06:09:56PM -0400] > If your tables are small enough to fit (mostly) in memory, then the > planner tends to overestimate the cost of a nestloop because it fails to > account for cacheing effects across multiple scans of the inner table. > This is addressed in 8.2, but in earlier versions about all you can do > is reduce random_page_cost, and a sane setting of that (ie not less than > 1.0) may not be enough to push the cost estimates where you want them. > Still, reducing random_page_cost ought to be your first recourse. Thank you. Reducing the random page hit cost did reduce the nested loop cost significantly, sadly the merge join costs where reduced even further, causing the planner to favor those even more than before. Setting the effective_cache_size really low solved the issue, but I believe we rather want to have a high effective_cache_size. Eventually, setting the effective_cache_size to near-0, and setting random_page_cost to 1 could maybe be a desperate measure. Another one is to turn off merge/hash joins and seq scans. It could be a worthwhile experiment if nothing else :-) The bulk of our database is historical data that most often is not touched at all, though one never knows for sure until the queries have run all through - so table partitioning is not an option, it seems like. My general idea is that nested loops would cause the most recent data and most important part of the indexes to stay in the OS cache. Does this make sense from an experts point of view? :-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Forcing the use of particular execution plans
Tim Truman wrote: Query: SELECT count(*) as count FROM ( SELECT * FROM transaction t, merchant m WHERE t.merchant_id = m.id AND m.id = 198 AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' UNION ALL SELECT * FROM transaction t, merchant m WHERE t.merchant_id = m.id AND m.parent_merchant_id = 198 AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' ) AS foobar Actually, I think the best course of action is to rewrite the query to a faster alternative. What you can try is: SELECT SUM(count) AS count FROM ( SELECT count(*) AS count FROM transaction t, merchant m WHERE t.merchant_id = m.id AND m.id = 198 AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' UNION ALL SELECT count(*) AS count FROM transaction t, merchant m WHERE t.merchant_id = m.id AND m.parent_merchant_id = 198 AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' ) AS foobar; The next optimization is to merge the 2 subqueries into one. If you schema is such that m.id can not be the same as m.parent_merchant_id I think your query can be reduced to: SELECT count(*) AS count FROM transaction t, merchant m WHERE t.merchant_id = m.id AND ( m.id = 198 OR m.parent_merchant_id = 198 ) AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' If m.id can be the same as m.parent_merchant_id you need something like: SELECT SUM( CASE WHEN m.id = m.parent_merchant_id THEN 2 ELSE 1 END ) AS count FROM transaction t, merchant m WHERE t.merchant_id = m.id AND ( m.id = 198 OR m.parent_merchant_id = 198 ) AND t.transaction_date >= '20050101' AND t.transaction_date <= '20060925' AND credit_card_no LIKE '%111' Jochem ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Merge Join vs Nested Loop
On Wed, 2006-09-27 at 11:48 +0200, Tobias Brox wrote: > [Tom Lane - Tue at 06:09:56PM -0400] > > If your tables are small enough to fit (mostly) in memory, then the > > planner tends to overestimate the cost of a nestloop because it fails to > > account for cacheing effects across multiple scans of the inner table. > > This is addressed in 8.2, but in earlier versions about all you can do > > is reduce random_page_cost, and a sane setting of that (ie not less than > > 1.0) may not be enough to push the cost estimates where you want them. > > Still, reducing random_page_cost ought to be your first recourse. > > Thank you. Reducing the random page hit cost did reduce the nested loop > cost significantly, sadly the merge join costs where reduced even > further, causing the planner to favor those even more than before. > Setting the effective_cache_size really low solved the issue, but I > believe we rather want to have a high effective_cache_size. > > Eventually, setting the effective_cache_size to near-0, and setting > random_page_cost to 1 could maybe be a desperate measure. Another one > is to turn off merge/hash joins and seq scans. It could be a worthwhile > experiment if nothing else :-) > > The bulk of our database is historical data that most often is not > touched at all, though one never knows for sure until the queries have > run all through - so table partitioning is not an option, it seems like. > My general idea is that nested loops would cause the most recent data > and most important part of the indexes to stay in the OS cache. Does > this make sense from an experts point of view? :-) Have you tried chaning the cpu_* cost options to see how they affect merge versus nested loop? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Merge Join vs Nested Loop
[Scott Marlowe - Wed at 09:58:30AM -0500] > Have you tried chaning the cpu_* cost options to see how they affect > merge versus nested loop? As said in the original post, increasing any of them shifts the planner towards nested loops instead of merge_join. I didn't check which one of the cost constants made the most impact. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Merge Join vs Nested Loop
On Wed, 2006-09-27 at 17:05 +0200, Tobias Brox wrote: > [Scott Marlowe - Wed at 09:58:30AM -0500] > > Have you tried chaning the cpu_* cost options to see how they affect > > merge versus nested loop? > > As said in the original post, increasing any of them shifts the planner > towards nested loops instead of merge_join. I didn't check which one of > the cost constants made the most impact. So, by decreasing them, you should move away from nested loops then, right? Has that not worked for some reason? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Merge Join vs Nested Loop
[Scott Marlowe - Wed at 10:19:24AM -0500] > So, by decreasing them, you should move away from nested loops then, > right? Has that not worked for some reason? I want to move to nested loops, they are empirically faster in many of our queries, and that makes sense since we've got quite big tables and most of the queries only touch a small partition of the data. I've identified that moving any of the cost constants (including random_page_cost) upwards gives me the right result, but I'm still wary if this is the right thing to do. Even if so, what constants should I target first? I could of course try to analyze a bit what constants give the biggest impact. Then again, we have many more queries hitting the database than the few I'm doing research into (and those I'm doing research into is even very simplified versions of the real queries). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Merge Join vs Nested Loop
On Wed, 2006-09-27 at 10:26, Tobias Brox wrote: > [Scott Marlowe - Wed at 10:19:24AM -0500] > > So, by decreasing them, you should move away from nested loops then, > > right? Has that not worked for some reason? > > I want to move to nested loops, they are empirically faster in many of > our queries, and that makes sense since we've got quite big tables and > most of the queries only touch a small partition of the data. > > I've identified that moving any of the cost constants (including > random_page_cost) upwards gives me the right result, but I'm still wary > if this is the right thing to do. Even if so, what constants should I > target first? I could of course try to analyze a bit what constants > give the biggest impact. Then again, we have many more queries hitting > the database than the few I'm doing research into (and those I'm doing > research into is even very simplified versions of the real queries). Ahh, the other direction then. I would think it's safer to nudge these a bit than to drop random page cost to 1 or set effective_cache_size to 1000 etc... But I'm sure you should test the other queries and / or keep an eye on your database while running to make sure those changes don't impact other users. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Merge Join vs Nested Loop
[Scott Marlowe - Wed at 10:31:35AM -0500] > And remember, you can always change any of those settings in session for > just this one query to force the planner to make the right decision. sure ... I could identify the most problematic queries, and hack up the software application to modify the config settings for those exact queries ... but it's a very ugly solution. :-) Particularly if Tom Lane is correct saying the preferance of merge join instead of nested loop is indeed a bug. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Forcing the use of particular execution plans
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Truman > > Hi, > > I have the following query which has been running very slowly > and after a > lot of testing/trial and error I found an execution plan that > ran the query > in a fraction of the time (and then lost the statistics that > produced it). > What I wish to know is how to force the query to use the > faster execution > plan. It would be a bit easier to diagnose the problem if you posted EXPLAIN ANALYZE rather than just EXPLAIN. The two plans you posted looked very similar except for the order of the nested loop in subquery 1 and an index scan rather than a seq scan in subquery 2. My guess would be that the order of the nested loop is determined mostly by estimates of matching rows. If you ran an EXPLAIN ANALYZE you could tell if the planner is estimating correctly. If it is not, you could try increasing your statistics target and running ANALYZE. To make the planner prefer an index scan over a seq scan, I would first check the statistics again, and then you can try setting enable_seqscan to false (enable_seqscan is meant more for testing than production) or, you could try reducing random_page_cost, but you should test that against a range of queries before putting it in production. Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] autovacuum on a -mostly- r/o table
On Wednesday 27 September 2006 09:08, Edoardo Ceccarelli <[EMAIL PROTECTED]> wrote: > > How can I configure the vacuum to run after the daily batch > insert/update? > If you really only want it to run then, you should disable autovacuum and continue to run the vacuum manually. You might also investigate the vacuum cost delay options, which will make vacuum take longer but will have less of an impact on your database while running. -- "If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." -- Somerset Maugham, Author ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] autovacuum on a -mostly- r/o table
Bill Moran wrote: In response to Edoardo Ceccarelli <[EMAIL PROTECTED]>: I have read that autovacuum cannot check to see pg load before launching vacuum but is there any patch about it? that would sort out the problem in a good and simple way. Otherwise, which kind of set of parameters I should put in autovacuum configuration? I am stuck because in our case the table gets mostly read and if I set up things as to vacuum the table after a specific amount of insert/updates, I cannot foresee whether this could happen during daytime when server is under high load. How can I configure the vacuum to run after the daily batch insert/update? It doesn't sound as if your setup is a good match for autovacuum. You might be better off going back to the cron vacuums. That's the beauty of Postgres -- it gives you the choice. If you want to continue with autovac, you may want to experiment with vacuum_cost_delay and associated parameters, which can lessen the impact of vacuuming. The db is constantly monitored during high peak so that we can switch to a backup pg7.3 database that is being vacuumed every night. This is giving me the opportunity to try it so I tried this: vacuum_cost_delay = 200 vacuum_cost_page_hit = 5 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_limit = 100 I know these values affect the normal vacuum process but apparently this means setting #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay and #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit for the rest of them I am currently trying the deafults: #autovacuum_naptime = 60 # time between autovacuum runs, in secs #autovacuum_vacuum_threshold = 1000 # min # of tuple updates before vacuum #autovacuum_analyze_threshold = 500 # min # of tuple updates before analyze #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze Does anybody know which process is actually AUTO-vacuum-ing the db? So that I can check when is running...
Re: [PERFORM] autovacuum on a -mostly- r/o table
Rod Taylor wrote: On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote: I have read that autovacuum cannot check to see pg load before launching vacuum but is there any patch about it? that would sort out the problem in a good and simple way. In some cases the solution to high load is to vacuum the tables being hit the heaviest -- meaning that simply checking machine load isn't enough to make that decision. In fact, that high load problem is exactly why autovacuum was created in the first place. True, but autovacuum could check load -before- and -during- it's execution and it could adjust himself automatically to perform more or less aggressively depending on the difference between those two values. Maybe with a parameter like: maximum-autovacuum-load=0.2 that would mean: "never load the machine more than 20% for the autovacuum" ...another thing is, how could autovacuum check for machine load, this is something I cannot imagine right now...
Re: [PERFORM] autovacuum on a -mostly- r/o table
[Edoardo Ceccarelli - Wed at 06:49:23PM +0200] > ...another thing is, how could autovacuum check for machine load, this > is something I cannot imagine right now... One solution I made for our application, is to check the pg_stats_activity view. It requires some config to get the stats available in that view, though. When the application is to start a low-priority transaction, it will first do: select count(*) from pg_stat_activity where current_query not like '
[PERFORM] autovacuum on a -mostly- r/o table
Hello, we are running a 7.3 postgres db with only a big table (avg 500.000records) and 7 indexes for a search engine. we have 2 of this databases and we can switch from one to another. Last week we decided to give a try to 8.1 on one of them and everything went fine, db is faster (about 2 or 3 times in our case) and the server load is higher - which should mean that faster response time is achieved by taking a better use of the server. We also activated the autovacuum feature to give it a try and that's were our problems started. I left the standard autovacuum configuration just to wait and see, pg decided to start a vacuum on the table just midday when users were launching search queries on the table and server load reached a very high value so that in a couple of minutes the db was unusable With pg7.3 we use to vacuum the db night time, mostly because the insert and updates in this table is made in a batch way: a single task that puts 100.000 records in the db in 10/20minutes, so the best time to actually vacuum the db would be after this batch. I have read that autovacuum cannot check to see pg load before launching vacuum but is there any patch about it? that would sort out the problem in a good and simple way. Otherwise, which kind of set of parameters I should put in autovacuum configuration? I am stuck because in our case the table gets mostly read and if I set up things as to vacuum the table after a specific amount of insert/updates, I cannot foresee whether this could happen during daytime when server is under high load. How can I configure the vacuum to run after the daily batch insert/update? Any help appreciated Thank you very much Edoardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] autovacuum on a -mostly- r/o table
[Edoardo Ceccarelli - Wed at 06:08:30PM +0200] > We also activated the autovacuum feature to give it a try and that's > were our problems started. (...) > How can I configure the vacuum to run after the daily batch insert/update? I think you shouldn't use autovacuum in your case. We haven't dared testing out autovacuum yet even though we probably should, so we're running vacuum at fixed times of the day. We have a very simple script to do this, the most important part of it reads: echo "vacuum verbose analyze;" | psql $DB_NAME > $logdir/$filename 2>&1 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] autovacuum on a -mostly- r/o table
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote: > How can I configure the vacuum to run after the daily batch insert/update? Check out this: http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html By inserting the right row you can disable autovacuum to vacuum your big tables, and then you can schedule vacuum nightly for those just as before. There's still a benefit in that you don't need to care about vacuuming the rest of the tables, which will be done just in time. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] autovacuum on a -mostly- r/o table
In response to Edoardo Ceccarelli <[EMAIL PROTECTED]>: > Hello, > > we are running a 7.3 postgres db with only a big table (avg > 500.000records) and 7 indexes for a search engine. > we have 2 of this databases and we can switch from one to another. > Last week we decided to give a try to 8.1 on one of them and everything > went fine, db is faster (about 2 or 3 times in our case) and the server > load is higher - which should mean that faster response time is achieved > by taking a better use of the server. > > We also activated the autovacuum feature to give it a try and that's > were our problems started. > I left the standard autovacuum configuration just to wait and see, pg > decided to start a vacuum on the table just midday when users were > launching search queries on the table and server load reached a very > high value so that in a couple of minutes the db was unusable > > With pg7.3 we use to vacuum the db night time, mostly because the insert > and updates in this table is made in a batch way: a single task that > puts 100.000 records in the db in 10/20minutes, so the best time to > actually vacuum the db would be after this batch. > > I have read that autovacuum cannot check to see pg load before launching > vacuum but is there any patch about it? that would sort out the problem > in a good and simple way. > Otherwise, which kind of set of parameters I should put in autovacuum > configuration? I am stuck because in our case the table gets mostly read > and if I set up things as to vacuum the table after a specific amount of > insert/updates, I cannot foresee whether this could happen during > daytime when server is under high load. > How can I configure the vacuum to run after the daily batch insert/update? It doesn't sound as if your setup is a good match for autovacuum. You might be better off going back to the cron vacuums. That's the beauty of Postgres -- it gives you the choice. If you want to continue with autovac, you may want to experiment with vacuum_cost_delay and associated parameters, which can lessen the impact of vacuuming. -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] autovacuum on a -mostly- r/o table
On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote: > > I have read that autovacuum cannot check to see pg load before > launching > vacuum but is there any patch about it? that would sort out the > problem > in a good and simple way. In some cases the solution to high load is to vacuum the tables being hit the heaviest -- meaning that simply checking machine load isn't enough to make that decision. In fact, that high load problem is exactly why autovacuum was created in the first place. -- ---(end of broadcast)--- TIP 1: 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: [ADMIN] [PERFORM] autovacuum on a -mostly- r/o table
Csaba Nagy wrote: On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote: How can I configure the vacuum to run after the daily batch insert/update? Check out this: http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html By inserting the right row you can disable autovacuum to vacuum your big tables, and then you can schedule vacuum nightly for those just as before. There's still a benefit in that you don't need to care about vacuuming the rest of the tables, which will be done just in time. In addition autovacuum respects the work of manual or cron based vacuums, so if you issue a vacuum right after a daily batch insert / update, autovacuum won't repeat the work of that manual vacuum. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Problems with inconsistant query performance.
List, I posted a little about this a while back to the general list, but never really got any where with it so I'll try again, this time with a little more detail and hopefully someone can send me in the right direction. Here is the problem, I have a procedure that is called 100k times a day. Most of the time it's screaming fast, but other times it takes a few seconds. When it does lag my machine can get behind which causes other problems, so I'm trying to figure out why there is such a huge delta in performance with this proc. The proc is pretty large (due to the number of vars) so I will summarize it here: == CREATE acctmessage( )RETURNS void AS $$ BEGIN INSERT into tmpaccounting_tab ( ... ) values ( ... ); IF _acctType = 'start' THEN BEGIN INSERT into radutmp_tab ( ... ) valuse ( ... ); EXCEPTION WHEN UNIQUE_VIOLATION THEN NULL; END; ELSIF _acctType = 'stop' THEN UPDATE radutmp_tab SET ... WHERE sessionId = _sessionId AND userName = _userName; IF (NOT FOUND) THEN INSERT into radutmp_tab ( ... ) values ( ... ); END IF; END IF; END; $$ LANGUAGE plpgsql; == So in a nutshell, if I get an accounting record put it in the tmpaccounting_tab and then insert or update the radutmp_tab based on what kind of record it is. If for some reason the message is a start message and a duplicate, drop it, and if the message is a stop message and we don't have the start then insert it. The tmpaccounting_tab table doesn't have any indexes and gets flushed to the accounting_tab table nightly so it should have very good insert performance as the table is kept small (compared to accounting_tab) and doesn't have index overhead. The radutmp_tab is also kept small as completed sessions are flushed to another table nightly, but I do keep an index on sessionId and userName so the update isn't slow. Now that you have the layout, the problem: I log whenever a query takes more than 250ms and have logged this query: duration: 3549.229 ms statement: select acctMessage( 'stop', 'username', 'address', 'time', 'session', 'port', 'address', 'bytes', 'bytes', 0, 0, 1, 'reason', '', '', '', 'proto', 'service', 'info') But when I go do an explain analyze it is very fast: QUERY PLAN Result (cost=0.00..0.03 rows=1 width=0) (actual time=6.812..6.813 rows=1 loops=1) Total runtime: 6.888 ms So the question is why on a relatively simple proc and I getting a query performance delta between 3549ms and 7ms? Here are some values from my postgres.conf to look at: shared_buffers = 6 # min 16 or max_connections*2, 8KB each temp_buffers = 5000 # min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 131072 # min 64, size in KB maintenance_work_mem = 262144 # min 1024, size in KB max_stack_depth = 2048 # min 100, size in KB effective_cache_size = 65536# typically 8KB each Thanks for any help you can give, schu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Merge Join vs Nested Loop
I found a way to survive yet some more weeks :-) One of the queries we've had most problems with today is principially something like: select A.*,sum(B.*) from A join B where A.created>x and ... order by A.created desc limit 32 group by A.* There is by average two rows in B for every row in A. Note the 'limit 32'-part. I rewrote the query to: select A.*,(select sum(B.*) from B ...) where A.created>x and ... order by A.created desc limit 32; And voila, the planner found out it needed just some few rows from A, and execution time was cutted from 1-2 minutes down to 20 ms. :-) I've also started thinking a bit harder about table partitioning, if we add some more redundancy both to the queries and the database, it may help us drastically reduce the real expenses of some of the merge joins... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Forcing the use of particular execution plans
On Wed, Sep 27, 2006 at 10:51:26AM -0500, Dave Dutcher wrote: > To make the planner prefer an index scan over a seq scan, I would first > check the statistics again, and then you can try setting enable_seqscan to > false (enable_seqscan is meant more for testing than production) or, you > could try reducing random_page_cost, but you should test that against a > range of queries before putting it in production. Index scans are also pretty picky about correlation. If you have really low correlation you don't want to index scan, but I think our current estimates make it too eager to switch to a seqscan. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] autovacuum on a -mostly- r/o table
In response to Edoardo Ceccarelli <[EMAIL PROTECTED]>: > Rod Taylor wrote: > > On Wed, 2006-09-27 at 18:08 +0200, Edoardo Ceccarelli wrote: > > > >> I have read that autovacuum cannot check to see pg load before > >> launching > >> vacuum but is there any patch about it? that would sort out the > >> problem > >> in a good and simple way. > >> > > > > In some cases the solution to high load is to vacuum the tables being > > hit the heaviest -- meaning that simply checking machine load isn't > > enough to make that decision. > > > > In fact, that high load problem is exactly why autovacuum was created in > > the first place. > > > True, > but autovacuum could check load -before- and -during- it's execution and > it could adjust himself automatically to perform more or less > aggressively depending on the difference between those two values. > Maybe with a parameter like: maximum-autovacuum-load=0.2 > that would mean: "never load the machine more than 20% for the autovacuum" This is pretty non-trivial. How do you define 20% load? 20% of the CPU? Does that mean that it's OK for autovac to use 3% cpu and 100% of your IO? Ok, so we need to calculate an average of IO and CPU -- which disks? If your WAL logs are on one disk, and you've used tablespaces to spread the rest of your DB across different partitions, it can be pretty difficult to determine which IO parameters you want to take into consideration. -- Bill Moran Collaborative Fusion Inc. IMPORTANT: This message contains confidential information and is intended only for the individual named. If the reader of this message is not an intended recipient (or the individual responsible for the delivery of this message to an intended recipient), please be advised that any re-use, dissemination, distribution or copying of this message is prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Problems with inconsistant query performance.
Periodically taking longer is probably a case of some other process in the database holding a lock you need, or otherwise bogging the system down, especially if you're always running acctmessage from the same connection (because the query plans shouldn't be changing then). I'd suggest looking at what else is happening at the same time. Also, it's more efficient to operate on chunks of data rather than one row at a time whenever possible. If you have to log each row individually, consider simply logging them into a table, and then periodically pulling data out of that table to do additional processing on it. BTW, your detection of duplicates/row existance has a race condition. Take a look at example 36-1 at http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING for a better way to handle it. On Wed, Sep 27, 2006 at 10:37:22AM -0800, Matthew Schumacher wrote: > List, > > I posted a little about this a while back to the general list, but never > really got any where with it so I'll try again, this time with a little > more detail and hopefully someone can send me in the right direction. > > Here is the problem, I have a procedure that is called 100k times a day. > Most of the time it's screaming fast, but other times it takes a few > seconds. When it does lag my machine can get behind which causes other > problems, so I'm trying to figure out why there is such a huge delta in > performance with this proc. > > The proc is pretty large (due to the number of vars) so I will summarize > it here: > > == > CREATE acctmessage( )RETURNS void AS $$ > BEGIN > INSERT into tmpaccounting_tab ( ... ) values ( ... ); > > IF _acctType = 'start' THEN > BEGIN > INSERT into radutmp_tab ( ... ) valuse ( ... ); > EXCEPTION WHEN UNIQUE_VIOLATION THEN > NULL; > END; > ELSIF _acctType = 'stop' THEN > UPDATE radutmp_tab SET ... WHERE sessionId = _sessionId AND userName = > _userName; > IF (NOT FOUND) THEN > INSERT into radutmp_tab ( ... ) values ( ... ); > END IF; > > END IF; > END; > $$ > LANGUAGE plpgsql; > == > > So in a nutshell, if I get an accounting record put it in the > tmpaccounting_tab and then insert or update the radutmp_tab based on > what kind of record it is. If for some reason the message is a start > message and a duplicate, drop it, and if the message is a stop message > and we don't have the start then insert it. > > The tmpaccounting_tab table doesn't have any indexes and gets flushed to > the accounting_tab table nightly so it should have very good insert > performance as the table is kept small (compared to accounting_tab) and > doesn't have index overhead. The radutmp_tab is also kept small as > completed sessions are flushed to another table nightly, but I do keep > an index on sessionId and userName so the update isn't slow. > > Now that you have the layout, the problem: I log whenever a query takes > more than 250ms and have logged this query: > > duration: 3549.229 ms statement: select acctMessage( 'stop', > 'username', 'address', 'time', 'session', 'port', 'address', 'bytes', > 'bytes', 0, 0, 1, 'reason', '', '', '', 'proto', 'service', 'info') > > But when I go do an explain analyze it is very fast: > > QUERY PLAN > > Result (cost=0.00..0.03 rows=1 width=0) (actual time=6.812..6.813 > rows=1 loops=1) > Total runtime: 6.888 ms > > So the question is why on a relatively simple proc and I getting a query > performance delta between 3549ms and 7ms? > > Here are some values from my postgres.conf to look at: > > shared_buffers = 6 # min 16 or max_connections*2, > 8KB each > temp_buffers = 5000 # min 100, 8KB each > #max_prepared_transactions = 5 # can be 0 or more > # note: increasing max_prepared_transactions costs ~600 bytes of shared > memory > # per transaction slot, plus lock space (see max_locks_per_transaction). > work_mem = 131072 # min 64, size in KB > maintenance_work_mem = 262144 # min 1024, size in KB > max_stack_depth = 2048 # min 100, size in KB > effective_cache_size = 65536# typically 8KB each > > > Thanks for any help you can give, > schu > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can ge
Re: [PERFORM] Problems with inconsistant query performance.
Jim, Thanks for the help. I went and looked at that example and I don't see how it's different than the "INSERT into radutmp_tab" I'm already doing. Both raise an exception, the only difference is that I'm not doing anything with it. Perhaps you are talking about the "IF (NOT FOUND)" I put after the "UPDATE radutmp_tab". Should this be an EXCEPTION instead? Also I don't know how this could cause a race condition. As far as I understand each proc is run in it's own transaction, and the code in the proc is run serially. Can you explain more why this could case a race? Thanks, schu Jim C. Nasby wrote: > Periodically taking longer is probably a case of some other process in > the database holding a lock you need, or otherwise bogging the system > down, especially if you're always running acctmessage from the same > connection (because the query plans shouldn't be changing then). I'd > suggest looking at what else is happening at the same time. > > Also, it's more efficient to operate on chunks of data rather than one > row at a time whenever possible. If you have to log each row > individually, consider simply logging them into a table, and then > periodically pulling data out of that table to do additional processing > on it. > > BTW, your detection of duplicates/row existance has a race condition. > Take a look at example 36-1 at > http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > for a better way to handle it. >> == >> CREATE acctmessage( )RETURNS void AS $$ >> BEGIN >> INSERT into tmpaccounting_tab ( ... ) values ( ... ); >> >> IF _acctType = 'start' THEN >> BEGIN >> INSERT into radutmp_tab ( ... ) valuse ( ... ); >> EXCEPTION WHEN UNIQUE_VIOLATION THEN >> NULL; >> END; >> ELSIF _acctType = 'stop' THEN >> UPDATE radutmp_tab SET ... WHERE sessionId = _sessionId AND userName = >> _userName; >> IF (NOT FOUND) THEN >> INSERT into radutmp_tab ( ... ) values ( ... ); >> END IF; >> >> END IF; >> END; >> $$ >> LANGUAGE plpgsql; >> == ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Problems with inconsistant query performance.
On Wed, Sep 27, 2006 at 01:33:09PM -0800, Matthew Schumacher wrote: > Jim, > > Thanks for the help. I went and looked at that example and I don't see > how it's different than the "INSERT into radutmp_tab" I'm already doing. > Both raise an exception, the only difference is that I'm not doing > anything with it. Perhaps you are talking about the "IF (NOT FOUND)" I > put after the "UPDATE radutmp_tab". Should this be an EXCEPTION > instead? Also I don't know how this could cause a race condition. As > far as I understand each proc is run in it's own transaction, and the > code in the proc is run serially. Can you explain more why this could > case a race? It can cause a race if another process could be performing those same inserts or updates at the same time. I know the UPDATE case can certainly cause a race. 2 connections try to update, both hit NOT FOUND, both try to insert... only one will get to commit. I think that the UNIQUE_VIOLATION case should be safe, since a second inserter should block if there's another insert that's waiting to commit. DELETEs are something else to think about for both cases. If you're certain that only one process will be performing DML on those tables at any given time, then what you have is safe. But if that's the case, I'm thinking you should be able to group things into chunks, which should be more efficient. > Thanks, > schu > > > > Jim C. Nasby wrote: > > Periodically taking longer is probably a case of some other process in > > the database holding a lock you need, or otherwise bogging the system > > down, especially if you're always running acctmessage from the same > > connection (because the query plans shouldn't be changing then). I'd > > suggest looking at what else is happening at the same time. > > > > Also, it's more efficient to operate on chunks of data rather than one > > row at a time whenever possible. If you have to log each row > > individually, consider simply logging them into a table, and then > > periodically pulling data out of that table to do additional processing > > on it. > > > > BTW, your detection of duplicates/row existance has a race condition. > > Take a look at example 36-1 at > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > for a better way to handle it. > > >> == > >> CREATE acctmessage( )RETURNS void AS $$ > >> BEGIN > >> INSERT into tmpaccounting_tab ( ... ) values ( ... ); > >> > >> IF _acctType = 'start' THEN > >> BEGIN > >> INSERT into radutmp_tab ( ... ) valuse ( ... ); > >> EXCEPTION WHEN UNIQUE_VIOLATION THEN > >> NULL; > >> END; > >> ELSIF _acctType = 'stop' THEN > >> UPDATE radutmp_tab SET ... WHERE sessionId = _sessionId AND userName = > >> _userName; > >> IF (NOT FOUND) THEN > >> INSERT into radutmp_tab ( ... ) values ( ... ); > >> END IF; > >> > >> END IF; > >> END; > >> $$ > >> LANGUAGE plpgsql; > >> == > -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: 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] Problems with inconsistant query performance.
Jim C. Nasby wrote: > > It can cause a race if another process could be performing those same > inserts or updates at the same time. There are inserts and updates running all of the time, but never the same data. I'm not sure how I can get around this since the queries are coming from my radius system which is not able to queue this stuff up because it waits for a successful query before returning an OK packet back to the client. > > I know the UPDATE case can certainly cause a race. 2 connections try to > update, both hit NOT FOUND, both try to insert... only one will get to > commit. Why is that? Doesn't the first update lock the row causing the second one to wait, then the second one stomps on the row allowing both to commit? I must be confused > > I think that the UNIQUE_VIOLATION case should be safe, since a second > inserter should block if there's another insert that's waiting to > commit. Are you saying that inserts inside of an EXCEPTION block, but normal inserts don't? > > DELETEs are something else to think about for both cases. I only do one delete and that is every night when I move the data to the primary table and remove that days worth of data from the tmp table. This is done at non-peak times with a vacuum, so I think I'm good here. > > If you're certain that only one process will be performing DML on those > tables at any given time, then what you have is safe. But if that's the > case, I'm thinking you should be able to group things into chunks, which > should be more efficient. Yea, I wish I could, but I really need to do one at a time because of how radius waits for a successful query before telling the access server all is well. If the query fails, the access server won't get the 'OK' packet and will send the data to the secondary radius system where it gets queued. Do you know of a way to see what is going on with the locking system other than "select * from pg_locks"? I can't ever seem to catch the system when queries start to lag. Thanks again, schu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Problems with inconsistant query performance.
On Wed, Sep 27, 2006 at 02:17:23PM -0800, Matthew Schumacher wrote: > Jim C. Nasby wrote: > > > > It can cause a race if another process could be performing those same > > inserts or updates at the same time. > > There are inserts and updates running all of the time, but never the > same data. I'm not sure how I can get around this since the queries are > coming from my radius system which is not able to queue this stuff up > because it waits for a successful query before returning an OK packet > back to the client. > > > > > I know the UPDATE case can certainly cause a race. 2 connections try to > > update, both hit NOT FOUND, both try to insert... only one will get to > > commit. > > Why is that? Doesn't the first update lock the row causing the second > one to wait, then the second one stomps on the row allowing both to > commit? I must be confused What if there's no row to update? Process A Process B UPDATE .. NOT FOUND UPDATE .. NOT FOUND INSERT INSERT blocks COMMIT UNIQUE_VIOLATION That's assuming that there's a unique index. If there isn't one, you'd get duplicate records. > > I think that the UNIQUE_VIOLATION case should be safe, since a second > > inserter should block if there's another insert that's waiting to > > commit. > > Are you saying that inserts inside of an EXCEPTION block, but normal > inserts don't? No... if there's a unique index, a second INSERT attempting to create a duplicate record will block until the first INSERT etiher commits or rollsback. > > DELETEs are something else to think about for both cases. > > I only do one delete and that is every night when I move the data to the > primary table and remove that days worth of data from the tmp table. > This is done at non-peak times with a vacuum, so I think I'm good here. Except that you might still have someone fire off that function while the delete's running, or vice-versa. So there could be a race condition (I haven't thought enough about what race conditions that could cause). > > If you're certain that only one process will be performing DML on those > > tables at any given time, then what you have is safe. But if that's the > > case, I'm thinking you should be able to group things into chunks, which > > should be more efficient. > > Yea, I wish I could, but I really need to do one at a time because of > how radius waits for a successful query before telling the access server > all is well. If the query fails, the access server won't get the 'OK' > packet and will send the data to the secondary radius system where it > gets queued. In that case, the key is to do the absolute smallest amount of work possible as part of that transaction. Ideally, you would only insert a record into a queue table somewhere, and then periodically process records out of that table in batches. > Do you know of a way to see what is going on with the locking system > other than "select * from pg_locks"? I can't ever seem to catch the > system when queries start to lag. No. Your best bet is to open two psql sessions and step through things in different combinations (make sure and do this in transactions). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] slow queue-like empty table
I have a query which really should be lightning fast (limit 1 from index), but which isn't. I've checked the pg_locks table, there are no locks on the table. The database is not under heavy load at the moment, but the query seems to draw CPU power. I checked the pg_locks view, but found nothing locking the table. It's a queue-like table, lots of rows beeing added and removed to the queue. The queue is currently empty. Have a look: NBET=> vacuum verbose analyze my_queue; INFO: vacuuming "public.my_queue" INFO: index "my_queue_pkey" now contains 34058 row versions in 390 pages DETAIL: 288 index pages have been deleted, 285 are current ly reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "my_queue": found 0 removable, 34058 nonremovable row versions in 185 pages DETAIL: 34058 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.my_queue" INFO: "my_queue": scanned 185 of 185 pages, containing 0 live rows and 34058 dead rows; 0 rows in sample, 0 estimated total rows VACUUM NBET=> explain analyze select bet_id from my_queue order by bet_id limit 1; QUERY PLAN --- Limit (cost=0.00..0.04 rows=1 width=4) (actual time=402.525..402.525 rows=0 loops=1) -> Index Scan using my_queue_pkey on stats_bet_queue (cost=0.00..1314.71 rows=34058 width=4) (actual time=402.518..402.518 rows=0 loops=1) Total runtime: 402.560 ms (3 rows) NBET=> select count(*) from my_queue; count --- 0 (1 row) It really seems like some transaction is still viewing the queue, since it found 38k of non-removable rows ... but how do I find the pid of the transaction viewing the queue? As said, the pg_locks didn't give me any hints ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
