From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres becoming slow, only full vacuum fixes it
Date: Mon, 24 Sep 2012 14:21:09 +0200
Message-ID: <50605035.8020107@ulb.ac.be> (raw)
In-Reply-To: <71DC95CE-16EF-4D53-9971-C573DCD30622@gmail.com>
References: <D15ECA12-8D6E-4E53-A294-5E175564CA61@gmail.com>
<71DC95CE-16EF-4D53-9971-C573DCD30622@gmail.com>
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', '1111', 1,
>> '2012-06-16 13:39:19', '111');
>> DELETE FROM "AWAITINGSTATUSSMPP" WHERE "SMSCMSGID" = '1111' AND
>> "CONNECTIONID" = 1;
>> SELECT * FROM "AWAITINGSTATUSSMPP" WHERE "SMSCMSGID" = '1111' 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',
>> '1111', 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" =
>> '1111' 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 = '1111'::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"
>> = '1111' 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 = '1111'::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 = 'pg_catalog.english'
>> =======================
>>
>> As you will see, I have altered the shared_buffers
>> and synchronous_commit values.
>> The shared_buffers had the default value 32Mb. When I changed it to
>> 256Mb the problem still appears but it takes more time to appear (3-4
>> days). With 32MB, it appeared faster, probably after 24 hours.
>> Also, I have changed the autovacuum daemon to work every 8 hours but
>> I changed its values to make sure it vacuums pretty much all tables
>> (the ones for which at least 100 rows have changed).
>> Please note, though, that my problem existed even before playing
>> around with the autovacuum. This is why I tried to change its values
>> in the first place.
>>
>> The server is synchronized with another server using bucardo. Bucardo
>> process is running on the other server.
>> The same problem appears on the 2nd server too... after 3-4 days,
>> postgres is running slower and slower.
>>
>> Our server configuration :
>> DELL PowerEdge T610 Tower Chassis for Up to 8x 3.5" HDDs
>> 2x Intel Xeon E5520 Processor (2.26GHz, 8M Cache, 5.86 GT/s QPI,
>> Turbo, HT), 1066MHz Max Memory
>> 8GB Memory,1333MHz
>> 2 x 146GB SAS 15k 3.5" HD Hot Plug
>> 6 x 1TB SATA 7.2k 3.5" Additional HD Hot Plug
>> PERC 6/i RAID Controller Card 256MB PCIe, 2x4 Connectors
>> SUSE Linux Enterprise Server 10, SP2
>>
>> The 2 HDs are set up with RAID-1
>> The 6 HDs are set up with RAID-5
>>
>> Linux is running on the RAID-1 configuration
>> Postgres is running on the RAID-5 configuration
>>
>>
>> Finally a top before and after the full vacuum :
>> top - 11:27:44 up 72 days, 13:27, 37 users, load average: 1.05,
>> 1.31, 1.45
>> Tasks: 279 total, 3 running, 276 sleeping, 0 stopped, 0 zombie
>> Cpu(s): 3.6%us, 0.8%sy, 0.0%ni, 95.5%id, 0.0%wa, 0.0%hi,
>> 0.1%si, 0.0%st
>> Mem: 8166432k total, 7963116k used, 203316k free, 115344k buffers
>> Swap: 2097144k total, 2097020k used, 124k free, 2337636k cached
>>
>> top - 11:30:58 up 72 days, 13:31, 38 users, load average: 1.53,
>> 1.59, 1.53
>> Tasks: 267 total, 2 running, 265 sleeping, 0 stopped, 0 zombie
>> Cpu(s): 1.3%us, 0.4%sy, 0.0%ni, 98.0%id, 0.3%wa, 0.0%hi,
>> 0.1%si, 0.0%st
>> Mem: 8166432k total, 6016268k used, 2150164k free, 61092k buffers
>> Swap: 2097144k total, 2010204k used, 86940k free, 2262896k cached
>>
>>
>> I hope I have provided enough info and hope that someone can point me
>> to the correct direction.
>>
>>
>> Thank you very much even for reading up to here !
>>
>> Best regards,
>> Kiriakos
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:jcigar.vcf (text/x-vcard, 290B) download
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: Postgres becoming slow, only full vacuum fixes it
In-Reply-To: <50605035.8020107@ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox