Re: [PERFORM] Performance Concern
Christopher -
Thanks.
Answer 1:
I believe auto commit was off (but I'm not at my dev box right now). I'll
double-check that and the commit interval.
Answer 2:
Ah ha!! No indexes on FKs. I'll try that.
Yes, each baz is a uniquely identifiable. I had started a SP to create gen
the key but scrapped it when I saw no rand() function in pgpsql. Did I miss
something?
Turns out switching to ints no improvement on the inserts but a rather large
one on the updates. Also, I saw evidence in my testing that Postgres seemed
to like doing single updates as opposed to being handed a group of updates;
see my response to Rod Taylor's post here (and Rod, if you're reading this:
you were *GREAT* in "The Time Machine" !!
Answer 3:
Oh, there was no question three !!
Thanks again Christopher!!
__
John Pagakis
Email: [EMAIL PROTECTED]
"I am *SINCERE* about life, but I'm not *SERIOUS* about it."
-- Alan Watts
This signature generated by
... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc.
www.spazmodicfrog.com
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Christopher
Browne
Sent: Friday, October 24, 2003 12:11 PM
To: [EMAIL PROTECTED]
Subject: Re: [PERFORM] Performance Concern
[EMAIL PROTECTED] ("John Pagakis") writes:
> Greetings.
>
> I have a table that will require 100,000 rows initially.
>
> Assume the following (some of the field names have been changed for
> confidentiality reasons):
>
> CREATE TABLE baz (
> baz_number CHAR(15) NOT NULL,
> customer_id CHAR(39),
> foobar_id INTEGER,
> is_cancelled BOOL DEFAULT false NOT NULL,
> create_user VARCHAR(60) NOT NULL,
> create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
> last_update_user VARCHAR(60) NOT NULL,
> last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL,
> CONSTRAINT PK_baz PRIMARY KEY (baz_number)
> );
>
> ALTER TABLE baz
> ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id);
>
> ALTER TABLE baz
> ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id);
>
> Using JDBC, it took approximately one hour to insert 100,000 records. I
> have an algorithm to generate a unique baz_number - it is a mixture of
alpha
> and numerics.
Question #1: How did you do the inserts?
If AUTO-COMMIT was turned on, then that would indicate that you
invoked 100,000 transactions, and that would contribute considerably
to the process being slow. Put them all in as one transaction and
you'd probably see it run in a fraction of the time.
Question #2. Do you have indices on purchase(customer_id) and on
foobar(foobar_id)?
If not, then the foreign key check would be rather inefficient.
> There is a purchase table; one purchase can have many associated baz
> records, but the baz records will be pre-allocated - baz.customer_id
> allows null. The act of purchasing a baz will cause baz.customer_id
> to be populated from the customer_id (key) field in the purchase
> table.
>
> If it took an hour to insert 100,000 records, I can only imagine how
> much time it will take if one customer were to attempt to purchase
> all 100,000 baz. Certainly too long for a web page.
I take it that each "baz" is a uniquely identifiable product, akin to
(say) an RSA certificate or the like?
By the way, if you set up a stored procedure in PostgreSQL that can
generate the "baz_number" identifiers, you could probably do the
inserts Right Well Fast...
Consider the following. I have a stored procedure, genauth(), which
generates quasi-random values. (They're passwords, sort of...)
cctld=# explain analyze insert into baz (baz_number, create_user,
last_update_user)
cctld-# select substr(genauth(), 1, 15), 'cbbrowne', 'cbbrowne' from
big_table;
QUERY PLAN
---
Seq Scan on big_table (cost=0.00..789.88 rows=28988 width=0) (actual
time=0.20..1713.60 rows=28988 loops=1)
Total runtime: 3197.40 msec
(2 rows)
It took about 3 seconds to insert 28988 rows into baz. (big_table,
also renamed, to protect the innocent, has 28988 rows. I didn't care
about its contents, just that it had a bunch of rows.)
And the above is on a cheap desktop PC with IDE disk.
> I've not had to deal with this kind of volume in Postgres before; I
> have my suspicions on what is wrong here (could it be using a CHAR(
> 15 ) as a key?) but I'd *LOVE* any thoughts.
> Would I be better off making the key an identity field and not
> indexing on baz_number?
That might be something of an improvement, but it oughtn't be
cripplingly different to use a text field rather than an integer.
What's crippling is submitting 100,000 queries in 100,000
transactions. Cut THAT down to size and you'll see performance return
to being reasonable
Re: [PERFORM] Performance Concern
John Pagakis kirjutas L, 25.10.2003 kell 10:16: > Christopher - > Thanks. > > Answer 1: > I believe auto commit was off (but I'm not at my dev box right now). I'll > double-check that and the commit interval. > > Answer 2: > Ah ha!! No indexes on FKs. I'll try that. > > Yes, each baz is a uniquely identifiable. I had started a SP to create gen > the key but scrapped it when I saw no rand() function in pgpsql. Did I miss > something? hannu=# select random(); random -- 0.59924242859671 (1 row) \df lists all available functions in psql to generate string keys you could use something like: hannu=# select 'key' || to_hex(cast(random()*10 as int)); ?column? - key1e22d8ea (1 row) - Hannu ---(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] Performance Concern
Bear with me all - working my way through this. First of all, thanks for all the terrific advice. I think I focused you on the inserts, when my *REAL* concern is the updates. Initially, I was surmising that if the insert of 100,000 baz took an hour, an update to customer_id of, say 1000 baz, would simply be outrageous. I now have a better feel for how bad it is. I have already learned that making an integer the key of baz as opposed to baz_number - a CHAR( 15 ) - cuts my update cost almost in half, so my reiteration of the example uses this schema change. Please let me start again and perhaps do a little better job of explaining: Assume the following (some of the field names have been changed for confidentiality reasons): CREATE TABLE baz ( baz_key int4 NOT NULL, baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_key) ); ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); There is a purchase table; one purchase can have many associated baz records, but the 100,00 baz records will be pre-allocated - baz.customer_id allows null. The act of purchasing a baz will cause baz.customer_id to be populated from the customer_id (key) field in the purchase table. The column customer_id is actually the key to the purchase table despite the name. The foobar table is inconsequential as it will not be populated until the baz table is sold out. So for the inserts and updates, foobar will be empty. I could probably not even gen it until I needed it. As I said earlier I'm less concerned about the inserts than I am about the updates. The reason is the 100,000 inserts will happen before the site is live. The updates will happen as part of the purchase process, so updates need to be as fast as possible. I needed to do this because I absolutely positively cannot over-allocate baz. I cannot allocate more than 100,000 period, and any number of users can attempt to purchase one or more baz simultaneously. I am attempting to avoid a race condition and avoid using database locks as I feared this table would turn into a bottleneck. Note, as this question came up more than once from my previous post: Auto Commit was off for the inserts. This will be for a public website and multiple users will be "competing" for baz resources. My thought was for each user wishing to purchase one or more bazes: - Create a list of potentially available baz: SELECT baz_key WHERE customer_id IS NULL LIMIT 100; - If there are no more records in baz with customer_id of NULL, it's a sell-out. - Iterate through the list attempting to reserve a BAZ. Iterate until you have reserved the number of baz requested or until the list is exhausted: UPDATE baz SET customer_id = WHERE baz_key = AND customer_id IS NULL; - For a given update, if no record was updated, someone else set the customer_id before you could - go to the next baz_key in the list and try again. - If the list is exhausted go get the next block of 100 potential available baz keys and go again. Anyway, given this scenario, I *HAVE* to have auto commit on for updates so that everyone is aware of everyone else immediately. I wrote a JAVA simulation of the above that did 1000 updates in 37 seconds. That left me scratching my head because in psql when I did the semi-equivalent: UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM baz WHERE customer_id IS NULL LIMIT 1000 ); it took 1:27 (one minute 27 seconds) to execute. This led me (erroneously) to the conclusion that Postgres was somehow happier doing single updates than "grouping" them. I realized today that I missed something in my simulation (pulling an all-nighter will do that to you): my JAVA simulation had Auto Commit off and I was doing a commit at the end. Obviously that won't work given what I'm trying to do. Any updates must *IMMEDIATLY* be visible to all other processes, or I could get hit with a race condition. I re-ran with Auto Commit on and the timing fell more in line with what I saw in psql - 1:13. This seems a slow to me. Is there any way to optimize the update? Or, perhaps my design is the issue and I just need to do something else. Perhaps a lock on the table and an insert would be quicker. I'm just worried about locking in a multi-user environment. On the other hand, it looks to me like this table will be a bottleneck no matter what I do. Your thoughts, as always, are much appreciated. __ John Pagakis Email: [EMAIL PROTECTED] "If I had a formula for bypassing trouble, I
Re: [PERFORM] Performance Concern
At 05:56 10/25/2003, John Pagakis wrote: Snipping most of this, I only have one suggestion/comment to make. [snip] CREATE TABLE baz ( baz_key int4 NOT NULL, baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, create_user VARCHAR(60) NOT NULL, create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, last_update_user VARCHAR(60) NOT NULL, last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, CONSTRAINT PK_baz PRIMARY KEY (baz_key) ); ALTER TABLE baz ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); ALTER TABLE baz ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); [snip] I needed to do this because I absolutely positively cannot over-allocate baz. I cannot allocate more than 100,000 period, and any number of users can attempt to purchase one or more baz simultaneously. I am attempting to avoid a race condition and avoid using database locks as I feared this table would turn into a bottleneck. [snip] I have a similar situation in the database here, using the following example schema: CREATE TABLE foo ( nID serial UNIQUE NOT NULL, bAvailable boolean NOT NULL DEFAULT true, nSomeField int4 NOT NULL, sSomeField text NOT NULL ); CREATE TABLE bar ( nfoo_id int4 UNIQUE NOT NULL ); Assume foo is the table with the 100k pre-populated records that you want to assign to visitors on your site. bar is a table whos only purpose is to eliminate race conditions, working off the following business rules: 1. -- someone attempts to get a 'foo' SELECT nID from foo WHERE bAvailable; 2. -- we first try to assign this 'foo' to ourselves -- the ? is bound to the foo.nID we selected in step 1. INSERT INTO bar (nfoo_ID) VALUES (?) 3. -- Only if step 2 is successful, do we continue, otherwise someone beat us to it. UPDATE foo SET ... WHERE nID=? The key here is step 2. Since there is a UNIQUE constraint defined on the bar.nfoo_id (could even be an FK), only one INSERT will ever succeed. All others will fail. In step 3, you can set the bAvailable flag to false, along with whatever other values you need to set for your 'baz'. This will get much easier once 7.4 is production-ready, as the WHERE IN .. or WHERE NOT IN.. subselects are (according to the HISTORY file) going to be as efficient as joins, instead of the O(n) operation they apparently are right now. Until then however, I've found this simple trick works remarkably well. -Allen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance Concern
"John Pagakis" <[EMAIL PROTECTED]> writes: > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHERE customer_id IS NULL LIMIT 1000 ); Do an "explain analyze" on this query. I bet it's doing two sequential scans. Unfortunately in 7.3 the WHERE IN type of clause is poorly handled. If you're still in development perhaps you should move to the 7.4 beta as it should handle this much better: test74=> explain UPDATE test SET customer_id = 1 WHERE a IN (SELECT a FROM test WHERE customer_id IS NULL LIMIT 1000 ); QUERY PLAN - Nested Loop (cost=1447.26..2069.43 rows=201 width=10) -> HashAggregate (cost=1447.26..1447.26 rows=200 width=4) -> Subquery Scan "IN_subquery" (cost=0.00..1446.01 rows=501 width=4) -> Limit (cost=0.00..1441.00 rows=501 width=4) -> Seq Scan on test (cost=0.00..1441.00 rows=501 width=4) Filter: (customer_id IS NULL) -> Index Scan using ii on test (cost=0.00..3.10 rows=1 width=10) Index Cond: (test.a = "outer".a) (8 rows) However notice you still get at the one sequential scan. One way to help the situation would be to create a partial index WHERE customer_id IS NULL. This would especially help when things are almost completely sold out and available slots are sparse. slo=> explain UPDATE test SET customer_id = 1 WHERE a IN (SELECT a FROM test WHERE customer_id IS NULL LIMIT 1000 ); QUERY PLAN -- Nested Loop (cost=181.01..803.18 rows=201 width=10) -> HashAggregate (cost=181.01..181.01 rows=200 width=4) -> Subquery Scan "IN_subquery" (cost=0.00..179.76 rows=501 width=4) -> Limit (cost=0.00..174.75 rows=501 width=4) -> Index Scan using i on test (cost=0.00..174.75 rows=501 width=4) Filter: (customer_id IS NULL) -> Index Scan using ii on test (cost=0.00..3.10 rows=1 width=10) Index Cond: (test.a = "outer".a) (8 rows) Notice the both sequential scans are gone and replaced by index scans. I kind of worry you might still have a race condition with the above query. Two clients could do the subquery and pick up the same records, then both run and update them. The database would lock the records until the first one commits but I don't think that would stop the second one from updating them a second time. Perhaps moving to serializable transactions would help this, I'm not sure. It's too bad the LIMIT clause doesn't work on UPDATEs. Then you could simply do: UPDATE baz SET customer_id = '1234' where customer_id IS NULL LIMIT 1000 Which shouldn't have to scan the table twice at all and I don't think suffer from any race conditions. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Performance Concern
Sean - I believe auto-commit was off (not at the box right now). I'll play with the commit interval; I know commits are expensive operations. Thanks for item 2. I was toying with the notion of pre-creating 10 bazes off-loading them and then seeing if the COPY would be any faster; you saved me the effort of experimenting. Thanks for the benefit of your experience. __ John Pagakis Email: [EMAIL PROTECTED] "Oh, you hate your job? Why didn't you say so? There's a support group for that. It's called EVERYBODY, and they meet at the bar." -- Drew Carey This signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -Original Message- From: Sean Shanny [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data warehouse using PG. I have to load millions of records each night. I do two different things: 1) If I need to keep the insertions inside the java process I turn off auto-commit and every n insertions (5000 seems to give me the best performance for my setup) issue a commit. Make sure you do a final commit in a finally block so you don't miss anything. 2) Dump all the data to a file and then use a psql COPY (columns) FROM 'file path' call to load it. Very fast. --sean John Pagakis wrote: >Greetings. > >I have a table that will require 100,000 rows initially. > >Assume the following (some of the field names have been changed for >confidentiality reasons): > >CREATE TABLE baz ( >baz_number CHAR(15) NOT NULL, >customer_id CHAR(39), >foobar_id INTEGER, >is_cancelled BOOL DEFAULT false NOT NULL, >create_user VARCHAR(60) NOT NULL, >create_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >last_update_user VARCHAR(60) NOT NULL, >last_update_datetime TIMESTAMP DEFAULT 'now()' NOT NULL, >CONSTRAINT PK_baz PRIMARY KEY (baz_number) >); > >ALTER TABLE baz >ADD FOREIGN KEY (customer_id) REFERENCES purchase (customer_id); > >ALTER TABLE baz >ADD FOREIGN KEY (foobar_id) REFERENCES foobar (foobar_id); > > >Using JDBC, it took approximately one hour to insert 100,000 records. I >have an algorithm to generate a unique baz_number - it is a mixture of alpha >and numerics. > >There is a purchase table; one purchase can have many associated baz >records, but the baz records will be pre-allocated - baz.customer_id allows >null. The act of purchasing a baz will cause baz.customer_id to be >populated from the customer_id (key) field in the purchase table. > >If it took an hour to insert 100,000 records, I can only imagine how much >time it will take if one customer were to attempt to purchase all 100,000 >baz. Certainly too long for a web page. > >I've not had to deal with this kind of volume in Postgres before; I have my >suspicions on what is wrong here (could it be using a CHAR( 15 ) as a key?) >but I'd *LOVE* any thoughts. > >Would I be better off making the key an identity field and not indexing on >baz_number? > >Thanks in advance for any help. > >__ >John Pagakis >Email: [EMAIL PROTECTED] > > >"The best way to make your dreams come true is to wake up." >-- Paul Valery > >This signature generated by > ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. > www.spazmodicfrog.com > > >---(end of broadcast)--- >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Performance Concern
John Pagakis kirjutas L, 25.10.2003 kell 12:56: > I wrote a JAVA simulation of the above that did 1000 updates in 37 seconds. > That left me scratching my head because in psql when I did the > semi-equivalent: > > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHERE customer_id IS NULL LIMIT 1000 ); try it this way, maybe it will start using an index : UPDATE baz SET customer_id = '1234' WHERE baz_key IN ( SELECT baz_key FROM baz innerbaz WHERE customer_id IS NULL and innerbaz.baz_key = baz.baz_key LIMIT 1000 ); you may also try to add a conditional index to baz: CREATE INDEX baz_key_with_null_custid_nxd ON baz WHERE customer_id IS NULL; to make the index access more efficient. Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] analyzing postgresql performance for dbt-2
Mark Wong wrote: > > > Here are a pair of results where I just raise the load on the > > > database, where increasing the load increases the area of the database > > > touched in addition to increasing the transaction rate. The overall > > > metric increases somewhat, but the response time for most of the > > > interactions also increases significantly: > > > > > > http://developer.osdl.org/markw/dbt2-pgsql/158/ [baseline] > > > - load of 100 warehouses > > > - metric 1249.65 > > > > > > http://developer.osdl.org/markw/dbt2-pgsql/149/ > > > - load of 140 warehouses > > > - metric 1323.90 > > > > I looked at these charts and they looked normal to me. It looked like > > your the load increased until your computer was saturated. Is there > > something I am missing? > > I've run some i/o tests so I'm pretty sure I haven't saturated that. And it > looks like I have almost 10% more processor time left. I do agree that it > appears something might be saturated, I just don't know where to look... Could the 10% be context switching time, or is the I/O saturated? -- 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
