I have been researching how to improve my overall performance of
postgres. I am a little confused on the reasoning for how work-mem is
used in the postgresql.conf file. The way I understand the
documentation is you define with work-mem how much memory you want to
allocate per search. Couldn't
If I set work-mem at a particular amount of memory how do I answer the
following questions:
1) How many of my queries were able to run inside the memory I
allocated for work-mem?
2) How many of my queries had to run from disk because work-mem
was not set high enough?
3) If
I would like to get someone's recommendations on the best initial
settings for a dedicated PostgreSQL server. I do realize that there are
a lot of factors that influence how one should configure a database. I
am just looking for a good starting point. Ideally I would like the
database to reside
Huxton [mailto:[EMAIL PROTECTED]
Sent: Friday, February 23, 2007 10:29 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Recommended Initial Settings
Campbell, Lance wrote:
I would like to get someone's recommendations on the best initial
settings for a dedicated
I am a Java Software architect, DBA, and project manager for the
University of Illinois, Department of Web Services. We use PostgreSQL
to serve about 2 million pages of dynamic content a month; everything
from calendars, surveys, forms, discussion boards, RSS feeds, etc. I am
really impressed
/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-Original Message-
From: Mario Gonzalez [mailto:[EMAIL PROTECTED]
Sent: Monday, June 18, 2007 10:16 AM
To: Campbell, Lance
Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org
Please read the whole email before replying:
I love the feedback I have received but I feel that somehow I did not
communicate the intent of this mini project very well. So let me
outline a few basics and who the audience was intended for.
Mini project title:
Initial Configuration
://webservices.uiuc.edu
-Original Message-
From: Francisco Reyes [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 19, 2007 11:58 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
Campbell, Lance writes:
3) I
PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Y Sidhu
Sent: Tuesday, June 19, 2007 12:49 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
On 6/19/07, Francisco Reyes [EMAIL PROTECTED] wrote:
Campbell, Lance writes:
Francisco
PROTECTED] On Behalf Of D'Arcy J.M.
Cain
Sent: Tuesday, June 19, 2007 12:32 PM
To: Francisco Reyes
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
On Tue, 19 Jun 2007 12:58:26 -0400
Francisco Reyes [EMAIL PROTECTED] wrote:
Campbell, Lance writes:
3
Below is a link to the HTML JavaScript configuration page I am creating:
http://www.webservices.uiuc.edu/postgresql/
I had many suggestions. Based on the feedback I received, I put
together the initial list of questions. This list of questions can be
change.
Memory
There are many
Now I am at the difficult part, what parameters to calculate and how to
calculate them. Everything below has to do with PostgreSQL version 8.2:
The parameters I would think we should calculate are:
max_connections
shared_buffers
work_mem
maintenance_work_mem
effective_cache_size
It is amazing how many times you can read something before it actually
sinks in.
There seems to be two possible approaches to optimizing PostgreSQL 8.2:
File caching approach:
This approach is based on the fact that the OS will cache the necessary
PostgreSQL files. The key here is to set the
Of Greg Smith
Sent: Thursday, June 21, 2007 2:15 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Volunteer to build a configuration tool
On Wed, 20 Jun 2007, Campbell, Lance wrote:
If everything I said is correct then I agree Why have
effective_cache_size? Why not just go down
I have installed pgAdmin III 1.6. In the tool when you click on a
particular table you can select a tab called Statistics. This tab has
all kinds of info on your table. For some reason the only info I see is
for table size, toast table size and indexes size. Is there a reason
that the other 15
Sent: 24/07/07, 18:23:53
Subject: Re: [PERFORM] Table Statistics with pgAdmin III
Campbell, Lance a écrit :
I have installed pgAdmin III 1.6. In the tool when you click on a
particular table you can select a tab called Statistics. This tab has
all kinds of info on your table
Terminology Question:
If I use the following statement:
I am backing up schema XYZ every 30 minutes.
Does this statement imply that I am only backing up the definition of
the data? Or does it mean that I am backing up the definition of the
data and the data within the schema object?
Should installation questions be sent here or to the admin listserv?
OS: redhat linux
Version of PostgreSQL: 8.2.4
I had a group that now manages our server set up a directory/partition
for us to put postgreSQL into. The directory is called pgsql_data. The
directory is more than a
]
Sent: Thursday, August 23, 2007 12:08 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Installing PostgreSQL
Campbell, Lance wrote:
Should installation questions be sent here or to the admin listserv?
Probably the pgsql-general/admin/novice lists
OS: redhat
: Thursday, August 23, 2007 12:26 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Installing PostgreSQL
Campbell, Lance wrote:
Richard,
So what you are saying is that if you install PostgeSQL into a data
directory /abc/data you could then stop the database, move
PGSQL 8.2.4
I have noticed a slight spike in the amount of CPU usage in the last few
weeks. I am sure it has to do with a change or two that was made to
some queries. What is the best way to log the SQL that is being
executed? I would prefer to limit the size of the log file to 2 G. Is
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Campbell,
Lance
Sent: Friday, October 26, 2007 3:27 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Suggestions on an update query
PostgreSql version 8.2.4
Memory = 8 Gig
CPUs 1 dual core Zeon running at 3.0
PostgreSql version 8.2.4
Memory = 8 Gig
CPUs 1 dual core Zeon running at 3.0
I have a problem with an update query taking over 10 hours in order to
run. I rebooted my server. I ran the SQL command analyze. Could
you please help me with any suggestions? I have included the two tables
Of Gregory
Stark
Sent: Friday, October 26, 2007 9:05 PM
To: Joshua D. Drake
Cc: Campbell, Lance; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Suggestions on an update query
Joshua D. Drake [EMAIL PROTECTED] writes:
On Fri, 26 Oct 2007 15:31:44 -0500
Campbell, Lance [EMAIL PROTECTED] wrote
PostgreSQL:8.2.4
I am collecting statistics info now on my database. I have used the
following two queries:
select * from pg_stat_all_indexes;
select * from pg_statio_all_indexes;
How can I use the information from these two queries to better optimize
my indexes? Or maybe even get
PostgreSQL: 8.2.4
Does anyone have any companies they would recommend using for
performance tuning training of PostgreSQL for Linux? Or general DBA
training?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
Does the amount of memory allocate to work_mem get subtracted from
shared_buffers?
Example:
If work_mem is 1M and there are 10 connections and shared_buffers is
100M then would the total be 90 M left for shared_buffers?
Or does the amount of memory allocated for work_mem have nothing
, 2007 2:08 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] work_mem and shared_buffers
On Fri, 9 Nov 2007 12:08:57 -0600
Campbell, Lance [EMAIL PROTECTED] wrote:
How do you know when you should up the value of work_mem? Just play
with the number
: Scott Marlowe [mailto:[EMAIL PROTECTED]
Sent: Friday, November 09, 2007 1:13 PM
To: Campbell, Lance
Cc: Heikki Linnakangas; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] work_mem and shared_buffers
On Nov 9, 2007 12:08 PM, Campbell, Lance [EMAIL PROTECTED] wrote:
How do you know when
How can I clear the pg_stats views without restarting PostgreSQL? I
thought there was a function.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
PostgreSQL: 8.2
I am looking at the possibility of storing files in some of my database
tables. My concern is obviously performance. I have configured
PostgreSQL to take advantage of Linux file caching. So my PostgreSQL
does not have a large setting for shared_buffers even though I have 24G
of
/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Alexander Staubo
Sent: Thursday, December 13, 2007 1:39 PM
To: Campbell, Lance
Cc: pgsql
13, 2007 2:22 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org list
Subject: Re: [PERFORM] Putting files into fields in a table
On Dec 13, 2007, at 2:09 PM, Campbell, Lance wrote:
I did not see much info in the 8.2 documentation on BLOB. I did ready
about bytea or binary data type
PostgreSQL: 8.2
My understanding is that when one creates a large object there is no way
to link the large object to a field in a table so that cascading delete
can occur. Is this correct? My understanding is that you have to
manually delete the large object.
I also read something about
I posted this question to the admin group. I just realized that I
should have sent it here.
I just read the following article:
http://people.planetpostgresql.org/mha/index.php?/archives/162-PostgreSQ
L-vs-64-bit-windows.html
Would there be benefits in running PostgreSQL in a 32 bit
I back up around 10 Gig of data every half hour using pg_dump. I don't
backup the entire database at once. Instead I backup at the schema
namespace level. But I do all of them every half hour. It takes four
minutes. That includes the time to copy the files to the backup server.
I do each
PostgreSQL: 8.2.4
We currently backup all of our database tables per schema using pg_dump
every half hour. We have been noticing that the database performance
has been very poor during the backup process. How can I improve the
performance?
Server Specs:
Dedicated DB server
Database
Marlowe [mailto:[EMAIL PROTECTED]
Sent: Monday, May 05, 2008 10:06 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Backup causing poor performance - suggestions
On Mon, May 5, 2008 at 8:59 AM, Campbell, Lance [EMAIL PROTECTED] wrote:
PostgreSQL: 8.2.4
You should
PostgreSQL: 8.2
When you create a foreign key to a table is there an index that is
created on the foreign key automatically?
Example:
Table A has a field called ID.
Table B has a field called fk_a_id which has a constraint of being a
foreign key to table A to field ID.
Is there an
Message-
From: Shaun Thomas [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 08, 2008 12:19 PM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Creating a foreign key
On Thu, 2008-05-08 at 17:52 +0100, Campbell, Lance wrote:
Is there an index automatically
PostgreSQL: 8.2
How can you identify how many inserts are being done in a given time
frame for a database?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
My e-mail address has
PostgreSQL: 8.2
How can I identify how many inserts and updates are being done in a
given time frame for a database?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
My e-mail
PostgreSQL 8.3
Linux RedHat 4.X
24G of memory
When loading a file generated from pg_dumpall is there a key setting in
the configuration file that would allow the load to work faster.
Thanks,
Lance Campbell
Project Manager/Software Architect/DBA
Web Services at Public Affairs
217-333-0382
PostgreSQL 8.4.3
Linux Redhat 5.0
Question: How much memory do I really need?
From my understanding there are two primary strategies for setting up
PostgreSQL in relationship to memory:
1) Rely on Linux to cache the files. In this approach you set the
shared_buffers to a
PostgreSQL 8.4.3
OS: Linux Red Hat 4.x
I changed my strategy with PostgreSQL recently to use a large segment of
memory for shared buffers with the idea of caching disk blocks. How can
I see how much memory PostgreSQL is using for this?
I tried:
ps aux | grep post | sort -k4
This
217-333-0382
-Original Message-
From: Greg Smith [mailto:g...@2ndquadrant.com]
Sent: Monday, March 29, 2010 11:54 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How much memory is PostgreSQL using
Campbell, Lance wrote:
Or is there some way to ask
PostgreSQL 9.0.x
We have around ten different applications that use the same database. When one
particular application is active it does an enormous number of inserts. Each
insert is very small. During this time the database seems to slow down in
general. The application in question is
Affairs
217-333-0382
From: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Campbell, Lance
Sent: Thursday, March 29, 2012 12:59 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] database slowdown while a lot of inserts occur
PostgreSQL
PostgreSQL 9.0.x
When PostgreSQL storage is using a relatively large raid 5 or 6 array is
there any value in having your tables distributed across multiple tablespaces
if those tablespaces will exists on the same raid array? I understand the
value if you were to have the tablespaces on
PostgreSQL: 9.1
OS: Red Hat 6
This PostgreSQL instance is used for dynamic web content. It runs on a
dedicated server.
So I need some PostgreSQL monitoring advice. There are two basic strategies
that I am aware of for configuring PostgreSQL:
1) In Memory: With an in memory option you
50 matches
Mail list logo
