Обсуждение: Postgresql slow on XEON 2.4ghz/1gb ram
Hello.
I have this problem: i'm running the postgre 7.3 on a windows 2000 server with P3 1GHZ DUAL/1gb ram with good performance. For best performance i have change the server for a XEON 2.4/1gb ram and for my suprise the performance decrease 80%. anybody have a similar experience? does exist any special configuration to postgre running on a Xeon processor? Any have any idea to help-me? Excuse-me my bad english.
Very Thanks
Wilson
icq 77032308
msn wgalafassijr@hotmail.com
On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: > Hello. > I have this problem: i'm running the postgre 7.3 on a windows 2000 > server with P3 1GHZ DUAL/1gb ram with good performance. For best > performance i have change the server for a XEON 2.4/1gb ram and for my > suprise the performance decrease 80%. anybody have a similar experience? > does exist any special configuration to postgre running on a Xeon > processor? Any have any idea to help-me? Excuse-me my bad english. Don't know how Windows2000 deals with it, but I've noticed "noticeable" slowdowns with Xeon+Hyperthreading turned on ...
Hello, yes - I had the same problem using dual xeon machines. The problem seems to be related to the kernel that suffers from a lack of xeon cpu support. also the intel compiler seems to get much faster binaries than the gcc does. I finally skipped the whole xeon hardware for the use as postgresql database server. regards, -andreas On Wednesday 06 August 2003 15:20, Wilson A. Galafassi Jr. wrote: > Hello. > I have this problem: i'm running the postgre 7.3 on a windows 2000 server > with P3 1GHZ DUAL/1gb ram with good performance. For best performance i > have change the server for a XEON 2.4/1gb ram and for my suprise the > performance decrease 80%. anybody have a similar experience? does exist any > special configuration to postgre running on a Xeon processor? Any have any > idea to help-me? Excuse-me my bad english. Very Thanks > Wilson > icq 77032308 > msn wgalafassijr@hotmail.com
----- Original Message ----- From: "The Hermit Hacker" <scrappy@postgresql.org> To: "Wilson A. Galafassi Jr." <juniorlist@yahoo.com.br> Cc: <pgsql-admin@postgresql.org> Sent: Wednesday, August 06, 2003 3:10 PM Subject: Re: [ADMIN] Postgresql slow on XEON 2.4ghz/1gb ram > > > > On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: > > > Hello. > > > I have this problem: i'm running the postgre 7.3 on a windows 2000 > > server with P3 1GHZ DUAL/1gb ram with good performance. For best > > performance i have change the server for a XEON 2.4/1gb ram and for my > > suprise the performance decrease 80%. anybody have a similar experience? > > does exist any special configuration to postgre running on a Xeon > > processor? Any have any idea to help-me? Excuse-me my bad english. > > Don't know how Windows2000 deals with it, but I've noticed "noticeable" > slowdowns with Xeon+Hyperthreading turned on ... I would say that you are on the right track with this suggestion. Hyperthreading in P4/Xeon processors is supposed to emulate multiple processors but be more efficient by being in one processor. I can't remember which mag. it was I read now, but real world tests have shown that it isn't all what its made out to be and that dual processors on most cases out-performed the so called equivalent P4/Xeon processor. The other point is, when installing W2K, windows may or may not detect the Xeon as a multiple processor system and therefore only install the single processor version of the kernel. So you might want to check that you have multiple processor version of the kernel installed? If you have got the multiple processor version installed then the kernel obviously doesn't handle the Xeon hardware well and you might want to try a single processor kernel version and see how you get on. Regards Donald Fraser.
A P3 1GHz is probably roughly equivalent to a P4 1.5GHz, so going from dual P3 1GHz to single P4 2.4GHz would likely be slower in any case. Don't forget that unless you're talking about the "Xeon MP" then the whole "Xeon" tag is pretty meaningless for the P4 range.
If you moved to a *dual* P4 setup, then the only obvious reason for a slowdown would be if the disk subsystem is much slower on the new machine, or the new machine has a hardware problem.
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Wilson A. Galafassi Jr.
Sent: 06 August 2003 14:21
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Postgresql slow on XEON 2.4ghz/1gb ramHello.I have this problem: i'm running the postgre 7.3 on a windows 2000 server with P3 1GHZ DUAL/1gb ram with good performance. For best performance i have change the server for a XEON 2.4/1gb ram and for my suprise the performance decrease 80%. anybody have a similar experience? does exist any special configuration to postgre running on a Xeon processor? Any have any idea to help-me? Excuse-me my bad english.Very ThanksWilsonicq 77032308msn wgalafassijr@hotmail.com
Deacitvate the HyperThreading and it should run like a young cat *g*
"Wilson A. Galafassi Jr." <juniorlist@yahoo.com.br> schrieb im Newsbeitrag news:005a01c35c1d$86482d70$70c0b4c8@notebook...Hello.I have this problem: i'm running the postgre 7.3 on a windows 2000 server with P3 1GHZ DUAL/1gb ram with good performance. For best performance i have change the server for a XEON 2.4/1gb ram and for my suprise the performance decrease 80%. anybody have a similar experience? does exist any special configuration to postgre running on a Xeon processor? Any have any idea to help-me? Excuse-me my bad english.Very ThanksWilsonicq 77032308msn wgalafassijr@hotmail.com
On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: > do you now how to turn off the xeon Hyperthreading ?? Not sure ... in Unix, you just dont' enable it ... at least under FreeBSD (its disabled by default) ... check in your BIOS though, I *thought* I heard mention that there is a setting in there to disable it, but have never looked :(
The Hermit Hacker wrote: > On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: > > > do you now how to turn off the xeon Hyperthreading ?? > > Not sure ... in Unix, you just dont' enable it ... at least under FreeBSD > (its disabled by default) ... check in your BIOS though, I *thought* I > heard mention that there is a setting in there to disable it, but have > never looked :( I thought you could only enable/disable hyperthreading in the bios. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
--On Wednesday, August 06, 2003 19:43:53 -0400 Bruce Momjian <pgman@candle.pha.pa.us> wrote: > The Hermit Hacker wrote: >> On Wed, 6 Aug 2003, Wilson A. Galafassi Jr. wrote: >> >> > do you now how to turn off the xeon Hyperthreading ?? >> >> Not sure ... in Unix, you just dont' enable it ... at least under FreeBSD >> (its disabled by default) ... check in your BIOS though, I *thought* I >> heard mention that there is a setting in there to disable it, but have >> never looked :( > > I thought you could only enable/disable hyperthreading in the bios. The OS can disable/enable it as well. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
I have read somewhere that text fields are "compressed". What I
am curious about is how the compression of text fields by PostgreSQL might
be affecting the performance of my software. I currently store entire
copies of documents in a table called "fulltext" as such:
CREATE TABLE fulltext (
uid serial8 PRIMARY KEY,
content text NOT NULL,
contentidx txtidx
) ;
As you can see, I am using contrib/tsearch to find documents for display,
and then I dump out 'content' to the user.
Anyway, when I first created this table, I was concerned about the size of
'content' so I linked my program to zlib and deflate the content field
before storing it into the table. This means that every time someone
views a document I have to inflate it, also if what I have read is correct
about the text fields, PostgreSQL is trying to deflate/compress/whatever
the field when it stores it so I am duplicating effort. This probably
slows down the performance of my software, and because compressed
documents are often larger when re-compressed (at least with older
algorithms like LZW) I might be using extra space to store my data.
So:
1) do text fields get compressed
2) what compression method is used
3) is there a way to view actual storage space used versus
the value I get from length()
4) Can I disable the compression to improve storage speed
if the compression algorithm is not as good as deflate
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
On Wed, 20 Aug 2003, Brian McCane wrote: > I have read somewhere that text fields are "compressed". What I > am curious about is how the compression of text fields by PostgreSQL might > be affecting the performance of my software. I currently store entire > copies of documents in a table called "fulltext" as such: > > CREATE TABLE fulltext ( > uid serial8 PRIMARY KEY, > content text NOT NULL, > contentidx txtidx > ) ; > > As you can see, I am using contrib/tsearch to find documents for display, > and then I dump out 'content' to the user. > > Anyway, when I first created this table, I was concerned about the size of > 'content' so I linked my program to zlib and deflate the content field > before storing it into the table. This means that every time someone > views a document I have to inflate it, also if what I have read is correct > about the text fields, PostgreSQL is trying to deflate/compress/whatever > the field when it stores it so I am duplicating effort. This probably > slows down the performance of my software, and because compressed > documents are often larger when re-compressed (at least with older > algorithms like LZW) I might be using extra space to store my data. > > So: > 1) do text fields get compressed They can (if large enough and depending on their storage attributes). If the value turns out bigger it won't store the larger compressed one though (but it'll still attempt to compress it on insert). > 2) what compression method is used Looks like some LZ. > 4) Can I disable the compression to improve storage speed > if the compression algorithm is not as good as deflate See ALTER TABLE ALTER COLUMN SET STORAGE (I would guess you would want external, but I'm not 100% sure, check the docs :) ) Also, I'm not sure if storing a compressed version in a text field is a good idea. I'd think that bytea would be a better match.
Stephan Szabo wrote: > On Wed, 20 Aug 2003, Brian McCane wrote: >> 4) Can I disable the compression to improve storage speed >> if the compression algorithm is not as good as deflate > > See ALTER TABLE ALTER COLUMN SET STORAGE (I would guess you would want > external, but I'm not 100% sure, check the docs :) ) Yes, EXTERNAL is correct. We had a good thread on this topic on PERFORM a couple of weeks ago. Starts here: http://archives.postgresql.org/pgsql-performance/2003-08/msg00030.php Ends here: http://archives.postgresql.org/pgsql-performance/2003-08/msg00144.php Note the issue with UPDATE in place of the data. You need to concatenate an empty string to force the change of storage. > Also, I'm not sure if storing a compressed version in a text field is a > good idea. I'd think that bytea would be a better match. > You can pretty much guarantee problems if you store binary (i.e. pre-compressed) data in a text field. If your uncompressed data is not binary, you should probably do one of these options: 1) pre-compress and store in a bytea field with storage set to EXTERNAL 2) store as-is in a text field and let Postgres do the compression for you 3) store as-is in a text field with storage set to EXTERNAL Personally, I'd use #3 if you consider disk space cheap and performance important, or #2 if you really need the data compressed. You might get moderately better compression using an external program, but it is probably at a big performance hit. HTH, Joe