Thread: Update Performance from 6.5.0 to 6.5.3 to 7.0
I have a database that is used to manage email mailing lists (opt in
ezines). When an email message bounces the bouncecount in the customer
record is incremented.
My process used to do this on the fly as a bounce was recieved. This was on
6.5.0. We recently updgraded to 6.5.3 and faster hardware. For the most
part the update went smooth except for this process. It seemed to overload
the server. I did not have time to deal with it then, so I changed the
process to just log the bounce for later processing.
Now I need to fix the problem. I have written a script to process the log
file. It filters out duplicates which will reduce the total number of
updates that are done. It works inside a transaction. Too keep the size of
the transacations down (I have had problems in the past with large
transations) it closes one transaction and opens a new one after every 100th
update.
The problem is that performance is still less than I need. I am getting
about 5 updates processed per second on my test server. I never analysed it
on 6.5.0 but I was not having a problem and that was running on slower
hardware with more activity on the box.
My test server is running Postgres 7.0. It is a PIII 733 with 384M RAM and
2 IDE HDs running RedHat 6.2.
The backend is using about 80% of the CPU. The load average is around 1.00.
The is what the update statement looks like:
update customer
set bouncecount = bouncecount + 1,
bouncedate = CURRENT_DATE
where email = 'bryan@arcamax.com'
and bouncedate != CURRENT_DATE;
Explain says:
Index Scan using icusem2 on customer (cost=0.00..4.98 rows=1 width=238)
* Bryan White <bryan@arcamax.com> [000526 13:00] wrote: > I have a database that is used to manage email mailing lists (opt in > ezines). When an email message bounces the bouncecount in the customer > record is incremented. > > My process used to do this on the fly as a bounce was recieved. This was on > 6.5.0. We recently updgraded to 6.5.3 and faster hardware. For the most > part the update went smooth except for this process. It seemed to overload > the server. I did not have time to deal with it then, so I changed the > process to just log the bounce for later processing. > > Now I need to fix the problem. I have written a script to process the log > file. It filters out duplicates which will reduce the total number of > updates that are done. It works inside a transaction. Too keep the size of > the transacations down (I have had problems in the past with large > transations) it closes one transaction and opens a new one after every 100th > update. > > The problem is that performance is still less than I need. I am getting > about 5 updates processed per second on my test server. I never analysed it > on 6.5.0 but I was not having a problem and that was running on slower > hardware with more activity on the box. > > My test server is running Postgres 7.0. It is a PIII 733 with 384M RAM and > 2 IDE HDs running RedHat 6.2. > > The backend is using about 80% of the CPU. The load average is around 1.00. > > The is what the update statement looks like: > update customer > set bouncecount = bouncecount + 1, > bouncedate = CURRENT_DATE > where email = 'bryan@arcamax.com' > and bouncedate != CURRENT_DATE; > > Explain says: > Index Scan using icusem2 on customer (cost=0.00..4.98 rows=1 width=238) Please describe your customer table better. One thing I found was that postgresql (and just about any other database) is excrutiatingly slow on update/insert if you made too many indecies on the table being updated. how many indecies do you have on this table? you may want to try a combined index on both bouncedate and email. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> Please describe your customer table better.
CREATE TABLE "customer" (
"custid" int4 NOT NULL,
"lname" text DEFAULT '',
"fname" text DEFAULT '',
"email" text,
"offersubscribed" character DEFAULT '1',
"addr1" text DEFAULT '',
"addr2" text DEFAULT '',
"city" text DEFAULT '',
"state" text DEFAULT '',
"zip" text DEFAULT '',
"country" text DEFAULT '',
"phone" text DEFAULT '',
"fax" text DEFAULT '',
"firstcontactdate" date DEFAULT date(now()),
"note" text DEFAULT '',
"deliverable" character DEFAULT '1',
"mastersubscribed" character DEFAULT '1',
"url" text DEFAULT '',
"company" text DEFAULT '',
"title" text DEFAULT '',
"poregdate" date,
"bouncecount" int4,
"bouncedate" date
);
CREATE INDEX "icusln" on "customer" using btree ( "lname" "text_ops" );
CREATE UNIQUE INDEX "icusem2" on "customer" using btree ( "email"
"text_ops" );
CREATE INDEX "icusph" on "customer" using btree ( "phone" "text_ops" );
CREATE UNIQUE INDEX "icusid" on "customer" using btree ( "custid"
"int4_ops" );
> One thing I found was that postgresql (and just about any other
> database) is excrutiatingly slow on update/insert if you made too
> many indecies on the table being updated.
>
> how many indecies do you have on this table?
4, I could probably get by with just 2 If I had to. I will give it a try.
> you may want to try a combined index on both bouncedate and email.
Why, Email is a unique index and the explain says it is using it.
* Bryan White <bryan@arcamax.com> [000526 13:18] wrote: > > Please describe your customer table better. > > CREATE TABLE "customer" ( > "custid" int4 NOT NULL, > "lname" text DEFAULT '', > "fname" text DEFAULT '', > "email" text, > "offersubscribed" character DEFAULT '1', > "addr1" text DEFAULT '', > "addr2" text DEFAULT '', > "city" text DEFAULT '', > "state" text DEFAULT '', > "zip" text DEFAULT '', > "country" text DEFAULT '', > "phone" text DEFAULT '', > "fax" text DEFAULT '', > "firstcontactdate" date DEFAULT date(now()), > "note" text DEFAULT '', > "deliverable" character DEFAULT '1', > "mastersubscribed" character DEFAULT '1', > "url" text DEFAULT '', > "company" text DEFAULT '', > "title" text DEFAULT '', > "poregdate" date, > "bouncecount" int4, > "bouncedate" date > ); > CREATE INDEX "icusln" on "customer" using btree ( "lname" "text_ops" ); > CREATE UNIQUE INDEX "icusem2" on "customer" using btree ( "email" > "text_ops" ); > CREATE INDEX "icusph" on "customer" using btree ( "phone" "text_ops" ); > CREATE UNIQUE INDEX "icusid" on "customer" using btree ( "custid" > "int4_ops" ); > > > One thing I found was that postgresql (and just about any other > > database) is excrutiatingly slow on update/insert if you made too > > many indecies on the table being updated. > > > > how many indecies do you have on this table? > > 4, I could probably get by with just 2 If I had to. I will give it a try. yes! this should fix it for you. > > > you may want to try a combined index on both bouncedate and email. > > Why, Email is a unique index and the explain says it is using it. yah, don't do that. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
> > 4, I could probably get by with just 2 If I had to. I will give it a try. > > yes! this should fix it for you. With the indexes deleted I am now getting 40 or 50 updates per second instead of 5. Thanks alot.
* Bryan White <bryan@arcamax.com> [000526 13:59] wrote: > > > 4, I could probably get by with just 2 If I had to. I will give it a > try. > > > > yes! this should fix it for you. > > With the indexes deleted I am now getting 40 or 50 updates per second > instead of 5. It concerns me that with 6.5 this sort of operation with multiple indecies was fine and now it's slow. Can anyone explain it? -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
"Bryan White" <bryan@arcamax.com> writes:
>>>> 4, I could probably get by with just 2 If I had to. I will give it a
>>>> try.
> With the indexes deleted I am now getting 40 or 50 updates per second
> instead of 5.
That's good, but does it mean that 7.0 is slower about adding index
entries than 6.5 was? Or did you have fewer indexes on the table
when you were using 6.5?
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [000526 15:43] wrote: > "Bryan White" <bryan@arcamax.com> writes: > >>>> 4, I could probably get by with just 2 If I had to. I will give it a > >>>> try. > > > With the indexes deleted I am now getting 40 or 50 updates per second > > instead of 5. > > That's good, but does it mean that 7.0 is slower about adding index > entries than 6.5 was? Or did you have fewer indexes on the table > when you were using 6.5? I'm quite sure we experianced the same problem (mutliple indexes causing slow update/inserts) when we moved an identical table structure from 6.5 to 7.0 -Alfred
> That's good, but does it mean that 7.0 is slower about adding index > entries than 6.5 was? Or did you have fewer indexes on the table > when you were using 6.5? No the indexes have been there all along. My impression is the performance loss was between 6.5.0 and 6.5.3. I had just ignored the problem for a while.