Re: [PERFORM] Huge performance problem between 7.4.1 and 8.0.3 -
Sounds like either someone is holding a lock on your pg8 db, or maybe you need a vacuum full. No amount of normal vacuuming will fix a table that needs a vacuum full. Although if that were the case I'd expect you to have slow queries regardless of the number of concurrent connections. Maybe you should check who is holding locks. David Robert Creager wrote: I am, and it is. It's ANALYZING and VACUUM'ing tables every interval (5 minutes - 8.0.3). Right now, for that last 4 hours, I'm not VACUUMing the 7.4.1 database and it's still clicking along at .2 second queries. Last year (7.4.1), I noticed that it took about a week of heavy activity (for this DB) before I'd really need a vacuum. That's when I put in the 5 min cron. When I first switched over to 8.0.3, I was still running the cron vacuum. I got into big trouble when I had vacuum's backed up for 6 hours. That's when I started noticing the query problem, and the CS numbers being high. 7.4.1 vacuums every 5 minutes always take 30 seconds (when I'm watching). Cheers, Rob When grilled further on (Sun, 17 Jul 2005 23:48:20 -0400), Matthew T. O'Connor matthew@zeut.net confessed: Robert Creager wrote: For 8.03, pg_autovacuum is running. On 7.4.1, I set up a cron job to vacuum analyze every 5 minutes. Are you sure that pg_autovacuum is doing it's job? Meaning are you sure it's vacuuming as often as needed? Try to run it with -d2 or so and make sure that it is actually doing the vacuuming needed. -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] performance problems ... 100 cpu utilization
What is the load average on this machine? Do you do many updates? If you do a lot of updates, perhaps you haven't vacuumed recently. We were seeing similar symptoms when we started load testing our stuff and it turned out we were vacuuming too infrequently. David Dennis wrote: Qingqing Zhou wrote: Are you sure 100% CPU usage is solely contributed by Postgresql? Also, from the ps status you list, I can hardly see that's a problem because of problem you mentioned below. The postgreSQL processes are what is taking up all the cpu. There aren't any other major applications on the machine. Its a dedicated database server, only for this application. It doesn't seem to make sense that PostgreSQL would be maxed out at this point. I think given the size of the box, it could do quite a bit better. So, what is going on? I don't know. Dennis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] plain inserts and deletes very slow
Did you vacuum full? When you do lots of inserts and deletes, dead tuples get left behind. When you vacuum, postgres will reuse those dead tuples, but if you don't vacuum for a long time these tuples will build up lots. Even when you vacuum in this case, the dead tuples are still there, although they are marked for reuse. Vacuuming full actually removes the dead tuples. If you vacuum (normal) regularly, then the number of dead tuples will stay down, as they are regularly marked for reuse. David Enrico Weigelt wrote: * Enrico Weigelt [EMAIL PROTECTED] wrote: forgot to mention: + linux-2.6.9 + postgres-7.4.6 + intel celeron 2ghz + intel ultra ata controller + 768mb ram cu -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). David Enrico Weigelt wrote: * David Mitchell [EMAIL PROTECTED] wrote: Did you vacuum full? When you do lots of inserts and deletes, dead tuples get left behind. When you vacuum, postgres will reuse those dead tuples, but if you don't vacuum for a long time these tuples will build up lots. Even when you vacuum in this case, the dead tuples are still there, although they are marked for reuse. Vacuuming full actually removes the dead tuples. I'm doing a VACUUM ANALYZE every 6 hours. vacuum'ing manually doesnt seem to have any effect on that. cu -- David Mitchell Software Engineer Telogis NOTICE: This message (including any attachments) contains CONFIDENTIAL INFORMATION intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] plain inserts and deletes very slow
Hmm, you said you don't experience this when executing the query manually. What adapter are you using to access postgres from your application? libpq, npgsql or something else? And what is your method for running the query 'manually'. Are you running it locally or from a remote machine or what? Regards David Enrico Weigelt wrote: * David Mitchell [EMAIL PROTECTED] wrote: Perhaps if you are doing a lot of inserts and deletes, vacuuming every 6 minutes would be closer to your mark. Try vacuuming every 15 minutes for a start and see how that affects things (you will have to do a vacuum full to get the tables back into shape after them slowing down as they have). hmm. I've just done vacuum full at the moment on these tables, but it doesnt seem to change anything :( cu -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] How can I speed up this function?
The function I have exits the loop when the count hits 100 yes, but the
inner loop can push the count up as high as necessary to select all the
statements for a transaction, so by the time it exits, the count could
be much higher. I do want to limit the statements, but I want to get
enough for complete transactions.
David
Gnanavel Shanmugam wrote:
But in the function you are exiting the loop when the count hits 100. If you
do not want to limit the statements then remove the limit clause from the
query I've written.
with regards,
S.Gnanavel
-Original Message-
From: [EMAIL PROTECTED]
Sent: Tue, 28 Jun 2005 16:29:32 +1200
To: [EMAIL PROTECTED]
Subject: Re: [PERFORM] How can I speed up this function?
Hi Gnanavel,
Thanks, but that will only return at most 100 statements. If there is a
transaction with 110 statements then this will not return all the
statements for that transaction. We need to make sure that the function
returns all the statements for a transaction.
Cheers
David
Gnanavel Shanmugam wrote:
Merge the two select statements like this and try,
SELECT t.trans_id as ID,s.id, s.transaction_id, s.table_name, s.op,
s.data
FROM pending_trans AS t join dbmirror.pending_statement AS s
on (s.transaction_id=t.id)
WHERE t.fetched = false order by t.trans_id,s.id limit 100;
If the above query works in the way you want, then you can also do the
update
using the same.
with regards,
S.Gnanavel
-Original Message-
From: [EMAIL PROTECTED]
Sent: Tue, 28 Jun 2005 14:37:34 +1200
To: pgsql-performance@postgresql.org
Subject: [PERFORM] How can I speed up this function?
We have the following function in our home grown mirroring package, but
it isn't running as fast as we would like. We need to select statements
from the pending_statement table, and we want to select all the
statements for a single transaction (pending_trans) in one go (that is,
we either select all the statements for a transaction, or none of
them).
We select as many blocks of statements as it takes to top the 100
statement limit (so if the last transaction we pull has enough
statements to put our count at 110, we'll still take it, but then we're
done).
Here is our function:
CREATE OR REPLACE FUNCTION dbmirror.get_pending()
RETURNS SETOF dbmirror.pending_statement AS
$BODY$
DECLARE
count INT4;
transaction RECORD;
statement dbmirror.pending_statement;
BEGIN
count := 0;
FOR transaction IN SELECT t.trans_id as ID
FROM pending_trans AS t WHERE fetched = false
ORDER BY trans_id LIMIT 50
LOOP
update pending_trans set fetched = true where trans_id =
transaction.id;
FOR statement IN SELECT s.id, s.transaction_id, s.table_name,
s.op,
s.data
FROM dbmirror.pending_statement AS s
WHERE s.transaction_id = transaction.id
ORDER BY s.id ASC
LOOP
count := count + 1;
RETURN NEXT statement;
END LOOP;
IF count 100 THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Table Schemas:
CREATE TABLE dbmirror.pending_trans
(
trans_id oid NOT NULL,
fetched bool DEFAULT false,
CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
)
WITHOUT OIDS;
CREATE TABLE dbmirror.pending_statement
(
id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
transaction_id oid NOT NULL,
table_name text NOT NULL,
op char NOT NULL,
data text NOT NULL,
CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE UNIQUE INDEX idx_stmt_tran_id_id
ON dbmirror.pending_statement
USING btree
(transaction_id, id);
Postgres 8.0.1 on Linux.
Any Help would be greatly appreciated.
Regards
--
David Mitchell
Software Engineer
Telogis
---(end of
broadcast)---
TIP 8: explain analyze is your friend
--
David Mitchell
Software Engineer
Telogis
--
David Mitchell
Software Engineer
Telogis
---(end of broadcast)---
TIP 8: explain analyze is your friend
[PERFORM] How can I speed up this function?
We have the following function in our home grown mirroring package, but
it isn't running as fast as we would like. We need to select statements
from the pending_statement table, and we want to select all the
statements for a single transaction (pending_trans) in one go (that is,
we either select all the statements for a transaction, or none of them).
We select as many blocks of statements as it takes to top the 100
statement limit (so if the last transaction we pull has enough
statements to put our count at 110, we'll still take it, but then we're
done).
Here is our function:
CREATE OR REPLACE FUNCTION dbmirror.get_pending()
RETURNS SETOF dbmirror.pending_statement AS
$BODY$
DECLARE
count INT4;
transaction RECORD;
statement dbmirror.pending_statement;
BEGIN
count := 0;
FOR transaction IN SELECT t.trans_id as ID
FROM pending_trans AS t WHERE fetched = false
ORDER BY trans_id LIMIT 50
LOOP
update pending_trans set fetched = true where trans_id =
transaction.id;
FOR statement IN SELECT s.id, s.transaction_id, s.table_name, s.op,
s.data
FROM dbmirror.pending_statement AS s
WHERE s.transaction_id = transaction.id
ORDER BY s.id ASC
LOOP
count := count + 1;
RETURN NEXT statement;
END LOOP;
IF count 100 THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Table Schemas:
CREATE TABLE dbmirror.pending_trans
(
trans_id oid NOT NULL,
fetched bool DEFAULT false,
CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
)
WITHOUT OIDS;
CREATE TABLE dbmirror.pending_statement
(
id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
transaction_id oid NOT NULL,
table_name text NOT NULL,
op char NOT NULL,
data text NOT NULL,
CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE UNIQUE INDEX idx_stmt_tran_id_id
ON dbmirror.pending_statement
USING btree
(transaction_id, id);
Postgres 8.0.1 on Linux.
Any Help would be greatly appreciated.
Regards
--
David Mitchell
Software Engineer
Telogis
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] How can I speed up this function?
Christopher Kings-Lynne wrote: What's wrong with Slony? Because it's not multi-master. Our mirroring package is. -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] How can I speed up this function?
Christopher Kings-Lynne wrote: I'm curious - how did you write a multi-master replication package in pgsql, when pgsql doesn't have 2 phase commits or any kind of distributed syncing or conflict resolution in a release version? We didn't write it entirely in pgsql, there is a worker process that takes care of actually committing to the database. Cheers -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How can I speed up this function?
Hi Keith, Unfortunately, we must have those sorts. The statements within a transaction must be executed on the slave in the same order as they were on the master, and similarly, transactions must also go in the same order. As for aliasing the tables, that is just a remnant from previous versions of the code. Thanks David Keith Worthington wrote: I'm still a newbie and it may not affect performance but why are you aliasing the tables? Can you not simply use FOR transaction IN SELECT trans_id FROM pending_trans WHERE fetched = false ORDER BY trans_id LIMIT 50 and FOR statement IN SELECT id, transaction_id, table_name, op, data FROM dbmirror.pending_statement WHERE pending_statement.transaction_id = transaction.trans_id ORDER BY pending_statement.id I am pretty sure that the ORDER BY is slowing down both of these queries. Since you are going to go through the whole table eventually do you really need to sort the data at this point? -- David Mitchell Software Engineer Telogis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How can I speed up this function?
Hi Gnanavel,
Thanks, but that will only return at most 100 statements. If there is a
transaction with 110 statements then this will not return all the
statements for that transaction. We need to make sure that the function
returns all the statements for a transaction.
Cheers
David
Gnanavel Shanmugam wrote:
Merge the two select statements like this and try,
SELECT t.trans_id as ID,s.id, s.transaction_id, s.table_name, s.op, s.data
FROM pending_trans AS t join dbmirror.pending_statement AS s
on (s.transaction_id=t.id)
WHERE t.fetched = false order by t.trans_id,s.id limit 100;
If the above query works in the way you want, then you can also do the
update
using the same.
with regards,
S.Gnanavel
-Original Message-
From: [EMAIL PROTECTED]
Sent: Tue, 28 Jun 2005 14:37:34 +1200
To: pgsql-performance@postgresql.org
Subject: [PERFORM] How can I speed up this function?
We have the following function in our home grown mirroring package, but
it isn't running as fast as we would like. We need to select statements
from the pending_statement table, and we want to select all the
statements for a single transaction (pending_trans) in one go (that is,
we either select all the statements for a transaction, or none of them).
We select as many blocks of statements as it takes to top the 100
statement limit (so if the last transaction we pull has enough
statements to put our count at 110, we'll still take it, but then we're
done).
Here is our function:
CREATE OR REPLACE FUNCTION dbmirror.get_pending()
RETURNS SETOF dbmirror.pending_statement AS
$BODY$
DECLARE
count INT4;
transaction RECORD;
statement dbmirror.pending_statement;
BEGIN
count := 0;
FOR transaction IN SELECT t.trans_id as ID
FROM pending_trans AS t WHERE fetched = false
ORDER BY trans_id LIMIT 50
LOOP
update pending_trans set fetched = true where trans_id =
transaction.id;
FOR statement IN SELECT s.id, s.transaction_id, s.table_name, s.op,
s.data
FROM dbmirror.pending_statement AS s
WHERE s.transaction_id = transaction.id
ORDER BY s.id ASC
LOOP
count := count + 1;
RETURN NEXT statement;
END LOOP;
IF count 100 THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Table Schemas:
CREATE TABLE dbmirror.pending_trans
(
trans_id oid NOT NULL,
fetched bool DEFAULT false,
CONSTRAINT pending_trans_pkey PRIMARY KEY (trans_id)
)
WITHOUT OIDS;
CREATE TABLE dbmirror.pending_statement
(
id oid NOT NULL DEFAULT nextval('dbmirror.statement_id_seq'::text),
transaction_id oid NOT NULL,
table_name text NOT NULL,
op char NOT NULL,
data text NOT NULL,
CONSTRAINT pending_statement_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE UNIQUE INDEX idx_stmt_tran_id_id
ON dbmirror.pending_statement
USING btree
(transaction_id, id);
Postgres 8.0.1 on Linux.
Any Help would be greatly appreciated.
Regards
--
David Mitchell
Software Engineer
Telogis
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
David Mitchell
Software Engineer
Telogis
---(end of broadcast)---
TIP 8: explain analyze is your friend
