[ADMIN] Performance
Hello all! I need to setup high performance DB server. Some time ago I red there about processor cache influence on query execution performance. A question: What system would perform better? lh6000 with two xeon 7000Mhz 2MB cache or with four xeon 7000Mhz 1MB cache Mark ---(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
[ADMIN] adding fields
I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise. Thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] adding fields
Thank you. I found this doc just after I sent the email. I appreciate the quick response. Jodi - Original Message - From: Florian Helmberger To: Jodi Kanter Cc: [EMAIL PROTECTED] Sent: Friday, January 18, 2002 10:14 AM Subject: RE: [ADMIN] adding fields Hi. You should use ALTER TABLE ADD COLUMN ; instead. Also, take a look at the docs: http://www.postgresql.org/idocs/index.php?sql-altertable.html Cheers, Florian -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jodi KanterSent: Friday, January 18, 2002 3:56 PMTo: Postgres Admin ListSubject: [ADMIN] adding fields I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise. Thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] adding fields
Hi Jodi-- No, you shouldn't have to go to that amount of trouble. Try this instead (change the name of the table, the default value, the column name, and the column type to suit your needs): BEGIN WORK;LOCK TABLE table_adding_stuff_to IN ACCESS EXCLUSIVE MODE; ALTER TABLE table_adding_stuff_to ADD COLUMN new_column_name COLUMNTYPE;ALTER TABLE table_adding_stuff_to ALTER COLUMN new_column_name SET DEFAULT 'default value'; COMMIT WORK; Then you can do: BEGIN WORK; UPDATE table_adding_stuff_to SET new_column_name = 'default value'; COMMIT WORK; Heather - Original Message - From: Jodi Kanter To: Postgres Admin List Sent: Friday, January 18, 2002 9:55 AM Subject: [ADMIN] adding fields I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise. Thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
Re: [ADMIN] adding fields
Hi. You should use ALTER TABLE ADD COLUMN ; instead. Also, take a look at the docs: http://www.postgresql.org/idocs/index.php?sql-altertable.html Cheers, Florian -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jodi KanterSent: Friday, January 18, 2002 3:56 PMTo: Postgres Admin ListSubject: [ADMIN] adding fields I need to add a field to an existing table that contains a good amount of data. Do I need to drop table, recreate, and then reload data to do this? I cannot seem to find any documentation to say otherwise. Thanks Jodi ___Jodi L KanterBioInformatics Database AdministratorUniversity of Virginia(434) 924-2846[EMAIL PROTECTED]
[ADMIN] problem concerning vacuum/statistics & query performance
Hi, i have a performance problem with a db. This db consists of only a few tables and is continuously fed with new datasets (3min interval) and at night it is pruned (all but the last set are deleted) and vacuumed. the problem is that queries run (far) too slow because the planner seems to be using incorrect statistics (during the day the larger tables will grow to several hundred-thousand rows). if i manually VACUUM ANALYZE the db when it is pretty loaded (~300k rows) the planner starts using a different plan which runs acceptable performance wise. however after the daily cleanup it reverts back to the old (bad) plan. so it seems the nightly VACUUM does some analyzing as well? in which case the planner is using totally off-base statistics for most of the day, which in itself isnt bad but i'd rather have it use large table stats on small tables than vice versa. any thoughts? (other than upgrading to 7.2 ;) ) (postgres is 7.0.3 btw) Esger -- NeoMail - Webmail that doesn't suck... as much. http://neomail.sourceforge.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] problem concerning vacuum/statistics & query performance
Hi, i have a performance problem with a db. This db consists of only a few tables and is continuously fed with new datasets (3min interval) and at night it is pruned (all but the last set are deleted) and vacuumed. the problem is that queries run (far) too slow because the planner seems to be using incorrect statistics (during the day the larger tables will grow to several hundred-thousand rows). if i manually VACUUM ANALYZE the db when it is pretty loaded (~300k rows) the planner starts using a different plan which runs acceptable performance wise. however after the daily cleanup it reverts back to the old (bad) plan. so it seems the nightly VACUUM does some analyzing as well? in which case the planner is using totally off-base statistics for most of the day, which in itself isnt bad but i'd rather have it use large table stats on small tables than vice versa. any thoughts? (other than upgrading to 7.2 ;) ) (postgres is 7.0.3 btw) Esger -- NeoMail - Webmail that doesn't suck... as much. http://neomail.sourceforge.net ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] problem concerning vacuum/statistics & query performance
"Esger Abbink" <[EMAIL PROTECTED]> writes: > if i manually VACUUM ANALYZE the db when it is pretty loaded (~300k > rows) the planner starts using a different plan which runs acceptable > performance wise. however after the daily cleanup it reverts back to > the old (bad) plan. > so it seems the nightly VACUUM does some analyzing as well? Plain VACUUM (no ANALYZE) won't touch pg_statistic, but it does update the pages and tuples estimates in pg_class. > in which case the planner is using totally off-base statistics for > most of the day, which in itself isnt bad but i'd rather have it use > large table stats on small tables than vice versa. You could do this in your nightly script: -- delete lotsa stuff from mytable; vacuum mytable; update pg_class set reltuples = M, relpages = N where relname = 'mytable'; where M and N correspond to the peak values instead of the minima. Kinda grotty but it'll get the job done. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] duplicate post
did everyone else also receive two copies of my last post? if so, i hope it stays at 2 as on an other pg list one post got duplicated 5 times and tbh i'm stumped as to why it happens... /me scratches head.. -- NeoMail - Webmail that doesn't suck... as much. http://neomail.sourceforge.net ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Performance
On Friday, January 18, 2002, at 02:00 AM, Martins Zarins wrote: > Hello all! > > I need to setup high performance DB server. Some time ago I red > there about processor cache influence on query execution > performance. > A question: > What system would perform better? > lh6000 with two xeon 7000Mhz 2MB cache > or > with four xeon 7000Mhz 1MB cache It's more than just processor cache, it's your whole I/O subsystem. How fast are your drives? How fast is the drive controller? How much cache is on each drive? How much cache is on the drive controller? Are you going to use a RAID? If so, what type? Do you have enough memory for the size of the database and type of queries you're going to run? As far as processor cache goes, your goal is to avoid cache misses...so it depends on how many connections you're expecting, what those connections will be doing, etc. The best advice is to run your own benchmarks and find out for yourself. --Jeremy ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Using Database, 2 or more filesystem
At 05:00 AM 1/14/2002 , Rodrigo Miguel wrote: >My problem is, my filesystem is full and I can't extend it, so can i use a >secondary filesystem ? Yeah, move some files to another file system, and then use symbolic links to point to them from within $PGDATA. -crl -- Chad R. Larson (CRL22)[EMAIL PROTECTED] Eldorado Computing, Inc. 602-604-3100 5353 North 16th Street, Suite 400 Phoenix, Arizona 85016-3228 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] HELP: language option software package not installed
It's all good. The root of the problem lied in the fact that the default perl install for Solaris8 was built using SUNWspro (Sun's compiler), and therefore the Config.pm had flags/paths/arguments to reflect that compiler However, we are using the sunfreeware installs of GCC and associated tools The bottom line is that the Config.pm had to be adjusted to use GCC instead of cc Thanks for the help. --Randall Heather Johnson wrote: >It looks like the user you are when you install postgres does not know the >path to your compiler. Try installing gcc and use it instead of cc to >compile postgres. Make sure your user's .profile has the path to gcc before >installing. > >Heather > >- Original Message - >From: "Allan C. Huffman" <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> >Sent: Monday, January 14, 2002 12:11 PM >Subject: [ADMIN] HELP: language option software package not installed > > >>Hello Everyone, >> >>It has been sometime since I have installed PostgreSQL. It has been >>running great with no crashes for like a year! But alas, my faithful >>Sparc 20 turned belly-up. It is sad to loose an old friend @* | *@ >> >>I now have a Ultra10 :o) I've loaded Solaris 8 with the companion disk >>that loaded Perl5. Everything appears to be loaded into >>/usr/Perl5/5.00503. It looks OK to me but this happens: >> >>./configure --with-perl --enable-odbc --enable-syslog --enable-cassert >> >>Then during the gmake: >> >>cp Pg.pm blib/lib/Pg.pm >>AutoSplitting blib/lib.Pg.pm (blib/lib/auto/Pg) >>/usr/bin/perl -I/usr/perl5/5.00503/sun4-solaris -I/usr/perl5/5.00503 >>/usr/perl5/5.00503/ExtUtils/xsubpp -typemap >>/usr/perl5/5.00503/ExtUtils/typemap -typemap typemap Pg.xs >xstmp.c && >>mv xstmp.c Pg.c cc -c -I/usr/local/pgsql/include -x03 -xdepend >>-DVERSION=\"1.8.0\" -DXS_VERSION=\"1.8.0\" -KPIC >>-I/usr/perl5/5.00503/sun4-solaris/CORE Pg.c >>/usr/ucb/cc: language optional software package not installed >>gmake[4]: *** [Pg.o] Error 1 >>gmake[4]: Leaving directory >>'/opt/pgsql/postgresql-7.1.3/src/interfaces/perl5' >>gmake[3]: *** [Install] Error 2 >>gmake[3]: Leaving directory >>'/opt/pgsql/postgresql-7.1.3/src/interfaces/perl5' >>gmake[2]: *** [Install] Error 2 >>gmake[2]: Leaving directory '/opt/pgsql/postgresql-7.1.3/src/interfaces' >> >>gmake[1]: *** [Install] Error 2 >>gmake[1]: Leaving directory '/opt/pgsql/postgresql-7.1.3/src' >>gmake: *** [Install] Error 2 >> >> >> >> >>---(end of broadcast)--- >>TIP 4: Don't 'kill -9' the postmaster >> > -- Randall S. Shutt Systems Engineer Computer Systems and Communications Corporation A General Dynamics Company [EMAIL PROTECTED] 1-703-814-9105 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
