Re: [PERFORM] Planning hot/live backups?
Steve Poe wrote: The owners of the animal hospital where I work at want to consider live/hot backups through out the day so we're less likely to lose a whole day of transaction. We use Postgresql 8.0.15. We do 3AM backups, using pg_dumpall, to a file when there is very little activity. You probably want to look into PITR, you can have a constant ongoing backup of your data and never lose more than a few minutes of data. The overhead isn't all the big especially if you are shipping the log files to a separate server. - 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] Planning hot/live backups?
Steve Poe wrote: At this point, I am just moving the pg_dumpall file to another server. Pardon my question: how would you 'ship the log files'? [ You should cc the mailing list so that everyone can benefit from the conversation. ] RTM: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html - 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] GiST indexing tuples
Matthew wrote: For instance, the normal B-tree index on (a, b) is able to answer queries like a = 5 AND b 1 or a 5. An R-tree would be able to index these, plus queries like a 5 AND b 1. Sorry in advance if this is a stupid question, but how is this better than two index, one on a and one on b? I supposed there could be a space savings but beyond that? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Autoanalyze settings with zero scale factor
Jeremy Haile wrote: I changed the table-specific settings so that the ANALYZE base threshold was 5000 and the ANALYZE scale factor is 0. According to the documented formula: analyze threshold = analyze base threshold + analyze scale factor * number of tuples, I assumed that this would cause the table to be analyzed everytime 5000 tuples were inserted/updated/deleted. That is right, and exactly how the scaling factor / base value are supposed to work, so this should be fine. However, the tables have been updated with tens of thousands of inserts and the table has still not been analyzed (according to pg_stat_user_tables). Does a scale factor of 0 cause the table to never be analyzed? What am I doing wrong? I'm using PG 8.2.1. No a scaling factor of 0 shouldn't stop the table from being analyzed. Unless it's just a bug, my only guess is that autovacuum may be getting busy at times (vacuuming large tables for example) and hasn't had a chance to even look at that table for a while, and by the time it gets to it, there have been tens of thousands of inserts. Does that sounds plausible? Also, are other auto-vacuums and auto-analyzes showing up in the pg_stats table? Maybe it's a stats system issue. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Autoanalyze settings with zero scale factor
Jeremy Haile wrote: Also, are other auto-vacuums and auto-analyzes showing up in the pg_stats table? Maybe it's a stats system issue. No tables have been vacuumed or analyzed today. I had thought that this problem was due to my pg_autovacuum changes, but perhaps not. I restarted PostgreSQL (in production - yikes) About a minute after being restarted, the autovac process fired up. What could get PG in a state where autovac isn't running? Is there anything I should watch to debug or monitor for this problem in the future? I wish I'd noticed whether or not the stats collector process was running before I restarted. First off you shouldn't need to restart PG. When it wasn't working did you ever check the autovacuum_enabled setting? For example within psql: show autovacuum;. I would venture to guess that autovacuum was disabled for some reason. Perhaps last time you started the server the stats settings weren't enabled? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Is Vacuum/analyze destroying my performance?
Carlo Stonebanks wrote: Just a wild guess, but the performance problem sounds like maybe as your data changes, eventually the planner moves some query from an index scan to a sequential scan, do you have any details on what queries are taking so long when things are running slow? You can turn on the GUC var log_min_duration_statement and see what queries are slow and then manually check them with an explain analyze, that might help. This is pretty well what I think is happening - I expect all queries to eventually move from seq scans to index scans. I actually have a SQL logging opion built into the import app. I just can't figure out how the planner can be so wrong. We are running a 4 CPU server (two dual core 3.2 GHz Xeons) with 4GB RAM and Windows Server 2003 x64 and a PERC RAID subsystem (I don't know the RAID type). I know that the metrics for the planner can be changed - is the default config for postgesql not suitable for our setup? For this server, we would like to be optimised for high speed over a few connections, rather than the classic balanced speed over many connections. If it is the planner choosing a very bad plan, then I don't think your hardware has anything to do with it. And, we can't diagnose why the planner is doing what it's doing without a lot more detail. I suggest you do something to figure out what queries are taking so long, then send us an explain analyze, that might shine some light on the subject. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] [PERFORM] autovacuum on a -mostly- r/o table
Tobias Brox wrote: [Matthew T. O'Connor - Wed at 02:33:10PM -0400] 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. I was experimenting a bit with autovacuum now. To make the least effect possible, I started with a too high cost_delay/cost_limit-ratio. The effect of this was that autovacuum never finished the transactions it started with, and this was actually causing the nightly vacuum to not do it's job good enough. Yeah, I think if the delay settings are too high it can cause problems, that's part of the reason we have yet to turn these on be default since we won't have enough data to suggest good values. Can you tell us what settings you finally settled on? BTW hopefully for 8.3 we are going to add the concept of maintenance windows to autovacuum, during these periods you can lower the thresholds and perhaps even change the delay settings to make autovacuum more aggressive during the maintenance window. This hopefully will just about eliminate the need for nightly cron based vacuum runs. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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
Re: [PERFORM] count(*) performance
Gábriel Ákos wrote: Luke Lonergan wrote: Gabriel, On 3/27/06 10:05 AM, Gábriel Ákos [EMAIL PROTECTED] wrote: That gave me an idea. I thought that autovacuum is doing it right, but I issued a vacuum full analyze verbose , and it worked all the day. After that I've tweaked memory settings a bit too (more fsm_pages) Oops! I replied to your disk speed before I saw this. The only thing is - you probably don't want to do a vacuum full, but rather a simple vacuum should be enough. I thought that too. Autovacuum is running on our system but it didn't do the trick. Anyway the issue is solved, thank you all for helping. :) Yeah, it would be nice of autovacuum had some way of raising a flag to the admin that given current settings (thresholds, FSM etc...), it's not keeping up with the activity. I don't know how to do this, but I hope someone else has some good ideas. Matt ---(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] count(*) performance
Mikael Carneholm wrote: This is where a last_vacuumed (and last_analyzed) column in pg_statistic(?) would come in handy. Each time vacuum or analyze has finished, update the row for the specific table that was vacuumed/analyzed with a timestamp in the last_vacuumed/last_analyzed column. No more guessing maybe I haven't vacuumed/analyzed in a while, and each time a user complains about bad performance, one could request the user to do a select s.last_vacuumed, s.last_analyzed from pg_statistic s, pg_attribute a, pg_class c where ... It SOUNDS easy to implement, but that has fooled me before... :-) It is fairly easy to implement, however it has been discussed before and decided that it wasn't necessary. What the system cares about is how long it's been since the last vacuum in terms of XIDs not time. Storing a timestamp would make it more human readable, but I'm not sure the powers that be want to add two new columns to some system table to accommodate this. Matt ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] n00b autovacuum question
More detail please. It sounds like you running 8.1 and talking about the integrated autovacuum is that correct? Also, what is the message specifically from pgadmin? Matt Antoine wrote: Hi, I have enabled the autovacuum daemon, but occasionally still get a message telling me I need to run vacuum when I access a table in pgadmin. Is this normal? Should I use scripts instead of the daemon? Would posting config options make this a much more sensible question? Cheers Antoine -- This is where I should put some witty comment. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 10+hrs vs 15min because of just one index
Aaron Turner wrote: So I'm trying to figure out how to optimize my PG install (8.0.3) to get better performance without dropping one of my indexes. What about something like this: begin; drop slow_index_name; update; create index slow_index_name; commit; vacuum; Matt ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Default autovacuum settings too conservative
Jim C. Nasby wrote: Small tables are most likely to have either very few updates (ie: a 'lookup table') or very frequent updates (ie: a table implementing a queue). In the former, even with vacuum_threshold = 0 vacuum will be a very rare occurance. In the later case, a high threshold is likely to cause a large amount of un-nececcasry bloat. Well a threshold of 0 won't work because then a 0 tuple table will get vacuumed every time. Or at least autovacuum needs to special case this. Also, vacuum_scale_factor of 0.4 seems unreasonably large. It means tables will be 40% dead space, which seems excessively wasteful. Something between 0.1 and 0.2 seems much better. Depends on the app and the usage patterns as to what too much slack space is. Has anyone looked at how effective these two settings are? As far I as I know, we are still looking for real world feedback. 8.1 is the first release to have the integrated autovacuum. The thresholds in 8.1 are a good bit less conservative than the thresholds in the contrib version. The contrib thresholds were universally considered WAY to conservative, but that was somewhat necessary since you couldn't set them on a per table basis as you can in 8.1. If we continue to hear from people that the current 8.1 default thresholds are still to conservative we can look into lowering them. I think the default settings should be designed to minimize the impact autovacuum has on the system while preventing the system from ever getting wildly bloated (also protect xid wraparound, but that doesn't have anything to do with the thresholds). Matt ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 -
Tom Lane wrote: Robert Creager [EMAIL PROTECTED] writes: I've vacuum_cost_delay = 10 in the conf file for 803. Hmm, did you read this thread? http://archives.postgresql.org/pgsql-performance/2005-07/msg00088.php It's still far from clear what's going on there, but it might be interesting to see if turning off the vacuum delay changes your results with 8.0. With the contrib autovacuum code if you don't specify vacuum delay settings from the command line, then autovacuum doesn't touch them. Therefore (if you aren't specifying them from the command line), on 803, the vacuum delay settings should be the same for a cron issued vacuum and an autovacuum issued vacuum. So if the vacuum delay settings are the problem, then it should show up either way. Matt ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 -
Robert Creager wrote: For 8.03, pg_autovacuum is running. On 7.4.1, I set up a cron job to vacuum analyze every 5 minutes. Are you sure that pg_autovacuum is doing it's job? Meaning are you sure it's vacuuming as often as needed? Try to run it with -d2 or so and make sure that it is actually doing the vacuuming needed. ---(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] Huge performance problem between 7.4.1 and 8.0.3 -
Ok, it doesn't look like an autovacuum problem. The only other thing I can think of is that some query is doing a seq scan rather than an index scan. Have you turned on the query logging to see what queries are taking so long? Matt Robert Creager wrote: I am, and it is. It's ANALYZING and VACUUM'ing tables every interval (5 minutes - 8.0.3). Right now, for that last 4 hours, I'm not VACUUMing the 7.4.1 database and it's still clicking along at .2 second queries. Last year (7.4.1), I noticed that it took about a week of heavy activity (for this DB) before I'd really need a vacuum. That's when I put in the 5 min cron. When I first switched over to 8.0.3, I was still running the cron vacuum. I got into big trouble when I had vacuum's backed up for 6 hours. That's when I started noticing the query problem, and the CS numbers being high. 7.4.1 vacuums every 5 minutes always take 30 seconds (when I'm watching). Cheers, Rob When grilled further on (Sun, 17 Jul 2005 23:48:20 -0400), Matthew T. O'Connor matthew@zeut.net confessed: Robert Creager wrote: For 8.03, pg_autovacuum is running. On 7.4.1, I set up a cron job to vacuum analyze every 5 minutes. Are you sure that pg_autovacuum is doing it's job? Meaning are you sure it's vacuuming as often as needed? Try to run it with -d2 or so and make sure that it is actually doing the vacuuming needed. ---(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] How to avoid database bloat
Mindaugas Riauba wrote: Might e aggressive enough, but might not. I have seen some people run -V 0.1. Also you probably don't need -A that low. This could an issue where analyze results in an inaccurate reltuples value which is preventing autovacuum from doing it's job. Could you please run it with -d 2 and show us the relevant log output. Relevant parts are below. And we had to set so aggressive analyze because otherwise planer statistics were getting old too fast. As I said table has very high turnover most of the records live here only for a few seconds. Looked like pg_autovacuum is operating as expected. One of the annoying limitations of pg_autovacuum in current releases is that you can't set thresholds on a per table basis. It looks like this table might require an even more aggressive vacuum threshold. Couple of thoughts, are you sure it's the table that is growing and not the indexes? (assuming this table has indexes on it). And one more question - anyway why table keeps growing? It is shown that it occupies 1 pages and max_fsm_pages = 20 so vacuum should keep up with the changes? Or is it too low according to pg_class system table? What should be the reasonable value? Does the table keep growing? Or does it grow to a point an then stop growing? It's normal for a table to operate at a steady state size that is bigger that it's fresly vacuum full'd size. And with -V set at 0.5 it should be at a minimum 50% larger than it's minimum size. Your email before said that this table went from 20M to 70M but does it keep going? Perhaps it would start leveling off at this point, or some point shortly there-after. Anyway, I'm not sure if there is something else going on here, but from the log it looks as though pg_autovacuum is working as advertised. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] How to avoid database bloat
Mindaugas Riauba wrote: AFAICT the vacuum is doing what it is supposed to, and the problem has to be just that it's not being done often enough. Which suggests either an autovacuum bug or your autovacuum settings aren't aggressive enough. -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10 That is autovacuum settings. Should be aggressive enough I think? Might e aggressive enough, but might not. I have seen some people run -V 0.1. Also you probably don't need -A that low. This could an issue where analyze results in an inaccurate reltuples value which is preventing autovacuum from doing it's job. Could you please run it with -d 2 and show us the relevant log output. Which PG version is this exactly? Some of the earlier autovacuum releases do have known bugs, so it'd be worth your while to update if you're not on the latest point release of your series. 8.0.3 That should be fine. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL strugling during high load
Mindaugas Riauba wrote: The vacuum cost parameters can be adjusted to make vacuums fired by pg_autovacuum less of a burden. I haven't got any specific numbers to suggest, but perhaps someone else does. It looks like that not only vacuum causes our problems. vacuum_cost seems to lower vacuum impact but we are still noticing slow queries storm. We are logging queries that takes 2000ms to process. And there is quiet periods and then suddenly 30+ slow queries appears in log within the same second. What else could cause such behaviour? WAL log switch? One WAL file seems to last 1 minute. How long are these quite periods? Do the strom periods correspond to pg_autovacuum loops? I have heard from one person who had LOTS of databases and tables that caused the pg_autovacuum to create a noticable load just updateing all its stats. The solution in that case was to add a small delay insidet the inner pg_autovacuum loop. ---(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] pg_autovacuum not having enough suction ?
Well the simple answer is that pg_autovacuum didn't see 10,000 inserts updates or deletes. pg_autovacuum saw:476095 - 471336 = 4759 U/D's relevant for vacuuming and 634119 - 629121 = 4998 I/U/D's relevant for performing analyze. The tough question is why is pg_autovacuum not seeing all the updates. Since autovacuum depends on the stats system for it's numbers, the most likely answer is that the stats system is not able to keep up with the workload, and is ignoring some of the updates. Would you check to see what the stats system is reporting for numbers of I/U/D's for the file_92 table? The query pg_autovacuum uses is: select a.oid,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples, b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.oid=b.relid and a.relkind = 'r' Take a look at the n_tup_ins, upd, del numbers before and see if they are keeping up with the actual number if I/U/D's that you are performing. If they are, then it's a pg_autovacuum problem that I will look into further, if they are not, then it's a stats system problem that I can't really help with. Good luck, Matthew Otto Blomqvist wrote: Hello ! I'm running pg_autovacuum on a 1GHz, 80Gig, 512Mhz machine. The database is about 30MB tarred. We have about 5 Updates/Inserts/Deletes per day. It runs beautifully for ~4 days. Then the HDD activity and the Postmaster CPU usage goes up ALOT. Even though I have plenty (?) of FSM (2 million) pages. I perform a vacuum and everything is back to normal for another 4 days. I could schedule a manual vacuum each day but the util is not called pg_SemiAutoVacuum so I'm hoping this is not necessary. The same user that ran the manual vacuum is running pg_autovacuum. The normal CPU usage is about 10% w/ little HD activity. Im running autovacuum with the following flags -d 3 -v 300 -V 0.1 -s 180 -S 0.1 -a 200 -A 0.1 Below are some snipplets regarding vacuuming from the busiest table This is the last VACUUM ANALYZE performed by pg_autovacuum before I ran the manual vacuum [2005-03-24 02:05:43 EST] DEBUG:Performing: VACUUM ANALYZE public.file_92 [2005-03-24 02:05:52 EST] INFO: table name: secom.public.file_92 [2005-03-24 02:05:52 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-24 02:05:52 EST] INFO: reltuples: 106228.00; relpages: 9131 [2005-03-24 02:05:52 EST] INFO: curr_analyze_count: 629121; curr_vacuum_count: 471336 [2005-03-24 02:05:52 EST] INFO: last_analyze_count: 629121; last_vacuum_count: 471336 [2005-03-24 02:05:52 EST] INFO: analyze_threshold: 10822; vacuum_threshold: 10922 This is the last pg_autovacuum debug output before I ran the manual vacuum [2005-03-24 09:18:44 EST] INFO: table name: secom.public.file_92 [2005-03-24 09:18:44 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-24 09:18:44 EST] INFO: reltuples: 106228.00; relpages: 9131 [2005-03-24 09:18:44 EST] INFO: curr_analyze_count: 634119; curr_vacuum_count: 476095 [2005-03-24 09:18:44 EST] INFO: last_analyze_count: 629121; last_vacuum_count: 471336 [2005-03-24 09:18:44 EST] INFO: analyze_threshold: 10822; vacuum_threshold: 10922 file_92 had about 1 Inserts/Deletes between 02:05 and 9:20 Then i Ran a vacuum verbose 23 Mar 05 - 9:20 AM INFO: vacuuming public.file_92 INFO: index file_92_record_number_key now contains 94 row versions in 2720 pages DETAIL: 107860 index row versions were removed. 2712 index pages have been deleted, 2060 are currently reusable. CPU 0.22s/0.64u sec elapsed 8.45 sec. INFO: file_92: removed 107860 row versions in 9131 pages DETAIL: CPU 1.13s/4.27u sec elapsed 11.75 sec. INFO: file_92: found 107860 removable, 92 nonremovable row versions in 9131 pages DETAIL: 91 dead row versions cannot be removed yet. There were 303086 unused item pointers. 0 pages are entirely empty. CPU 1.55s/5.00u sec elapsed 20.86 sec. INFO: file_92: truncated 9131 to 8423 pages DETAIL: CPU 0.65s/0.03u sec elapsed 5.80 sec. INFO: free space map: 57 relations, 34892 pages stored; 34464 total pages needed DETAIL: Allocated FSM size: 1000 relations + 200 pages = 11784 kB shared memory. Also, file_92 is just a temporary storage area, for records waiting to be processed. Records are in there typically ~10 sec. Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could autovacuum let this happen ? I would estimate the table had about 1 inserts/deletes between the last pg_autovacuum Vacuum analyze and my manual vacuum verbose. It is like the suction is not strong enough ;) Any ideas ? It would be greatly appreciated as this is taking me one step closer to the looney bin. Thanks /Otto Blomqvist ---(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] pg_autovacuum not having enough suction ?
hmm the value in reltuples should be accurate after a vacuum (or vacuum analyze) if it's not it's a vacuum bug or something is going on that isn't understood. If you or pg_autovacuum are running plain analyze commands, that could explain the invalid reltules numbers. Was reltuples = 113082 correct right after the vacuum? Matthew Otto Blomqvist wrote: It looks like the reltuples-values are screwed up. Even though rows are constantly being removed from the table the reltuples keep going up. If I understand correctly that also makes the Vacuum threshold go up and we end up in a vicious circle. Right after pg_autovacuum performed a vacuum analyze on the table it actually had 31000 records, but reltuples reports over 100k. I'm not sure if this means anything But i thought i would pass it along. PG version 8.0.0, 31MB tarred DB. [2005-03-25 09:16:14 EST] INFO:dbname: testing [2005-03-25 09:16:14 EST] INFO: oid: 9383816 [2005-03-25 09:16:14 EST] INFO: username: (null) [2005-03-25 09:16:14 EST] INFO: password: (null) [2005-03-25 09:16:14 EST] INFO: conn is null, (not connected) [2005-03-25 09:16:14 EST] INFO: default_analyze_threshold: 1000 [2005-03-25 09:16:14 EST] INFO: default_vacuum_threshold: 500 [2005-03-25 09:05:12 EST] INFO: table name: secom.public.file_92 [2005-03-25 09:05:12 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-25 09:05:12 EST] INFO: reltuples: 49185.00; relpages: 8423 [2005-03-25 09:05:12 EST] INFO: curr_analyze_count: 919274; curr_vacuum_count: 658176 [2005-03-25 09:05:12 EST] INFO: last_analyze_count: 899272; last_vacuum_count: 560541 [2005-03-25 09:05:12 EST] INFO: analyze_threshold: 49685; vacuum_threshold: 100674 [2005-03-25 09:10:12 EST] DEBUG: Performing: VACUUM ANALYZE public.file_92 [2005-03-25 09:10:33 EST] INFO: table name: secom.public.file_92 [2005-03-25 09:10:33 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-25 09:10:33 EST] INFO: reltuples: 113082.00; relpages: 6624 [2005-03-25 09:10:33 EST] INFO: curr_analyze_count: 923820; curr_vacuum_count: 662699 [2005-03-25 09:10:33 EST] INFO: last_analyze_count: 923820; last_vacuum_count: 662699 [2005-03-25 09:10:33 EST] INFO: analyze_threshold: 113582; vacuum_threshold: 227164 [2005-03-25 09:16:14 EST] INFO: table name: secom.public.file_92 [2005-03-25 09:16:14 EST] INFO: relid: 9384219; relisshared: 0 [2005-03-25 09:16:14 EST] INFO: reltuples: 113082.00; relpages: 6624 -- Actually has 31k rows [2005-03-25 09:16:14 EST] INFO: curr_analyze_count: 923820; curr_vacuum_count: 662699 [2005-03-25 09:16:14 EST] INFO: last_analyze_count: 923820; last_vacuum_count: 662699 [2005-03-25 09:16:14 EST] INFO: analyze_threshold: 113582; vacuum_threshold: 227164 DETAIL: Allocated FSM size: 1000 relations + 200 pages = 11784 kB shared memory. - Original Message - From: Matthew T. O'Connor matthew@zeut.net To: Otto Blomqvist [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Thursday, March 24, 2005 3:58 PM Subject: Re: [PERFORM] pg_autovacuum not having enough suction ? I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: It does not seem to be a Stats collector problem. oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del -+-+--+--+-+---+--- - +---+---+--- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public |158176 |318527 |158176 (1 row) I insert 5 records secom=# select createfile_92records(1, 5);--- this is a pg script that inserts records 1 threw 5. createfile_92records -- 0 oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del -+-+--+--+-+---+--- - +---+---+--- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public |208179 |318932 |158377 (1 row) reltuples does not change ? Hmm. n_tup_ins looks fine. That is expected, reltuples only gets updated by a vacuum or an analyze. This table is basically a queue full of records waiting to get transfered over from our 68030 system to the PG database. The records are then moved into folders (using a trigger) like file_92_myy depending on what month the record was created on the 68030. During normal operations there should not be more than 10 records at a time in the table, although during the course of a day a normal system will get about 50k records. I create 5 records to simulate incoming traffic, since we
Re: [PERFORM] pg_autovacuum not having enough suction ?
Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: hmm the value in reltuples should be accurate after a vacuum (or vacuum analyze) if it's not it's a vacuum bug or something is going on that isn't understood. If you or pg_autovacuum are running plain analyze commands, that could explain the invalid reltules numbers. Was reltuples = 113082 correct right after the vacuum? Another thing to check is whether the reltuples (and relpages!) that autovacuum is reporting are the same as what's actually in the pg_class row for the relation. I'm wondering if this could be a similar issue to the old autovac bug where it wasn't reading the value correctly. I don't think so, as he did some manual selects from pg_class and pg_stat_all in one of the emails he sent that were showing similar numbers to what autovac was reporting. If they are the same then it seems like it must be a backend issue. One thing that is possibly relevant here is that in 8.0 a plain VACUUM doesn't set reltuples to the exactly correct number, but to an interpolated value that reflects our estimate of the steady state average between vacuums. I wonder if that code is wrong, or if it's operating as designed but is confusing autovac. Ahh Now that you mention it, I do remember the discussion during 8.0 development. This sounds very much like the cause of the problem. Autovac is not vacuuming often enough for this table because reltuples is telling autovac that there are alot more tuples in this table than there really are. Really this is just another case of the more general problem with autovac as it stands now. That is, you can't set vacuum thresholds on a per table basis, and databases like this can't survive with a one size fits all threshold. I would suggest that Otto perform regular cron based vacuums of this one table in addition to autovac, that is what several people I have heard from in the field are doing. Come hell or high water I'm gonna get autovac integrated into 8.1, at which point per table thresholds would be easy todo. Can autovac be told to run the vacuums in VERBOSE mode? It would be useful to compare what VERBOSE has to say to the changes in reltuples/relpages. Not as it stands now. That would be an interesting feature for debugging purposes though. ---(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: [HACKERS] lazy_update_relstats considered harmful (was Re: [PERFORM]
Tom Lane wrote: I wrote: One thing that is possibly relevant here is that in 8.0 a plain VACUUM doesn't set reltuples to the exactly correct number, but to an interpolated value that reflects our estimate of the steady state average between vacuums. I wonder if that code is wrong, or if it's operating as designed but is confusing autovac. Now that I think it over, I'm thinking that I must have been suffering severe brain fade the day I wrote lazy_update_relstats() (see vacuumlazy.c). The numbers that that routine is averaging are the pre- and post-vacuum physical tuple counts. But the difference between them consists of known-dead tuples, and we shouldn't be factoring dead tuples into reltuples. The planner has always considered reltuples to count only live tuples, and I think this is correct on two grounds: 1. The numbers of tuples estimated to be returned by scans certainly shouldn't count dead ones. 2. Dead tuples don't have that much influence on scan costs either, at least not once they are marked as known-dead. Certainly they shouldn't be charged at full freight. It's possible that there'd be some value in adding a column to pg_class to record dead tuple count, but given what we have now, the calculation in lazy_update_relstats is totally wrong. The idea I was trying to capture is that the tuple density is at a minimum right after VACUUM, and will increase as free space is filled in until the next VACUUM, so that recording the exact tuple count underestimates the number of tuples that will be seen on-the-average. But I'm not sure that idea really holds water. The only way that a table can be at steady state over a long period is if the number of live tuples remains roughly constant (ie, inserts balance deletes). What actually increases and decreases over a VACUUM cycle is the density of *dead* tuples ... but per the above arguments this isn't something we should adjust reltuples for. So I'm thinking lazy_update_relstats should be ripped out and we should go back to recording just the actual stats. Sound reasonable? Or was I right the first time and suffering brain fade today? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] pg_autovacuum not having enough suction ?
The version that shipped with 8.0 should be fine. The only version that had the problem Tom referred to are in the early 7.4.x releases. Did you get my other message about information from the stats system (I'm not sure why my other post has yet to show up on the performance list). Matthew Otto Blomqvist wrote: Sorry about that. I'm Running 8.0.0 on Linux Redhat 8.0 Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Otto Blomqvist [EMAIL PROTECTED] writes: Over 100'000 Index Rows removed, 300'000 unused item pointers ? How could autovacuum let this happen ? What PG version is this? (The earlier autovacuum releases had some bugs with large tables, thus the question...) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] pg_autovacuum not having enough suction ?
I would rather keep this on list since other people can chime in. Otto Blomqvist wrote: It does not seem to be a Stats collector problem. oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del -+-+--+--+-+---+ +---+---+--- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public |158176 |318527 |158176 (1 row) I insert 5 records secom=# select createfile_92records(1, 5);--- this is a pg script that inserts records 1 threw 5. createfile_92records -- 0 oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del -+-+--+--+-+---+ +---+---+--- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public |208179 |318932 |158377 (1 row) reltuples does not change ? Hmm. n_tup_ins looks fine. That is expected, reltuples only gets updated by a vacuum or an analyze. This table is basically a queue full of records waiting to get transfered over from our 68030 system to the PG database. The records are then moved into folders (using a trigger) like file_92_myy depending on what month the record was created on the 68030. During normal operations there should not be more than 10 records at a time in the table, although during the course of a day a normal system will get about 50k records. I create 5 records to simulate incoming traffic, since we don't have much traffic in the test lab. After a few hours we have secom=# select count(*) from file_92; count --- 42072 So we have sent over approx 8000 Records. oid | relname | relnamespace | relpages | relisshared | reltuples | schemaname | n_tup_ins | n_tup_upd | n_tup_del -+-+--+--+-+---+ +---+---+--- 9384219 | file_92 | 2200 | 8423 | f | 49837 | public |208218 |334521 |166152 (1 row) n_tup_upd: 318932 + (5-42072)*2 = 334788 pretty close. (Each record gets updated twice, then moved) n_tup_del: 158377 + (5-42072) = 166305 pretty close. (there are also minor background traffic going on) I could send over the full vacuum verbose capture as well as the autovacuum capture if that is of interest. That might be helpful. I don't see a stats system problem here, but I also haven't heard of any autovac problems recently, so this might be something new. Thanks, Matthew O'Connor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] is pg_autovacuum so effective ?
Gaetano Mendola wrote: pg_class after the vacuum full for that table relfilenode | relname | relpages | reltuples -+--+--+- 18376 | messages |63307 | 1.60644e+06 pg_class before the vacuum full for that table relfilenode | relname | relpages | reltuples -+--+--+- 18376 | messages |69472 | 1.60644e+06 how was possible accumulate 6000 pages wasted on that table? Between these two calls: [2005-02-22 05:25:03 CET] Performing: VACUUM ANALYZE public.messages [2005-02-22 15:20:39 CET] Performing: VACUUM ANALYZE public.messages 1768 rows where inserted, and I had 21578 updated for that rows ( each row have a counter incremented for each update ) so that table is not so heavy updated I'm running autovacuum with these parameters: pg_autovacuum -d 3 -v 300 -V 0.1 -S 0.8 -a 200 -A 0.1 -D shall I run it in a more aggressive way ? May be I'm missing something. Well without thinking too much, I would first ask about your FSM settings? If they aren't big enought that will cause bloat. Try bumping your FSM settings and then see if you reach steady state. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [PERFORM] Assimilation of these versus and hardware
Josh Berkus wrote: Matt, I had one comment on the pg_autovacuum section. Near the bottom it lists some of it's limitations, and I want to clarify the 1st one: Does not reset the transaction counter. I assume this is talking about the xid wraparound problem? If so, then that bullet can be removed. pg_autovacuum does check for xid wraparound and perform a database wide vacuum analyze when it's needed. Keen. That's an 8.0 fix? Nope, been there since before 7.4 was released. ---(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] query plan question
David Parker wrote: We're using postgresql 7.4.5. I've only recently put pg_autovacuum in place as part of our installation, and I'm basically taking the defaults. I doubt it's a problem with autovacuum itself, but rather with my configuration of it. I have some reading to do, so any pointers to existing autovacuum threads would be greatly appreciated! Well the first thing to do is increase the verbosity of the pg_autovacuum logging output. If you use -d2 or higher, pg_autovacuum will print out a lot of detail on what it thinks the thresholds are and why it is or isn't performing vacuums and analyzes. Attach some of the log and I'll take a look at it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] query plan question
Well based on the autovacuum log that you attached, all of those tables
are insert only (at least during the time period included in the log.
Is that correct? If so, autovacuum will never do a vacuum (unless
required by xid wraparound issues) on those tables. So this doesn't
appear to be an autovacuum problem. I'm not sure about the missing
pg_statistic entries anyone else care to field that one?
Matthew
David Parker wrote:
Thanks. The tables I'm concerned with are named: 'schema', 'usage',
'usageparameter', and 'flow'. It looks like autovacuum is performing
analyzes:
% grep Performing: logs/.db.tazz.vacuum.log
[2004-11-17 12:05:58 PM] Performing: ANALYZE
public.scriptlibrary_library
[2004-11-17 12:15:59 PM] Performing: ANALYZE
public.scriptlibraryparm
[2004-11-17 12:15:59 PM] Performing: ANALYZE public.usageparameter
[2004-11-17 12:21:00 PM] Performing: ANALYZE public.usageproperty
[2004-11-17 12:21:00 PM] Performing: ANALYZE public.route
[2004-11-17 12:21:00 PM] Performing: ANALYZE public.usageparameter
[2004-11-17 12:21:00 PM] Performing: ANALYZE
public.scriptlibrary_library
[2004-11-17 12:26:01 PM] Performing: ANALYZE public.usage
[2004-11-17 12:26:01 PM] Performing: ANALYZE public.usageparameter
[2004-11-17 12:31:04 PM] Performing: ANALYZE public.usageproperty
[2004-11-17 12:36:04 PM] Performing: ANALYZE public.route
[2004-11-17 12:36:04 PM] Performing: ANALYZE public.service_usage
[2004-11-17 12:36:04 PM] Performing: ANALYZE public.usageparameter
But when I run the following:
select * from pg_statistic where starelid in
(select oid from pg_class where relname in
('schema','usageparameter','flow','usage'))
it returns no records. Shouldn't it? It doesn't appear to be doing a
vacuum anywhere, which makes sense because none of these tables have
over the default threshold of 1000. Are there statistics which only get
generated by vacuum?
I've attached a gzip of the pg_autovacuum log file, with -d 3.
Thanks again.
- DAP
-Original Message-
From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 17, 2004 11:41 AM
To: David Parker
Cc: Tom Lane; Jeff; Russell Smith; [EMAIL PROTECTED]
Subject: Re: [PERFORM] query plan question
David Parker wrote:
We're using postgresql 7.4.5. I've only recently put pg_autovacuum in
place as part of our installation, and I'm basically taking the
defaults. I doubt it's a problem with autovacuum itself, but rather
with my configuration of it. I have some reading to do, so
any pointers
to existing autovacuum threads would be greatly appreciated!
Well the first thing to do is increase the verbosity of the
pg_autovacuum logging output. If you use -d2 or higher,
pg_autovacuum will print out a lot of detail on what it thinks
the thresholds are and
why it is or isn't performing vacuums and analyzes. Attach
some of the
log and I'll take a look at it.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] What is the difference between these?
Matt Nuzum wrote:
To me, these three queries seem identical... why doesn't the first one
(simplest to understand and write) go the same speed as the third one?
If you look at the explain output, you will notice that only the 3rd
query is using an Index Scan, where as the 1st and 2nd are doing a
sequential scan over the entire table of 25M rows. My guess is that the
problem is related to outdated statistics on the atime column. If you
notice the 1st and 2nd queries estimate 8.4M rows returned at which
point a seq scan is the right choice, but the 3rd query using the
between statement only estimates 127k rows which make the Index a better
option. All of these queries only return 2964 rows so it looks like
your stats are out of date. Try running an analyze command right before
doing any of these queries and see what happens.
I'll I'm trying to do is get statistics for one day (in this case,
today) summarized. Table has ~25M rows. I'm using postgres 7.3.? on
rh linux 7.3 (note that i think the difference between the first two
might just be related to the data being in memory for the second
query).
EXPLAIN ANALYZE
select count(distinct sessionid) from usage_access where atime
date_trunc('day', now());
QUERY PLAN
--
Aggregate (cost=933439.69..933439.69 rows=1 width=4) (actual
time=580350.43..580350.43 rows=1 loops=1)
- Seq Scan on usage_access (cost=0.00..912400.11 rows=8415831
width=4) (actual time=580164.48..580342.21 rows=2964 loops=1)
Filter: (atime date_trunc('day'::text, now()))
Total runtime: 580350.65 msec
(4 rows)
EXPLAIN ANALYZE
select count(distinct sessionid) from (select * from usage_access
where atime date_trunc('day', now())) as temp;
QUERY PLAN
--
Aggregate (cost=933439.69..933439.69 rows=1 width=4) (actual
time=348012.85..348012.85 rows=1 loops=1)
- Seq Scan on usage_access (cost=0.00..912400.11 rows=8415831
width=4) (actual time=347960.53..348004.68 rows=2964 loops=1)
Filter: (atime date_trunc('day'::text, now()))
Total runtime: 348013.10 msec
(4 rows)
EXPLAIN ANALYZE
select count(distinct sessionid) from usage_access where atime
between date_trunc('day', now()) and date_trunc('day', now()) + '1
day'::interval;
QUERY PLAN
--
Aggregate (cost=89324.98..89324.98 rows=1 width=4) (actual
time=27.84..27.84 rows=1 loops=1)
- Index Scan using usage_access_atime on usage_access
(cost=0.00..89009.39 rows=126237 width=4) (actual time=0.51..20.37
rows=2964 loops=1)
Index Cond: ((atime = date_trunc('day'::text, now())) AND
(atime = (date_trunc('day'::text, now()) + '1 day'::interval)))
Total runtime: 28.11 msec
(4 rows)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance Anomalies in 7.4.5
Andrew Sullivan wrote: Probably the most severe objection to doing things this way is that the selected plan could change unexpectedly as a result of the physical table size changing. Right now the DBA can keep tight rein on actions that might affect plan selection (ie, VACUUM and ANALYZE), but that would go by the board with this. OTOH, we seem to be moving towards autovacuum, which also takes away any guarantees in this department. But aren't we requiring that we can disable autovacuum on some tables? Yes that is the long term goal, but the autovac in 8.0 is still all or nothing. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] pg_autovacuum parameters
Lending, Rune wrote: Hello all. I am managing a large database with lots of transactions in different tables. The largest tables have around 5-6 millions tuples and around 5-6 inserts and maybe 2 updates pr day. While the smalest tables have only a few tuples and a few updates /inserts pr day. In addition we have small tables with many updates/inserts. So what I am saying is that there is all kinds of tables and uses of tables in our database. This, I think, makes it difficult to set up pg_autovacuum. I am now running vacuum jobs on different tables in cron. What things should I consider when setting but base and threshold values in pg_autovacuum? Since the running of vacuum and analyze is relative to the table size, as it must be, I think it is difficult to cover all tables.. One of the biggest problems with the version of pg_autovacuum in 7.4 contrib is that you can only specify one set of thresholds, which often isn't flexible enough. That said the thresholds are based on table since since you specify both a base value and a scaling factor so pg_autovacuum -v 1000 -V 1 will vacuum a table with 100 rows every 200 updates, but will vacuum a table with 1,000,000 rows every 1,000,100 updates. Are there anyone who have some thoughts around this? Basically, you should be able to use pg_autovacuum to do most of the vacuuming, if there are a few tables that aren't getting vacuumed often enough, then you can add a vacuum command to cron for those specific tables. Matthew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] my boss want to migrate to ORACLE
Stephane Tessier wrote: I think with your help guys I'll do it! I'm working on it! I'll work on theses issues: we have space for more ram(we use 2 gigs on possibility of 3 gigs) iowait is very high 98% -- look like postgresql wait for io access raid5 --raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each write on disk use more transactions (we have a lot of insert/update without transaction). cpu look like not running very hard *php is not running on the same machine *redhat enterprise 3.0 ES *the version of postgresql is 7.3.4(using RHDB from redhat) *pg_autovacuum running at 12 and 24 hour each day What do you mean by pg_autovacuum running at 12 and 24 hour each day? ---(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] Timestamp-based indexing
VACUUM FULL ANALYZE every 3 hours seems a little severe. You will
probably be be served just as well by VACUUM ANALYZE. But you probably
don't need the VACUUM part most of the time. You might try doing an
ANALYZE on the specific tables you are having issues with. Since
ANALYZE should be much quicker and not have the performance impact of a
VACUUM, you could do it every hour, or even every 15 minutes.
Good luck...
Harmon S. Nine wrote:
Hello --
To increase query (i.e. select) performance, we're trying to get
postgres to use an index based on a timestamp column in a given table.
Event-based data is put into this table several times a minute, with
the timestamp indicating when a particular row was placed in the table.
The table is purged daily, retaining only the rows that are less than
7 days old. That is, any row within the table is less than 1 week old
(+ 1 day, since the purge is daily).
A typical number of rows in the table is around 400,000.
A VACUUM FULL ANALYZE is performed every 3 hours.
The problem:
We often query the table to extract those rows that are, say, 10
minutes old or less.
Given there are 10080 minutes per week, the planner could, properly
configured, estimate the number of rows returned by such a query to be:
10 min/ 10080 min * 400,000 = 0.001 * 400,000 = 400.
Making an index scan, with the timestamp field the index, far faster
then a sequential scan.
However, we can't get the planner to do an timestamp-based index scan.
Anyone know what to do?
Here's the table specs:
monitor=# \d eventtable
Table public.eventtable
Column |Type |
Modifiers
---+-+--
timestamp | timestamp without time zone | not null default
('now'::text)::timestamp(6) with time zone
key | bigint | not null default
nextval('public.eventtable_key_seq'::text)
propagate | boolean |
facility | character(10) |
priority | character(10) |
host | character varying(128) | not null
message | text| not null
Indexes:
eventtable_pkey primary key, btree (timestamp, key)
eventtable_host btree (host)
eventtable_timestamp btree (timestamp)
Here's a query (with explain analyze):
monitor=# explain analyze select * from eventtable where timestamp
CURRENT_TIMESTAMP - INTERVAL '10 minutes';
QUERY PLAN
Seq Scan on eventtable (cost=0.00..19009.97 rows=136444 width=155)
(actual time=11071.073..11432.522 rows=821 loops=1)
Filter: ((timestamp)::timestamp with time zone
(('now'::text)::timestamp(6) with time zone - '@ 10 mins'::interval))
Total runtime: 11433.384 ms
(3 rows)
Here's something strange. We try to disable sequential scans, but to
no avail. The estimated cost skyrockets, though:
monitor=# set enable_seqscan = false;
SET
monitor=# explain analyze select * from eventtable where timestamp
CURRENT_TIMESTAMP - INTERVAL '10 minutes';
QUERY PLAN
-
Seq Scan on eventtable (cost=1.00..100019009.97 rows=136444
width=155) (actual time=9909.847..9932.438 rows=1763 loops=1)
Filter: ((timestamp)::timestamp with time zone
(('now'::text)::timestamp(6) with time zone - '@ 10 mins'::interval))
Total runtime: 9934.353 ms
(3 rows)
monitor=# set enable_seqscan = true;
SET
monitor=#
Any help is greatly appreciated :)
-- Harmon
---(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
---(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] [HACKERS] Wrong index choosen?
Gaetano Mendola wrote: Tom Lane wrote: | Given the nature of the data (login times), I'd imagine that the problem | is simply that he hasn't analyzed recently enough. A bump in stats | target may not be needed, but he's going to have to re-analyze that | column often if he wants this sort of query to be estimated accurately, | because the fraction of entries later than a given time T is *always* | going to be changing. Well know that I think about it, I felt my shoulders covered by pg_autovacuum but looking at the log I see that table never analyzed! Aaargh. I already applied the patch for the autovacuum but evidently I have to make it more aggressive, I'm sorry that I can not made him more aggressive only for this table. Yeah, the version of autovacuum in 7.4 contrib doesn't allow table specific settings. The patch I have sumbitted for 7.5 does, so hopefully this will be better in the future. You can however set the VACUUM and ANALYZE thresholds independently. So perhpaps it will help you if you set your ANALYZE setting to be very aggressive and your VACUUM settings to something more standard. Matthew ---(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] [HACKERS] Wrong index choosen?
Gaetano Mendola wrote: Well I think pg_autovacuum as is in 7.4 can not help me for this particular table. The table have 4.8 milions rows and I have for that table almost 10252 new entries for day. I'm using pg_autovacuum with -a 200 -A 0.8 this means a threashold for that table equal to: 3849008 and if I understod well the way pg_autovacuum works this means have an analyze each 375 days, and I need an analyze for each day, at least. So I think is better for me put an analyze for that table in the cron. Am I wrong ? No, I think you are right. You could do something like -a 1000 -A .00185, but that will probably for an analyze too often for most of your other tables. ---(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] Swapping in 7.4.3
Jim Ewert wrote: When I went to 7.4.3 (Slackware 9.1) w/ JDBC, the improvements are that it doesn't initially take much memory (have 512M) and didn't swap. I ran a full vaccum and a cluster before installation, however speed degaded to 1 *second* / update of one row in 150 rows of data, within a day! pg_autovacuum now gives excellent performance however it is taking 66M of swap; only 270k cached. Are you saying that your system stays fast now that you are using pg_autovacuum, but pg_autovacuum is using 66M of memory? Please clarify, I'm not sure what question you want an answered. Matthew ---(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] Avoiding vacuum full on an UPDATE-heavy table
Is there any way to avoid doing a periodic VACUUM FULL on this table, given the fairly radical usage pattern? Or is the (ugly) answer to redesign our application to avoid this usage pattern? Yes, you should be able to doing avoid periodic VACUUM FULL. The problem is that your table needs to be vacuumed MUCH more often. What should happen is that assuming you have enough FSM space allocated and assuming you vacuum the right amount, your table will reach a steady state size. As you could see your from you vacumm verbose output your table was almost entriely dead space. pg_autovacuum would probably help as it monitors activity and vacuumus tables accordingly. It is not included with 7.3.x but if you download it and compile yourself it will work against a 7.3.x server. Good luck, Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Wierd context-switching issue on Xeon
On Wed, 2004-05-19 at 21:59, Robert Creager wrote: When grilled further on (Wed, 19 May 2004 21:20:20 -0400 (EDT)), Bruce Momjian [EMAIL PROTECTED] confessed: Did we ever come to a conclusion about excessive SMP context switching under load? I just figured out what was causing the problem on my system Monday. I'm using the pg_autovacuum daemon, and it was not vacuuming my db. I've no idea why and didn't get a chance to investigate. Strange. There is a known bug in the 7.4.2 version of pg_autovacuum related to data type mismatches which is fixed in CVS. But that bug doesn't cause pg_autovacuum to stop vacuuming but rather to vacuum to often. So perhaps this is a different issue? Please let me know what you find. Thanks, Matthew O'Connor ---(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] MySQL vs PG TPC-H benchmarks
Paul Thomas wrote: Looks like he's using the default postgresql.conf settings in which case I'm not suprised at pg looking so slow. His stated use of foreign keys invalidates the tests anyway as MyISAM tables don't support FKs so we're probably seeing FK check overheads in pg that are simply ignore by MySQL. In an honest test, MySQL should be reported as failing those tests. Either failures, or they should not have been using MyISAM, they should have used the table format that supports FK's. This is just not apples to apples. ---(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] performance comparission postgresql/ms-sql server
Heiko Kehlenbrink wrote: i use suse 8.1 postgresql 7.2 compiled from the rpms for using postgis, but that is Try v7.4, there are many performance improvements. It may not make up all the differences but it should help. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] rapid degradation after postmaster restart
Andrew Sullivan wrote: The vacuum delay stuff that you're working on may help, but I can't really believe it's your salvation if this is happening after only a few minutes. No matter how much you're doing inside those functions, you surely can't be causing so many dead tuples that a vacuum is necessary that soon. Did you try not vacuuming for a little while to see if it helps? Some of this thread was taken off line so I'm not sure it was mentioned on the list, but a big part of the problem was that Joe was running into the same bug that Cott Lang ran into a while ago which caused the vacuum threshold to get set far too low resulting in vacuums far too often.. This has been fixed and the patch has been committed unfortunately it didn't make it into 7.4.2, but it will be in 7.4.3 / 7.5. I didn't see it anywhere in this thread, but are you quite sure that you're not swapping? Note that vmstat on multiprocessor Solaris machines is not notoriously useful. You may want to have a look at what the example stuff in the SE Toolkit tells you, or what you get from sar. I believe you have to use a special kernel setting on Solaris to mark shared memory as being ineligible for swap. I haven't heard from Joe how things are going with the fixed pg_autovacuum but that in combination with the vacuum delay stuff should work well. Matthew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] rapid degradation after postmaster restart
On Tue, 2004-03-16 at 23:49, Joe Conway wrote: I have tested Tom's original patch now. The good news -- it works great in terms of reducing the load imposed by vacuum -- almost to the level of being unnoticeable. The bad news -- in a simulation test which loads an hour's worth of data, even with delay set to 1 ms, vacuum of the large table exceeds two hours (vs 12-14 minutes with delay = 0). Since that hourly load is expected 7 x 24, this obviously isn't going to work. If memory serves, the problem is that you actually sleep 10ms even when you set it to 1. One of the thing changed in Jan's later patch was the ability to specify how many pages to work on before sleeping, rather than how long to sleep inbetween every 1 page. You might be able to do a quick hack and have it do 10 pages or so before sleeping. Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] rapid degradation after postmaster restart
Joe Conway wrote: A few pg_autovacuum questions came out of this: First, the default vacuum scaling factor is 2, which I think implies the big table should only get vacuumed every 56 million or so changes. I didn't come anywhere near that volume in my tests, yet the table did get vacuumed more than once (I was watching the pg_autovacuum log output). Do I misunderstand this setting? I think you understand correctly. A table with 1,000,000 rows should get vacuumed approx every 2,000,000 changes (assuming default values for -V ). FYI and insert and a delete count as one change, but and update counts as two. Unfortunately, the running with -d2 would show the numbers that pg_autovacuum is using to decide if it when it should vacuum or analyze.Also, are you sure that it vacuumed more than once and wasn't doing analyzes most of the time? Also, I'm not sure if 2 is a good default value for the scaling factor but I erred on the side of not vacuuming too often. Second, Matthew requested pg_autovacuum run with -d2; I found that with -d2 set, pg_autovacuum would immediately exit on start. -d0 and -d1 work fine however. That's unfortunate as that is the detail we need to see what pg_autovacuum thinks is really going on. We had a similar sounding crash on FreeBSD due to some unitialized variables that were being printed out by the debug code, however that was fixed a long time ago. Any chance you can look into this? That's all I can think of at the moment. I'd like to try the 7.4 patch that makes vacuum sleep every few pages -- can anyone point me to the latest and greatest that will apply to 7.4? Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] rapid degradation after postmaster restart
Joe Conway wrote: Yeah, I'm sure. Snippets from the log: [...lots-o-tables...] [2004-03-14 12:44:48 PM] added table: specdb.public.parametric_states [2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 02:08:26 PM] Performing: ANALYZE public.out_of_spec [2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE public.spc_graphs [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE public.out_of_spec [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE public.spc_graphs [2004-03-14 03:21:09 PM] Performing: ANALYZE public.parametric_states [2004-03-14 03:54:57 PM] Performing: ANALYZE public.out_of_spec [2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE public.spc_graphs [2004-03-14 04:09:33 PM] Performing: ANALYZE public.equip_status_history [2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE public.parametric_states [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE public.out_of_spec [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE public.transaction_data [2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE public.spc_graphs [2004-03-14 04:58:32 PM] Performing: ANALYZE public.parametric_states [2004-03-14 05:28:58 PM] added database: specdb Yeah, you're right. This is the entire period of the first test, with default autovac settings. The table public.transaction_data is the one with 28 million active rows. The entire test run inserts about 600 x 600 = 360,000 rows, out of which roughly two-thirds are later deleted. Strange... I wonder if this is some integer overflow problem. There was one reported recently and fixed as of CVS head yesterday, you might try that, however without the -d2 output I'm only guessing at why pg_autovacuum is vacuuming so much / so often. I can try. The server belongs to another department, and they are under the gun to get back on track with their testing. Also, they compiled without debug symbols, so I need to get permission to recompile. Good luck, I hope you can get permission. Would e nice to fix this little crash. Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. I think Jan posted one a while back [searches archives...] But I must say I'm at a loss to find it in the archives. Anyone know where a good delay patch is for 7.4? If we can't find one, any chance you can do some testing with CVS HEAD just to see if that works any better. I know there has been a fair amount of work done to improve this situation (not just vacuum delay, but ARC etc...) . ---(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] rapid degradation after postmaster restart
Joe Conway wrote: Tom Lane wrote: Just to be clear on this: you have to restart the postmaster to bring the time back down? Simply starting a fresh backend session doesn't do it? IIRC, shared buffers was reasonable, maybe 128MB. One thing that is worthy of note is that they are using pg_autovacuum and a very low vacuum_mem setting (1024). But I also believe that max_fsm_relations and max_fsm_pages have been bumped up from default (something like 1 20). pg_autovacuum could be a problem if it's vacuuming too often. Have you looked to see if a vacuum or analyze is running while the server is slow? If so, have you played with the pg_autovacuum default vacuum and analyze thresholds? If it appears that it is related to pg_autovacuum please send me the command options used to run it and a logfile of it's output running at at a debug level of -d2 Matthew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] autovacuum daemon stops doing work after about an hour
MTO == Matthew T O'Connor [EMAIL PROTECTED] writes:
MTO I don't run FreeBSD, so I haven't tested with FreeBSD. Recently
Craig MTO Boston reported and submitted a patch for a crash on FreeBSD,
but that
some more debugging data:
(gdb) print now
$2 = {tv_sec = 1070565077, tv_usec = 216477}
(gdb) print then
$3 = {tv_sec = 1070561568, tv_usec = 668963}
(gdb) print diff
$4 = -5459981371352
(gdb) print sleep_secs
$5 = -1272
so for some reason, instead of calculating 3508547514 as the diff, it
got a hugely negative number.
I'll bet it has something to do with the compiler... more debugging to
follow (without -O compilation...)
Could this be the recently reported bug where time goes backwards on
FreeBSD? Can anyone who knows more about this problem chime in, I know it
was recently discussed on Hackers.
The simple fix is to just make sure it's a positive number. If not, then
just sleep for some small positive amount. I can make a patch for this,
probably sometime this weekend.
Thanks for tracking this down.
---(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: [HACKERS] [PERFORM] More detail on settings for pgavd?
Josh Berkus wrote: Matthew, For small tables, you don't need to vacuum too often. In the testing I did a small table ~100 rows, didn't really show significant performance degredation until it had close to 1000 updates. This is accounted for by using the threshold value. That way small tables get vacuumed less often. However, the way large tables work is very different and I think your strategy shows a lack of testing on large active tables. Probably more true than I would like to think... For large tables, vacuum is so expensive, that you don't want to do it very often, and scanning the whole table when there is only 5% wasted space is not very helpful. 5% is probably too low, you're right ... in my experience, performance degredation starts to set in a 10-15% updates to, for example, a 1.1 million row table, particularly since users tend to request the most recently updated rows. As long as we have the I/O issues that Background Writer and ARC are intended to solve, though, I can see being less agressive on the defaults; perhaps 20% or 25%. If you wait until 110% of a 1.1 million row table is updated, though, that vaccuum will take an hour or more. True, but I think it would be one hour once, rather than 30 minutes 4 times. Additionally, you are not thinking of this in terms of an overall database maintanence strategy. Lazy Vacuum needs to stay below the threshold of the Free Space Map (max_fsm_pages) to prevent creeping bloat from setting in to your databases. With proper configuration of pg_avd, vacuum_mem and FSM values, it should be possible to never run a VACUUM FULL again, and as of 7.4 never run an REINDEX again either. This is one of the things I had hoped to add to pg_autovacuum, but never got to. In addition to just the information from the stats collector on inserts updates and deletes, pg_autovacuum should also look at the FSM, and make decisions based on it. Anyone looking for a project? But this means running vacuum frequently enough that your max_fsm_pages threshold is never reached. Which for a large database is going to have to be more frequently than 110% updates, because setting 20,000,000 max_fsm_pages will eat your RAM. Again, the think the only way to do this efficiently is to look at the FSM. Otherwise the only way to make sure you keep the FSM populated is to run vacuum more than needed. Yes, the I set the defaults a little high perhaps so as to err on the side of caution. I didn't want people to say pg_autovacuum kills the performance of my server. A small table will get vacuumed, just not until it has reached the threshold. So a table with 100 rows, will get vacuumed after 1200 updates / deletes. Ok, I can see that for small tables. In my testing it showed that there was no major performance problems until you reached several thousand updates / deletes. Sure. But several thousand updates can be only 2% of a very large table. But I can't imagine that 2% makes any difference on a large table. In fact I would think that 10-15% would hardly be noticable, beyond that I'm not sure. HUH? analyze is very very cheap compared to vacuum. Why not do it more often? Because nothing is cheap if it's not needed. Analyze is needed only as often as the *aggregate distribution* of data in the tables changes. Depending on the application, this could be frequently, but far more often (in my experience running multiple databases for several clients) the data distribution of very large tables changes very slowly over time. Valid points, and again I think this points to the fact that pg_autovacuum needs to be more configurable. Being able to set different thresholds for different tables will help considerably. In fact, you may find that some tables should have a vac threshold much larger than the analyze thresold, while other tables might want the opposite. One client's database, for example, that I have running VACUUM on chron scripts runs on this schedule for the main tables: VACUUM only: twice per hour VACUUM ANALYZE: twice per day I would be surprized if you can notice the difference between a vacuum analyze and a vacuum, especially on large tables. On the other hand, I've another client's database where most activity involves updates to entire classes of records. They run ANALYZE at the end of every transaction. So if you're going to have a seperate ANALYZE schedule at all, it should be slightly less frequent than VACUUM for large tables. Either that, or drop the idea, and simplify pg_avd by running VACUUM ANALYZE all the time instead of having 2 seperate schedules. I think you need two separate schedules. There are lots of times where a vacuum doesn't help, and an analyze is all that is needed, and an analyze is MUCH cheaper than a vacuum. BUT now I see how you arrived at the logic you did. If you're testing only on small tables, and not
Re: [HACKERS] [PERFORM] More detail on settings for pgavd?
Tom Lane wrote: Chester Kustarz [EMAIL PROTECTED] writes: vacuum is to reclaim dead tuples. this means it depends on update and delete. analyze depends on data values/distribution. this means it depends on insert, update, and delete. thus the dependencies are slightly different between the 2 operations, an so you can come up with use-cases that justify running either more frequently. Agreed. And that is why pg_autovacuum looks at insert, update and delete when deciding to do an analyze, but only looks at update and delete when deciding to do a vacuum. In addition, this is why pg_autovacuum was given knobs so that the vacuum and analyze thresholds can be set independently. Matthew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] More detail on settings for pgavd?
Josh Berkus wrote: Matthew, True, but I think it would be one hour once, rather than 30 minutes 4 times. Well, generally it would be about 6-8 times at 2-4 minutes each. Are you saying that you can vacuum a 1 million row table in 2-4 minutes? While a vacuum of the same table with an additional 1 million dead tuples would take an hour? This is one of the things I had hoped to add to pg_autovacuum, but never got to. In addition to just the information from the stats collector on inserts updates and deletes, pg_autovacuum should also look at the FSM, and make decisions based on it. Anyone looking for a project? Hmmm ... I think that's the wrong approach. Once your database is populated, it's very easy to determine how to set the FSM for a given pg_avd level. If you're vacuuming after 20% updates, for example, just set fsm_pages to 20% of the total database pages plus growth safety margins. Ok. I'd be really reluctant to base pv-avd frequency on the fsm settings instead. What if the user loads 8GB of data but leaves fsm_pages at the default of 10,000? You can't do much with that; you'd have to vacuum if even 1% of the data changed. Ok, but as you said above it's very easy to set the FSM once you know your db size. The other problem is that calculating data pages from a count of updates+deletes would require pg_avd to keep more statistics and do more math for every table. Do we want to do this? I would think the math is simple enough to not be a big problem. Also, I did not recommend looking blindly at the FSM as our guide, rather consulting it as another source of information as to when it would be useful to vacuum. I don't have a good plan as to how to incorporate this data, but to a large extent the FSM already tracks table activity and gives us the most accurate answer about storage growth (short of using something like contrib/pgstattuple which takes nearly the same amount of time as an actual vacuum) But I can't imagine that 2% makes any difference on a large table. In fact I would think that 10-15% would hardly be noticable, beyond that I'm not sure. I've seen performance lag at 10% of records, especially in tables where both update and select activity focus on one subset of the table (calendar tables, for example). Ok. Valid points, and again I think this points to the fact that pg_autovacuum needs to be more configurable. Being able to set different thresholds for different tables will help considerably. In fact, you may find that some tables should have a vac threshold much larger than the analyze thresold, while other tables might want the opposite. Sure. Though I think we can make the present configuration work with a little adjustment of the numbers. I'll have a chance to test on production databases soon. I look forward to hearing results from your testing. I would be surprized if you can notice the difference between a vacuum analyze and a vacuum, especially on large tables. It's substantial for tables with high statistics settings. A 1,000,000 row table with 5 columns set to statistics=250 can take 3 minutes to analyze on a medium-grade server. In my testing, I never changed the default statistics settings. I think you need two separate schedules. There are lots of times where a vacuum doesn't help, and an analyze is all that is needed Agreed. And I've just talked to a client who may want to use pg_avd's ANALYZE scheduling but not use vacuum at all. BTW, I think we should have a setting for this; for example, if -V is -1, don't vacuum. That would be nice. Easy to add, and something I never thought of I'm open to discussion on changing the defaults. Perhaps what it would be better to use some non-linear (perhaps logorithmic) scaling factor. That would be cool, too.Though a count of data pages would be a better scale than a count of rows, and equally obtainable from pg_class. But we track tuples because we can compare against the count given by the stats system. I don't know of a way (other than looking at the FSM, or contrib/pgstattuple ) to see how many dead pages exist. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] More detail on settings for pgavd?
Shridhar Daithankar wrote: Matthew T. O'Connor wrote: But we track tuples because we can compare against the count given by the stats system. I don't know of a way (other than looking at the FSM, or contrib/pgstattuple ) to see how many dead pages exist. I think making pg_autovacuum dependent of pgstattuple is very good idea. Only if pgstattuple can become much cheaper than it is now. Based on the testing I did when I wrote pg_autovacuum, pgstattuple cost nearly the same amount as a regular vacuum. Given that, what have we gained from that work? Wouldn't it just be better to run a vacuum and actually reclaim space rather than running pgstattuple, and just look and see if there is free space to be reclaimed? Perhaps we could use pgstattuple ocasionally to see if we are going a good job of keeping the amount of dead space to a reasonable level, but I'm still not really sure about this. Probably it might be a good idea to extend pgstattuple to return pages that are excessively contaminated and clean them ASAP. Step by step getting closer to daemonized vacuum. I don't know of anyway to clean a particular set of pages. This is something that has been talked about (partial vacuums and such), but I think Tom has raised several issues with it, I don't remember the details. Right now the only tool we have to reclaim space is vacuum, a whole table at a time. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PERFORM] More detail on settings for pgavd?
Josh Berkus wrote: Matthew, But we could create a config file that would store stuff in a flatfile table, OR we could add our own system table that would be created when one initializes pg_avd. I don't want to add tables to existing databases, as I consider that clutter and I never like using tools that clutter my production databases. I had considered using a pg_autovacuum database that if found, would store customized settings for individual tables / databases. Dunno if this is a good idea, but it might make a good stopgap until people are comfortable modifying the system catalogs for autovacuum. Actually, this might be a necessary addition as pg_autovacuum currently suffers from the startup transients that the FSM used to suffer from, that is, it doesn't remember anything that happened the last time it ran. A pg_autovacuum database could also be used to store thresholds and counts from the last time it ran. Just an idea. Mind you, I'm not so sure that we want to focus immediately on per-table settings. I think that we want to get the automatic settings working fairly well first; a lot of new DBAs would use the per-table settings to shoot themselves in the foot. So we need to be able to make a strong recommendation to try the automatic settings first. I agree in principle, question is what are the best settings, I still think it will be hard to find a one size fits all, but I'm sure we can do better than what we have. Actually, thinking about this I realize that PG_AVD and the Perl-based postgresql.conf configuration script I was working on (darn, who was doing that with me?) need to go togther. With pg_avd, setting max_fsm_pages is very easy; without it its a bit of guesswork. So I think we can do this: for 'auto' settings: If max_fsm_pages is between 13% and 100% of the total database pages, then set the vacuum scale factor to match 3/4 of the fsm_pages setting, e.g. database = 18,000,000 data pages; max_fsm_pages = 3,600,000; set vacuum scale factor = 3.6mil/18mil * 3/4 = 0.15 Where are you getting 13% from? Do you know of an easy way to get a count of the total pages used by a whole cluster? I guess we can just iterate over all the tables in all the databases and sum up the total num of pages. We already iterate over them all, we just don't sum it up. If max_fsm_pages is less than 13% of database pages, issue a warning to the user (log it, if possible) and set scale factor to 0.1. If it's greater than 100% set it to 1 and leave it alone. Again I ask where 13% is coming from and also where is 0.1 coming from? I assume these are your best guesses right now, but not more than that. I do like the concept though as long as we find good values for min_fsm_percentage and min_autovac_scaling_factor. But we track tuples because we can compare against the count given by the stats system. I don't know of a way (other than looking at the FSM, or contrib/pgstattuple ) to see how many dead pages exist. No, but for scaling you don't need the dynamic count of tuples or of dead tuples; pg_class holds a reasonable accurate count of pages per table as of last vacuum. Which we already keep a copy of inside of pg_autovacuum, and update after we issue a vacuum. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PERFORM] More detail on settings for pgavd?
Josh Berkus wrote: Matthew, I don't see how a seperate database is better than a table in the databases., except that it means scanning only one table and not one per database. For one thing, making it a seperate database could make it hard to back up and move your database+pg_avd config. Basically, I don't like the idea of modifying users databases, besides, in the long run most of what needs to be tracked will be moved to the system catalogs. I kind of consider the pg_autvacuum database to equivalent to the changes that will need to be made to the system catalogs. I guess it could make it harder to backup if you are moving your database between clusters. Perhaps, if you create a pg_autovacuum schema inside of your database then we would could use that. I just don't like tools that drop things into your database. Where are you getting 13% from? 13% * 3/4 ~~ 10% And I think both of use agree that vacuuming tables with less than 10% changes is excessive and could lead to problems on its own, like overlapping vacuums. I certainly agree that less than 10% would be excessive, I still feel that 10% may not be high enough though. That's why I kinda liked the sliding scale I mentioned earlier, because I agree that for very large tables, something as low as 10% might be useful, but most tables in a database would not be that large. Do you know of an easy way to get a count of the total pages used by a whole cluster? Select sum(relpages) from pg_class. duh BTW, do we have any provisions to avoid overlapping vacuums? That is, to prevent a second vacuum on a table if an earlier one is still running? Only that pg_autovacuum isn't smart enough to kick off more than one vacuum at a time. Basically, pg_autovacuum issues a vacuum on a table and waits for it to finish, then check the next table in it's list to see if it needs to be vacuumed, if so, it does it and waits for that vacuum to finish. There was some discussion of issuing concurrent vacuum against different tables, but it was decided that since vacuum is I/O bound, it would only make sense to issue concurrent vacuums that were on different spindles, which is not something I wanted to get into. Also, given the recent talk about how vacuum is still such a performance hog, I can't imagine what multiple concurrent vacuums would do to performance. Maybe as 7.5 develops and many of the vacuum performance issues are addressed, we can revisit this question. ---(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: [HACKERS] [PERFORM] More detail on settings for pgavd?
Josh Berkus wrote: Matthew, I certainly agree that less than 10% would be excessive, I still feel that 10% may not be high enough though. That's why I kinda liked the sliding scale I mentioned earlier, because I agree that for very large tables, something as low as 10% might be useful, but most tables in a database would not be that large. Yes, but I thought that we were taking care of that through the threshold value? Well the threshold is a combination of the base value and the scaling factor which you are proposing is 0.1, so the threshold is base + (scaling factor)(num of tuples) So with the default base of 1000 and your 0.1 you would have this: Num Rowsthreshold Percent 1,0001,100 110% 10,0002,000 20% 100,000 11,000 11% 1,000,000 102,000 10% I don't like how that looks, hence the thought of some non-linear scaling factor that would still allow the percent to reach 10%, but at a slower rate, perhaps just a larger base value would suffice, but I think small table performance is going to suffer much above 1000. Anyone else have an opinion on the table above? Good / Bad / Indifferent? A sliding scale would also be OK. However, that would definitely require a leap to storing per-table pg_avd statistics and settings. I don't think it would, it would correlate the scaling factor with the number of tuples, no per-table settings required. Only that pg_autovacuum isn't smart enough to kick off more than one vacuum at a time. Basically, pg_autovacuum issues a vacuum on a table and waits for it to finish, then check the next table in it's list to see if it needs to be vacuumed, if so, it does it and waits for that vacuum to finish. OK, then, we just need to detect the condition of the vacuums piling up because they are happening too often. That would be good to look into at some point, especially if vacuum is going to get slower as a result of the page loop delay patch that has been floating around. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some vacuum tuning help
From: Tom Lane [EMAIL PROTECTED] Matthew T. O'Connor [EMAIL PROTECTED] writes: So, now is precisely the time to be experimenting to find out what works well and what features are needed. Another quick question while I have your attention :-) Since pg_autovaccum is a contrib module does that mean I can make functional changes that will be included in point release of 7.4? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Some vacuum tuning help
On Tue, 2003-08-05 at 17:40, Christopher Browne wrote:
Unfortunately, a configurable-via-tables pg_autovacuum is also going
to be quite different from the current unconfigurable version.
true, however I would like to preserve the unconfigured functionality
so that it can be run against a totally unmodified database cluster. If
it finds configuration information on the server then it uses it,
otherwise it just acts as it does now.
But at that point, it makes sense to add in quite a bit of
configurable behaviour, such as:
- Specifying that certain tables should _never_ be automatically
vacuumed.
agreed
- Establishing a queue of tables that pg_autovacuum plans to
vacuum, so that users could add in desired vacuums (after the
other stuff being handled, force in a vacuum of app_table_foo).
That way, vacuums can be 'forced in' without introducing the
possibility that multiple vacuums might be done at once...
makes sense.
- Making information about what vacuums have been done/planned
persistent across runs of pg_autovacuum, and even across
shutdowns of the DBMS.
good.
This changes behaviour enough that I'm not sure it's the same
program as the unconfigurable version. Almost every option would be
substantially affected by the logic:
if (CONFIG_DATA_IN_DB) {
/* Logic path that uses data in Vacuum Schema */
} else {
/* More banal logic */
}
I'm not so sure it's that different. In either case we are going to
have a threshold and decide to vacuum based on that threshold. The
change is only that the data would be persistent, and could be
customized on a per table basis. The logic only really changes if
running unconfigured uses different data than the configured version,
which I don't see as being proposed.
If I can store configuration in the database, then I'd like to also
make up a view or two, and possibly even base the logic used on views
that combine configuration tables with system views. In effect, that
makes for a _third_ radically different option.
Not sure I see what all you are implying here. Please expand on this if
you deem it worthy.
I guess I'll start coding again. I'll send an email to the hackers list
tomorrow evening with as much info / design as I can think of.
Matthew
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
