Thread: Settings for autovacuum for batch uploading of data?
Hello Forum,
I would appreciate it if you could recommend settings to use auto-vacuum in my version 7.4 database. I am uploading several thousands records in the database at a rate of ~1 second per record (the data is uploading from a different server). I have noticed that the performance of the uploading is improved if I vacuum the database every so often so I decided to take advantage of auto-vacuuming to maintain the best performance. Since I am uploading several thousands records, I set autovacuum to 50 minutes with –s 3000 in the hope that a great deal of data would be committed before a regular vacuum would take place and that vacuuming would interfere the least possible with the uploading.
Thanks in advance for your recommendations.
More important than sleep value is the vacuum threshold. If for example you are expecting to import 5000 rows of data, you can tell pg_autovacuum to vacuum after every 5000 changes with like pg_autovacuum -v 5000 -V 0 Matthew On Wed, 2004-05-19 at 17:09, Carlos wrote: > Hello Forum, > > I would appreciate it if you could recommend settings to use > auto-vacuum in my version 7.4 database. I am uploading several > thousands records in the database at a rate of ~1 second per record > (the data is uploading from a different server). I have noticed that > the performance of the uploading is improved if I vacuum the database > every so often so I decided to take advantage of auto-vacuuming to > maintain the best performance. Since I am uploading several thousands > records, I set autovacuum to 50 minutes with –s 3000 in the hope that > a great deal of data would be committed before a regular vacuum would > take place and that vacuuming would interfere the least possible with > the uploading. > > > > Thanks in advance for your recommendations. > >
Thank you for your reply Matthew. Do you know if I can stop auto-vacuum, and re-start it with different parameters as the uploading continues? I do not know if my killing the auto-vacuum process would terminate my application also. I want to start auto-vacuum with different parameters to tune the vacuuming to my application. I would like to vacuum often as the data is being uploaded (inserts mainly) because I want to maintain a high speed of upload Thanks again. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Matthew T. O'Connor Sent: Thursday, May 20, 2004 12:54 AM To: Carlos Cc: 'pgsql-general@postgresql.org' Subject: Re: [GENERAL] Settings for autovacuum for batch uploading of data? More important than sleep value is the vacuum threshold. If for example you are expecting to import 5000 rows of data, you can tell pg_autovacuum to vacuum after every 5000 changes with like pg_autovacuum -v 5000 -V 0 Matthew On Wed, 2004-05-19 at 17:09, Carlos wrote: > Hello Forum, > > I would appreciate it if you could recommend settings to use > auto-vacuum in my version 7.4 database. I am uploading several > thousands records in the database at a rate of ~1 second per record > (the data is uploading from a different server). I have noticed that > the performance of the uploading is improved if I vacuum the database > every so often so I decided to take advantage of auto-vacuuming to > maintain the best performance. Since I am uploading several thousands > records, I set autovacuum to 50 minutes with -s 3000 in the hope that > a great deal of data would be committed before a regular vacuum would > take place and that vacuuming would interfere the least possible with > the uploading. > > > > Thanks in advance for your recommendations. > > ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Feel free to kill pg_autovacuum and try different parameters as much as you like, killing pg_autovacuum won't hurt anything. You should note that the current implementation doesn't keep any history of information, so when you restart pg_autovacuum, it sets it's update counts to 0. Matthew On Thu, 2004-05-20 at 10:12, Carlos Oliva wrote: > Thank you for your reply Matthew. Do you know if I can stop auto-vacuum, > and re-start it with different parameters as the uploading continues? I do > not know if my killing the auto-vacuum process would terminate my > application also. > > I want to start auto-vacuum with different parameters to tune the vacuuming > to my application. I would like to vacuum often as the data is being > uploaded (inserts mainly) because I want to maintain a high speed of upload > > Thanks again. > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Matthew T. O'Connor > Sent: Thursday, May 20, 2004 12:54 AM > To: Carlos > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] Settings for autovacuum for batch uploading of data? > > More important than sleep value is the vacuum threshold. If for example > you are expecting to import 5000 rows of data, you can tell > pg_autovacuum to vacuum after every 5000 changes with like > > pg_autovacuum -v 5000 -V 0 > > Matthew > > > On Wed, 2004-05-19 at 17:09, Carlos wrote: > > Hello Forum, > > > > I would appreciate it if you could recommend settings to use > > auto-vacuum in my version 7.4 database. I am uploading several > > thousands records in the database at a rate of ~1 second per record > > (the data is uploading from a different server). I have noticed that > > the performance of the uploading is improved if I vacuum the database > > every so often so I decided to take advantage of auto-vacuuming to > > maintain the best performance. Since I am uploading several thousands > > records, I set autovacuum to 50 minutes with -s 3000 in the hope that > > a great deal of data would be committed before a regular vacuum would > > take place and that vacuuming would interfere the least possible with > > the uploading. > > > > > > > > Thanks in advance for your recommendations. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >