[ADMIN] Libpq++.so
Hi, Does anyone know how to get the libpq++.so library added to a current build? One of the developers wants to know where to find this library. Is there a way to download or get that? We just have libpq.so, which is the C library. Thanks, ~Dan Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] version 8.0.3
Hi all, How do I figure out what's different in version 8.0.3 and how do I upgrade on linux from v8.0.1 to v8.0.3? Thanks, ~DjK ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] version 8.0.3
I installed version 8.0.1 in feb/2005. There's no difference in the changelog between that version and 8.0.3. -Original Message- From: tom [mailto:[EMAIL PROTECTED] Sent: Friday, May 13, 2005 2:26 PM To: Kavan, Dan (IMS) Subject: Re: [ADMIN] version 8.0.3 Kavan, Dan (IMS) wrote: Hi all, How do I figure out what's different in version 8.0.3 and how do I upgrade on linux from v8.0.1 to v8.0.3? Thanks, ~DjK Usually you read the changelog file in the source. There are plenty of instructions on how to upgrade in the documentation section of the postgresql site, always remember to make a backup of your databases first. (usually pgdump_all filename). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] memory allocation ; postgresql-8.0
Hi Scott, Thanks again for all your tips. If I knock the buffer size down to 65,536 (still higher than what you are recommending) then my shmmax becomes: 256,000 + 550,292,685 (65536*8396.8) + 1,454,100 = 552,002,785 That will leave me with 3.5 GB of free memory for the system work memory to use. Will those free system resources ever get used with a 10 million record, 10 GB database? If I go with 65,536 as my buffer size, Would having the SHMMAX set to 1 GB on my sysctl.conf system parameters allow me to run two seperate instances of postgresql on 2 seperate ports? ~DjK -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, May 06, 2005 3:31 PM To: Kavan, Dan (IMS) Subject: RE: [ADMIN] memory allocation ; postgresql-8.0 On Fri, 2005-05-06 at 14:12, Kavan, Dan (IMS) wrote: ScottUnless you routinely actually handle data sets that are 1.9 gigabytes in size, it's probably not a great idea. DjI knew I wouldn't be that easy. ;) We have a database going on here(in a couple weeks) that will handle 10 million records. I'm not sure how to measure if that means I will or won't have 1.9 GB in datasets. Right now, most of my memory shows up as free (top) and I have 4GB of swap, virtual memory, that hasn't been tapped, yet. I put a copy of what I have in sysctl.conf and postgresql.conf below ( the memory section ) Well, the only way you'll really know is when you have 10,000,000 records and start working with them. You might want to create a test data set and see how it runs on your setup with varying amounts of buffers allocated to postgresql. I'd bet that in most circumstances, you'll find 10,000 buffers, at most 20,000 buffers working best for you. ScottPostgreSQL doesn't really cache data in the classical sense. All the data stored in its internal buffers is tossed away when the last backend referencing said material stops referencing it. DjThat's the reason why I leave the remaining 2GB of memory to the system. But, keep in mind, that reduces the amount of space the kernel now has to play with, as well as the memory left for sorts (sort_mem in 8.0, working_mem in 8.0). It's all about tradeoffs. 1.9gig for pgsql is usually a bit much, but not always. ScottPlus, there's a fair bit of overhead to managing such a large data set, and, until 8.0, the algorithms for doing so with efficiency weren't a part of PostgreSQL. DjBesides full vacuuming and backups what kind of maintenance is involved in managing a database with 10 million records? That's not the ovrehead I'm talking about. I mean the CPU overhead from maintaining that large list of buffers and searching them each time you access a buffer. Again, 8.0 is better at it than 8.0 ScottWhile some future version might incorporate genuine caching that could utilize all that memory, for now, one is still better off giving postgresql about 250 megabytes max and letting the kernel use the rest for caching. DjDoes that logic scale to a 10 GB database with 10 million records and 2 other small databases all with 10-20 simultaneous users doing queries? It does for 7.4 and before, which really didn't handle large internal buffers all that efficiently. 8.0 is an unknown to me in that area. Dj #my startup script includes sysctl -w vm.overcommit_memory=2 Keep in mind that according to recent postings I've seen in the kernel mailing list and the pgsql mailing lists, the vm.overcommit settings are sometimes ignored, and the oom killer still stalks the night and kills processes that are memory hogs. IF postgresql is set to use 1.9 gig shared memory, it's likely to be the first victim should the oom killer come out to play. shared_buffers = 23 # min 16, at least max_connections*2, 8KB each was 65536 work_mem = 1024 # min 64, size in KB I'd increase our work mem to 16384 or so. Depends on how many parallel clients you're likely to have. You want to allow sorts to happen in memory when possible, but you don't want to starve the other processes for memory or you'll get a swap storm. max_fsm_pages = 3 # min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1500 # min 100, ~50 bytes each You'll likely want these larger too. With a hard working large database, 100,000 / 1 are not unusual settings for the fsm settings. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] memory allocation ; postgresql-8.0
Do psql calls/procedures access resources reserved from the kernel.shmmax? How about the tar or copy sysadmin commands? I would guess they don't use kernel.shmmax resources. Finally, work memory alos does not access resources reserved from kernel.shmmax, correct? Thanks for clearing things up. -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Thursday, May 12, 2005 11:21 AM To: Kavan, Dan (IMS) Cc: postgres Subject: RE: [ADMIN] memory allocation ; postgresql-8.0 On Thu, 2005-05-12 at 10:10, Kavan, Dan (IMS) wrote: Hi Scott, Thanks again for all your tips. If I knock the buffer size down to 65,536 (still higher than what you are recommending) then my shmmax becomes: 256,000 + 550,292,685 (65536*8396.8) + 1,454,100 = 552,002,785 That will leave me with 3.5 GB of free memory for the system work memory to use. Will those free system resources ever get used with a 10 million record, 10 GB database? Certainly. As you access the data the kernel will cache all the data sent through it. Once the machine's been up and processing for a while you should see a top output that shows free memory at a few megs (8 to 30 meg is typical) and all the rest of the memory being used as kernel cache. If I go with 65,536 as my buffer size, Would having the SHMMAX set to 1 GB on my sysctl.conf system parameters allow me to run two seperate instances of postgresql on 2 seperate ports? Yes, but you may want to set it just a tad higher for things like fsm and whatnot. Definitely benchmark both the 64k setting of shared_buffers and lower settings, looking for a knee with your data set. It may well be that the best performance happens at a lower number, and doesn't really increase as you bump up the shared_buffers. Be sure to test things as realistically as possible, i.e. the right amount of parallel users and all that. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] resource allocation ; postgresql-8.0
Scott, If you had two applications that were each connecting to two different databases, have you found that it's beneficial to have to different instances of postgres running? I have a few reasons why I think it would be better. One, it would be beneficial to customize parameters for each application. They would be different sizes for example and they may each have different amounts of people connecting to them. Two, from a networking perspective, it may be faster ( 2 different ports rather than one ) Do you agree with those reasons or can you think of other reasons it would be wise? It seems like it would be more administrative work to have 2 different instances, so there may be some cons to that idea as well. ~DjK ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] benchmarks with pgbench
Hi Guys, I''ve been running pgbench tests for a while. I have one server set up to run pgbench tpc tests (7.x). A new server that I just configured with SUSE and 8.0.0 just gets killed even though it has the same memory 8 GB and it's a x86-64 box. The other one is Solaris full 64-bit. That seems reasonable, but we thought postgres would run better on a linux box than solaris. Also, the x86-64 box does much worse than a 32-bit linux box with mandrake and a lot less RAM. I've restarted postgres with many different postgresql.conf configs and nothing seems to make much of a difference to pgbench. Has anyone else experienced slower performance on 64-bit linux as compared to 32-bit linux? D.J. ---(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: [ADMIN] benchmarks with pgbench
Yes, they are both running on the same hardware - NAS. On Mon, 2005-01-24 at 10:48, Kavan, Dan (IMS) wrote: Hi Guys, I''ve been running pgbench tests for a while. I have one server set up to run pgbench tpc tests (7.x). A new server that I just configured with SUSE and 8.0.0 just gets killed even though it has the same memory 8 GB and it's a x86-64 box. The other one is Solaris full 64-bit. That seems reasonable, but we thought postgres would run better on a linux box than solaris. Also, the x86-64 box does much worse than a 32-bit linux box with mandrake and a lot less RAM. I've restarted postgres with many different postgresql.conf configs and nothing seems to make much of a difference to pgbench. Has anyone else experienced slower performance on 64-bit linux as compared to 32-bit linux? pgbench is notorious for providing poor measure of a database's performance under real world load. Are you sure your Solaris and Linux boxes are both running on SCSI hard drives (IDE drives are well known for not obeying fsync() calls, but simply saying yep, synced that data when in fact they haven't. So, if you Linux box is set to both fsync properly AND is writing access time to each file, it may be quite a bit slower than a Solaris box if that box is writing to IDE drives, has fsync turned off, and / or has access time writing disabled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] benchmarks with pgbench
I hate to admit this publically, but I've been reading my results backwards. I was getting 100 tps on Solaris - postgres 64 bit and 300 tps on SUSE postgres both x86-64. So, 300 is better than 100 right? I was reading it backwards. I was thinking 300 was the actual speed to process a certain amount of transactions, but actually the x86-64 system is performing better than all, not worse. ~Dj Yes, they are both running on the same hardware - NAS. On Mon, 2005-01-24 at 10:48, Kavan, Dan (IMS) wrote: Hi Guys, I''ve been running pgbench tests for a while. I have one server set up to run pgbench tpc tests (7.x). A new server that I just configured with SUSE and 8.0.0 just gets killed even though it has the same memory 8 GB and it's a x86-64 box. The other one is Solaris full 64-bit. That seems reasonable, but we thought postgres would run better on a linux box than solaris. Also, the x86-64 box does much worse than a 32-bit linux box with mandrake and a lot less RAM. I've restarted postgres with many different postgresql.conf configs and nothing seems to make much of a difference to pgbench. Has anyone else experienced slower performance on 64-bit linux as compared to 32-bit linux? pgbench is notorious for providing poor measure of a database's performance under real world load. Are you sure your Solaris and Linux boxes are both running on SCSI hard drives (IDE drives are well known for not obeying fsync() calls, but simply saying yep, synced that data when in fact they haven't. So, if you Linux box is set to both fsync properly AND is writing access time to each file, it may be quite a bit slower than a Solaris box if that box is writing to IDE drives, has fsync turned off, and / or has access time writing disabled. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] PAM ldap
Thanks for the reply, I did compile --with-pam. Although, the $PATH for the postgres user - who I used to compile with didn't have /lib and /lib64 in it's path. I don't see anything is configure.in or config.log to hint that pam isn't configured, but I'll re-configure anyway. Is there a way to check PAM is configured with postgresql? pam_unix2.so is located in /lib(64)/security. I was wondering if both /lib and /lib/security needed to be in the $PATH or if just /lib/security was needed. Also, forget about PAM for a minute. Why does ident work locally, but the host entry not work as easily?ident sameuser in host doesn't work for me. When I think about it though it makes sense. I'm coming in on pgadmin iii from a windows machine and a user logged into a windows domain. So, no wonder, it doesn't map right. It doesn't have any smith user logged in at the time. I've tried other combinations like a map name, user ident, pg user, but it doesn't work. ie TEST smith smith. And then TEST smith smith in the pg_ident.conf file. I really don't think postgresql is talking to our LDAP server. The only thing it can do is local (using the unix ldap setup). Thanks for all your insight, ~DjK -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dick Davies Sent: Sunday, January 16, 2005 4:11 AM To: PostgreSQL Admin Subject: Re: [ADMIN] PAM ldap * Kavan, Dan (IMS) [EMAIL PROTECTED] [0149 18:49]: Hi, I'm running postgresql 8.0.rc5 on SUSE. I have the pg_hba.conf file configured with local all smith ident sameuser host all smith ident sameuser The way authentication works with that is that configuration is that if I'm logged in as smith with my company ldap server I can get in, but if I'm not directly logged in as smith, I can't get in. Having the word pam in this file at all causes an error. I'd like to use pam so postgres could do it's own ldap/pam lookups, but I keep getting an error that it doesn't know what pam is. I see in the logs that the pam server starts, but I still get an error. You didn't show the broken config, but assuming it's something like # TYPE DATABASEUSERIP-ADDRESS IP-MASK METHOD hostsslall all 127.0.0.1 255.255.255.255 pam then perhaps you don't have pam support built into postgres? /etc/pam.d/postgresql authrequiredpam_unix2.sonullok account requiredpam_unix2.so This is going to do unix auth, obviously, so you'll need to s/unix/ldap/ on that... -- 'You may need to metaphorically make a deal with the devil. By 'devil' I mean robot devil and by 'metaphorically' I mean get your coat.' -- Bender Rasputin :: Jack of All Trades - Master of Nuns ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] PAM ldap
Hi, I'm running postgresql 8.0.rc5 on SUSE. I have the pg_hba.conf file configured with local all smith ident sameuser hostall smith ident sameuser The way authentication works with that is that configuration is that if I'm logged in as smith with my company ldap server I can get in, but if I'm not directly logged in as smith, I can't get in. Having the word pam in this file at all causes an error. I'd like to use pam so postgres could do it's own ldap/pam lookups, but I keep getting an error that it doesn't know what pam is. I see in the logs that the pam server starts, but I still get an error. With pam listed I get the red X. An error has occured. Error connecting to the server: FATAL: missing or erroneous pg_hba.conf HINT: See server log for details. See server log details below. What's wrong with syntax of my pg_hba.conf file? I've tried pam in all caps, adding postgresql after pam and adding pg_hba.conf after pam in pg_hba.conf, none of which helps. Thanks for any tips. It's harder to play once these systems hit production. serverlog LOG: database system was shut down at 2005-01-14 13:34:47 EST LOG: checkpoint record is at 0/AEA370 LOG: redo record is at 0/AEA370; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 628; next OID: 17232 LOG: database system is ready LOG: invalid entry in file /sqldata/Linux.pgsql/tarpon5432/pg_hba.conf at line 66, token pam FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. /var/log/messages Jan 14 13:37:23 tarpon su: (to root) postgres on /dev/pts/0 Jan 14 13:37:23 tarpon su: pam_unix2: session started for user root, service su /etc/pam.d/postgresql authrequiredpam_unix2.sonullok account requiredpam_unix2.so D.J. Kavan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
