Re: [GENERAL] server hardware recommendations (the archives aredead)
> > > database server, a lot of ram will be required for fast operation. I was > > > thinking at a minimum 256 meg of ram. I also want to have the database run > > > on a RAID 5 array for speed and fault tolerance. Any suggestions here for > > > disk type, RAID scheme (software or hardware), controller type, etc.? Any Obviously, memory needs vary according to what apps are running. On my linux RH6.0 system, I'm finding that pgsql backends take ~4Mb each. Seems like I need something near ~100Mb for overhead (X server-20Mb, netscape-40Mb, xterms-10@4Mb each). For apache with 30 child processes @ 25Mb each (mod_perl with lots of cached data and lots of modules), that quickly adds up to a gig of ram to avoid performance-killing swapping. > > my preference tends to be software raid...whatever I've ever seen as far > > as hardware raid is concerned has been quite slower then software > > raid...and this is with high-end servers... > Jeff Hoffman wrote: > ...i have seen several places which told me > that software raid under linux _isn't_ safe for multiprocessors & no > place has told me for sure that it is. Would you mind clarifying your understanding on which versions of linux are unsafe for software raid and how? Browsing deja, RH 6.1 (2.2.12-20smp) reportedly handles software raid without known problems (other than less than glowing documentation reviews), and I'm setting up pgsql/apache on such a system with software raid (no clear showstopping problems yet). Thanks, Ed Loehr
[GENERAL] indices don't make much difference
I am trying to optimise a query which looks like: select prod_base.*, manu_base.name from prod_base, manu_base where prod_base.mid=manu_base.mid; manu_base is a table consisting of 3000 manufacturer with an id (not unique to support synonyms) and a name (declared as varchar(32)). prod_base is a table of products which each refer to the manufacturer id (mid). I have tried creating an index for manu_base using the following commands: create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops"); drop index manu_mid_idx create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops"); drop index manu_mid_idx I have then run benchmarks without index, with btree and with hash, but none seem to be faster than the other. My benchmark program is written in c and is attached to this email. Here are the results I obtained using time: without index: 17.25 real 1.42 user 0.26 sys with btree: 17.28 real 1.38 user 0.30 sys with hash: 17.22 real 1.37 user 0.32 sys If there is any way to make a query quicker when joining a product table and a manufacturer table, please let me know. I've tried everything and the results are quite fast enough. Thanks, Marc #include #include #include #include "libpq-fe.h" static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main() { int i, j; char *dbName = "wtbwts"; PGconn *conn; PGresult *res; conn = PQsetdb(NULL, NULL, NULL, NULL, dbName); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to database '%s' failed.\n", dbName); fprintf(stderr, "%s", PQerrorMessage(conn)); exit_nicely(conn); } for (i=0; i<50; i++) { res = PQexec(conn, "SELECT prod_base.*, manu_base.name FROM prod_base, manu_base where prod_base.mid = manu_base.mid"); if (!res || PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "SELECT failed\n"); PQclear(res); exit_nicely(conn); } for (j=0; j
Re: [GENERAL] How do I change port for the postmaster?
Syntax: postmaster -p Examples: postmaster -p 5432 postmaster -p 5433 Cheers, Ed Robert Wagner wrote: > I'm attempting to run two different postmasters on two different HP-UX > servers, servicing two different apps. The only commonality is that I'm on > one terminal. > > The first starts up OK. When I attempt to start the postmaster on the > other server, I get the error, > > FATAL: StreamServerPort: bind() failed: errno=226 Is another postmaster > already running on that port? If not, remove socket node > (/tmp/.s.PGSQL.5432) and retry./usr/local/pgsql/bin/postmaster: cannot > create UNIX stream port > > Tried deleting the file, /tmp/.s.PGSQL.5432 but this had no effect. > > Do I need to use a different port number, to run postmaster on two > different servers? If so, does anybody know how I can change the port > number? Is there something else, that I'm doing wrong? > > I'm looking through the docs but can't find anything. I'd like to keep > postgres in the organization and avoid switching to (and learning) some big > commercial product like oracle! >
[GENERAL] Czech2ASCII with --mb=Latin2
Hi, I have a database in Latin2 encoding (Czech stuff) and Latin2/Win1250 on-the-fly recoding with 'set client_encoding' works smoothly. Now, when I set client encoding to SQL_ASCII, accented characters are converted to (hexa) codes. Is there any (simple) way to make this recoding convert accented characters to just the chars themselves but without accents? Thanks in advance. - Robert P.S. Moreover, the non-Czech speakers tend to search the database with words without accents, it would be usefull to make this conversion works in the other direction: name LIKE 'ceske%' would return also names starting with accented version. P.S.2 I could do this quite easily in Perl on the application level, but don't want to start programming before I'm sure there's no standard postgres solution.
[GENERAL] How do I change port for the postmaster?
I'm attempting to run two different postmasters on two different HP-UX servers, servicing two different apps. The only commonality is that I'm on one terminal. The first starts up OK. When I attempt to start the postmaster on the other server, I get the error, FATAL: StreamServerPort: bind() failed: errno=226 Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry./usr/local/pgsql/bin/postmaster: cannot create UNIX stream port Tried deleting the file, /tmp/.s.PGSQL.5432 but this had no effect. Do I need to use a different port number, to run postmaster on two different servers? If so, does anybody know how I can change the port number? Is there something else, that I'm doing wrong? I'm looking through the docs but can't find anything. I'd like to keep postgres in the organization and avoid switching to (and learning) some big commercial product like oracle! Thanks! Rob Wagner Lowly Consultant, SIAC
Re: [GENERAL] server hardware recommendations (the archives aredead)
"Ross J. Reedstrom" wrote: > > On Wed, Dec 15, 1999 at 11:27:36AM -0400, The Hermit Hacker wrote: > > On Wed, 15 Dec 1999, Jeff Hoffmann wrote: > > > > Most of my RAID tests are on Solaris+Disksuite...with good drives > > in the machine, my writes are something like 18MB/s to the drive, stripe'd > > and mirrored...I think reads worked out to be 19MB/s...(bad drives, same > > Ah, this would be a RAID 0+1 setup, then? Very different from Jeff's RAID > 5 configuration. I'd be willing to believe that software RAID 0+1 _could_ > be faster than most hardware (it's just shuffling and dupping blocks > around to different drives, which could be done with clever pointer > twiddling) but calculating parity bits in hardware for RAID 5 had got > to be a win, doesn't it? > i would assume that this would be the case. for anybody who is going to spec a new machine for a database as small as 3-5G, RAID 0+1 has got to be the choice. i don't have a doubt that it'd be reasonably fast with software raid. anymore, it'd be hard to buy new disks that small to build a 0+1 for < 8G (4x4G will give you 8G). when you're on a budget with a backup server that needs 20+ drives (n+1 for raid5) vs. 40+ drives (2N for 0+1), though, raid 5 is a good solution. doing it again, i'd go with a hardware controller since no one seems to be refuting my assumption that the raid5 daemon can suck up a lot of CPU when calculating parity, even with 2 fairly fast processors.
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, 15 Dec 1999, Ross J. Reedstrom wrote: > Ah, this would be a RAID 0+1 setup, then? Very different from Jeff's RAID > 5 configuration. I'd be willing to believe that software RAID 0+1 _could_ > be faster than most hardware (it's just shuffling and dupping blocks > around to different drives, which could be done with clever pointer > twiddling) but calculating parity bits in hardware for RAID 5 had got > to be a win, doesn't it? Oops, overlooked the RAID5 issue...sorry about that... > > setup, same machine, same OS, were net'ng me something like 3MB/s...really > > killed performance *grin*) > > Hmm, bad drives as in broken, or slow? Slow...they were bought brand new a year ago...Fujitsu's...even a single, non-stripe'd drive, was performing atrociously...replaced them with Seagates and the machine flies ... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, Dec 15, 1999 at 11:27:36AM -0400, The Hermit Hacker wrote: > On Wed, 15 Dec 1999, Jeff Hoffmann wrote: > > > > my preference tends to be software raid...whatever I've ever seen as far > > > as hardware raid is concerned has been quite slower then software > > > raid...and this is with high-end servers... > > > > i kind of question this, and here's why: i just set up a linux dual > > P3/256MB with 4 software raid 5 volumes and even loading data into one > > of the databases slows it to a crawl. i've been looking around because > > > Most of my RAID tests are on Solaris+Disksuite...with good drives > in the machine, my writes are something like 18MB/s to the drive, stripe'd > and mirrored...I think reads worked out to be 19MB/s...(bad drives, same Ah, this would be a RAID 0+1 setup, then? Very different from Jeff's RAID 5 configuration. I'd be willing to believe that software RAID 0+1 _could_ be faster than most hardware (it's just shuffling and dupping blocks around to different drives, which could be done with clever pointer twiddling) but calculating parity bits in hardware for RAID 5 had got to be a win, doesn't it? As it turns out, I'm speccing a similar machine right now, myself, and I've been running into statements like yours re: software RAID that surprised me. > setup, same machine, same OS, were net'ng me something like 3MB/s...really > killed performance *grin*) Hmm, bad drives as in broken, or slow? Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] server hardware recommendations (the archives are dead)
At 08:41 AM 12/15/99 -0500, Adam Rossi wrote: >I know this question has been asked before. I have seen it in the archives. >Unfortunately the archives are dead right now (any search will yield "no >results") and I need to make some decisions. > >Can anyone give some general recommendations on hardware for a server >running Linux (RH6 or 6.1) and PostgreSQL? I estimate that there will be >about 2 GIG of data stored in the database initially, but this could grow as >high as 5 GIG in the future. > >Since the db is not multithreaded, I assume that buying a dual processor >board and two processors would not be helpful to performance. Like any >database server, a lot of ram will be required for fast operation. I was >thinking at a minimum 256 meg of ram. I also want to have the database run >on a RAID 5 array for speed and fault tolerance. Any suggestions here for >disk type, RAID scheme (software or hardware), controller type, etc.? Any >rule of thumb on the "extra" disk space needed above raw storage space for >PostgreSQL operations (temporary tables, vacuum issues, etc)? > >Any past experiences, benchmarks, guesses, or hearsay gladly accepted. >Thanks for your help. I think you may want to consider a software based volume manager (e.g. Veritas) and run a stripped mirror (RAID 10) rather than RAID 5. This will help out on writes. I'm not into RH, but surely they must offer something comparable to FreeBSD's Vinum. If not, I know Debian has one available. Ciao-- Ken http://www.y2know.org/safari Failure is not an option. It comes bundled with your Microsoft product.
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, 15 Dec 1999, J. Roeleveld wrote: > > What filesystem? I know (thank god) very little about Linux, but > > there have been comments here by some Linux folks (Thomas, wasn't it > > you?) that indicated that ext2fs sucks for this? Are you running with > > fsync() on or off? > > What is the problem with ext2fs? Is it just performance? or is there a > serious chance for me losing data? That, I do not know...this is just something I recall from previous discussions... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] Postgres restart problems
I remember having to do the same thing on a aix oracle installation. I wrote a little shell script that did it for me. bob "Differentiated Software Solutions Pvt. Ltd." wrote: > Hi, > > We have a postgres db which has been crashing about 5 times a day. It must > be failrly apparent given my other messages to this mailing list. > > Now we are faced with a new problem. After crash we were able to restart > postmaster. Now postgres is getting more temperamental. It refused to > restart saying that the data directory is not found. > When we posted this message last time... Somebody said... check out whether > hard disk mounted. This is hilarious. We are doing serious stuff and the > minimum we ensure that the directory exists. > > Finally I have cracked the problem. ipcclean utility does nothing. Instead > you have to explicitly use ipcs and ipcrm and to clear all semaphore and > shared memory which is blocked. After which restarting postmaster goes > thru'. > > Hope this helps somebody in a different state of distress than mine. > > Bye, > > Murali > > Differentiated Software Solutions Pvt. Ltd., > 176, Gr. Floor, 6th Main > 2nd Block RT Nagar > Bangalore - 560 032 > India > Ph: 91 80 3431470 > > +ACoAKgAqACoAKgAqACoAKgAqACoAKgAq- -- [EMAIL PROTECTED] [EMAIL PROTECTED] http://people.ne.mediaone.net/rsdavis
Re: [GENERAL] server hardware recommendations (the archives aredead)
> What filesystem? I know (thank god) very little about Linux, but > there have been comments here by some Linux folks (Thomas, wasn't it > you?) that indicated that ext2fs sucks for this? Are you running with > fsync() on or off? What is the problem with ext2fs? Is it just performance? or is there a serious chance for me losing data? Joost Roeleveld
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, 15 Dec 1999, Jeff Hoffmann wrote: > > my preference tends to be software raid...whatever I've ever seen as far > > as hardware raid is concerned has been quite slower then software > > raid...and this is with high-end servers... > > i kind of question this, and here's why: i just set up a linux dual > P3/256MB with 4 software raid 5 volumes and even loading data into one > of the databases slows it to a crawl. i've been looking around because > it seems absurd that the machine should slow down so much. i haven't > really found any answers, but i have seen several places which told me > that software raid under linux _isn't_ safe for multiprocessors & no > place has told me for sure that it is. What filesystem? I know (thank god) very little about Linux, but there have been comments here by some Linux folks (Thomas, wasn't it you?) that indicated that ext2fs sucks for this? Are you running with fsync() on or off? > appreciate it. assuming this is the case, software raid wouldn't be a > big problem if you don't do a lot of heavy writing. Most of my RAID tests are on Solaris+Disksuite...with good drives in the machine, my writes are something like 18MB/s to the drive, stripe'd and mirrored...I think reads worked out to be 19MB/s...(bad drives, same setup, same machine, same OS, were net'ng me something like 3MB/s...really killed performance *grin*) Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: [GENERAL] server hardware recommendations (the archives aredead)
The Hermit Hacker wrote: > > On Wed, 15 Dec 1999, Adam Rossi wrote: > > > I know this question has been asked before. I have seen it in the archives. > > Unfortunately the archives are dead right now (any search will yield "no > > results") and I need to make some decisions. > > > > Can anyone give some general recommendations on hardware for a server > > running Linux (RH6 or 6.1) and PostgreSQL? I estimate that there will be > > about 2 GIG of data stored in the database initially, but this could grow as > > high as 5 GIG in the future. > > > > Since the db is not multithreaded, I assume that buying a dual processor > > board and two processors would not be helpful to performance. Like any > > actually, if you are going to have concurrent connections to the backend, > in some circumstances, PostgreSQL will handle multi-processors better then > others...process one runs on CPU0, process two runs on CPU1, etc... just to expand on this a little, it really depends on how the OS handles threads. at worst case, a multithreaded process might be restricted to running all its threads on a single CPU. i don't know of any OS's that do that, though. the best case for a multithreaded process (DB) would be that it could run threads on all processors simultaneously, which should be nearly equivalent to a multiprocess DB which should (assign the processes to the CPUs automatically), minus the overhead of each of the processes. then again, my understanding is that in linux, a thread is pretty much equivalent to a process anyway, so it would be a wash. to sum up, SMP & postgres is a good idea. > > > database server, a lot of ram will be required for fast operation. I was > > thinking at a minimum 256 meg of ram. I also want to have the database run > > on a RAID 5 array for speed and fault tolerance. Any suggestions here for > > disk type, RAID scheme (software or hardware), controller type, etc.? Any > > my preference tends to be software raid...whatever I've ever seen as far > as hardware raid is concerned has been quite slower then software > raid...and this is with high-end servers... i kind of question this, and here's why: i just set up a linux dual P3/256MB with 4 software raid 5 volumes and even loading data into one of the databases slows it to a crawl. i've been looking around because it seems absurd that the machine should slow down so much. i haven't really found any answers, but i have seen several places which told me that software raid under linux _isn't_ safe for multiprocessors & no place has told me for sure that it is. i am running kernel 2.2.12 & i'm not having any problems with it other than it slowing down to a crawl & that only happens when i'm loading data. right now i'm guessing the reason for this is that the raid5 daemon is such a high priority that it's sucking away all the CPU cycles from everything else to calculate parity information. like i said, i haven't found a lot of information about this, so if someone could confirm or explain what's happening, i'd appreciate it. assuming this is the case, software raid wouldn't be a big problem if you don't do a lot of heavy writing.
[GENERAL] Mailing List Archives off of Web Site ...
I just fixed the script that creates the links for the mailing list archives off of each 'list' page (the 'mbox' link), so that it has the proper name... Any other problems, let us know... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] Connect from a new psql to an old 6.0 postmaster
Moin, I have two database servers. One running postgresql 6.0 and the new one running 6.5.1. If I run "psql -h oldone -d oldone -u" from the newer server and input username and password psql says: Connection to database 'oldone' failed. Failed to authenticate client as Postgres user 'olduser' using authentication scheme 131072. What does it mean and how can I fix this. Many thanks Matthias
Re: [GENERAL] server hardware recommendations (the archives aredead)
On Wed, 15 Dec 1999, Adam Rossi wrote: > I know this question has been asked before. I have seen it in the archives. > Unfortunately the archives are dead right now (any search will yield "no > results") and I need to make some decisions. > > Can anyone give some general recommendations on hardware for a server > running Linux (RH6 or 6.1) and PostgreSQL? I estimate that there will be > about 2 GIG of data stored in the database initially, but this could grow as > high as 5 GIG in the future. > > Since the db is not multithreaded, I assume that buying a dual processor > board and two processors would not be helpful to performance. Like any actually, if you are going to have concurrent connections to the backend, in some circumstances, PostgreSQL will handle multi-processors better then others...process one runs on CPU0, process two runs on CPU1, etc... > database server, a lot of ram will be required for fast operation. I was > thinking at a minimum 256 meg of ram. I also want to have the database run > on a RAID 5 array for speed and fault tolerance. Any suggestions here for > disk type, RAID scheme (software or hardware), controller type, etc.? Any my preference tends to be software raid...whatever I've ever seen as far as hardware raid is concerned has been quite slower then software raid...and this is with high-end servers... Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[GENERAL] server hardware recommendations (the archives are dead)
I know this question has been asked before. I have seen it in the archives. Unfortunately the archives are dead right now (any search will yield "no results") and I need to make some decisions. Can anyone give some general recommendations on hardware for a server running Linux (RH6 or 6.1) and PostgreSQL? I estimate that there will be about 2 GIG of data stored in the database initially, but this could grow as high as 5 GIG in the future. Since the db is not multithreaded, I assume that buying a dual processor board and two processors would not be helpful to performance. Like any database server, a lot of ram will be required for fast operation. I was thinking at a minimum 256 meg of ram. I also want to have the database run on a RAID 5 array for speed and fault tolerance. Any suggestions here for disk type, RAID scheme (software or hardware), controller type, etc.? Any rule of thumb on the "extra" disk space needed above raw storage space for PostgreSQL operations (temporary tables, vacuum issues, etc)? Any past experiences, benchmarks, guesses, or hearsay gladly accepted. Thanks for your help. - Adam --- Adam Rossi President, PlatinumSolutions, Inc. [EMAIL PROTECTED] http://www.platinumsolutions.com P.O. Box 31 Oakton, VA 22124 PH: 703.352.8576 FAX: 703.352.8577
[GENERAL] Postgres with PHP
Hello, I can´t get my local Postgres database work with PHP over TCP/IP (Browser) connection. PHP 3.12 is installed as Apache-module (Apache 1.3.9) Postgres version 6.5., both came with my SuSE 6.3 The query script is running without problems at my provider´s machine (apache 1.3.9, PHP 3.0.7 as CGI, Postgres 6.5). On my local SuSE-box, psql monitor works fine. Access per telnet from remote host (win98) is ok, there are no error messages or problems with queries. The problem occurs when connecting with $conn = pg_Connect("192.168.0.1", "5432", "username" ,"***", "name_db"); I get following error message: Unable to connect to PostgresSQL server: pgReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. Log message: Usage: /usr/lib/pgsql/bin/postgres [options] [dbname] -A on enable/disable assert checking [...] -v version set protocol version being used by frontend I searched all manuals and the archive, but I have no ideas. Thanks, Catharina *** hba.conf file entry: localall trust host all 127.0.0.1 255.255.255.255 trust host all 192.168.0.0 255.255.255.0 trust catha@server:~ > ps aux |grep pgsql postgres 1664 0.0 1.6 5180 1012 ? SDec05 0:00 /usr/lib/pgsql/bin/postmaster -i -o -F -D/var/lib/pgsql/data catha@server:~ > netstat -an |grep 5432 tcp0 0 0.0.0.0:54320.0.0.0:* LISTEN unix 0 [ ACC ] STREAM LISTENING 166 /tmp/.s.PGSQL.5432 catha@server:~ > ipcs -- Shared Memory Segments key shmid owner perms bytes nattchstatus 0x00280267 772 root 644 1048576 0 -- Semaphore Arrays key semid owner perms nsems status 0x00280269 514 root 666 14 -- Message Queues key msqid owner perms used-bytes messages -- Hempels Straßenmagazin im Internet: http://www.hempels-ev.de
Re: [GENERAL] Re: Installation problem on Suse linux 6.3
Karl Eichwalder wrote: > > Wim Ceulemans <[EMAIL PROTECTED]> writes: > > | I can't seem to install the latest postgresql opn Suse linux 6.3 (kernel > | 2.2.13, glibc 2.1.2). I always get the following message when I run > | ./configure : > | > | checking for c++... c++ > | checking wheather the C++ compiler (C++ ) works... no > | configure: error: installation or configuration problem': C++ compiler > | cannot create executables. > > Please check, whether "libgpp" from serie "d" is installed: > > rpm -q libgpp > > Of cource, you'll also need the package "gpp". > rpm -q lib gpp reports: libgpp-991012-3 And of course I had installed gpp and libgpp. I tried removing and re-installing them but this doesn't help. I can't find a libgpp under /lib or /usr/lib, I do find a libg++ library. Is this normal, or has the library another name? Regards Wim
[GENERAL] ORDER BY Problem
Hi see this : test=> select ch from test order by ch ; ch -- aa (1) bb cc -- cc --(2) dd -- vv -- www {www} (3) zz (9 rows) test=> My problem is I want the line (2) appears in first and the line (3) in last. I'm not found an answer to my problem in docs. So please help and explain to me how to configure correctly the sort. Thanks. I'm using the 6.5.3 version of Pg on RH 6.1. Thanks again -- Rodolphe QUIEDEVILLE ECILA : http://www.ecila.fr/ [EMAIL PROTECTED]