Re: [PERFORM] Cost of opening and closing an empty transaction
Jon Leighton wrote: I'm one of the developers of the Ruby on Rails web framework. In some situations, the framework generates an empty transaction block. I.e. we sent a BEGIN and then later a COMMIT, with no other queries in the middle. We currently can't avoid doing this, because a user *may* send queries inside the transaction. I am considering the possibility of making the transaction lazy. So we would delay sending the BEGIN until we have the first query ready to go. If that query never comes then neither BEGIN nor COMMIT would ever be sent. So my question is: is this a worthwhile optimisation to make? In particular, I am wondering whether empty transactions increase the work the database has to do when there are several other connections open? I.e. does it cause contention? If anyone has any insight about other database servers that would also be welcome. The one thing that will be the same for all databases is that saving the two client-server roud trips for BEGIN and COMMIT is probably worth the effort if it happens often enough. The question which resources an empty transaction consumes is probably database specific; for PostgreSQL the expense is not high, as far as I can tell. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Postgres becoming slow, only full vacuum fixes it
Hi,
The problem : Postgres is becoming slow, day after day, and only a full vacuum
fixes the problem.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but we can
focus on a single table for better comprehension.
The queries I am running to test the speed are :
INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1, '2012-06-16
13:39:19', '111');
DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND CONNECTIONID
= 1;
SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
After a full vacuum, they run in about 100ms.
Today, before the full vacuum, they were taking around 500ms.
Below is an explain analyze of the commands AFTER a full vacuum. I did not run
it before, so I can not post relevant info before the vacuum. So, after the
full vacuum :
explain analyze INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1,
'2012-06-16 13:39:19', '111');
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1
loops=1)
Trigger for constraint FK_AWAITINGSTATUSSMPP_MESSAGES: time=0.131 calls=1
Trigger bucardo_add_delta: time=0.454 calls=1
Trigger bucardo_triggerkick_MassSMs: time=0.032 calls=1
Total runtime: 0.818 ms
explain analyze DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;Seq Scan on AWAITINGSTATUSSMPP (cost=0.00..2.29 rows=1
width=6) (actual time=0.035..0.035 rows=0 loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID = 1))
Trigger bucardo_triggerkick_MassSMs: time=0.066 calls=1
Total runtime: 0.146 ms
explain analyze SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = ''
AND CONNECTIONID = 1;
Seq Scan on AWAITINGSTATUSSMPP (cost=0.00..2.29 rows=1 width=557) (actual
time=0.028..0.028 rows=0 loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID = 1))
Total runtime: 0.053 ms
Below are the metadata of the table :
=
CREATE TABLE AWAITINGSTATUSSMPP
(
MESSAGEID bigint NOT NULL,
SMSCMSGID character varying(50) NOT NULL,
CONNECTIONID smallint NOT NULL,
EXPIRE_TIME timestamp without time zone NOT NULL,
RECIPIENT character varying(20) NOT NULL,
CLIENT_MSG_ID character varying(255),
CONSTRAINT PK_AWAITINGSTATUSSMPP PRIMARY KEY (SMSCMSGID, CONNECTIONID),
CONSTRAINT FK_AWAITINGSTATUSSMPP_MESSAGES FOREIGN KEY (MESSAGEID)
REFERENCES MESSAGES (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE AWAITINGSTATUSSMPP OWNER TO postgres;
GRANT ALL ON TABLE AWAITINGSTATUSSMPP TO MassSMsUsers;
CREATE INDEX IX_AWAITINGSTATUSSMPP_MSGID_RCP
ON AWAITINGSTATUSSMPP
USING btree
(MESSAGEID, RECIPIENT);
CREATE TRIGGER bucardo_add_delta
AFTER INSERT OR UPDATE OR DELETE
ON AWAITINGSTATUSSMPP
FOR EACH ROW
EXECUTE PROCEDURE bucardo.bucardo_add_delta_SMSCMSGID|CONNECTIONID();
CREATE TRIGGER bucardo_triggerkick_MassSMs
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON AWAITINGSTATUSSMPP
FOR EACH STATEMENT
EXECUTE PROCEDURE bucardo.bucardo_triggerkick_MassSMs();
=
The table only has about 200 records because it is being used a temporary
storage and records are constantly inserted and deleted.
BUT please don't get hold on this fact, because as I already said, the speed
problem is not restricted to this table. The same problems appear on the
following query
UPDATE MESSAGES SET SENT = SENT + 1 WHERE ID = 143447;
and MESSAGES table has mainly inserts and few deletes...
My postgresql.conf file :
==
port = 5433 # (change requires restart)
max_connections = 100 # (change requires restart)
shared_buffers = 256MB # min 128kB. DoubleIP - Default was 32MB
synchronous_commit = off# immediate fsync at commit. DoubleIP -
Default was on
effective_cache_size = 512MB# DoubleIP - Default was 128MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
silent_mode = on# Run server silently.
log_line_prefix = '%t %d %u ' # special values:
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
autovacuum_naptime = 28800 # time between autovacuum runs.
DoubleIP - default was 1min
autovacuum_vacuum_threshold = 100 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.0# fraction of table size before vacuum.
DoubleIP - default was 0.2
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error
message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
default_text_search_config =
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
Sorry, forgot to mention the most obvious and important information :
My postgres is 8.4.2
On Sep 24, 2012, at 13:33, Kiriakos Tsourapas wrote:
Hi,
The problem : Postgres is becoming slow, day after day, and only a full
vacuum fixes the problem.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but we can
focus on a single table for better comprehension.
The queries I am running to test the speed are :
INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1, '2012-06-16
13:39:19', '111');
DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
After a full vacuum, they run in about 100ms.
Today, before the full vacuum, they were taking around 500ms.
Below is an explain analyze of the commands AFTER a full vacuum. I did not
run it before, so I can not post relevant info before the vacuum. So, after
the full vacuum :
explain analyze INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1,
'2012-06-16 13:39:19', '111');
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1
loops=1)
Trigger for constraint FK_AWAITINGSTATUSSMPP_MESSAGES: time=0.131 calls=1
Trigger bucardo_add_delta: time=0.454 calls=1
Trigger bucardo_triggerkick_MassSMs: time=0.032 calls=1
Total runtime: 0.818 ms
explain analyze DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = ''
AND CONNECTIONID = 1;Seq Scan on AWAITINGSTATUSSMPP (cost=0.00..2.29
rows=1 width=6) (actual time=0.035..0.035 rows=0 loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID = 1))
Trigger bucardo_triggerkick_MassSMs: time=0.066 calls=1
Total runtime: 0.146 ms
explain analyze SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = ''
AND CONNECTIONID = 1;
Seq Scan on AWAITINGSTATUSSMPP (cost=0.00..2.29 rows=1 width=557) (actual
time=0.028..0.028 rows=0 loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID = 1))
Total runtime: 0.053 ms
Below are the metadata of the table :
=
CREATE TABLE AWAITINGSTATUSSMPP
(
MESSAGEID bigint NOT NULL,
SMSCMSGID character varying(50) NOT NULL,
CONNECTIONID smallint NOT NULL,
EXPIRE_TIME timestamp without time zone NOT NULL,
RECIPIENT character varying(20) NOT NULL,
CLIENT_MSG_ID character varying(255),
CONSTRAINT PK_AWAITINGSTATUSSMPP PRIMARY KEY (SMSCMSGID,
CONNECTIONID),
CONSTRAINT FK_AWAITINGSTATUSSMPP_MESSAGES FOREIGN KEY (MESSAGEID)
REFERENCES MESSAGES (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE AWAITINGSTATUSSMPP OWNER TO postgres;
GRANT ALL ON TABLE AWAITINGSTATUSSMPP TO MassSMsUsers;
CREATE INDEX IX_AWAITINGSTATUSSMPP_MSGID_RCP
ON AWAITINGSTATUSSMPP
USING btree
(MESSAGEID, RECIPIENT);
CREATE TRIGGER bucardo_add_delta
AFTER INSERT OR UPDATE OR DELETE
ON AWAITINGSTATUSSMPP
FOR EACH ROW
EXECUTE PROCEDURE bucardo.bucardo_add_delta_SMSCMSGID|CONNECTIONID();
CREATE TRIGGER bucardo_triggerkick_MassSMs
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON AWAITINGSTATUSSMPP
FOR EACH STATEMENT
EXECUTE PROCEDURE bucardo.bucardo_triggerkick_MassSMs();
=
The table only has about 200 records because it is being used a temporary
storage and records are constantly inserted and deleted.
BUT please don't get hold on this fact, because as I already said, the speed
problem is not restricted to this table. The same problems appear on the
following query
UPDATE MESSAGES SET SENT = SENT + 1 WHERE ID = 143447;
and MESSAGES table has mainly inserts and few deletes...
My postgresql.conf file :
==
port = 5433 # (change requires restart)
max_connections = 100 # (change requires restart)
shared_buffers = 256MB # min 128kB. DoubleIP - Default was
32MB
synchronous_commit = off# immediate fsync at commit. DoubleIP
- Default was on
effective_cache_size = 512MB# DoubleIP - Default was 128MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and
csvlog
silent_mode = on# Run server silently.
log_line_prefix = '%t %d %u ' # special values:
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
autovacuum_naptime = 28800 # time between autovacuum runs.
DoubleIP - default was 1min
autovacuum_vacuum_threshold = 100 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.0# fraction of table size before
vacuum. DoubleIP - default was 0.2
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
Hello,
1) upgrade your PostgreSQL installation, there have been numerous
bugfixes releases since 8.4.2
2) you'll have to show us an explain analyze of the slow queries. If I
take a look at those you provided everything run i less than 1ms.
3) with 200 records you'll always have a seqscan
4) how much memory do you have ? shared_buffers = 256MB and
effective_cache_size = 512MB looks OK only if you have between 1 and 2GB
of RAM
5) synchronous_commit = off should only be used if you have a
battery-backed write cache.
6) autovacuum_naptime should be changed only if autovacuum is constantly
running (so if you have dozen of databases in your cluster)
7) are you sure the problem isn't related to Bucardo ?
Julien
On 09/24/2012 13:55, Kiriakos Tsourapas wrote:
Sorry, forgot to mention the most obvious and important information :
My postgres is 8.4.2
On Sep 24, 2012, at 13:33, Kiriakos Tsourapas wrote:
Hi,
The problem : *Postgres is becoming slow, day after day, and only a
full vacuum fixes the problem*.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but
we can focus on a single table for better comprehension.
The queries I am running to test the speed are :
INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1,
'2012-06-16 13:39:19', '111');
DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
After a full vacuum, they run in about 100ms.
Today, before the full vacuum, they were taking around 500ms.
Below is an explain analyze of the commands AFTER a full vacuum. I
did not run it before, so I can not post relevant info before the
vacuum. So, after the full vacuum :
explain analyze INSERT INTO AWAITINGSTATUSSMPP VALUES('143428',
'', 1, '2012-06-16 13:39:19', '111');
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002
rows=1 loops=1)
Trigger for constraint FK_AWAITINGSTATUSSMPP_MESSAGES: time=0.131
calls=1
Trigger bucardo_add_delta: time=0.454 calls=1
Trigger bucardo_triggerkick_MassSMs: time=0.032 calls=1
Total runtime: 0.818 ms
explain analyze DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID =
'' AND CONNECTIONID = 1;Seq Scan on AWAITINGSTATUSSMPP
(cost=0.00..2.29 rows=1 width=6) (actual time=0.035..0.035 rows=0
loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID
= 1))
Trigger bucardo_triggerkick_MassSMs: time=0.066 calls=1
Total runtime: 0.146 ms
explain analyze SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID
= '' AND CONNECTIONID = 1;
Seq Scan on AWAITINGSTATUSSMPP (cost=0.00..2.29 rows=1 width=557)
(actual time=0.028..0.028 rows=0 loops=1)
Filter: (((SMSCMSGID)::text = ''::text) AND (CONNECTIONID
= 1))
Total runtime: 0.053 ms
Below are the metadata of the table :
=
CREATE TABLE AWAITINGSTATUSSMPP
(
MESSAGEID bigint NOT NULL,
SMSCMSGID character varying(50) NOT NULL,
CONNECTIONID smallint NOT NULL,
EXPIRE_TIME timestamp without time zone NOT NULL,
RECIPIENT character varying(20) NOT NULL,
CLIENT_MSG_ID character varying(255),
CONSTRAINT PK_AWAITINGSTATUSSMPP PRIMARY KEY (SMSCMSGID,
CONNECTIONID),
CONSTRAINT FK_AWAITINGSTATUSSMPP_MESSAGES FOREIGN KEY (MESSAGEID)
REFERENCES MESSAGES (ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE AWAITINGSTATUSSMPP OWNER TO postgres;
GRANT ALL ON TABLE AWAITINGSTATUSSMPP TO MassSMsUsers;
CREATE INDEX IX_AWAITINGSTATUSSMPP_MSGID_RCP
ON AWAITINGSTATUSSMPP
USING btree
(MESSAGEID, RECIPIENT);
CREATE TRIGGER bucardo_add_delta
AFTER INSERT OR UPDATE OR DELETE
ON AWAITINGSTATUSSMPP
FOR EACH ROW
EXECUTE PROCEDURE bucardo.bucardo_add_delta_SMSCMSGID|CONNECTIONID();
CREATE TRIGGER bucardo_triggerkick_MassSMs
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON AWAITINGSTATUSSMPP
FOR EACH STATEMENT
EXECUTE PROCEDURE bucardo.bucardo_triggerkick_MassSMs();
=
The table only has about 200 records because it is being used a
temporary storage and records are constantly inserted and deleted.
BUT please don't get hold on this fact, because as I already said,
the speed problem is not restricted to this table. The same problems
appear on the following query
UPDATE MESSAGES SET SENT = SENT + 1 WHERE ID = 143447;
and MESSAGES table has mainly inserts and few deletes...
My postgresql.conf file :
==
port = 5433 # (change requires restart)
max_connections = 100 # (change requires restart)
shared_buffers = 256MB # min 128kB. DoubleIP -
Default was 32MB
synchronous_commit = off# immediate fsync at commit.
DoubleIP - Default was on
effective_cache_size = 512MB# DoubleIP - Default was 128MB
log_destination = 'stderr' # Valid values are
combinations
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote: 5) synchronous_commit = off should only be used if you have a battery-backed write cache. Huh? Are you possibly confusing this with full_page_writes? Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
On 09/24/2012 14:34, Andres Freund wrote: On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote: 5) synchronous_commit = off should only be used if you have a battery-backed write cache. Huh? Are you possibly confusing this with full_page_writes? indeed...! sorry for that (note that you still have a (very) small chance of loosing data with synchronous_commit = off if your server crashes between two commit chunks) Greetings, Andres -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. attachment: jcigar.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
Hi, Thank you for your response. Please find below my answers/comments. On Sep 24, 2012, at 15:21, Julien Cigar wrote: Hello, 1) upgrade your PostgreSQL installation, there have been numerous bugfixes releases since 8.4.2 Not possible right now. It will have to be the last solution. 2) you'll have to show us an explain analyze of the slow queries. If I take a look at those you provided everything run i less than 1ms. Will do so in a couple of days that it will get slow again. 3) with 200 records you'll always have a seqscan Does it really matter? I mean, with 200 records any query should be ultra fast. Right ? 4) how much memory do you have ? shared_buffers = 256MB and effective_cache_size = 512MB looks OK only if you have between 1 and 2GB of RAM I have included the server specs and the results of top commands, showing that we have 8GB ram and how much memory is used/cached/swapped. Personally I don't quite understand the linux memory, but I have posted them hoping you may see something I don't. 5) synchronous_commit = off should only be used if you have a battery-backed write cache. I agree with the comments that have followed my post. I have changed it, knowing there is a small risk, but hoping it will help our performance. 6) autovacuum_naptime should be changed only if autovacuum is constantly running (so if you have dozen of databases in your cluster) As I said, changing the autovacuum values have not changed the problem. So, you may as well consider that we have the default values for autovacuuming... the problem existed with the default values too. 7) are you sure the problem isn't related to Bucardo ? Not at all sure... I have no idea. Can you suggest of a way to figure it out ? Thank you -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
On Monday, September 24, 2012 02:53:59 PM Julien Cigar wrote: On 09/24/2012 14:34, Andres Freund wrote: On Monday, September 24, 2012 02:21:09 PM Julien Cigar wrote: 5) synchronous_commit = off should only be used if you have a battery-backed write cache. Huh? Are you possibly confusing this with full_page_writes? indeed...! sorry for that (note that you still have a (very) small chance of loosing data with synchronous_commit = off if your server crashes between two commit chunks) Sure, you have a chance of loosing the last some transactions, but you won't corrupt anything. Thats the entire point of the setting ;) Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
On 09/24/2012 15:51, Kiriakos Tsourapas wrote: Hi, Thank you for your response. Please find below my answers/comments. On Sep 24, 2012, at 15:21, Julien Cigar wrote: Hello, 1) upgrade your PostgreSQL installation, there have been numerous bugfixes releases since 8.4.2 Not possible right now. It will have to be the last solution. 2) you'll have to show us an explain analyze of the slow queries. If I take a look at those you provided everything run i less than 1ms. Will do so in a couple of days that it will get slow again. 3) with 200 records you'll always have a seqscan Does it really matter? I mean, with 200 records any query should be ultra fast. Right ? right..! 4) how much memory do you have ? shared_buffers = 256MB and effective_cache_size = 512MB looks OK only if you have between 1 and 2GB of RAM I have included the server specs and the results of top commands, showing that we have 8GB ram and how much memory is used/cached/swapped. Personally I don't quite understand the linux memory, but I have posted them hoping you may see something I don't. with 8GB of RAM I would start with shared_buffers to 1GB and effective_cache_size to 4GB. I would also change the default work_mem to 32MB and maintenance_work_mem to 512MB 5) synchronous_commit = off should only be used if you have a battery-backed write cache. I agree with the comments that have followed my post. I have changed it, knowing there is a small risk, but hoping it will help our performance. 6) autovacuum_naptime should be changed only if autovacuum is constantly running (so if you have dozen of databases in your cluster) As I said, changing the autovacuum values have not changed the problem. So, you may as well consider that we have the default values for autovacuuming... the problem existed with the default values too. 7) are you sure the problem isn't related to Bucardo ? Not at all sure... I have no idea. Can you suggest of a way to figure it out ? Unfortunately I never used Bucardo, but be sure that it's not a problem with your network (and that you understand all the challenges involved in multi-master replication) Thank you -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. attachment: jcigar.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
i remember having a server with 8.4.4 where we had multiple problems with autovacuum. if i am not mistaken there are some bugs related with vacuum until 8.4.7. i would suggest you to upgrade to the latest 8.4.x version BR, -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgres-becoming-slow-only-full-vacuum-fixes-it-tp5725119p5725129.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
Hi,
On 24 September 2012 20:33, Kiriakos Tsourapas kts...@gmail.com wrote:
The problem : Postgres is becoming slow, day after day, and only a full
vacuum fixes the problem.
Information you may need to evaluate :
The problem lies on all tables and queries, as far as I can tell, but we can
focus on a single table for better comprehension.
The queries I am running to test the speed are :
INSERT INTO AWAITINGSTATUSSMPP VALUES('143428', '', 1, '2012-06-16
13:39:19', '111');
DELETE FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
SELECT * FROM AWAITINGSTATUSSMPP WHERE SMSCMSGID = '' AND
CONNECTIONID = 1;
After a full vacuum, they run in about 100ms.
Today, before the full vacuum, they were taking around 500ms.
I had similar issue and I disabled cost based auto vacuum:
autovacuum_vacuum_cost_delay = -1
-1 says that vacuum_cost_delay will be used and default value for
vacuum_cost_delay is 0 (ie. off)
Of couse you need to change other autovacuum settings but you did that.
--
Ondrej Ivanic
(ondrej.iva...@gmail.com)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Postgres becoming slow, only full vacuum fixes it
On 24/09/12 22:33, Kiriakos Tsourapas wrote: Hi, The problem : Postgres is becoming slow, day after day, and only a full vacuum fixes the problem. My postgresql.conf file : == port = 5433 # (change requires restart) max_connections = 100 # (change requires restart) shared_buffers = 256MB # min 128kB. DoubleIP - Default was 32MB synchronous_commit = off# immediate fsync at commit. DoubleIP - Default was on effective_cache_size = 512MB# DoubleIP - Default was 128MB log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog silent_mode = on# Run server silently. log_line_prefix = '%t %d %u ' # special values: log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and autovacuum_naptime = 28800 # time between autovacuum runs. DoubleIP - default was 1min autovacuum_vacuum_threshold = 100 # min number of row updates before autovacuum_vacuum_scale_factor = 0.0# fraction of table size before vacuum. DoubleIP - default was 0.2 datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' # locale for system error message lc_monetary = 'en_US.UTF-8' # locale for monetary formatting lc_numeric = 'en_US.UTF-8' # locale for number formatting lc_time = 'en_US.UTF-8' # locale for time formatting default_text_search_config = 'pg_catalog.english' Given that vacuum full fixes the issue I suspect you need to have autovacuum set wake up much sooner, not later. So autovacuum_naptime = 28800 or even = 60 (i.e the default) is possibly too long. We have several database here where I change this setting to 10 i.e: autovacuum_naptime = 10s in order to avoid massive database bloat and queries that get slower and slower... You might want to be a bit *less* aggressive with autovacuum_vacuum_scale_factor - I usually have this at 0.1, i.e: autovacuum_vacuum_scale_factor = 0.1 otherwise you will be vacuuming all the time - which is usually not what you want (not for all your tables anyway). regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
