Re: [PERFORM] [ADMIN] syslog slowing the database?
On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote: > > You could also consider not using syslog at all: let the postmaster > > output to its stderr, and pipe that into a log-rotation program. > > I believe some people use Apache's log rotator for this with good > > results. > > Not an option I'm afraid. PostgreSQL just jams and stops logging after > the first rotation... > > I've read in the docs that syslog logging is the only "production" > solution... Can you use the apache log rotator? It's known to work in my environment (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my rc.local file: su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&' ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [ADMIN] syslog slowing the database?
scott.marlowe wrote: > On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote: > > > > You could also consider not using syslog at all: let the postmaster > > > output to its stderr, and pipe that into a log-rotation program. > > > I believe some people use Apache's log rotator for this with good > > > results. > > > > Not an option I'm afraid. PostgreSQL just jams and stops logging after > > the first rotation... > > > > I've read in the docs that syslog logging is the only "production" > > solution... > > Can you use the apache log rotator? It's known to work in my environment > (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my > rc.local file: > > su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&' Sure, our documentation specifically mentions using rotatelogs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [ADMIN] syslog slowing the database?
On Mon, 15 Mar 2004, Bruce Momjian wrote: > scott.marlowe wrote: > > On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote: > > > > > > You could also consider not using syslog at all: let the postmaster > > > > output to its stderr, and pipe that into a log-rotation program. > > > > I believe some people use Apache's log rotator for this with good > > > > results. > > > > > > Not an option I'm afraid. PostgreSQL just jams and stops logging after > > > the first rotation... > > > > > > I've read in the docs that syslog logging is the only "production" > > > solution... > > > > Can you use the apache log rotator? It's known to work in my environment > > (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my > > rc.local file: > > > > su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&' > > Sure, our documentation specifically mentions using rotatelogs. hehe. What I meant was can Christopher use it, or does he have a limitation in his environment where he can't get ahold of the apache log rotater... :-) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Large CASE-statement is pretty slow?
Arjen van der Meijden <[EMAIL PROTECTED]> writes: > > Of course I wanted to know how long it'd take on postgresql, selecting the > pkey-field only (without the case) took also some 0.7 seconds (the entire table > may have been more). > But the CASE-version took 9026139.201 ms, i.e. over 9000 seconds about 8 times > slower than MySQL. Was this the select with the CASE, or the update? If you did the update and have lots of foreign key references to the table then every record that's updated forces a check to make sure there are no references to that record (or an update if it's ON UPDATE CASCADE). If there are no indexes on the referencing table columns that will be very slow. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Large CASE-statement is pretty slow?
Arjen van der Meijden <[EMAIL PROTECTED]> writes: > Well, I have discarded this type of query as "too inefficient" and found a > better way Loading the mapping into a table with an index and doing an update using "from" to do a join seems likely to end up being the most efficient method. Postgres would probably not even bother with the index and do a hash join. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Large CASE-statement is pretty slow?
Greg Stark wrote: Arjen van der Meijden <[EMAIL PROTECTED]> writes: Was this the select with the CASE, or the update? It was just the select to see how long it'd take. I already anticipated it to be possibly a "slow query", so I only did the select first. Best regards, Arjen van der Meijden ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Scaling further up
On Fri, Mar 12, 2004 at 06:25:48PM -0500, Anjan Dave wrote: > We upgraded from 8GB to 12GB RAM a month or so ago, but even in the > past, I've never seen the system exhaust on it's system cache (~6GB, in > 'top'), while it's swapping. > > Some one had mentioned why not have the entire DB in memory? How do I > configure that, for knowledge? You don't. It'll automatically be in memory if (a) you have enough memory, (b) you don't have anything else on the machine using the memory, and (c) it's been read at least one time. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] atrocious update performance
We're in the throes of an MS SQL to PostgreSQL migration; our databases
include a number of ~5M row tables. We decided to take this opportunity
to clean up and slightly re-normalize our schemas, given what we've
learned about the data over its lifetime and such, else we wouldn't be
experiencing any of the following (we could instead just dump and `copy
from`).
We have a temporary table, public.tempprod, containing 4.7M rows, one
for each row in account.cust. account.cust has, among others, two
columns, prod and subprod, which we're trying to update from tempprod
joined against prod. The update tends to take unnecessarily long--
rather, we've had to finally kill it after its taking obscenely too
long.
The table:
# \d account.cust
Table "account.cust"
Column |Type | Modifiers
---+-+--
custid| bigint | not null default
| |
nextval('account.custid_seq'::text)
ownerid | integer | not null
origid| text| not null
pname | text|
fname | text|
mname | text|
lname | text|
suffix| text|
addr1 | text|
addr2 | text|
addr3 | text|
city | text|
state | text|
zip | text|
zipplus | text|
homeph| text|
workph| text|
otherph | text|
ssn | text|
isactive | boolean | default true
createddt | timestamp without time zone | default now()
prodid| bigint |
subprodid | bigint |
Indexes:
"cust_pkey" primary key, btree (custid)
"ix_addr1" btree (addr1) WHERE (addr1 IS NOT NULL)
"ix_addr2" btree (addr2) WHERE (addr2 IS NOT NULL)
"ix_city" btree (city) WHERE (city IS NOT NULL)
"ix_fname" btree (fname) WHERE (fname IS NOT NULL)
"ix_homeph" btree (homeph) WHERE (homeph IS NOT NULL)
"ix_lname" btree (lname) WHERE (lname IS NOT NULL)
"ix_mname" btree (mname) WHERE (mname IS NOT NULL)
"ix_origid" btree (origid)
"ix_ssn" btree (ssn) WHERE (ssn IS NOT NULL)
"ix_state" btree (state) WHERE (state IS NOT NULL)
"ix_workph" btree (workph) WHERE (workph IS NOT NULL)
"ix_zip" btree (zip) WHERE (zip IS NOT NULL)
We're currently running on a dual Xeon 700 (I know, I know; it's what
we've got) with 2.5GB RAM and 4x36GB SCSI in hardware RAID 5 (Dell
Perc3 something-or-other controller). If we can demonstrate that
PostgreSQL will meet our needs, we'll be going production on a dual
Opteron, maxed memory, with a 12-disk Fibre Channel array.
The query is:
update account.cust set prodid =
(select p.prodid from account.prod p
join public.tempprod t on t.pool = p.origid
where custid = t.did)
And then, upon its completion, s/prod/subprod/.
That shouldn't run overnight, should it, let alone for -days-?
In experimenting with ways of making the updates take less time, we tried
adding product and subproduct columns to tempprod, and updating those.
That seemed to work marginally better:
explain analyze update public.tempprod set prodid =
(select account.prod.prodid::bigint
from account.prod
where public.tempprod.pool::text = account.prod.origid::text)
Seq Scan on tempprod (cost=0.00..9637101.35 rows 4731410 width=56) (actual
time=24273.467..16090470.438 rows=4731410 loops=1)
SubPlan
-> Limit (cost=0.00..2.02 rows=2 width=8) (actual time=0.134..0.315
rows=1 loops=4731410)
-> Seq Scan on prod (cost=0.00..2.02 rows=2 width=8) (actual
time=0.126..0.305 rows=1 loops=4731410)
Filter: (($0)::text = (origid)::text)
Total runtime: 2284551.962 ms
But then going from public.tempprod to account.cust again takes days. I
just cancelled an update that's been running since last Thursday.
Alas, given how long the queries take to run, I can't supply an `explain
analyze`. The `explain` seems reasonable enough:
# explain update account.cust set prodid = tempprod.prodid
where tempprod.did = origid;
Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
Merge Cond: (("outer".origid)::text = ("inner".did)::text)
-> Index Scan using ix_origid on cust (cost=0.00..94876.83
rows=4731410 width=244)
-> Index Scan using ix_did on tempprod (cost=0.00..66916.71
rows=4731410 width=18)
The relevant bits from my postgreql.conf (note, we built with a BLCKSZ
of 16K):
shared_buffers = 4096
sort_mem = 32768
vac
Re: [PERFORM] Scaling further up
Quoting Andrew Sullivan <[EMAIL PROTECTED]>: > On Fri, Mar 12, 2004 at 06:25:48PM -0500, Anjan Dave wrote: > > We upgraded from 8GB to 12GB RAM a month or so ago, but even in the > > past, I've never seen the system exhaust on it's system cache (~6GB, in > > 'top'), while it's swapping. > > > > Some one had mentioned why not have the entire DB in memory? How do I > > configure that, for knowledge? > > You don't. It'll automatically be in memory if (a) you have enough > memory, (b) you don't have anything else on the machine using the > memory, and (c) it's been read at least one time. This is the preferred method, but you could create a memory disk if running linux. This has several caveats, though. 1. You may have to recompile the kernel for support. 2. You must store the database on a hard drive partition during reboots. 3. Because of #2 this option is generally useful if you have static content that is loaded to the MD upon startup of the system. You could have some fancy methodology of shutting down the system and then copying the data to a disk-based filesystem, but this is inherently bad since at any moment a power outage would erase any updates changes. The option is there to start with all data in memory, but in general, this is probablt not what you want. Just an FYI. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] High CPU with 7.4.1 after running for about 2 weeks
>Since the postmaster is a single unthreaded process, it's quite >impossible for it to take up 100% of two CPUs. Could you be more >precise about which processes were eating CPU, and what they were >doing according to the available state data? (ps auxww and >pg_stat_activity can be helpful tools.) > > regards, tom lane I shut down all our clients (all java except one in perl), and pg_stat_activity showed that there was still one query active. That's a good table to know about! Anyway, it didn't end until I sent it a TERM signal. I assume this means there's a runaway query somewhere, which I'll have to hunt down. But if the client dies, doesn't postgresql normally terminate the query that that client initiated? Or do I need to set statement_timeout? (As for the 100% CPU, I was confused by the fact that I was getting two lines in "top" (on Linux) with 99% utilization---I assume with two runaway queries.) Thanks for your help! -Mike ---(end of broadcast)--- TIP 3: 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] Large CASE-statement is pretty slow?
Arjen van der Meijden <[EMAIL PROTECTED]> writes: > [ huge CASE is pretty slow ] I did some profiling of the test case that Arjen was kind enough to send me. It seems there are two distinct problems. One is that the parser uses repeated lappend()'s to construct the list of CASE arms; this makes building the structure O(N^2) in the number of arms. (If you simply try to EXPLAIN the query, you find out that the parse time is about a third of the run time :-( ... and 90% of that is spent inside nconc() which is the guts of lappend.) This problem is slated to be fixed by Neil Conway's upcoming rewrite of the list support, which will convert lappend into a constant-time operation. The other difficulty is that the evaluation machinery for arithmetic expressions has a lot of overhead. The profile run shows: % cumulative self self total time seconds secondscalls s/call s/call name 38.15 41.9241.92 229646 0.00 0.00 nconc 21.76 65.8423.92 199054454 0.00 0.00 ExecEvalExpr 11.38 78.3412.501 0.00 0.00 ExecEvalCase 8.43 87.61 9.27 66348151 0.00 0.00 ExecEvalFuncArgs 8.12 96.54 8.93 66348151 0.00 0.00 ExecMakeFunctionResult 2.96 99.78 3.25 66348151 0.00 0.00 ExecEvalVar 1.23101.14 1.3610058 0.00 0.00 AllocSetCheck 1.23102.49 1.35 66348151 0.00 0.00 ExecEvalOper 1.12103.72 1.2476537 0.00 0.00 OpernameGetCandidates 0.85104.66 0.94 66424693 0.00 0.00 int4eq (Note: I added LIMIT 1 to the query so that the CASE is only carried out 1 times, rather than nearly 9 times as in Arjen's original test case. Without this, the call-counter overflows for ExecEvalExpr, and the time percentages seem to get confused. One must recognize though that this overstates the parser overhead compared to the original test case.) Clearly the useful work (int4eq) is getting a bit swamped by the ExecEval mechanism. I have some ideas about reducing the overhead, which I'll post to the pghackers list in a bit. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] rapid degradation after postmaster restart
Joe Conway wrote: A few pg_autovacuum questions came out of this: First, the default vacuum scaling factor is 2, which I think implies the big table should only get vacuumed every 56 million or so changes. I didn't come anywhere near that volume in my tests, yet the table did get vacuumed more than once (I was watching the pg_autovacuum log output). Do I misunderstand this setting? I think you understand correctly. A table with 1,000,000 rows should get vacuumed approx every 2,000,000 changes (assuming default values for -V ). FYI and insert and a delete count as one change, but and update counts as two. Unfortunately, the running with -d2 would show the numbers that pg_autovacuum is using to decide if it when it should vacuum or analyze.Also, are you sure that it vacuumed more than once and wasn't doing analyzes most of the time? Also, I'm not sure if 2 is a good default value for the scaling factor but I erred on the side of not vacuuming too often. Second, Matthew requested pg_autovacuum run with -d2; I found that with -d2 set, pg_autovacuum would immediately exit on start. -d0 and -d1 work fine however. That's unfortunate as that is the detail we need to see what pg_autovacuum thinks is really going on. We had a similar sounding crash on FreeBSD due to some unitialized variables that were being printed out by the debug code, however that was fixed a long time ago. Any chance you can look into this? That's all I can think of at the moment. I'd like to try the 7.4 patch that makes vacuum sleep every few pages -- can anyone point me to the latest and greatest that will apply to 7.4? Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] atrocious update performance
> # explain update account.cust set prodid = tempprod.prodid
> where tempprod.did = origid;
>
> Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
>Merge Cond: (("outer".origid)::text = ("inner".did)::text)
>-> Index Scan using ix_origid on cust (cost=0.00..94876.83
>rows=4731410 width=244)
>-> Index Scan using ix_did on tempprod (cost=0.00..66916.71
>rows=4731410 width=18)
I'm going to hazard a guess and say you have a number of foreign keys
that refer to account.cust.prodid? This is probably the time consuming
part -- perhaps even a missing index on one of those keys that refers to
this field.
Going the other way should be just as good for your purposes, and much
faster since you're not updating several foreign key'd fields bound to
account.cust.prodid.
UPDATE tempprod.prodid = prodid
FROM account.cust
WHERE temprod.did = cust.origid;
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [PERFORM] atrocious update performance
> > # explain update account.cust set prodid = tempprod.prodid
> > where tempprod.did = origid;
> > Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
> >Merge Cond: (("outer".origid)::text = ("inner".did)::text)
> >-> Index Scan using ix_origid on cust (cost=0.00..94876.83
> >rows=4731410 width=244)
> >-> Index Scan using ix_did on tempprod (cost=0.00..66916.71
> >rows=4731410 width=18)
> I'm going to hazard a guess and say you have a number of foreign keys
> that refer to account.cust.prodid? This is probably the time consuming
> part -- perhaps even a missing index on one of those keys
> that refers to
> this field.
Actually, there are no foreign keys to those columns. Once they're
populated, I'll apply a foreign key constraint and they'll refer to the
appropriate row in the prod and subprod tables, but nothing will
reference account.cust.[sub]prodid. There are, of course, several foreign
keys referencing account.cust.custid.
> Going the other way should be just as good for your purposes, and much
> faster since you're not updating several foreign key'd fields bound to
> account.cust.prodid.
> UPDATE tempprod.prodid = prodid
> FROM account.cust
> WHERE temprod.did = cust.origid;
Not quite. Without this update, acount.cust.[sub]prodid are null. The
data was strewn across multiple tables in MS SQL; we're normalizing it
into one, hence the need to populate the two columns independently.
/rls
--
Rosser Schwarz
Total Card, Inc.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] atrocious update performance
Bulk updates are generally dogs (not just in pg), so I avoid doing them by
doing faster selects and inserts. You can create a new table using 'create
table as' to produce your target results. This is real fast - avoiding the
row iteration in insert, allowing the select optimizer to run and no index
overhead. Then alter/rename, add indexes and whatever else hangs off the
table (or if you're lazy do an insert/select into the original target
table). I often see 2 orders of magnitude improvement doing this, and no
need to vacuum.
/Aaron
- Original Message -
From: "Rosser Schwarz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, March 15, 2004 3:28 PM
Subject: [PERFORM] atrocious update performance
We're in the throes of an MS SQL to PostgreSQL migration; our databases
include a number of ~5M row tables. We decided to take this opportunity
to clean up and slightly re-normalize our schemas, given what we've
learned about the data over its lifetime and such, else we wouldn't be
experiencing any of the following (we could instead just dump and `copy
from`).
We have a temporary table, public.tempprod, containing 4.7M rows, one
for each row in account.cust. account.cust has, among others, two
columns, prod and subprod, which we're trying to update from tempprod
joined against prod. The update tends to take unnecessarily long--
rather, we've had to finally kill it after its taking obscenely too
long.
The table:
# \d account.cust
Table "account.cust"
Column |Type | Modifiers
---+-+--
custid| bigint | not null default
| |
nextval('account.custid_seq'::text)
ownerid | integer | not null
origid| text| not null
pname | text|
fname | text|
mname | text|
lname | text|
suffix| text|
addr1 | text|
addr2 | text|
addr3 | text|
city | text|
state | text|
zip | text|
zipplus | text|
homeph| text|
workph| text|
otherph | text|
ssn | text|
isactive | boolean | default true
createddt | timestamp without time zone | default now()
prodid| bigint |
subprodid | bigint |
Indexes:
"cust_pkey" primary key, btree (custid)
"ix_addr1" btree (addr1) WHERE (addr1 IS NOT NULL)
"ix_addr2" btree (addr2) WHERE (addr2 IS NOT NULL)
"ix_city" btree (city) WHERE (city IS NOT NULL)
"ix_fname" btree (fname) WHERE (fname IS NOT NULL)
"ix_homeph" btree (homeph) WHERE (homeph IS NOT NULL)
"ix_lname" btree (lname) WHERE (lname IS NOT NULL)
"ix_mname" btree (mname) WHERE (mname IS NOT NULL)
"ix_origid" btree (origid)
"ix_ssn" btree (ssn) WHERE (ssn IS NOT NULL)
"ix_state" btree (state) WHERE (state IS NOT NULL)
"ix_workph" btree (workph) WHERE (workph IS NOT NULL)
"ix_zip" btree (zip) WHERE (zip IS NOT NULL)
We're currently running on a dual Xeon 700 (I know, I know; it's what
we've got) with 2.5GB RAM and 4x36GB SCSI in hardware RAID 5 (Dell
Perc3 something-or-other controller). If we can demonstrate that
PostgreSQL will meet our needs, we'll be going production on a dual
Opteron, maxed memory, with a 12-disk Fibre Channel array.
The query is:
update account.cust set prodid =
(select p.prodid from account.prod p
join public.tempprod t on t.pool = p.origid
where custid = t.did)
And then, upon its completion, s/prod/subprod/.
That shouldn't run overnight, should it, let alone for -days-?
In experimenting with ways of making the updates take less time, we tried
adding product and subproduct columns to tempprod, and updating those.
That seemed to work marginally better:
explain analyze update public.tempprod set prodid =
(select account.prod.prodid::bigint
from account.prod
where public.tempprod.pool::text = account.prod.origid::text)
Seq Scan on tempprod (cost=0.00..9637101.35 rows 4731410 width=56) (actual
time=24273.467..16090470.438 rows=4731410 loops=1)
SubPlan
-> Limit (cost=0.00..2.02 rows=2 width=8) (actual time=0.134..0.315
rows=1 loops=4731410)
-> Seq Scan on prod (cost=0.00..2.02 rows=2 width=8) (actual
time=0.126..0.305 rows=1 loops=4731410)
Filter: (($0)::text = (origid)::text)
Total runtime: 2284551.962 ms
But then going from public.tempprod to account.cust again takes days. I
just cancelled an update that's been runn
Re: [PERFORM] atrocious update performance
On Mon, 2004-03-15 at 16:15, Rosser Schwarz wrote:
> > > # explain update account.cust set prodid = tempprod.prodid
> > > where tempprod.did = origid;
>
> > > Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
> > >Merge Cond: (("outer".origid)::text = ("inner".did)::text)
> > >-> Index Scan using ix_origid on cust (cost=0.00..94876.83
> > >rows=4731410 width=244)
> > >-> Index Scan using ix_did on tempprod (cost=0.00..66916.71
> > >rows=4731410 width=18)
>
> > I'm going to hazard a guess and say you have a number of foreign keys
> > that refer to account.cust.prodid? This is probably the time consuming
> > part -- perhaps even a missing index on one of those keys
> > that refers to
> > this field.
>
> Actually, there are no foreign keys to those columns. Once they're
> populated, I'll apply a foreign key constraint and they'll refer to the
> appropriate row in the prod and subprod tables, but nothing will
> reference account.cust.[sub]prodid. There are, of course, several foreign
> keys referencing account.cust.custid.
If there are no feign keys to it, I wouldn't expect it to take more than
10 minutes on slow hardware.
Fresh out of ideas here.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Scaling further up
On Tue, 2004-03-16 at 07:28, Matt Davies wrote: > This is the preferred method, but you could create a memory disk if running > linux. This has several caveats, though. > > 1. You may have to recompile the kernel for support. > 2. You must store the database on a hard drive partition during reboots. > 3. Because of #2 this option is generally useful if you have static content that > is loaded to the MD upon startup of the system. And 4. You use twice as much memory - one lot for the FS, the second for buffer cache. It's generally going to be slower than simply doing some typical queries to preload the data into buffer cache, I think. Stephen signature.asc Description: This is a digitally signed message part
Re: [PERFORM] atrocious update performance
> You can create a new table using 'create table as' to produce your > target results. This is real fast ... > I often see 2 orders of magnitude improvement doing this, and no > need to vacuum. Indeed: "Query returned successfully with no result in 582761 ms." Though I must say, ten minutes is nominally more than two orders of mangitude performance improvement, versus several days. Many thanks, Aaron. /rls -- Rosser Schwarz Total Card, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] atrocious update performance
"Rosser Schwarz" <[EMAIL PROTECTED]> writes: >> You can create a new table using 'create table as' to produce your >> target results. This is real fast ... >> I often see 2 orders of magnitude improvement doing this, and no >> need to vacuum. > Indeed: > "Query returned successfully with no result in 582761 ms." > Though I must say, ten minutes is nominally more than two orders of > mangitude performance improvement, versus several days. Hm. There is no way that inserting a row is two orders of magnitude faster than updating a row --- they both require storing a new row and making whatever index entries are needed. The only additional cost of the update is finding the old row (not a very big deal AFAICS in the examples you gave) and marking it deleted (definitely cheap). So there's something awfully fishy going on here. I'm inclined to suspect an issue with foreign-key checking. You didn't give us any details about foreign key relationships your "cust" table is involved in --- could we see those? And the schemas of the other tables involved? Also, exactly which PG version is this? regards, tom lane ---(end of broadcast)--- TIP 3: 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] atrocious update performance
The original point was about a very slow update of an entire table with a plan that looped, and over a dozen conditional indices - vs. a 'create as' in a CPU starved environment. I stand by my statement about observing the orders of magnitude difference. In theory I agree that the update should be in the same order of magnitude as the create as, but in practice I disagree. I also think something is wrong on the logical side (besides FKs, are there any triggers?) but was responding to the Gordian knot issue of bailing out of pg. Can you post a sample extract, Rosser? Otherwise, I'll try to put together a sample of a slow mass join update. /Aaron - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Rosser Schwarz" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, March 15, 2004 7:08 PM Subject: Re: [PERFORM] atrocious update performance > "Rosser Schwarz" <[EMAIL PROTECTED]> writes: > >> You can create a new table using 'create table as' to produce your > >> target results. This is real fast ... > >> I often see 2 orders of magnitude improvement doing this, and no > >> need to vacuum. > > > Indeed: > > "Query returned successfully with no result in 582761 ms." > > Though I must say, ten minutes is nominally more than two orders of > > mangitude performance improvement, versus several days. > > Hm. There is no way that inserting a row is two orders of magnitude > faster than updating a row --- they both require storing a new row and > making whatever index entries are needed. The only additional cost of > the update is finding the old row (not a very big deal AFAICS in the > examples you gave) and marking it deleted (definitely cheap). So > there's something awfully fishy going on here. > > I'm inclined to suspect an issue with foreign-key checking. You didn't > give us any details about foreign key relationships your "cust" table is > involved in --- could we see those? And the schemas of the other tables > involved? > > Also, exactly which PG version is this? > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: 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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] rapid degradation after postmaster restart
Matthew T. O'Connor wrote: I think you understand correctly. A table with 1,000,000 rows should get vacuumed approx every 2,000,000 changes (assuming default values for -V ). FYI and insert and a delete count as one change, but and update counts as two. Unfortunately, the running with -d2 would show the numbers that pg_autovacuum is using to decide if it when it should vacuum or analyze.Also, are you sure that it vacuumed more than once and wasn't doing analyzes most of the time? Yeah, I'm sure. Snippets from the log: [...lots-o-tables...] [2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states" [2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:09:33 PM] Performing: ANALYZE "public"."equip_status_history" [2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE "public"."parametric_states" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 05:28:58 PM] added database: specdb This is the entire period of the first test, with default autovac settings. The table "public"."transaction_data" is the one with 28 million active rows. The entire test run inserts about 600 x 600 = 360,000 rows, out of which roughly two-thirds are later deleted. That's unfortunate as that is the detail we need to see what pg_autovacuum thinks is really going on. We had a similar sounding crash on FreeBSD due to some unitialized variables that were being printed out by the debug code, however that was fixed a long time ago. Any chance you can look into this? I can try. The server belongs to another department, and they are under the gun to get back on track with their testing. Also, they compiled without debug symbols, so I need to get permission to recompile. Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. Thanks, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] rapid degradation after postmaster restart
Joe Conway <[EMAIL PROTECTED]> writes: > Any idea where I can get my hands on the latest version. I found the > original post from Tom, but I thought there was a later version with > both number of pages and time to sleep as knobs. That was as far as I got. I think Jan posted a more complex version that would still be reasonable to apply to 7.4. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] rapid degradation after postmaster restart
Joe Conway wrote: Yeah, I'm sure. Snippets from the log: [...lots-o-tables...] [2004-03-14 12:44:48 PM] added table: specdb."public"."parametric_states" [2004-03-14 12:49:48 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 01:29:59 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:08:26 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 02:08:26 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 02:22:44 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 03:06:45 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 03:19:51 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 03:21:09 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 03:54:57 PM] Performing: ANALYZE "public"."out_of_spec" [2004-03-14 03:54:57 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:07:52 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:09:33 PM] Performing: ANALYZE "public"."equip_status_history" [2004-03-14 04:09:33 PM] Performing: VACUUM ANALYZE "public"."parametric_states" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."out_of_spec" [2004-03-14 04:43:46 PM] Performing: VACUUM ANALYZE "public"."transaction_data" [2004-03-14 04:56:35 PM] Performing: VACUUM ANALYZE "public"."spc_graphs" [2004-03-14 04:58:32 PM] Performing: ANALYZE "public"."parametric_states" [2004-03-14 05:28:58 PM] added database: specdb Yeah, you're right. This is the entire period of the first test, with default autovac settings. The table "public"."transaction_data" is the one with 28 million active rows. The entire test run inserts about 600 x 600 = 360,000 rows, out of which roughly two-thirds are later deleted. Strange... I wonder if this is some integer overflow problem. There was one reported recently and fixed as of CVS head yesterday, you might try that, however without the -d2 output I'm only guessing at why pg_autovacuum is vacuuming so much / so often. I can try. The server belongs to another department, and they are under the gun to get back on track with their testing. Also, they compiled without debug symbols, so I need to get permission to recompile. Good luck, I hope you can get permission. Would e nice to fix this little crash. Yes I would be very curious to see the results with the vacuum delay patch installed (is that patch applied to HEAD?) Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. I think Jan posted one a while back [searches archives...] But I must say I'm at a loss to find it in the archives. Anyone know where a good delay patch is for 7.4? If we can't find one, any chance you can do some testing with CVS HEAD just to see if that works any better. I know there has been a fair amount of work done to improve this situation (not just vacuum delay, but ARC etc...) . ---(end of broadcast)--- TIP 3: 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] rapid degradation after postmaster restart
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Any idea where I can get my hands on the latest version. I found the original post from Tom, but I thought there was a later version with both number of pages and time to sleep as knobs. That was as far as I got. I think Jan posted a more complex version that would still be reasonable to apply to 7.4. I thought that too, but was having trouble finding it. I'll look again. Thanks, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] rapid degradation after postmaster restart
Matthew T. O'Connor wrote: Strange... I wonder if this is some integer overflow problem. There was one reported recently and fixed as of CVS head yesterday, you might try that, however without the -d2 output I'm only guessing at why pg_autovacuum is vacuuming so much / so often. I'll see what I can do tomorrow to track it down. I have already recommended to the program manager that they switch to 7.4.2 plus the autovacuum patch. Not sure they will be willing to make any changes at this stage in their release process though. If we can't find one, any chance you can do some testing with CVS HEAD just to see if that works any better. I know there has been a fair amount of work done to improve this situation (not just vacuum delay, but ARC etc...) I might do that, but not likely on Solaris. I can probably get a copy of the current database and testing scripts, and give it a try on one of my own machines (all Linux, either RHAS3, RH9, or Fedora). Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] atrocious update performance
Rosser Schwarz wrote: > shared_buffers = 4096 sort_mem = 32768 vacuum_mem = 32768 wal_buffers = 16384 checkpoint_segments = 64 checkpoint_timeout = 1800 checkpoint_warning = 30 commit_delay = 5 effective_cache_size = 131072 You didn't mention the OS so I would take it as either linux/freeBSD. First of all, your shared buffers are low. 4096 is 64MB with 16K block size. I would say at least push them to 150-200MB. Secondly your sort mem is too high. Note that it is per sort per query. You could build a massive swap storm with such a setting. Similarly pull down vacuum and WAL buffers to around 512-1024 each. I know that your problem is solved by using insert rather than updates. But I just want to point out that you still need to analyze the table to update the statistics or the further queres will not be exactly good. And lastly, you can bundle entire thing including creating duplicate table, populating it, renaming original table etc in a single transaction and nobody will notice it. I am almost sure MS-SQL can not do that. Not many databases have trasact-safe DDLs out there.. HTH Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
