Thanks for all the replies. As of right now I think I have it narrowed down to
checkpoints based on the iostat activity I see when the hangs occur as well as
the checkpoint_timeout defaulting to 5 min.
I've upped checkpoint_warnings to 3600 to confirm but also made a few other
changes. I
Cantor wrote:
On Aug 7, 1:26 pm, Arthernan [EMAIL PROTECTED] wrote:
I want to learn how a real database works. And I am about to
start reading the Postgre source code.
Are there any online documents that may document the code? Even
if it was a general guideline.
Any
On Wed, 2007-07-25 at 19:32 +0300, Devrim GÜNDÜZ wrote:
Hi,
On Sat, 2007-07-21 at 15:57 -0700, Steve Wampler wrote:
I need the Java and Python interfaces supplied with
(from 8.1.9):
postgresql-jdbc-8.1.4-1.centos.1
postgresql-python-8.1.9-1.el4s1.1
The actual problem is I
Hi all,
I am facing a performance issue here. Whenever I do a count(*) on a table
that contains about 300K records, it takes few minutes to complete. Whereas
my other application which is counting 500K records just take less than 10
seconds to complete.
I have indexed all the essential
Peter Marius [EMAIL PROTECTED] writes:
I created a view on all entries with stop=null.
The DB-Interaction should be done over the view,
so I added rules for INSERT, UPDATE an DELETE.
Insert and Update work fine, but the DELETE_RULE
stopps after the first UPDATE statement in the Rule-Body,
The out-of-the-box configs are pretty awful for you. Read some
list archives (from this list and pgsql-performance) and also take a look
at http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
On Fri, 10 Aug 2007, Nathan Wilhelmi wrote:
Hello - Just installed 8.2.4 on a Solaris 9
Hi all,
I have a table mytable to log the validity of
data records with start and stop time.
To see, which records are still valid,
I created a view on all entries with stop=null.
The DB-Interaction should be done over the view,
so I added rules for INSERT, UPDATE an DELETE.
Insert and Update
Hello - Just installed 8.2.4 on a Solaris 9 box. It's an 8-way (15000
MHz sparc) with 32GB of ram. We don't know the exact table structure yet
or access patterns, although the first thing that will be looked at is a
Sesame triple store DB. I would expect that this DB will be more skewed
to
Hello.
I have a number of deadlock because of the foreign key constraint:
Assume we have 2 tables: A and B. Table A has a field fk referenced to
B.idas a foreign key constraint.
-- transaction #1
BEGIN;
...
INSERT INTO A(x, y, fk) VALUES (1, 2, 666);
...
END;
-- transaction #2
BEGIN;
UPDATE
Ok, partial day results. Looks like my changes have not solved the problem,
just spread it out a little more (as would be expected based on your
responses). The delays are now shorter (about half) but occur more frequently
(maybe 1x / minute). The params I used are:
bgwriter_lru_percent =
On 09/08/2007 23:40, [EMAIL PROTECTED] wrote:
My database is restored from a dump file every day. How I know that this
database is up to date (as it has no timestamp in any table).
If I create a file, I can know when I created it by seeing its property.
How I can do the same thing with a back
On Fri, 10 Aug 2007, Marc Rossi wrote:
Thanks for the heads up. The box in question is a dual cpu (xeon dual
cores) with 8 gig a pair of 10k 146gb raid 1 arrays. I have the
pg_xlog dir on one array (along with the OS) the rest of the data on
the other array by itself.
Yeah, that's kinda
On 10.08.2007, at 06:58, .ep wrote:
Hi, what if I need to do a count with a WHERE condition? E.g.,
SELECT count(*) from customers where cust_id = 'georgebush' and
created_on current_date - interval '1 week' ;
Can I get the info about this from somewhere in the pg system tables
as well?
On Aug 10, 2007, at 5:56 , Alejandro Torras wrote:
Is there some way to put values in a INSERT statement
without taking care of apostrophes?
In example:
INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
This is pretty much a solved problem: don't interpolate into SQL
statements. Use
--- Raymond O'Donnell [EMAIL PROTECTED] wrote:
On 09/08/2007 23:40, [EMAIL PROTECTED] wrote:
My database is restored from a dump file every day. How I know that this
database is up to date (as it has no timestamp in any table).
If I create a file, I can know when I created it by
On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote:
I just want to confirm that the cluster/MVCC issues are due to
transaction visibility. Assuming that no concurrent access is happening
to a given table when the cluster command is issued (when takes it
visibility snapshot), it is safe
On 8/10/07, Mary Ellen Fitzpatrick [EMAIL PROTECTED] wrote:
Hi,
We are running postgresql-7.3.3 and we had a hardware controller and
disk failure on the system. And of course the database does not appear
to be backup anywhere.
I was reading about PITR and was wondering if that is
Hi Tom,
thanks for your answer, I have also thought of combining
the statements, but my SQL-knowledge is too small for that.
I thought, the example with mylog would be better to
demonstrate the problem, but it's missing the point.
Below, if have added the code with my real problem.
What I want
On 8/10/07, Mary Ellen Fitzpatrick [EMAIL PROTECTED] wrote:
Merlin,
I am willing to spend the time, as it is an important table. I am a
newbie at this and it has fallen into my lap.
From what the user tells me, it is only the one table.
Not sure if fsync was running, how can I tell?
check
On 10/08/2007 18:40, Richard Broersma Jr wrote:
If you need to, you can append your own timestamp to the dump file if you need
it.
Heh heh, I just gave this same advice in reply to the post that prompted
this idea. :-)
Thanks,
Ray.
On Fri, 10 Aug 2007, Nathan Wilhelmi wrote:
are the out of the box configs pretty good or are there any recommended
changes I should be making to start with?
The out of the box configuration is wildly inappropriate for your system,
and there are few examples of something appropriate to point
On 09/08/2007 23:40, [EMAIL PROTECTED] wrote:
My database is restored from a dump file every day. How I know that this
database is up to date (as it has no timestamp in any table).
If I create a file, I can know when I created it by seeing its property.
How I can do the same thing with a back
Hi,
We are running postgresql-7.3.3 and we had a hardware controller and
disk failure on the system. And of course the database does not appear
to be backup anywhere.
I was reading about PITR and was wondering if that is applicable to my
version. We do have pg_xlog files and I am
On Aug 10, 9:42 pm, [EMAIL PROTECTED] (A.
Kretschmer) wrote:
am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
Hi all,
I am facing a performance issue here. Whenever I do a count(*) on a table
that contains about 300K records, it takes few minutes to complete. Whereas
-- English --
Hi,
Is there some way to put values in a INSERT statement
without taking care of apostrophes?
In example:
INSERT INTO persons VALUES ('Harry', 'O'Callaghan');
^^^
I think that it can be used some kind of length-marker
to help the
On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote:
So if I understand correctly, a timestamp_tz is ...
... stored as UTC in the backend
... sent to clients shifted by whatever timezone was
requested by the client by one of several mechanisms:
- set timezone to
Doh! It was the Vista firewall. I've got a couple of other services running on
that machine and they worked. That's why I assumed that it wasn't a FW problem
(using Vistas internal).But it was thanks. Date: Thu, 9 Aug 2007 10:06:19
-0700 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC:
On Mon, Aug 06, 2007 at 04:44:29PM -0300, Ranieri Mazili wrote:
1) Can I use a function that will return a string in a where clause like
bellow?
2) Can I use a function that will return a string to return the list of
columns that I want to show like below?
not in sql. you can in pl/pgsql.
Hi all,
Given the following tables -
create table people (
person_id text primary key,
person_name text,
[...etc...]
);
create table items (
item_id text primary key,
item_name text,
is_required boolean,
[...etc...]
);
create table items_for_people (
On Thu, 9 Aug 2007, Marc Rossi wrote:
as well as made changes to the bgwriter settings as shown below (taken
from a post in the pgsql-performance list)
bgwriter_lru_percent = 20.0 # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 200 # 0-1000 buffers max
On Thu, Aug 09, 2007 at 10:49:38AM -0500, Scott Marlowe wrote:
On 8/9/07, Louis-David Mitterrand
[EMAIL PROTECTED] wrote:
Hi,
After our 7.4 to 8.2 upgrade using debian tools, we realized that some
of our timestamps with tz had shifted:
For example '2007-04-01 00:00:00+02' became
Hello,
the following is a rework of what I wanted to achieve when posting
yesterday. Since that post didn't seem to attract attention, I tried
to do what I wanted to do differently.
Now, creating a RULE for a view allows defining several operations for
it. I was happy to discover that actually
On 8/10/07, Raymond O'Donnell [EMAIL PROTECTED] wrote:
- how can I find those people who don't have _all_ of the items which
are marked required?
In other words, how do I select those rows in people which don't have
a corresponding row in items_for_people for *each* row in items
which has
On 10/08/2007 21:29, Scott Marlowe wrote:
select table1.id from table1 where table1.id is not in (select id from table2);
Duh! I should have thought of that thanks for that, and apologies
for the stupidity (blame it on the glass of wine I had with dinner!).
Ray.
I just want to confirm that the cluster/MVCC issues are due to
transaction visibility. Assuming that no concurrent access is happening
to a given table when the cluster command is issued (when takes it
visibility snapshot), it is safe to cluster that table. Correct?
--
Brad Nicholson
Raymond O'Donnell [EMAIL PROTECTED] writes:
Actually, it *would* be really handy if pg_dump included a timestamp in
the plain-text output.
Use the verbose option.
regards, tom lane
---(end of broadcast)---
TIP 4: Have
On 8/10/07, carter ck [EMAIL PROTECTED] wrote:
Hi all,
I am facing a performance issue here. Whenever I do a count(*) on a table
that contains about 300K records, it takes few minutes to complete. Whereas
my other application which is counting 500K records just take less than 10
seconds to
It seems to me that the real solution is for me to stop using the database as
an IPC system to pass somewhat time-critical data between processes. Given
the time constraints I'm working under this unfortunately was the quickest
route.
At least for the first 5 minutes. :) I was wondering
Greg -
Thanks for the heads up. The box in question is a dual cpu (xeon dual cores)
with 8 gig a pair of 10k 146gb raid 1 arrays. I have the pg_xlog dir on one
array (along with the OS) the rest of the data on the other array by itself.
Given that this is a production system I'm going to
Karsten Hilbert [EMAIL PROTECTED] writes:
On Fri, Aug 10, 2007 at 10:11:29AM +0200, Louis-David Mitterrand wrote:
So if I understand correctly, a timestamp_tz is ...
... stored as UTC in the backend
... sent to clients shifted by whatever timezone was
requested by the client by one of
On 8/10/07, Raymond O'Donnell [EMAIL PROTECTED] wrote:
On 10/08/2007 21:29, Scott Marlowe wrote:
select table1.id from table1 where table1.id is not in (select id from
table2);
Duh! I should have thought of that thanks for that, and apologies
for the stupidity (blame it on the glass
Peter Marius [EMAIL PROTECTED] writes:
I thought, the example with mylog would be better to
demonstrate the problem, but it's missing the point.
Below, if have added the code with my real problem.
CREATE RULE sru AS ON UPDATE TO myview DO INSTEAD
(
UPDATE mytable SET stop = now() WHERE id
am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
Hi all,
I am facing a performance issue here. Whenever I do a count(*) on a table
that contains about 300K records, it takes few minutes to complete. Whereas
my other application which is counting 500K records just
In response to .ep [EMAIL PROTECTED]:
On Aug 10, 9:42 pm, [EMAIL PROTECTED] (A.
Kretschmer) wrote:
am Fri, dem 10.08.2007, um 17:46:11 +0800 mailte carter ck folgendes:
Hi all,
I am facing a performance issue here. Whenever I do a count(*) on a table
that contains about 300K
On 10/08/2007 19:10, Tom Lane wrote:
Use the verbose option.
[/me tries it out]
That'll do nicely - thanks.
Ray.
---
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
[EMAIL PROTECTED]
AFAICS, all you need to do is swap the ordering of those two operations.
It might help to understand that what you write as an INSERT/VALUES is
really more like INSERT ... SELECT ... FROM myview WHERE ..., the WHERE
condition being the same as was given in the UPDATE myview command
that the
Hi,
I'm moving from the mysql camp and quite liking things like functions
and such, but a lot of my functionality depends on queries such as
SELECT id, name, start_date
FROM customer
WHERE name LIKE 'eri%';
These kinds of queries are super fast in MySQL because eri% type
conditions
On 8/10/07, .ep [EMAIL PROTECTED] wrote:
Hi,
I'm moving from the mysql camp and quite liking things like functions
and such, but a lot of my functionality depends on queries such as
SELECT id, name, start_date
FROM customer
WHERE name LIKE 'eri%';
These kinds of queries are
El jue, 09-08-2007 a las 14:51 +, John Coulthard escribió:
Hi
I'm trying to set up a new webserver running php and pgsql. PHP was
connecting to postgres but I needed to install the php-gd module and now I
get the error...
PHP Warning: pg_connect() [a
49 matches
Mail list logo
