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...
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
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] 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
