Re: [PERFORM] autovacuum daemon stops doing work after about an hour
Christopher Browne wrote: The world rejoiced as [EMAIL PROTECTED] (Gaetano Mendola) wrote: I think is a good Idea put a fflush after: fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry); I thought I had put fflush()es at all the interesting locations... I just looked through the code, I think there are fflush()es at all but one interesting locations. The last log_entry call before sleeping doesn't have an fflush call after it. I'll submit a patch that adds it. Apparently it was an error to not go to the effort of making sure it worked well on FreeBSD. (It was on my list, but I never got the Round Tuits...) There's an AMD-64 box coming in soon, targeted at FreeBSD, so that should change... Yeah, FreeBSD testing would have been nice, but I don't have access to any FreeBSD boxes so. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] autovacuum daemon stops doing work after about an hour
The world rejoiced as [EMAIL PROTECTED] (Gaetano Mendola) wrote: > I think is a good Idea put a fflush after: > > fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry); I thought I had put fflush()es at all the interesting locations... Apparently it was an error to not go to the effort of making sure it worked well on FreeBSD. (It was on my list, but I never got the Round Tuits...) There's an AMD-64 box coming in soon, targeted at FreeBSD, so that should change... -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://www3.sympatico.ca/cbbrowne/linux.html What would a chair look like, if your knees bent the other way? ---(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
Gaetano Mendola wrote: Vivek Khera wrote: "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes: Then it just sits there. I started it at 11:35am, and it is now 3:30pm. MTO> Weird Alphabetically speaking, is vkmlm."public"."user_list" be the MTO> last table in the last schema in the last database? You are running conveniently, yes it is... MTO> with -d4, so you would get a message about going to sleep shortly after MTO> dealing with the last table, but you didn't get the sleep message, so I MTO> don't think the problem is that pg_autovacuum is sleeping for an MTO> inordinate amount time. The only sleep logged was [2003-12-03 04:47:13 PM] 1 All DBs checked in: 84996853 usec, will sleep for 469 secs. What I seen is: # tail -f auto.log [2003-12-04 07:10:18 PM] reltuples: 72; relpages: 1 [2003-12-04 07:10:18 PM] curr_analyze_count: 72; cur_delete_count: 0 [2003-12-04 07:10:18 PM] ins_at_last_analyze: 72; del_at_last_vacuum: 0 [2003-12-04 07:10:18 PM] insert_threshold:572; delete_threshold536 [2003-12-04 07:10:18 PM] table name: empdb."public"."contracts" [2003-12-04 07:10:18 PM] relfilenode: 17784; relisshared: 0 [2003-12-04 07:10:18 PM] reltuples: 347; relpages: 5 [2003-12-04 07:10:18 PM] curr_analyze_count: 347; cur_delete_count: 0 [2003-12-04 07:10:18 PM] ins_at_last_analyze: 347; del_at_last_vacuum: 0 [2003-12-04 07:10:18 PM] insert_threshold:847; delete_threshold673 [ 5 minutes of delay ] <- LOOK THIS [2003-12-04 07:10:18 PM] 503 All DBs checked in: 179396 usec, will sleep for 300 secs. [2003-12-04 07:15:19 PM] 504 All DBs checked in: 98814 usec, will sleep for 300 secs. I think is a good Idea put a fflush after: fprintf(LOGOUTPUT, "[%s] %s\n", timebuffer, logentry); Was I wrong ? If you are watching in tail the log believeme is really annoying. Regards Gaetano Mendola ---(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] autovacuum daemon stops doing work after about an
This has been fixed and will be in 7.4.1. --- Vivek Khera wrote: > > "LR" == Larry Rosenman <[EMAIL PROTECTED]> writes: > > >> I'd be curious to see the output of this program on other platforms > >> and other compilers. I'm using gcc 2.95.4 as shipped with FreeBSD > >> 4.8+. > LR> this is with the UnixWare compiler: > LR> $ cc -O -o testvk testvk.c > LR> $ ./testvk > LR> seconds = 3509 > LR> seconds1 = 350900 > LR> useconds = -452486 > LR> stepped diff = 3508547514 > LR> seconds2 = -785967296 > LR> seconds3 = 350900 > LR> diff = -786419782 > LR> long long diff = 3508547514 > LR> $ > > LR> I think this is a C bug. > > Upon further reflection, I think so to. The entire RHS is long's so > the arithmetic is done in longs, then assigned to a long long when > done (after things have overflowed). Forcing any one of the RHS > values to be long long causes the arithmetic to all be done using long > longs, and then you get the numbers you expect. > > I think you only notice this in autovacuum when it takes a long time > to complete the work, like my example of about 3500 seconds. > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Help tracking down problem with inserts slowing
On Fri, 2003-12-05 at 16:38, Neil Conway wrote: > > (1) Can you confirm that the VACUUM FULL on site B actually > removed all the tuples you intended it to remove? Concurrent > transactions can limit the amount of data that VACUUM FULL is > able to reclaim. If you run contrib/pgstattuple (or compare > the database's disk consumption with the number of live rows > in it), you should be able to tell. Hmmm, I installed 7.2.3 from RPMs, but the contrib package seems to be missing the pgstattuple library code. (According to the readme, I should do: $ make $ make install $ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test but the first two lines don't make sense with the binary rpm distribution and trying the last line as (for my world): ->psql -e -f /usr/share/pgsql/contrib/pgstattuple.sql farm.devel.configdb yields: DROP FUNCTION pgstattuple(NAME); psql:/usr/share/pgsql/contrib/pgstattuple.sql:1: ERROR: RemoveFunction: function 'pgstattuple(name)' does not exist CREATE FUNCTION pgstattuple(NAME) RETURNS FLOAT8 AS '$libdir/pgstattuple', 'pgstattuple' LANGUAGE 'c' WITH (isstrict); psql:/usr/share/pgsql/contrib/pgstattuple.sql:4: ERROR: stat failed on file '$libdir/pgstattuple': No such file or directory I don't need this right now (a reindex seems to have fixed our problem for now...), but it sounds like it would be useful in the future. Thanks! Steve -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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] Help tracking down problem with inserts slowing down...
Steve Wampler <[EMAIL PROTECTED]> writes: > Hmmm, I have a feeling that's not as obvious as I thought... I can't > identify the index (named 'id_index') in the output of vacuum verbose. In 7.2, the index reports look like Index %s: Pages %u; Tuples %.0f. and should appear in the part of the printout that deals with their owning table. regards, tom lane ---(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] Help tracking down problem with inserts slowing down...
On Fri, Dec 05, 2003 at 09:54:52PM -0500, Robert Treat wrote: >... > A vacuum verbose could give you a good indication if you need to reindex, > compare the # of pages in the index with the # in the table. Hmmm, I have a feeling that's not as obvious as I thought... I can't identify the index (named 'id_index') in the output of vacuum verbose. The closest I can find is: NOTICE: --Relation pg_index-- NOTICE: Pages 2: Changed 0, Empty 0; Tup 56: Vac 0, Keep 0, UnUsed 42. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. Which probably isn't correct, right (the name doesn't seem to match)? The table's entry is: NOTICE: --Relation attributes_table-- NOTICE: Pages 639: Changed 0, Empty 0; Tup 52846: Vac 0, Keep 0, UnUsed 48. Total CPU 0.00s/0.01u sec elapsed 0.01 sec. Thanks! Steve -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help tracking down problem with inserts slowing down...
On Fri, Dec 05, 2003 at 09:54:52PM -0500, Robert Treat wrote: > On Friday 05 December 2003 16:51, Steve Wampler wrote: > > I need some help tracking down a sudden, massive slowdown > > in inserts in one of our databases. > > > > PG: 7.2.3 (RedHat 8.0) > > > > Background. We currently run nearly identical systems > > at two sites: Site A is a 'lab' site used for development, > > Site B is a production site. > > > > The databases in question have identical structure: > > > > A simple table with 4 columns with a trigger function > > on inserts (which checks to see if the entry already > > exists, and if so, changes the insert into an update...) > > A simple view with 4 columns into the above table. > > > > All access is through jdbc (JDK 1.3.1, jdbc 7.1-1.3), > > postgresql.conf's are identical. > > > > The two sites were performing at comparable speeds until > > a few days ago, when we deleted several million records > > from each database and then did a vacuum full; analyze > > on both. Now inserts at Site B are several orders of > > magnitude slower than at Site A. The odd thing is that > > Site B's DB now has only 60,000 records while Site A's is > > up around 3 million. Inserts at A average 63ms, inserts > > at B are now up at 4.5 seconds! > > > > EXPLAIN doesn't show any difference between the two. > > > > Can someone suggest ways to track this down? I don't know > > much about postgresql internals/configuration. > > > > What does explain analyze show for the insert query? > > Are there FK and/or Indexes involved here? Did you you reindex? > A vacuum verbose could give you a good indication if you need to reindex, > compare the # of pages in the index with the # in the table. Thanks Robert! It looks like reindex did the trick. Now I have a general question - what are the relationships between: vacuum, analyze, reindex, and dropping/recreating the indices? That is, which is the following is 'best' (or is there a different ordering that is better)?: (1) vacuum analyze reindex (2) vacuum reindex analyze (3) drop indices vacuum create indices analyze (4) drop indices vacuum analyze create indices And, is reindex equivalent to dropping, then recreating the indices? [it appears to be "no", from what I've just seen, but I don't know...] Thanks! Steve -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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
