473,727 Members | 2,018 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 .
Nov 23 '05 #1
3 2362
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 ma*******@postg resql.org

Nov 23 '05 #2
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: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Matthew T. O'Connor
Sent: Thursday, May 20, 2004 12:54 AM
To: Carlos
Cc: 'p************@ 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 ma*******@postg resql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3
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: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org] On Behalf Of Matthew T. O'Connor
Sent: Thursday, May 20, 2004 12:54 AM
To: Carlos
Cc: 'p************@ 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 ma*******@postg resql.org

---------------------------(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

Nov 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
7835
by: Kevin Ollivier | last post by:
Hi all, I've come across a problem that has me stumped, and I thought I'd send a message to the gurus to see if this makes sense to anyone else. =) Basically, I'm trying to upload a series of files via FTP. I'm using ftplib to do it, and for each file I'm using transfercmd("STOR " + myfile) to get the socket, then uploading 4096 bytes at a time and providing status updates via a GUI interface. Finally, I close the socket, set it to...
3
10754
by: Matt Rink | last post by:
Getting an "incorrect settings: 'ANSI_NULLS., QUOTED_IDENTIFIER'." error after creating a view. We wanted a composite unique constraint that ignored nulls, so we set up a view using the following script: /* --- start --- */ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON
1
1819
by: Socketd | last post by:
Hi all Will autovacuum both VACUUM and ANALYZE all databases, so that I can delete my script which runs daily: "vacuumdb -a -z"? br socketd (a Postgresql newbie) ps: Please cc to me as I am not on this list
4
1705
by: Dani | last post by:
Hi, I'm hoping that some of you will have experience with uploading images via ASP to an Access db on a remote server. I've scoured the threads for code samples, recommendations for particular components (must be able to upload directly to the db), etc., but all I've found are suggestions on uploading to a directory. Any and all help will be sincerely appreciated. TIA,
19
1777
by: Linda | last post by:
In classic ASP I used to have a file called settings.asp included on every page of my web, it consisted of a number of different settings unique to this application, among them the database path etc. I used to set some of the values contidional to which server executed the script (see listing below). This way I could download the files, edit and test run them locally, then put them back on the production server again without having to...
1
2036
by: Steve Crawford | last post by:
I've been running pg_autovacuum for a week or so, now and while I like it I'm having trouble trusting it. I have several small (~0-50 record) working tables that have high insert/update/delete activity. When I first set up pg_autovacuum they were being vacuumed every ~1-4 hours during the day and less often at night. Most of the time the tuple count was reasonable. Now these tables haven't been vacuumed for a couple of days although...
0
1211
by: Sim Zacks | last post by:
I reconfigured the redhat startup script for postgresql to work with version 8 and that is working fine. I thought I could put autovacuum in the startup script and that didn't cause any problems, but it didn't turn on and I had to start it manually. Does anyone know why it didn't catch? I was watching the bootup and it seemed that the program was actually called. (a screen flew by my eyes very quickly but since it was the last thing, I...
0
12085
by: sonu | last post by:
I have following client side code which i have used in my asp.net project SummaryFeatured Resources from the IBM Business Values Solution Center WHITEPAPER : CRM Done Right Improve the likelihood of CRM success from less than 20 percent to 60 percent. WHITEPAPER :
0
1684
by: Assyst | last post by:
Hi All, Nice to come back after a long time o the Scipts Forum , Now Can any body tell me 1 . Steps to sctivate the AUTOVACUUM thread ? 2 . Can we believe the AUTOVACUUM process ? I will be greately helpful if some one can answer my above queries.
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9260
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9120
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8103
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6703
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.