Thread: vacuum strategy
What's the typical way to handle pg vacuuming? Have a somewhat frequently called script do it periodically, or just make a script for cron? Also, isn't there a project related to this that handles it automatically (if so please give the URL)? __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus � Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
On Mon, 25 Nov 2002, CSN wrote: > What's the typical way to handle pg vacuuming? Have a > somewhat frequently called script do it periodically, > or just make a script for cron? > > Also, isn't there a project related to this that > handles it automatically (if so please give the URL)? The basic rule of thumb is to vacuum a table whenever it's had more than about 25% turnover. Of course, on very large tables, as little as 5% turnover could justify a vacuum. The same basic number applies for analyzing. Plain vacuums can be run as often as you'd like really, as they consume little bandwidth and are non-blocking. vacuum full should be run during off peak hours. While normal non-full vacuums are good enough for most uses, it's occasionally necessary to run a full vacuum to reclaim tuples that the normal vacuum couldn't free (if there are a bunch freed between regular vacuums, it sometimes isn't possible to free them. Also, full vacuums are required every so often to keep the transaction id from rolling over. It rolls over at 4 billion, so you don't have to run a full vacuum all that often for that. There is an autovacuum daemon in the works, you can find it on gborg at: http://gborg.postgresql.org/project/pgavd/projdisplay.php I haven't had a chance to play with it, but I did just download it and plan on playing with it a bit.
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Also, full vacuums are required every so often to keep the transaction id
> from rolling over.
Not so; a plain vacuum is fine for that. The critical point is that
*every* table in *every* database has to be vacuumed (plain or full)
at least once every billion transactions or so.
regards, tom lane
Tom Lane wrote: >"scott.marlowe" <scott.marlowe@ihs.com> writes: > > >>Also, full vacuums are required every so often to keep the transaction id >>from rolling over. >> >> > >Not so; a plain vacuum is fine for that. The critical point is that >*every* table in *every* database has to be vacuumed (plain or full) >at least once every billion transactions or so. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > dump question: what means this exactly? does this include the template1 database? So i have to vacuum the tempate1 database too on a regular basis to avoid the rolling over of the transaction id? regards tina
On 26 Nov 2002 at 9:56, Tina Messmann wrote: > dump question: > what means this exactly? > does this include the template1 database? So i have to vacuum the > tempate1 database too on a regular basis to avoid the rolling over of > the transaction id? Why transaction id in template1 should roll? You aren't doing any transactions on it, are you? Or I misread the question? Bye Shridhar -- Love sometimes expresses itself in sacrifice. -- Kirk, "Metamorphosis", stardate 3220.3
Hi All, I am importing data from text files in to postgres. I would like to know if some field is not present in Text file,then how does COPY command handle it ,specially for date field. -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
On 26 Nov 2002 at 15:20, Savita wrote: > I would like to know if some field is not present in Text file,then how does > COPY command handle it ,specially for date field. I am not very sure about this, but one thing you can always do is to insert a blank field separator, making copy believe that the field value is NULL.. Some sed/awk script should do the trick.. HTH Bye Shridhar -- "We all know Linux is great...it does infinite loops in 5 seconds."(Linus Torvalds about the superiority of Linux on the AmterdamLinux Symposium)
Hello All, I would like to know if there are a way to see the lasts queries executed. For example, if you work with php then apache interact with the postmaster to run your queries. Then it's could be really interresting to intend to find the slowest queries of my web application. I know that in Mysql there are a log file in which it is stored ! Best Regards del Kiki Alias Areski
Hi, I am able to insert the null field using \N in the text file,but while retiving the data I am not able to retrive it. After inserting data I tried this select statement select * from os_customer_master where updated_by=' '; and select * from os_customer_master where updated_by=null; but it gives 0 rows.then how will I select this values. Shridhar Daithankar wrote: > On 26 Nov 2002 at 15:20, Savita wrote: > > > I would like to know if some field is not present in Text file,then how does > > COPY command handle it ,specially for date field. > > I am not very sure about this, but one thing you can always do is to insert a > blank field separator, making copy believe that the field value is NULL.. Some > sed/awk script should do the trick.. > > HTH > > Bye > Shridhar > > -- > "We all know Linux is great...it does infinite loops in 5 seconds."(Linus > Torvalds about the superiority of Linux on the AmterdamLinux Symposium) > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
Hi Savita, --On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com> wrote: > Hi, > I am able to insert the null field using \N in the text file,but while > retiving the data I am not able to retrive it. > > After inserting data I tried this select statement > > select * from os_customer_master where updated_by=' '; > and > select * from os_customer_master where updated_by=null; > > but it gives 0 rows.then how will I select this values. Nothing to do with copy :) Just try: select * from os_customer_master where isnull updated_by; Regards Tino
On 26 Nov 2002 at 16:02, Savita wrote: > Hi, > I am able to insert the null field using \N in the text file,but while retiving > the data I am not able to retrive it. > > After inserting data I tried this select statement > > select * from os_customer_master where updated_by=' '; > and > select * from os_customer_master where updated_by=null; > > but it gives 0 rows.then how will I select this values. Umm.. Is this a text field? What happens when you select for string length=0? IMO for a string/char field, NULL will be different than empty string but I am still puzzled why '' did not work... Bye Shridhar -- QOTD: "Our parents were never our age."
On Tuesday 26 Nov 2002 10:31 am, Areski wrote: > Hello All, > > > I would like to know if there are a way to see the lasts queries executed. > For example, if you work with php then apache interact with the postmaster > to run your queries. Then it's could be really interresting to intend to > find > the slowest queries of my web application. > > I know that in Mysql there are a log file in which it is stored ! There are settings in postgresql.conf to control what gets logged - I personally like to log using the system logger, but that's not your only option. See the administrator's manual - 3.4.2. Logging and Debugging -- Richard Huxton
Tina Messmann <tina.messmann@xinux.de> writes:
> does this include the template1 database? So i have to vacuum the
> tempate1 database too on a regular basis to avoid the rolling over of
> the transaction id?
Only if you're in the habit of modifying template1.
If you make occasional one-time changes to template1 (like, say,
installing plpgsql in it) you can do a "vacuum freeze" afterward
to make sure all is well; then you needn't include template1 in
your list of things to vacuum regularly.
regards, tom lane
What about the date field???? Shridhar Daithankar wrote: > On 26 Nov 2002 at 16:02, Savita wrote: > > > Hi, > > I am able to insert the null field using \N in the text file,but while retiving > > the data I am not able to retrive it. > > > > After inserting data I tried this select statement > > > > select * from os_customer_master where updated_by=' '; > > and > > select * from os_customer_master where updated_by=null; > > > > but it gives 0 rows.then how will I select this values. > > Umm.. Is this a text field? What happens when you select for string length=0? > > IMO for a string/char field, NULL will be different than empty string but I am > still puzzled why '' did not work... > > Bye > Shridhar > > -- > QOTD: "Our parents were never our age." > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
On 26 Nov 2002 at 16:40, Savita wrote: > What about the date field???? ISNULL didn't work on date field? That's surprising.. > Shridhar Daithankar wrote: > > Umm.. Is this a text field? What happens when you select for string length=0? > > IMO for a string/char field, NULL will be different than empty string but I am > > still puzzled why '' did not work... I am looking thr. documentation but I don't see any equivalent of char_length for a timestamp. Bye Shridhar -- One of the advantages of being a captain is being able to ask foradvice without necessarily having to take it. -- Kirk, "Dagger of the Mind", stardate 2715.2
Hi Tino, These query does not work. Tino Wildenhain wrote: > Hi Savita, > > --On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com> > wrote: > > > Hi, > > I am able to insert the null field using \N in the text file,but while > > retiving the data I am not able to retrive it. > > > > After inserting data I tried this select statement > > > > select * from os_customer_master where updated_by=' '; > > and > > select * from os_customer_master where updated_by=null; > > > > but it gives 0 rows.then how will I select this values. > > Nothing to do with copy :) > Just try: > > select * from os_customer_master where isnull updated_by; > > Regards > Tino -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
Hi Savita, --On Dienstag, 26. November 2002 17:01 +0530 Savita <savita@india.hp.com> wrote: > Hi Tino, > > These query does not work. Sorry, was a typo. But it schould at least serve as a hint, shouldnt it? ;) From documentation: ---- excerpt --- To check whether a value is or is not null, use the constructs expression IS NULL expression IS NOT NULL or the equivalent, but nonstandard, constructs expression ISNULL expression NOTNULL Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.) Some applications may (incorrectly) require that expression = NULL returns true if expression evaluates to the null value. To support these applications, the run-time option transform_null_equals can be turned on (e.g., SET transform_null_equals TO ON;). PostgreSQL will then convert x = NULL clauses to x IS NULL. This was the default behavior in releases 6.5 through 7.1. ---- excerpt --- Regards Tino > > Tino Wildenhain wrote: > >> Hi Savita, >> >> --On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com> >> wrote: >> >> > Hi, >> > I am able to insert the null field using \N in the text file,but while >> > retiving the data I am not able to retrive it. >> > >> > After inserting data I tried this select statement >> > >> > select * from os_customer_master where updated_by=' '; >> > and >> > select * from os_customer_master where updated_by=null; >> > >> > but it gives 0 rows.then how will I select this values. >> >> Nothing to do with copy :) >> Just try: >> >> select * from os_customer_master where isnull updated_by; >> >> Regards >> Tino > > -- > Best Regards > - Savita > ---------------------------------------------------- > Hewlett Packard (India) > +91 80 2051288 (Phone) > 847 1288 (HP Telnet) > ---------------------------------------------------- > >
I think you need to take a step back and pause for a minute to clear your mind. Now, try: SELECT * FROM os_customer_master WHERE updated_by is null; and then: SELECT * FROM os_customer_master WHERE updated_by is not null; One of these will show you the tuples you are looking for. If it's the first then it was a simple misunderstanding. If it's the second there is something else going on if there should be NULLs. Look for rows with nothing shown for this column, if there are any they must have invalid data which can't be converted to string form, or can be converted to string form but are converted to a zero length string or one with spaces. Well it's a starting point; if not entirely accurate it's probably accurate enough for a now considering it's probably just a misunderstanding. -- Nigel Andrews On Tue, 26 Nov 2002, Savita wrote: > Hi Tino, > > These query does not work. > > > Tino Wildenhain wrote: > > > Hi Savita, > > > > --On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com> > > wrote: > > > > > Hi, > > > I am able to insert the null field using \N in the text file,but while > > > retiving the data I am not able to retrive it. > > > > > > After inserting data I tried this select statement > > > > > > select * from os_customer_master where updated_by=' '; > > > and > > > select * from os_customer_master where updated_by=null; > > > > > > but it gives 0 rows.then how will I select this values. > > > > Nothing to do with copy :) > > Just try: > > > > select * from os_customer_master where isnull updated_by; > > > > Regards > > Tino >
Hi Thanks to all of you for the help this works now with IS NULL or IS NOT NULL. "Nigel J. Andrews" wrote: > I think you need to take a step back and pause for a minute to clear your mind. > > Now, try: > > SELECT * FROM os_customer_master WHERE updated_by is null; > > and then: > > SELECT * FROM os_customer_master WHERE updated_by is not null; > > One of these will show you the tuples you are looking for. If it's the first > then it was a simple misunderstanding. If it's the second there is something > else going on if there should be NULLs. Look for rows with nothing shown for > this column, if there are any they must have invalid data which can't be > converted to string form, or can be converted to string form but are converted > to a zero length string or one with spaces. > > Well it's a starting point; if not entirely accurate it's probably accurate > enough for a now considering it's probably just a misunderstanding. > > -- > Nigel Andrews > > On Tue, 26 Nov 2002, Savita wrote: > > > Hi Tino, > > > > These query does not work. > > > > > > Tino Wildenhain wrote: > > > > > Hi Savita, > > > > > > --On Dienstag, 26. November 2002 16:02 +0530 Savita <savita@india.hp.com> > > > wrote: > > > > > > > Hi, > > > > I am able to insert the null field using \N in the text file,but while > > > > retiving the data I am not able to retrive it. > > > > > > > > After inserting data I tried this select statement > > > > > > > > select * from os_customer_master where updated_by=' '; > > > > and > > > > select * from os_customer_master where updated_by=null; > > > > > > > > but it gives 0 rows.then how will I select this values. > > > > > > Nothing to do with copy :) > > > Just try: > > > > > > select * from os_customer_master where isnull updated_by; > > > > > > Regards > > > Tino > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Best Regards - Savita ---------------------------------------------------- Hewlett Packard (India) +91 80 2051288 (Phone) 847 1288 (HP Telnet) ----------------------------------------------------
On Mon, 25 Nov 2002, Tom Lane wrote: > "scott.marlowe" <scott.marlowe@ihs.com> writes: > > Also, full vacuums are required every so often to keep the transaction id > > from rolling over. > > Not so; a plain vacuum is fine for that. The critical point is that > *every* table in *every* database has to be vacuumed (plain or full) > at least once every billion transactions or so. Really? Sorry for the misiniformation. I could have sworn that I read it on this or the hackers mailing list that only full vacuums could reset the transaction counter. Thanks for the catch.