Re: [PERFORM] Pg+Linux swap use
On Thu, 30 Oct 2003 17:49:08 -0200 (BRST) "alexandre :: aldeia digital" <[EMAIL PROTECTED]> wrote: > Both use: Only postgresql on server. Buffers = 8192, effective cache = > 10 > Well, I'm assuming you meant 1GB of ram, not 1MB :) Check a ps auxw to see what is running. Perhaps X is running gobbling up your precious mem. But still.. with 1GB there should be virtually no swap activity. How busy is the DB? How many connections? and is sort_mem set high? -- 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] Pg+Linux swap use
Jeff wrote: On Thu, 30 Oct 2003 17:49:08 -0200 (BRST) "alexandre :: aldeia digital" <[EMAIL PROTECTED]> wrote: Both use: Only postgresql on server. Buffers = 8192, effective cache = 10 Well, I'm assuming you meant 1GB of ram, not 1MB :) Check a ps auxw to see what is running. Perhaps X is running gobbling up your precious mem. But still.. with 1GB there should be virtually no swap activity. How busy is the DB? How many connections? and is sort_mem set high? Also are two kernels exactly same? In my experience linux kernel behaves slightly different from version to version w.r.t swap aggressiveness... Shridhar ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] redundent index?
On Wed, 29 Oct 2003 10:17:24 -0500, Rod Taylor <[EMAIL PROTECTED]> wrote: >On Wed, 2003-10-29 at 09:03, Robert Treat wrote: >> Indexes: entity_watch_map_pkey primary key btree (entity_id, watch_id), >> ewm_entity_id btree (entity_id), >> >> I can't think of why the second index is there, as ISTM there is no >> instance where the first index wouldn't be used in place of the second > >The cost in evaluating the first index will be a little higher Yes, the actual cost may be a little higher. But the cost estimation might be significantly higher, so there can be border cases where the planner chooses a sequential scan over a multi-column index scan while a single-column index would correctly be recognized as being faster ... Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Pg+Linux swap use
Scott, Jeff and Shridhar: 1 GB RAM :) The stock kernels are not the same, HyperThreading enabled. 80 simultaneous connections. sort_mem = 4096 I will compile my own kernel on this weekend, and I will report to the list after. Thank's all Alexandre > Also are two kernels exactly same? In my experience linux kernel behaves > slightly different from version to version w.r.t swap aggressiveness... > > Shridhar > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Pg+Linux swap use
On Fri, Oct 31, 2003 at 12:03:59PM -0200, alexandre :: aldeia digital wrote: > Scott, Jeff and Shridhar: > > 1 GB RAM :) > > The stock kernels are not the same, HyperThreading enabled. 80 Some people have reported that things actually slow down with HT enabled. Have you tried turning it off? A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Pg+Linux swap use
Not being one to hijack threads, but I haven't heard of this performance hit when using HT, I have what should all rights be a pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it is only 50% as fast as my old server which was a dual AMD MP 1400's with a 45gb raid 5 array and 1gb of ram. I have read everything I could find on Pg performance tweaked all the variables that were suggested and nothing. Which is why I subscribed to this list, just been lurking so far but this caught my eye. Rob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Friday, October 31, 2003 8:36 AM To: [EMAIL PROTECTED] Subject: Re: [PERFORM] Pg+Linux swap use On Fri, Oct 31, 2003 at 12:03:59PM -0200, alexandre :: aldeia digital wrote: > Scott, Jeff and Shridhar: > > 1 GB RAM :) > > The stock kernels are not the same, HyperThreading enabled. 80 Some people have reported that things actually slow down with HT enabled. Have you tried turning it off? A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] Pg+Linux swap use
On Fri, 31 Oct 2003 09:31:19 -0600 "Rob Sell" <[EMAIL PROTECTED]> wrote: > Not being one to hijack threads, but I haven't heard of this > performance hit when using HT, I have what should all rights be a > pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig > of memory. And it is only 50% as fast as my old server which was a > dual AMD MP 1400's with a 45gb raid 5 array and 1gb of ram. I have > read everything I could find on Pg performance tweaked all the > variables that were suggested and nothing. Which is why I subscribed > to this list, just been lurking so far but this caught my eye. > > Rob There's benchmarks around that show in _some_ cases HT is not all it is cracked up to be, somtimes running slower. But the real point of this thread isn't all this stuff about hyperthreading, the problem is the guy is seeing swapping.. I'm guessing RH is running some useless stuff in the BG.. or maybe he's running a retarded kernel... or.. maybe.. just.. maybe.. little elves are doing it. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Pg+Linux swap use
Jeff wrote: On Fri, 31 Oct 2003 09:31:19 -0600 "Rob Sell" <[EMAIL PROTECTED]> wrote: Not being one to hijack threads, but I haven't heard of this performance hit when using HT, I have what should all rights be a pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it is only 50% as fast as my old server which was a dual AMD MP 1400's with a 45gb raid 5 array and 1gb of ram. I have read everything I could find on Pg performance tweaked all the variables that were suggested and nothing. Which is why I subscribed to this list, just been lurking so far but this caught my eye. Rob There's benchmarks around that show in _some_ cases HT is not all it is cracked up to be, somtimes running slower. To use HT effectively on needs. 1. A kernel that understands HT. 2. A task scheduler that understands HT 3. A CPU intensive load. So if you are running a stock RedHat and production postgresql database, turn it off. It won't hurt certainly(Almost certainly) I'm guessing RH is running some useless stuff in the BG.. or maybe he's running a retarded kernel... or.. maybe.. just.. maybe.. little elves are doing it. Too much..:-) I guess Alexandre can tune bdflush to be little less agressive. Comparing bdflush values on two machines might turn up something. His idea of compiling kernel is also good one. He can also try tuning some values in /proc/sys/vm but I don't find any documentation offhand. I usually run slackware and a handcompiled 2.6-test4. None of them use any swap unless true memory starts falling low. This touch-swap-even-if-oodles-of-ram-is-free is something I have't experienced on my desktop for quite a while. Shridhar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Pg+Linux swap use
Just for an additional viewpoint. I'm finishing up a project based on FreeBSD and PostgreSQL. The target server is a Dual 2.4G Intel machine. I have tested the application with hyperthreading enabled and disabled. To all appearances, enabling hyperthreading makes the box act like a quad, with the expected increase in processing capability - _for_this_application_. I have also heard the claims and seen the tests that show hyperthreading occasionally decreasing performance. I think in the end, you just have to test your particular application to see how it reacts. Rob Sell wrote: Not being one to hijack threads, but I haven't heard of this performance hit when using HT, I have what should all rights be a pretty fast server, dual 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it is only 50% as fast as my old server which was a dual AMD MP 1400's with a 45gb raid 5 array and 1gb of ram. I have read everything I could find on Pg performance tweaked all the variables that were suggested and nothing. Which is why I subscribed to this list, just been lurking so far but this caught my eye. Rob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Friday, October 31, 2003 8:36 AM To: [EMAIL PROTECTED] Subject: Re: [PERFORM] Pg+Linux swap use On Fri, Oct 31, 2003 at 12:03:59PM -0200, alexandre :: aldeia digital wrote: Scott, Jeff and Shridhar: 1 GB RAM :) The stock kernels are not the same, HyperThreading enabled. 80 Some people have reported that things actually slow down with HT enabled. Have you tried turning it off? A -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] index creation order?
Yet another question.. thanks to everyone responding to all these so far.. ;)
This one is basically.. given I have a big table already in COPY format,
about 28 million rows, all keys guaranteed to be unique, I'm trying to find
out which of the following will get the import finished the fastest:
a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then
CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
each fk needed.
b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE
UNIQUE INDEX on the PK.
c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on
needed columns, then run the COPY.
d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE
INDEX after table creation, then run the COPY.
My gut instinct tells me that in order, fastest to slowest, it's going to
be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and
Oracle.
If there isn't a significant difference between all of them, performance
wise, I think something is dreadfully wrong here. Running "a", the ALTER
TABLE to add the PK ran for 17 hours and still wasn't finished.
The table without indexes or keys is:
CREATE TABLE foo (
id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
master_id BIGINT NOT NULL,
other_id INTEGER NOT NULL,
status INTEGER NOT NULL,
addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
);
Details on machine and configuration are:
The machine is the same one I've mentioned before.. SMP AthlonMP 2800+
(2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a
quad-channel ICP-Vortex controller, 2GB physical memory. Running FreeBSD
RELENG_4, relevant filesystems with softupdates enabled and mounted noatime.
kernel options are:
maxusers0
options MAXDSIZ="(1536UL*1024*1024)" # maximum limit
options MAXSSIZ="(512UL*1024*1024)" # maximum stack
options DFLDSIZ="(512UL*1024*1024)" # default limit
options VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped
from default 200MB
options SYSVSHM #SYSV-style shared memory
options SYSVMSG #SYSV-style message queues
options SYSVSEM #SYSV-style semaphores
options SHMMAXPGS=262144
options SHMALL=262144
options SHMSEG=256
options SEMMNI=384
options SEMMNS=768
options SEMMNU=384
options SEMMAP=384
postgresql.conf settings are:
shared_buffers = 3
max_fsm_relations = 1
max_fsm_pages = 200
max_locks_per_transaction = 64
wal_buffers = 128
sort_mem = 1310720 (1.2GB)
vacuum_mem = 262144 (256MB)
checkpoint_segments = 64
checkpoint_timeout = 1200
commit_delay = 2
commit_siblings = 2
fsync=true
random_page_cost = 1.7
cpu_tuple_cost = 0.005
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0012
stats_start_collector = true
stats_command_string = true
stats_row_level = true
stats_block_level = true
---(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] index creation order?
If it is 7.4 beta 5 or later, I would definitely go with A.
Adding indexes after the fact seems to be much quicker. Foreign keys use
the same algorithm prior to beta 5 regardless of timing.
A primary key and unique index will have approx the same performance (a
check for NULL isn't very costly).
On Fri, 2003-10-31 at 11:02, Allen Landsidel wrote:
> Yet another question.. thanks to everyone responding to all these so far.. ;)
>
> This one is basically.. given I have a big table already in COPY format,
> about 28 million rows, all keys guaranteed to be unique, I'm trying to find
> out which of the following will get the import finished the fastest:
>
> a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
> each fk needed.
>
> b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE
> UNIQUE INDEX on the PK.
>
> c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on
> needed columns, then run the COPY.
>
> d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE
> INDEX after table creation, then run the COPY.
>
> My gut instinct tells me that in order, fastest to slowest, it's going to
> be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and
> Oracle.
>
> If there isn't a significant difference between all of them, performance
> wise, I think something is dreadfully wrong here. Running "a", the ALTER
> TABLE to add the PK ran for 17 hours and still wasn't finished.
>
> The table without indexes or keys is:
> CREATE TABLE foo (
> id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
> master_id BIGINT NOT NULL,
> other_id INTEGER NOT NULL,
> status INTEGER NOT NULL,
> addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
> );
>
> Details on machine and configuration are:
>
> The machine is the same one I've mentioned before.. SMP AthlonMP 2800+
> (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a
> quad-channel ICP-Vortex controller, 2GB physical memory. Running FreeBSD
> RELENG_4, relevant filesystems with softupdates enabled and mounted noatime.
>
> kernel options are:
> maxusers0
>
> options MAXDSIZ="(1536UL*1024*1024)" # maximum limit
> options MAXSSIZ="(512UL*1024*1024)" # maximum stack
> options DFLDSIZ="(512UL*1024*1024)" # default limit
> options VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped
> from default 200MB
> options SYSVSHM #SYSV-style shared memory
> options SYSVMSG #SYSV-style message queues
> options SYSVSEM #SYSV-style semaphores
> options SHMMAXPGS=262144
> options SHMALL=262144
> options SHMSEG=256
> options SEMMNI=384
> options SEMMNS=768
> options SEMMNU=384
> options SEMMAP=384
>
> postgresql.conf settings are:
>
> shared_buffers = 3
> max_fsm_relations = 1
> max_fsm_pages = 200
> max_locks_per_transaction = 64
> wal_buffers = 128
> sort_mem = 1310720 (1.2GB)
> vacuum_mem = 262144 (256MB)
> checkpoint_segments = 64
> checkpoint_timeout = 1200
> commit_delay = 2
> commit_siblings = 2
> fsync=true
> random_page_cost = 1.7
> cpu_tuple_cost = 0.005
> cpu_index_tuple_cost = 0.005
> cpu_operator_cost = 0.0012
>
> stats_start_collector = true
> stats_command_string = true
> stats_row_level = true
> stats_block_level = true
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
signature.asc
Description: This is a digitally signed message part
Re: [PERFORM] Pg+Linux swap use
Bill Moran <[EMAIL PROTECTED]> writes: > Just for an additional viewpoint. I'm finishing up a project based on FreeBSD > and PostgreSQL. The target server is a Dual 2.4G Intel machine. I have tested > the application with hyperthreading enabled and disabled. To all appearances, > enabling hyperthreading makes the box act like a quad, with the expected increase > in processing capability - _for_this_application_. > > I have also heard the claims and seen the tests that show hyperthreading > occasionally decreasing performance. I think in the end, you just have to > test your particular application to see how it reacts. My understanding is that the case where HT hurts is precisely your case. When you have two real processors with HT the kernel will sometimes schedule two jobs on the two virtual processors on the same real processor leaving the two virtual processors on the other real processor idle. As far as I know a single processor machine with HT does not benefit from disabling HT. -- 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] Pg+Linux swap use
For the record I am running on SuSE with a pretty much stock kernel. Not to sound naïve, but is turning of HT something done in the bios? Rob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Friday, October 31, 2003 9:56 AM To: [EMAIL PROTECTED] Subject: Re: [PERFORM] Pg+Linux swap use Just for an additional viewpoint. I'm finishing up a project based on FreeBSD and PostgreSQL. The target server is a Dual 2.4G Intel machine. I have tested the application with hyperthreading enabled and disabled. To all appearances, enabling hyperthreading makes the box act like a quad, with the expected increase in processing capability - _for_this_application_. I have also heard the claims and seen the tests that show hyperthreading occasionally decreasing performance. I think in the end, you just have to test your particular application to see how it reacts. Rob Sell wrote: > Not being one to hijack threads, but I haven't heard of this performance hit > when using HT, I have what should all rights be a pretty fast server, dual > 2.4 Xeons with HT 205gb raid 5 array, 1 gig of memory. And it is only 50% as > fast as my old server which was a dual AMD MP 1400's with a 45gb raid 5 > array and 1gb of ram. I have read everything I could find on Pg performance > tweaked all the variables that were suggested and nothing. Which is why I > subscribed to this list, just been lurking so far but this caught my eye. > > Rob > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan > Sent: Friday, October 31, 2003 8:36 AM > To: [EMAIL PROTECTED] > Subject: Re: [PERFORM] Pg+Linux swap use > > On Fri, Oct 31, 2003 at 12:03:59PM -0200, alexandre :: aldeia digital wrote: > >>Scott, Jeff and Shridhar: >> >>1 GB RAM :) >> >>The stock kernels are not the same, HyperThreading enabled. 80 > > > Some people have reported that things actually slow down with HT > enabled. Have you tried turning it off? > > A > -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Pg+Linux swap use
Greg Stark wrote: Bill Moran <[EMAIL PROTECTED]> writes: Just for an additional viewpoint. I'm finishing up a project based on FreeBSD and PostgreSQL. The target server is a Dual 2.4G Intel machine. I have tested the application with hyperthreading enabled and disabled. To all appearances, enabling hyperthreading makes the box act like a quad, with the expected increase in processing capability - _for_this_application_. I have also heard the claims and seen the tests that show hyperthreading occasionally decreasing performance. I think in the end, you just have to test your particular application to see how it reacts. My understanding is that the case where HT hurts is precisely your case. When you have two real processors with HT the kernel will sometimes schedule two jobs on the two virtual processors on the same real processor leaving the two virtual processors on the other real processor idle. Yup, that's why I tested it. While more testing is probably in order, I could find no disadvantages to running with HTT enabled. And when I ran many background jobs (which is likely to happen during batch processing on this sytem) the system seemed to perform as if it really had 4 processors. Perhaps this is an indication of the quality of the FreeBSD scheduler (maybe it's HTT aware and makes decisions accordingly?), but I'm not involved enough in that level of development to do any more than speculate. Again, this is pure speculation ... can anyone with a more technical insight comment on whether my guess is correct, or whether I'm not testing enough? -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Pg+Linux swap use
On Fri, Oct 31, 2003 at 10:42:06AM -0600, Rob Sell wrote: > For the record I am running on SuSE with a pretty much stock kernel. Not to > sound na?ve, but is turning of HT something done in the bios? As far as I know, yes. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(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] index creation order?
Allen, > a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and > each fk needed. Did you ANALYZE after the copy? > If there isn't a significant difference between all of them, performance > wise, I think something is dreadfully wrong here. Running "a", the ALTER > TABLE to add the PK ran for 17 hours and still wasn't finished. Adding the *primary key* locked up? This seems unlikely; we have a known problem with *foreign* keys until the current beta. But I've added primary keys on 20Gb tables and had it complete in a couple of hours. Ignore this adivice and look for Stephan Szabo's FK patch instead if what you really meant was that the FK creation locked up. > shared_buffers = 3 hmmm ... 236MB > max_fsm_pages = 200 2MB, fine ... > wal_buffers = 128 1MB, also fine ... > sort_mem = 1310720 (1.2GB) Problem here. As documented everywhere, sort_mem is allocated *per sort* not per query, user, or shared. This means that if the "add PK" operation involves 2 or more sorts (not sure, haven't tested it), then you're allocating .7GB RAM more than you acutally have. This may be the cause of your problem, particularly if *anything* is going on concurrent to the load. > checkpoint_segments = 64 IF you have the disk space (+ 2GB) I'd raise this to 150-300 during the load operation. > commit_delay = 2 > commit_siblings = 2 These settings are for heavy multi-user update activity. They are not useful for a single-user load, and may even lower performance. > stats_start_collector = true > stats_command_string = true > stats_row_level = true > stats_block_level = true If you can do without stats collection during load, I would suggest that you do so. The above add both RAM and I/O overhead to your operation. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] index creation order?
At 12:10 10/31/2003, Josh Berkus wrote: Allen, > a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and > each fk needed. Did you ANALYZE after the copy? No, and this was my major mistake. I normally run analyze periodically from cron, anywhere from once an hour to ever 15 minutes depending on the db.. I had disabled that for this because I didn't want anything competing with this stuff for disk I/O. I followed your other suggestions as well, canceled the index that was running, analyzed the whole db, and ran the queries again. All of them are running in under 10 or so minutes after the analyze. I'll just be adding the PKs and the Indexes, I can add triggers/rules of my own for the RI, rather than worry about FK creation screwing up. I had no idea analyze was playing such a big role in this sense.. I really thought that other than saving space, it wasn't doing much for tables that don't have indexes on the. Thanks for the help. > shared_buffers = 3 hmmm ... 236MB > max_fsm_pages = 200 2MB, fine ... > wal_buffers = 128 1MB, also fine ... > sort_mem = 1310720 (1.2GB) Problem here. As documented everywhere, sort_mem is allocated *per sort* not per query, user, or shared. This means that if the "add PK" operation involves 2 or more sorts (not sure, haven't tested it), then you're allocating .7GB RAM more than you acutally have. This may be the cause of your problem, particularly if *anything* is going on concurrent to the load. I didn't know this was per-sort per-backend, I thought it was per-backend for all sorts running on that backend. I've dropped it down to 256MB. > checkpoint_segments = 64 IF you have the disk space (+ 2GB) I'd raise this to 150-300 during the load operation. Done, at 128, which seems to be enough for now. I'll fiddle more with this later on. > commit_delay = 2 > commit_siblings = 2 These settings are for heavy multi-user update activity. They are not useful for a single-user load, and may even lower performance. That's what's going on.. this database I'm working on isn't the only one in the system, and some things are using different schemas in the database I'm working on, so this isn't something I can afford to turn off. Most of the activity is heavy and transient.. many INSERT/UPDATE/DELETE cycles. Again, thanks for the help, I really do appreciate it. It's gratifying and depressing to know the last two or so days work could've been compressed into 3 hours if I'd just run that damn analyze. ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] index creation order?
Nope, still 7.3.4 here.. I am very excited about 7.4 though.. almost as
excited as I am about FreeBSD 5.x going -STABLE.. it's a close race
between the two..
I'll keep this in mind for when I update though, thanks.
At 11:23 10/31/2003, Rod Taylor wrote:
If it is 7.4 beta 5 or later, I would definitely go with A.
Adding indexes after the fact seems to be much quicker. Foreign keys use
the same algorithm prior to beta 5 regardless of timing.
A primary key and unique index will have approx the same performance (a
check for NULL isn't very costly).
On Fri, 2003-10-31 at 11:02, Allen Landsidel wrote:
> Yet another question.. thanks to everyone responding to all these so
far.. ;)
>
> This one is basically.. given I have a big table already in COPY format,
> about 28 million rows, all keys guaranteed to be unique, I'm trying to
find
> out which of the following will get the import finished the fastest:
>
> a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min),
then
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
> each fk needed.
>
> b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE
> UNIQUE INDEX on the PK.
>
> c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on
> needed columns, then run the COPY.
>
> d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE
> INDEX after table creation, then run the COPY.
>
> My gut instinct tells me that in order, fastest to slowest, it's going to
> be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and
> Oracle.
>
> If there isn't a significant difference between all of them, performance
> wise, I think something is dreadfully wrong here. Running "a", the ALTER
> TABLE to add the PK ran for 17 hours and still wasn't finished.
>
> The table without indexes or keys is:
> CREATE TABLE foo (
> id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
> master_id BIGINT NOT NULL,
> other_id INTEGER NOT NULL,
> status INTEGER NOT NULL,
> addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
> );
>
> Details on machine and configuration are:
>
> The machine is the same one I've mentioned before.. SMP AthlonMP 2800+
> (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a
> quad-channel ICP-Vortex controller, 2GB physical memory. Running FreeBSD
> RELENG_4, relevant filesystems with softupdates enabled and mounted
noatime.
>
> kernel options are:
> maxusers0
>
> options MAXDSIZ="(1536UL*1024*1024)" # maximum limit
> options MAXSSIZ="(512UL*1024*1024)" # maximum stack
> options DFLDSIZ="(512UL*1024*1024)" # default limit
> options VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped
> from default 200MB
> options SYSVSHM #SYSV-style shared memory
> options SYSVMSG #SYSV-style message queues
> options SYSVSEM #SYSV-style semaphores
> options SHMMAXPGS=262144
> options SHMALL=262144
> options SHMSEG=256
> options SEMMNI=384
> options SEMMNS=768
> options SEMMNU=384
> options SEMMAP=384
>
> postgresql.conf settings are:
>
> shared_buffers = 3
> max_fsm_relations = 1
> max_fsm_pages = 200
> max_locks_per_transaction = 64
> wal_buffers = 128
> sort_mem = 1310720 (1.2GB)
> vacuum_mem = 262144 (256MB)
> checkpoint_segments = 64
> checkpoint_timeout = 1200
> commit_delay = 2
> commit_siblings = 2
> fsync=true
> random_page_cost = 1.7
> cpu_tuple_cost = 0.005
> cpu_index_tuple_cost = 0.005
> cpu_operator_cost = 0.0012
>
> stats_start_collector = true
> stats_command_string = true
> stats_row_level = true
> stats_block_level = true
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] index creation order?
On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote: > I had no idea analyze was playing such a big role in this sense.. I really > thought that other than saving space, it wasn't doing much for tables that > don't have indexes on the. ANALYZE doesn't save any space at all -- VACUUM is probably what you're thinking of. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Pg+Linux swap use
On Fri, 2003-10-31 at 11:37, Greg Stark wrote: > My understanding is that the case where HT hurts is precisely your case. When > you have two real processors with HT the kernel will sometimes schedule two > jobs on the two virtual processors on the same real processor leaving the two > virtual processors on the other real processor idle. If you're seeing this behavior, it's sounds like a bug/deficiency in your kernel's scheduler: if it is HT-aware, it should go to some lengths to avoid this kind of processor allocation. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] index creation order?
Allen, > I had no idea analyze was playing such a big role in this sense.. I really > thought that other than saving space, it wasn't doing much for tables that > don't have indexes on the. Among other things, ANALYZE tells postgres how many rows are in the table. So if you add a PK constraint after loading 10 million rows without ANALYZE, PostgreSQL is likely to think that there is only one row in the table ... and choose a nested loop or some other really inefficient method of checking for uniqueness. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] index creation order?
At 13:40 10/31/2003, Neil Conway wrote: On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote: > I had no idea analyze was playing such a big role in this sense.. I really > thought that other than saving space, it wasn't doing much for tables that > don't have indexes on the. ANALYZE doesn't save any space at all -- VACUUM is probably what you're thinking of. Actually, I was thinking VACUUM ANALYZE.. which is what I ran after the COPY.. sorry for my lack of precision. I've yet to run straight-up ANALYZE AFAIK. -Allen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Pg+Linux swap use
We had a problem at work that when a windows box would connect to a samba share with a lot of files in it, the kswapd was going nuts, even though we weren't low on memory at all. Updating to the 2.4.18 or so of the later redhats fixed that issue. It might be related. I think the kflush daemon can get fooled into thinking it needs to get hoppin right now in older 2.4.x kernels. On Fri, 31 Oct 2003, alexandre :: aldeia digital wrote: > Scott, Jeff and Shridhar: > > 1 GB RAM :) > > The stock kernels are not the same, HyperThreading enabled. 80 > simultaneous connections. sort_mem = 4096 > > I will compile my own kernel on this weekend, and I will report > to the list after. > > Thank's all > > Alexandre > > > > Also are two kernels exactly same? In my experience linux kernel behaves > > slightly different from version to version w.r.t swap aggressiveness... > > > > Shridhar > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] index creation order?
is there any way to update the stats inside a transaction? what i have is something like: select count(*) from foo; -> 0 begin; copy foo from '/tmp/foo'; -- about 100k rows -- run some queries on foo which perform horribly because the stats -- are way off (100k rows v. 0 rows) commit; it seems that you cannot run analyze inside a transaction: begin; analyze foo; ERROR: ANALYZE cannot run inside a BEGIN/END block i am using version 7.2.3. any work-a-rounds? should i try updating pg_statistic manually? On Fri, 31 Oct 2003, Josh Berkus wrote: > Among other things, ANALYZE tells postgres how many rows are in the table. So > if you add a PK constraint after loading 10 million rows without ANALYZE, > PostgreSQL is likely to think that there is only one row in the table ... and > choose a nested loop or some other really inefficient method of checking for > uniqueness. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] Postgres 7.3.4 + Slackware 9.1
Hello all! Do anyone have experience installing Postgres 7.3.4 on Slackware 9.1? Do exist any trouble, bug, problem... or is a good MIX? I want to "leave" RedHat (9) because is not "free" anymore and i don't want to use fedora BETA TEST versions. Any suggestion? THANKS ALL. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] index creation order?
> begin; > analyze foo; > ERROR: ANALYZE cannot run inside a BEGIN/END block > > i am using version 7.2.3. Time to upgrade. 7.3 / 7.4 allows this to happen. signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Postgres 7.3.4 + Slackware 9.1
Hi! I haven´t really tested it on Slackware 9.1. But i am running Postgresql now for over two years on various Slackware versions. My current server is running on Slackware 8.0 with a lot of packages (especially the core libs) upgraded to slack 9.1 packages. I had never problems with postgresql related to slackware except that the old 8.0 readline packages was too old for postgresql 7.3.x, but that was not really a problem. But it seems, there´s no prepackaged Postgresql for Slackware, so you would have to compile it yourself. Christoph Nelles Am Freitag, 31. Oktober 2003 um 21:55 schrieben Sie: P> Hello all! P> Do anyone have experience installing Postgres 7.3.4 on Slackware 9.1? P> Do exist any trouble, bug, problem... or is a good MIX? P> I want to "leave" RedHat (9) because is not "free" anymore and i don't P> want to use fedora BETA TEST versions. P> Any suggestion? P> THANKS ALL. P> ---(end of P> broadcast)--- P> TIP 5: Have you checked our extensive FAQ? P>http://www.postgresql.org/docs/faqs/FAQ.html -- Mit freundlichen Grüssen Evil Azraelmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] index creation order?
Chester Kustarz <[EMAIL PROTECTED]> writes: > it seems that you cannot run analyze inside a transaction: You can in 7.3.* ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ PROBLEM SOLVED ] Re: [PERFORM] Query puts 7.3.4 on endless loop but 7.4beta5 is fine.
Well Sorry everyone , The problem was tracked down to a silly datatype mismatch between two join columns in table Groups(instance) and Tickets(id) (int vs varchar ) 7.4b5 is automatically taking care of this mismatch hence it was getting executed there. But , The problem is will this behaviour not allow to go such mistakes unnoticed? Regards Mallah. On Friday 31 Oct 2003 4:08 am, Greg Stark wrote: > Well, you might want to try the EXISTS version. I'm not sure if it'll be > faster or slower though. In theory it should be the same. > > Hum, I didn't realize the principals table was the largest table. But > Postgres knew that so one would expect it to have found a better plan. The > IN/EXISTS handling was recently much improved but perhaps there's still > room :) > > SELECT * > FROM tickets > WHERE EXISTS ( >SELECT 1 > FROM groups > JOIN principals ON (groups.id = principals.objectid) > JOIN cachedgroupmembers ON (principals.id = > cachedgroupmembers.groupid) JOIN users ON (cachedgroupmembers.memberid = > users.id) > WHERE lower(users.emailaddress) = '[EMAIL PROTECTED]' > AND groups.domain = 'RT::Ticket-Role' > AND groups.type = 'Requestor' > AND principals.principaltype = 'group' > AND groups.instance = tickets.id > ) > AND type = 'ticket' > AND effectiveid = tickets.id > AND (status = 'new' OR status = 'open') > ORDER BY priority DESC > LIMIT 10; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [linux-lvm] RE: [ADMIN] [PERFORM] backup/restore - another ar ea.
On Thu, Oct 30, 2003 at 10:28:10AM -0700, [EMAIL PROTECTED] wrote: > Does xfs_freeze work on red hat 7.3? It works on any kernel with XFS (it talks directly to XFS). cheers. -- Nathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
