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 hour
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); 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 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...) MTO ---(end of broadcast)--- MTO TIP 7: don't forget to increase your free space map settings -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(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
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: [PERFORM] autovacuum daemon stops doing work after about an hour
MTO == Matthew T O'Connor [EMAIL PROTECTED] writes: MTO Could this be the recently reported bug where time goes backwards on MTO FreeBSD? Can anyone who knows more about this problem chime in, I know it MTO was recently discussed on Hackers. Time does not go backwards -- the now and then variables are properly incrementing in time as you see from the debugging output. The error appears to be with the computation of the diff. It is either a C programming error, or a compiler error. I'm not a C cop so I can't tell you which it is. Witness this program, below, compiled as cc -g -o t t.c and the output here: % ./t seconds = 3509 seconds1 = 350900 useconds = -452486 stepped diff = 3508547514 seconds2 = -785967296 seconds3 = 350900 diff = -786419782 long long diff = 3508547514 % apperantly, if you compute (now.tv_sec - then.tv_sec) * 100 all at once, it overflows since the RHS is all computed using longs rather than long longs. Fix is to cast at least one of the values to long long on the RHS, as in the computation of seconds3 below. compare that to the computation of seconds2 and you'll see that this is the cause. 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+. That all being said, you should never sleep less than the base time, and never for more than a max amount, perhaps 1 hour? --cut here-- #include sys/time.h #include stdio.h int main() { struct timeval now, then; long long diff = 0; long long seconds, seconds1, seconds2, seconds3, useconds; now.tv_sec = 1070565077L; now.tv_usec = 216477L; then.tv_sec = 1070561568L; then.tv_usec = 668963L; seconds = now.tv_sec - then.tv_sec; printf(seconds = %lld\n,seconds); seconds1 = seconds * 100; printf(seconds1 = %lld\n,seconds1); useconds = now.tv_usec - then.tv_usec; printf(useconds = %lld\n,useconds); diff = seconds1 + useconds; printf(stepped diff = %lld\n,diff); /* this appears to be the culprit... it should be same as seconds1 */ seconds2 = (now.tv_sec - then.tv_sec) * 100; printf(seconds2 = %lld\n,seconds2); /* seems we need to cast long's to long long's for this computation */ seconds3 = ((long long)now.tv_sec - (long long)then.tv_sec) * 100; printf(seconds3 = %lld\n,seconds3); diff = (now.tv_sec - then.tv_sec) * 100 + (now.tv_usec - then.tv_usec); printf (diff = %lld\n,diff); diff = ((long long)now.tv_sec - (long long)then.tv_sec) * 100 + (now.tv_usec - then.tv_usec); printf (long long diff = %lld\n,diff); exit(0); } --cut here-- ---(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] autovacuum daemon stops doing work after about an hour
Actually, you can simplify the fix thusly: diff = (long long)(now.tv_sec - then.tv_sec) * 100 + (now.tv_usec - then.tv_usec); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]