Re: [PERFORM] Adding foreign key performance
Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms Hmm, 298383 -> 83109 (since those are the 256k numbers). Not as much as I'd have hoped, but I'll take a factor of 3. Hi Jeff, Could you let us know the load times when you have done: 1. A full ANALYZE 2. A delete all from pg_statistic So we can see if ANALYZE stats make much difference? Chris ---(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] Adding foreign key performance
On Tue, 28 Oct 2003 14:22:04 -0500 Vivek Khera <[EMAIL PROTECTED]> wrote: > If you're restoring from a pg_dump -Fc (compressed dump) it already > happens for you. The indexes and foreign keys are not added until the > very end, from what I recall. > This happens with regular dumps - at the end is a pile of alter table's that create the indices, FK's and triggers. Is the -Fc method different? -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Adding foreign key performance
John K. Herreshoff wrote: > That did not take long... about 13 minutes to reload the tables from an *.mdb > file, and a second or two for each of the 'alter table foo add foreign > key...' lines. I tried to drop a 'referencing' table, and the database would > not let me, said that something depended on it ;o) > > Is there some way to name the foreign key so that it can be dropped later, or > is there a way to drop the foreign key using information already in the > database? You have to use ALTER TABLE DROP CONSTRAINT perhaps. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Adding foreign key performance
> "J" == Jeff <[EMAIL PROTECTED]> writes: J> And this leads to the place we'd get a huge benefit: Restoring J> backups.. If there were some way to bump up sort_mem while doing J> the restore.. things would be much more pleasant. [Although, even There was a rather substantial thread on this about the time when 7.4b1 was released. J> better would be to disable FK stuff while restoring a backup and J> assume the backup is "sane"] How we'd go about doing that is the J> subject of much debate. If you're restoring from a pg_dump -Fc (compressed dump) it already happens for you. The indexes and foreign keys are not added until the very end, from what I recall. J> Perhaps add the functionality to pg_restore? ie, pg_restore -s J> 256MB mybackup.db? It would just end up issuing a set J> sort_mem=256000.. This was essentially my proposal, though I had better speed enhancement by increasing the number of checkpoint buffers. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Very Poor Insert Performance
> "GS" == Greg Stark <[EMAIL PROTECTED]> writes: GS> At a guess the foreign key relationships you're enforcing don't GS> have indexes to help them. If they do perhaps postgres isn't using GS> them. Or, if you do have indexes, they've bloated to be way too big and are overwhelming your shared buffers. Reindex them and see it it helps. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Adding foreign key performance
That did not take long... about 13 minutes to reload the tables from an *.mdb file, and a second or two for each of the 'alter table foo add foreign key...' lines. I tried to drop a 'referencing' table, and the database would not let me, said that something depended on it ;o) Is there some way to name the foreign key so that it can be dropped later, or is there a way to drop the foreign key using information already in the database? John. On Tuesday 28 October 2003 13:34, Bruce Momjian wrote: > John K. Herreshoff wrote: > > FWIW: I'm fiddling with that right now, and the FK think was quick... a > > few seconds... the tables in question have 1400 records, 343000 records > > and 7200 records... I'm running Beta5... > > Did those tables have analyze statistics? Can you try it without > statistics (I think you have to drop the tables to erase the > statistics). ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Adding foreign key performance
John K. Herreshoff wrote: > I'm not sure about the analyze stats... Where would I find that (in > postgresql.conf I suppose) I'll go see what I have set up, and get back to > you in 30 minutes or less... They are in pg_statistic. If you have ever anaylzed the table, there are stats. I am interested in the non-analyze case because that's how the data will load into a fresh db via pg_dump. -- 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 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
[PERFORM] Ignoring index on (A is null), (A is not null) conditions
Hi, suppose, for simplicity, there is a table with index like this: create table TABLE1 ( A integer ); create index TABLE1_A on TABLE1 (A); My question is: why psql (7.3.3) does not use index when filtering by A IS NULL, A IS NOT NULL expressions? In fact, I need to filter by expression ((A is null) or (A > const)). Is there a way to filter by this expression using index? Functional index cannot be used (except strange solution with CASE-ing and converting NULL values into some integer constant) -- Index Scan using table1_a on table1 (cost=0.00..437.14 rows=29164 width=4) Index Cond: (a > 1000) -- Seq Scan on table1 (cost=0.00..448.22 rows=1 width=4) Filter: (a IS NULL) Seq Scan on table1 (cost=0.00..448.22 rows=30222 width=4) Filter: (a IS NOT NULL) Seq Scan on table1 (cost=0.00..523.77 rows=29164 width=4) Filter: ((a IS NULL) OR (a > 1000)) CH ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Adding foreign key performance
I'm not sure about the analyze stats... Where would I find that (in postgresql.conf I suppose) I'll go see what I have set up, and get back to you in 30 minutes or less... John. On Tuesday 28 October 2003 13:34, Bruce Momjian wrote: > John K. Herreshoff wrote: > > FWIW: I'm fiddling with that right now, and the FK think was quick... a > > few seconds... the tables in question have 1400 records, 343000 records > > and 7200 records... I'm running Beta5... > > Did those tables have analyze statistics? Can you try it without > statistics (I think you have to drop the tables to erase the > statistics). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Adding foreign key performance
John K. Herreshoff wrote: > FWIW: I'm fiddling with that right now, and the FK think was quick... a few > seconds... the tables in question have 1400 records, 343000 records and 7200 > records... I'm running Beta5... Did those tables have analyze statistics? Can you try it without statistics (I think you have to drop the tables to erase the statistics). -- 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 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] Adding foreign key performance
On Tue, 28 Oct 2003 10:32:36 -0800 (PST) Stephan Szabo <[EMAIL PROTECTED]> wrote: > Hmm, 298383 -> 83109 (since those are the 256k numbers). Not as > much as I'd have hoped, but I'll take a factor of 3. Yes. those are the numbers for 256MB of sort_mem. It seemed to saturate the IO so once I get more disks in here it should hopefully speed up. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Adding foreign key performance
On Tue, 28 Oct 2003, Jeff wrote: > On Tue, 28 Oct 2003 09:16:45 -0500 > Jeff <[EMAIL PROTECTED]> wrote: > > > > 7.3.4: 328912ms [cpu pegged] > > 7.4b4: 298383ms [cpu pegged] > > > > Just loaded up delicious 7.4b5 and wow... > > sort_mem 8192: 137038ms [lots of tmp file activity] > sort_mem 256000: 83109ms Hmm, 298383 -> 83109 (since those are the 256k numbers). Not as much as I'd have hoped, but I'll take a factor of 3. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Adding foreign key performance
FWIW: I'm fiddling with that right now, and the FK think was quick... a few seconds... the tables in question have 1400 records, 343000 records and 7200 records... I'm running Beta5... John. On Tuesday 28 October 2003 10:21, Stephan Szabo wrote: > On Tue, 28 Oct 2003, Jeff wrote: > > I recalled seeing a thread on -HACKERS about some major improvements to > > the speed of adding an FK to an existing table in 7.4. Naturally I was > > curious and decided to give it a whirl. My findings are not too good. In > > fact, they are bad. > > > > Could it be this patch never made it in? > > I think it went in between b4 and b5, can you try with b5? > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] analyzing postgresql performance for dbt-2
On 26 Oct, Bruce Momjian wrote: > 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? There are about 14,000 to 17,000 context switches/s according to the vmstat output. This is on a 1.5Ghz hyperthreaded Xeon processor. I don't know what I'm supposed to be able to expect in terms of context switching. I really doubt the i/o is saturated because I've run disktest (part of the Linux Test Project suite) and saw much higher throughput for various sequential/random read/write tests. I'm starting to collect oprofile data (and will hopefully have some results soon) to get an idea where the database is spending its time, just in case that may have something to do with it. Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Guesses on what this NestLoop is for?
Manfred, > Sorry, I have no answer to your question, but may I ask whether you > really want to get presumably 106 output rows for each event with > status 1? > > Or did you mean >WHERE (events.status = 1 OR events.status = 11) AND ... Thanks! I spent so much time tinkering around with the exists clauses, I completely missed that. Hopefully I'll get this client to upgrade to 7.4 so that the explains will be more readable -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Adding foreign key performance
On Tue, 28 Oct 2003 09:16:45 -0500 Jeff <[EMAIL PROTECTED]> wrote: > 7.3.4: 328912ms [cpu pegged] > 7.4b4: 298383ms [cpu pegged] > Just loaded up delicious 7.4b5 and wow... sort_mem 8192: 137038ms [lots of tmp file activity] sort_mem 256000: 83109ms That's some good work there Lou, You'll make sargent for that someday. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Optimizing Performance
[EMAIL PROTECTED] ("Kamalraj Singh Madhan") writes:
> Hi, I'am having major performance issues with post gre 7.3.1
> db. Kindly suggest all the possible means by which i can optimize
> the performance of this database. If not all, some ideas (even if
> they are common) are also welcome. There is no optimisation done to
> the default configuration of the installed database. Kindly suggest.
The best single document I am aware of on tuning the database may be
found here:
That may help with some of your problems, but there can be no
guarantees. What ultimately must happen is for you to discover what
are the bottlenecks on your system and addressing them. That involves
a process of exploration, as opposed to one of doing some unambiguous
"best practices."
--
select 'cbbrowne' || '@' || 'libertyrms.info';
Re: [PERFORM] Adding foreign key performance
Jeff <[EMAIL PROTECTED]> writes: > I recalled seeing a thread on -HACKERS about some major improvements to the speed of > adding an FK to an existing table in 7.4. Naturally I was curious and decided to > give it a whirl. My findings are not too good. In fact, they are bad. > 7.4b4, 10k shared buff, 256mb effective cache: 485706ms You are testing the wrong version. beta5 has the ADD FOREIGN KEY improvement. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Adding foreign key performance
On Tue, 28 Oct 2003, Jeff wrote: > I recalled seeing a thread on -HACKERS about some major improvements to > the speed of adding an FK to an existing table in 7.4. Naturally I was > curious and decided to give it a whirl. My findings are not too good. In > fact, they are bad. > > Could it be this patch never made it in? I think it went in between b4 and b5, can you try with b5? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Adding foreign key performance
I recalled seeing a thread on -HACKERS about some major improvements to the speed of adding an FK to an existing table in 7.4. Naturally I was curious and decided to give it a whirl. My findings are not too good. In fact, they are bad. Could it be this patch never made it in? Anyway, here's the info. Machine: Linux 2.4.18 [stock rh8], p3 500, 512mb, 4x18GB scsi raid 0 Two tables: members and watchedmembers with 1045720 and 829994 rows respectivly. freshly vacuum analyze'd for each PG: 7.4b4, 10k shared buff, 256mb effective cache: 485706ms 7.3.4 [same settings]: 412304.76 ms Now the odd thing during that operation was that the machine was about oh, 50-70% _idle_ during the whole time. Then I started thinking more about it and realized hearing if you bump sort_mem up ridiculously high during a foreign key add it helps. So I did. Bumped it up to 256MB. [again, vacuum analyze'd each beforehand] 7.3.4: 328912ms [cpu pegged] 7.4b4: 298383ms [cpu pegged] Quite an improvement I'd say. Perhaps we should make note of this somewhere? Performance guide? Docs? And this leads to the place we'd get a huge benefit: Restoring backups.. If there were some way to bump up sort_mem while doing the restore.. things would be much more pleasant. [Although, even better would be to disable FK stuff while restoring a backup and assume the backup is "sane"] How we'd go about doing that is the subject of much debate. Perhaps add the functionality to pg_restore? ie, pg_restore -s 256MB mybackup.db? It would just end up issuing a set sort_mem=256000.. What do you guys think? -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] More info in explain analyze
This has probably been asked before, but I'll re-ask to spark debate on it again. Is there any good reason to not have explain analyze also include information if temporary files will be required on sorts, hashes, etc. during the processing of a query. [Idea being setting your sort_mem won't be purely anecdotal]... maybe include how much space it needed in temp files? something along the lines of: Sort (Cost=1..10) (Actual=1..1000) (Temp Files=5MB) Seeing that and looking at your current sort_mem and seeing it is 4MB you'll have the info you need to get a nice boost by avoiding that spill at a low cost. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] Guesses on what this NestLoop is for?
On Mon, 27 Oct 2003 15:32:41 -0800, Josh Berkus <[EMAIL PROTECTED]> wrote: >FROM event_types, events > LEFT OUTER JOIN ... >WHERE events.status = 1 or events.status = 11 > and events.event_date > '2003-10-27' > and events.etype_id = event_types.etype_id > and ( ... > ); > > >What I can't figure out is what is that inredibly expensive nested loop for? Sorry, I have no answer to your question, but may I ask whether you really want to get presumably 106 output rows for each event with status 1? Or did you mean WHERE (events.status = 1 OR events.status = 11) AND ... >Ideas? I'd also try to push that NOT EXISTS condition into the FROM clause: ...LEFT JOIN (SELECT DISTINCT ON (event_id) event_id, mod_date, mod_user FROM event_history ORDER BY event_id, mod_date ) AS eh ON (events.event_id = eh.event_id) ... WHERE ... AND CASE WHEN eh.event_id IS NULL THEN events.mod_user ELSE eh.mod_user END = 562 If mod_user is NOT NULL in event_history, then CASE ... END can be simplified to COALESCE(eh.mod_user, events.mod_user). Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
