Hello,
We have installed postgres 8.2.0
default time zone which postgres server using is
template1=# SHOW timezone;
TimeZone
---
ETC/GMT-5
(1 row)
But we want to set this timezone parameter to IST.
Our system timezone is also in IST. We are using solaris.
Please provide me some help
soni de wrote:
But we want to set this timezone parameter to IST.
Our system timezone is also in IST. We are using solaris.
This is the performance-list, and this is not a performance-related
question. Please use the pgsql-general or pgsql-novice list for this
kind of questions.
PostgreSQL
am Tue, dem 19.06.2007, um 13:12:58 +0530 mailte soni de folgendes:
> Hello,
>
> We have installed postgres 8.2.0
>
> default time zone which postgres server using is
>
> template1=# SHOW timezone;
> TimeZone
> ---
> ETC/GMT-5
> (1 row)
>
>
> But we want to set this timezone pa
Hi list members,I have a question regarding hardware issues for a SDI (Spatial data infrastructure). It will consist of PostgreSQL with PostGIS and a UMN Mapserver/pmapper set up.At our institute we are currently establishing a small GIS working group. The data storage for vector data should be the
Tom Lane wrote:
Karl Wright <[EMAIL PROTECTED]> writes:
- At any given time, there are up to 100 of these operations going on at
once against the same database.
It sounds like your hardware is far past "maxed out". Which is odd
since tables with a million or so rows are pretty small for moder
At our institute we are currently establishing a small GIS working group.
The data storage for vector data should be the central PostGIS system.
Raster data will be held in file system.
Mostly the users are accessing the data base in read only mode. From the
client side there is not much write acc
On Jun 13, 2007, at 11:43 AM, Markus Schiltknecht wrote:
In the mean time, I've figured out that the box in question peaked
at about 1450 NOTPMs with 120 warehouses with RAID 1+0. I'll try to
compare again to RAID 6.
Is there any place where such results are collected?
There is the ill-use
An overnight VACUUM helped things quite a bit. I am now getting
throughput of around 75 transactions per minute, where before I was
getting 30. Also, the CPU is no longer pegged, and the machines load
average has dropped to an acceptable 6-10 from somewhere above 20.
While this is still pret
On Mon, Jun 18, 2007 at 04:35:11PM -0700, Steve Atkins wrote:
>
> On Jun 18, 2007, at 4:09 PM, [EMAIL PROTECTED] wrote:
>
> The tricky bits are going to be defining the problem and creating the
> alogrithm to do the maths from input to output.
Why not methodically discuss the the alogrithms on
Can we please trim this down to just advocacy?
On Jun 18, 2007, at 1:17 PM, Joshua D. Drake wrote:
Jonah H. Harris wrote:
On 6/18/07, Andreas Kostyrka <[EMAIL PROTECTED]> wrote:
As a cynic, I might ask, what Oracle is fearing?
As a realist, I might ask, how many times do we have to answer th
Karl Wright wrote:
> This particular run lasted four days before a VACUUM became essential.
> The symptom that indicates that VACUUM is needed seems to be that the
> CPU usage of any given postgresql query skyrockets. Is this essentially
> correct?
Are you saying you weren't used to run VACUU
Alvaro Herrera wrote:
Karl Wright wrote:
This particular run lasted four days before a VACUUM became essential.
The symptom that indicates that VACUUM is needed seems to be that the
CPU usage of any given postgresql query skyrockets. Is this essentially
correct?
Are you saying you weren't
"Karl Wright" <[EMAIL PROTECTED]> writes:
> This particular run lasted four days before a VACUUM became essential. The
> symptom that indicates that VACUUM is needed seems to be that the CPU usage of
> any given postgresql query skyrockets. Is this essentially correct?
Postgres is designed on t
Karl Wright wrote:
> Alvaro Herrera wrote:
> >Karl Wright wrote:
> >
> >>This particular run lasted four days before a VACUUM became essential.
> >>The symptom that indicates that VACUUM is needed seems to be that the
> >>CPU usage of any given postgresql query skyrockets. Is this essentially
>
On Tue, 2007-06-19 at 09:37 -0400, Karl Wright wrote:
> Alvaro Herrera wrote:
> > Karl Wright wrote:
> >
> >> This particular run lasted four days before a VACUUM became essential.
> >> The symptom that indicates that VACUUM is needed seems to be that the
> >> CPU usage of any given postgresql q
Gang,
Hoping you all can help me with a rather bizarre issue that I've run
across. I don't really need a solution, I think I have one, but I'd
really like to run it by everyone in case I'm headed in the wrong
direction.
I'm running a small Slony (v1.1.5)/postgresql 8.0.4 cluster (on
Re
Gregory Stark wrote:
"Karl Wright" <[EMAIL PROTECTED]> writes:
This particular run lasted four days before a VACUUM became essential. The
symptom that indicates that VACUUM is needed seems to be that the CPU usage of
any given postgresql query skyrockets. Is this essentially correct?
Postgre
Alvaro Herrera wrote:
Karl Wright wrote:
Alvaro Herrera wrote:
Karl Wright wrote:
This particular run lasted four days before a VACUUM became essential.
The symptom that indicates that VACUUM is needed seems to be that the
CPU usage of any given postgresql query skyrockets. Is this essentia
Kurt Overberg <[EMAIL PROTECTED]> writes:
> In my investigation of this anomaly, I noticed that the data/ dir on
> db1 (the master) is around 60 Gigs. The data directory on the slaves
> is around 25Gb. After about 3 months of head scratching, someone on
> the irc channel suggested that it m
Kurt Overberg wrote:
In my investigation of this anomaly, I noticed that the data/ dir on db1
(the master) is around 60 Gigs. The data directory on the slaves is
around 25Gb. After about 3 months of head scratching, someone on the
irc channel suggested that it may be due to index bloat. Al
In response to Karl Wright <[EMAIL PROTECTED]>:
> Alvaro Herrera wrote:
> > Karl Wright wrote:
> >> Alvaro Herrera wrote:
> >>> Karl Wright wrote:
> >>>
> This particular run lasted four days before a VACUUM became essential.
> The symptom that indicates that VACUUM is needed seems to b
Bill Moran wrote:
In response to Karl Wright <[EMAIL PROTECTED]>:
Alvaro Herrera wrote:
Karl Wright wrote:
Alvaro Herrera wrote:
Karl Wright wrote:
This particular run lasted four days before a VACUUM became essential.
The symptom that indicates that VACUUM is needed seems to be that the
"Karl Wright" <[EMAIL PROTECTED]> writes:
> Fine - but what if the previous vacuum is still in progress, and does not
> finish in 5 minutes?
Yes, well, there are problems with this design but the situation is already
much improved in 8.2 and there are more improvements on the horizon.
But it's l
A useful utility that I've found is PgFouine. It has an option to
analyze VACUUM VERBOSE logs. It has been instrumental in helping me
figure out whats been going on with my VACUUM that is taking 4+
hours, specifically tracking the tables that are taking the longest.
I highly recommend ch
Karl Wright <[EMAIL PROTECTED]> writes:
> Also, as I said before, I have done extensive query analysis and found
> that the plans for the queries that are taking a long time are in fact
> very reasonable. Here's an example from the application log of a query
> that took way more time than its p
Tom Lane wrote:
Karl Wright <[EMAIL PROTECTED]> writes:
Also, as I said before, I have done extensive query analysis and found
that the plans for the queries that are taking a long time are in fact
very reasonable. Here's an example from the application log of a query
that took way more time
Karl Wright <[EMAIL PROTECTED]> writes:
> [2007-06-18 09:39:49,797]ERROR Plan: Index Scan using i1181764142395 on
> intrinsiclink (cost=0.00..14177.29 rows=5 width=253)
> [2007-06-18 09:39:49,797]ERROR Plan: Index Cond: ((jobid = $2) AND
> ((childidhash)::text = ($3)::text))
> [2007-06-18 09
[EMAIL PROTECTED] (Kurt Overberg) writes:
> In my investigation of this anomaly, I noticed that the data/ dir on
> db1 (the master) is around 60 Gigs. The data directory on the slaves
> is around 25Gb. After about 3 months of head scratching, someone on
> the irc channel suggested that it may be
Tom Lane wrote:
Karl Wright <[EMAIL PROTECTED]> writes:
[2007-06-18 09:39:49,797]ERROR Plan: Index Scan using i1181764142395 on
intrinsiclink (cost=0.00..14177.29 rows=5 width=253)
[2007-06-18 09:39:49,797]ERROR Plan: Index Cond: ((jobid = $2) AND
((childidhash)::text = ($3)::text))
[2007-
[EMAIL PROTECTED] writes:
sizes etc., I wondered about the hardware. Most things were about the I/O
of harddisks, RAM and file system. Is the filesystem that relevant?
Because wo want to stay at Ubuntu because of the software support,
espacially for the GIS-Systems. I think we need at least ab
Gregory Stark writes:
VACUUM doesn't require shutting down the system, it doesn't lock any tables or
otherwise prevent other jobs from making progress. It does add extra i/o but
In addition to what Gregory pointed out, you may want to also consider using
Autovacuum. That may also help.
Alvaro Herrera writes:
How large is the database? I must admit I have never seen a database
that took 4 days to vacuum. This could mean that your database is
humongous, or that the vacuum strategy is wrong for some reason.
Specially with 16GB of RAM.
I have a setup with several databases (t
"Karl Wright" <[EMAIL PROTECTED]> writes:
>> In this case it looks like the planner is afraid that that's exactly
>> what will happen --- a cost of 14177 suggests that several thousand row
>> fetches are expected to happen, and yet it's only predicting 5 rows out
>> after the filter. It's using
Karl Wright <[EMAIL PROTECTED]> writes:
> I did an ANALYZE on that table and repeated the explain, and got this:
> ...
> ... even more wildly wrong.
Hmm. You might need to increase the statistics target for your larger
tables. It's probably not a big deal for queries like this one, but I'm
worri
Karl Wright wrote:
> I did an ANALYZE on that table and repeated the explain, and got this:
>
> >>
> metacarta=> analyze intrinsiclink;
> ANALYZE
> metacarta=> explain select count(*) from intrinsiclink where
> jobid=1181766706097 and
> childidhash='7E130F3B688687757187F1638D8776ECEF3009E0'
Karl Wright writes:
I'm not writing off autovacuum - just the concept that the large tables
aren't the ones that are changing. Unfortunately, they *are* the most
dynamically updated.
Would be possible for you to partition the tables?
By date or some other fashion to try to have some tables n
Chris Browne <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] (Kurt Overberg) writes:
>> In my investigation of this anomaly, I noticed that the data/ dir on
>> db1 (the master) is around 60 Gigs. The data directory on the slaves
>> is around 25Gb. After about 3 months of head scratching, someone
"Gregory Stark" <[EMAIL PROTECTED]> writes:
> "Karl Wright" <[EMAIL PROTECTED]> writes:
>
>>> In this case it looks like the planner is afraid that that's exactly
>>> what will happen --- a cost of 14177 suggests that several thousand row
>>> fetches are expected to happen, and yet it's only predi
"soni de" <[EMAIL PROTECTED]> writes:
> But we want to set this timezone parameter to IST.
Which "IST" are you interested in? Irish, Israel, or Indian Standard Time?
Postgres prefers to use the zic timezone names, which are less
ambiguous. Try this to see likely options:
regression=# select * f
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 Tool
Richard,
Thanks for the feedback! I found oid2name and have been mucking
about with it, but haven't really found anything that stands out
yet. Most of the tables/indexes I'm comparing across machines seem
to take up a similar amount of disk space. I think I'm going to have
to get fancy
Campbell, Lance wrote:
Please think simple. I stress the word simple. The real challenge here
is getting the formulas correct. Someone mentioned to not focus on the
values but just get something out there for everyone to help tweak. I
agree!
What questions do you think should be asked in ord
Campbell, Lance writes:
3) I suggested JavaScript because most people that get started with
PostgreSQL will go to the web in order to find out about issues relating
Why not c?
It could then go into contrib.
Anyways.. language is likely the least important issue..
As someone mentioned.. once t
Francisco and Richard,
Why ask about disk or raid? How would that impact any settings in
postgresql.conf?
I did forget the obvious question:
What OS are you using?
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http:
Kurt Overberg wrote:
Richard,
Thanks for the feedback! I found oid2name and have been mucking about
with it, but haven't really found anything that stands out yet. Most of
the tables/indexes I'm comparing across machines seem to take up a
similar amount of disk space. I think I'm going to
Campbell, Lance wrote:
Francisco and Richard,
Why ask about disk or raid? How would that impact any settings in
postgresql.conf?
Well, random_page_cost will depend on how fast your disk system can
locate a non-sequential page. If you have a 16-disk RAID-10 array that's
noticably less time th
What version of PostgreSQL are you using?
I think newbies should be pushed a bit to use the latest versions, maybe
with some advice on how to setup the apt sources (in debian/ubuntu) to get
them.
How much memory will be available to PostgreSQL?
How many connections will be made to Po
Chris,
I took your advice, and I had found that sl_log_1 seems to be causing
some of the problem. Here's the result of a VACUUM VERBOSE
mydb # vacuum verbose _my_cluster.sl_log_1 ;
INFO: vacuuming "_my_cluster.sl_log_1"
INFO: index "sl_log_1_idx1" now contains 309404 row versions in
1421
Campbell, Lance writes:
Francisco and Richard,
Why ask about disk or raid? How would that impact any settings in
postgresql.conf?
If the user has 2 disks and says that he will do a lot of updates he could
put pg_xlog in the second disk.
---(end of broadcast)---
On 6/19/07, Francisco Reyes <[EMAIL PROTECTED]> wrote:
Campbell, Lance writes:
> Francisco and Richard,
> Why ask about disk or raid? How would that impact any settings in
> postgresql.conf?
If the user has 2 disks and says that he will do a lot of updates he could
put pg_xlog in the second d
Yudhvir,
I completely agree. I was just putting together a similar email.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu
From: [EMAIL PROTEC
Francisco Reyes wrote:
I have a setup with several databases (the largest of which is 1TB
database) and I do a nightly vacuum analyze for ALL databases. It takes
about 22 hours. And this is with constant updates to the large 1TB
database. This is with Postgresql 8.1.3
22h nightly? Wow, you h
On Tue, 19 Jun 2007 12:58:26 -0400
Francisco Reyes <[EMAIL PROTECTED]> wrote:
> Campbell, Lance writes:
> > 3) I suggested JavaScript because most people that get started with
> > PostgreSQL will go to the web in order to find out about issues relating
>
> Why not c?
Why not whatever and instal
D'Arcy,
I wanted to put it on the www.postgresql.org site. That is what I said
in my original email. I don't believe anyone from the actual project
has contacted me.
I am setting up a JavaScript version first. If someone wants to do a
different one feel free. I will have all of the calculation
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 dif
On 6/19/07, Campbell, Lance <[EMAIL PROTECTED]> wrote:
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 o
Heikki Linnakangas writes:
On a serious note, the index vacuum improvements in 8.2 might help you
to cut that down. You seem to be happy with your setup, but I thought
I'd mention it..
I am really, really trying.. to go to 8.2.
I have a thread on "general" going on for about a week.
I am unab
Campbell, Lance writes:
For the "6) Are your searches:"
How about having "many simple"
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Tue, 19 Jun 2007, Y Sidhu wrote:
On 6/19/07, Francisco Reyes <[EMAIL PROTECTED]> wrote:
Campbell, Lance writes:
> Francisco and Richard,
> Why ask about disk or raid? How would that impact any settings in
> postgresql.conf?
If the user has 2 disks and says that he will do a lot of u
On Tue, 19 Jun 2007, Campbell, Lance wrote:
Memory
There are many different ways to ask about memory. Rather than ask a
series of questions I went with a single question, #2. If it is better
to ask about the memory in a series of questions then please give me the
questions you would ask and w
Vivek Khera writes:
no file will ever be larger than 1Gb I didn't need to make any
adjustments to the newfs parameters.
You should consider using "newfs -i 65536" for partitions to be used for
postgresql. You will get more usable space and will still have lots of free
inodes.
For my next
> there are three catagories of memory useage
>
> 1. needed by other software
> 2. available for postgres
> 3. needed by the OS
There's actually only two required memory questions:
M1) How much RAM do you have on this machine?
M2) Is this:
() Dedicated PostgreSQL Server?
() Serve
On Tue, Jun 19, 2007 at 10:49:01AM -0700, Y Sidhu wrote:
> On 6/19/07, Francisco Reyes <[EMAIL PROTECTED]> wrote:
> >
> >Campbell, Lance writes:
> >
> >> Francisco and Richard,
> >> Why ask about disk or raid? How would that impact any settings in
> >> postgresql.conf?
> >
> >If the user has 2 dis
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
random_p
On Mon, 2007-06-18 at 17:55 +0200, David Tokmatchi wrote:
> I am DBA for Oracle and beginner on Postgres. For an company in
> France, I must make a comparative study, between Postgres and Oracle.
> Can you send any useful document which can help me.
> Scalability ? Performance? Benchmark ? Availab
On Thu, 2007-06-14 at 16:14 -0700, Craig James wrote:
> Looking for replication solutions, I find:
>
> Slony-I
> Seems good, single master only, master is a single point of failure,
> no good failover system for electing a new master or having a failed
> master rejoin the cluster. Slave databa
Kurt Overberg <[EMAIL PROTECTED]> writes:
> mydb # vacuum verbose _my_cluster.sl_log_1 ;
> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
> versions in 13764 pages
> DETAIL: 0 dead row versions cannot be removed yet.
Hmm. So you don't have a long-running-transactions proble
PFC <[EMAIL PROTECTED]> writes:
>> What version of PostgreSQL are you using?
> I think newbies should be pushed a bit to use the latest versions,
How about pushed *hard* ? I'm constantly amazed at the number of people
who show up in the lists saying they installed 7.3.2 or whatever random
That's the thing thats kinda blowing my mind here, when I look at
that table:
db1=# select count(*) from _my_cluster.sl_log_1 ;
count
---
6788
(1 row)
As far as my DB is concerned, there's only ~7000 rows (on average)
when I look
in there (it does fluctuate, I've seen it go as high as
Lance,
> The parameters I would think we should calculate are:
>
> max_connections
>
> shared_buffers
>
> work_mem
>
> maintenance_work_mem
>
> effective_cache_size
>
> random_page_cost
Actually, I'm going to argue against messing with random_page_cost. It's a
cannon being used when a slingshot
Kurt Overberg <[EMAIL PROTECTED]> writes:
> That's the thing thats kinda blowing my mind here, when I look at
> that table:
> db1=# select count(*) from _my_cluster.sl_log_1 ;
> count
> ---
>6788
> (1 row)
Well, that's real interesting. AFAICS there are only two possibilities:
1. VACU
On Tue, 19 Jun 2007, Josh Berkus wrote:
"What OS Are You Using", of course, needs to have Linux, Solaris, BSD, OSX
and Windows. At some point, this tool will also need to generate for the
user any shmem settings that they need to make on the OS.
I also noticed that on FreeBSD (6.2) at least t
Kurt Overberg <[EMAIL PROTECTED]> wrote:
>
> That's the thing thats kinda blowing my mind here, when I look at
> that table:
>
> db1=# select count(*) from _my_cluster.sl_log_1 ;
> count
> ---
>6788
> (1 row)
>
> As far as my DB is concerned, there's only ~7000 rows (on average)
> whe
Campbell, Lance writes:
max_connections
Shouldn't that come straight from the user?
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
It would be cool if someone started a generic configuration+benchmark
utility that could be used with virtually any software. Something like
this:
1. Create a configuration file parser for your specific application, be
it PostgreSQL, MySQL, Apache, whatever.
2. Create a min/max or X,Y,Z configura
On Jun 19, 2007, at 7:26 PM, Tom Lane wrote:
Kurt Overberg <[EMAIL PROTECTED]> writes:
That's the thing thats kinda blowing my mind here, when I look at
that table:
db1=# select count(*) from _my_cluster.sl_log_1 ;
count
---
6788
(1 row)
Well, that's real interesting. AFAICS there
Kurt Overberg <[EMAIL PROTECTED]> writes:
> Okay, I've grabbed pg_filedump and got it running on the appropriate
> server.
> I really have No Idea how to read its output though. Where does the
> ctid from sl_log_1
> appear in the following listing?
ctid is (block number, item number)
> Block
On Wed, 20 Jun 2007, Mike Benoit wrote:
It would be cool if someone started a generic configuration+benchmark
utility that could be used with virtually any software.
It would be cool. It would also be impossible.
Create a benchmark script for the application that returns relevant
metrics. I
Greg Smith <[EMAIL PROTECTED]> writes:
> On Wed, 20 Jun 2007, Mike Benoit wrote:
>> I don't think something like this would be very difficult at all to
>> write
> Here I just smile and say that proves you've never tried to write one :)
I'm with Greg on this. It's not that easy to optimize in a
On Tue, 19 Jun 2007, Josh Berkus wrote:
I don't think the "mostly reads / mostly writes" question covers anything,
nor is it likely to produce accurate answers. Instead, we need to ask the
users to characterize what type of application they are running:
T1) Please characterize the general type
On Wed, 20 Jun 2007, Tom Lane wrote:
I think what would be much more useful in the long run is some serious
study of the parameters themselves. For instance, random_page_cost is a
self-admitted oversimplification of reality.
If I could figure out who would sponsor such a study that's what I'
81 matches
Mail list logo
