[ADMIN] Postgresql shared_buffer and SHMMAX configuration
Hi, Recently we upgraded our production server RAM from 7.5GB to 15GB for the following reasons: 1. to solve performance issues 2. the number of concurrent users increased from 150 to 300 Our production server is not a dedicated database server and is being shared with our other application softwares like: 1. Tomcat 2. Apache 3. Cron based scheduled programs 4. Also few Java-based thread programs all running in the same server. We are trying to configure postgresql parameters with 15GB RAM. Linux --- Kernal.SHMMAX From - 2147483648 To - 19818063053 250kB + 8.2kB * shared_buffers + 14.2kB * max_connections (250kB + 8.2kB * 2359296kB(2304*1024) + 14.2kB * 500) = 19353577.2 * 1024 = 19818063052.8 postgresql.conf shared_buffers From - 1536MB To - 2304MB Since we have other application also running we have taken 15% of the RAM value for shared_buffers. max_connection From - 500 To - 500 PGPOOL configuration - num_init_children From - 150 To - 420 child_max_connections From - 20 To - 30 We have more than 300 Users accessing our server concurrently so we need around 300 concurrent connections. My questions are: 1) Is my tuning of PostgreSQL parameters to 15 GB RAM configuration correct? Experts advice/recommendation on this are highly appreciated. 2) Will the above configuration improve the performance of the database overall? Thanks in advance Regards Gnanam -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] High Availability: Hot Standby vs. Warm Standby
Hi, we are contemplating the possibilities for a Postgres HA installation. As the rollout is targeted towards the end of the year, 9.0 and it's new features might be an option for us. Now from a HA point of view, what is the major difference between 9.0's Hot Standby and 8.x's Warm Standby? I am aware that I can use the 9.0 standby server for read only queries, but that is (currently) not something we need I'm wondering about the differences when the failover situation occurs. From reading the docs, I get the impression that 9.0's streaming replication might be faster than 8.4's WAL shipping, but otherwise offers the same level of data protection. Is there a difference in how much data could potentially be lost in case of a failover? E.g. because 9.0 replicates the changes quicker than 8.4? If there is no (big) difference in reliability (or potential data loss) I would rather go for 8.4 than 9.0 just because the feature is so new in 9.0 and might not be 100% reliable at the beginning. Any input is highly appreciated. Thanks in advance Thomas -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Monitoring PostgreSQL on Windows 2003
You can use select client_addr, current_query from pg_stat_activity; to show all db-connection and the running queries. Regards, murphy Von: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] Im Auftrag von Vítor Gesendet: Freitag, 2. Juli 2010 14:10 An: pgsql-admin@postgresql.org Betreff: [ADMIN] Monitoring PostgreSQL on Windows 2003 Hi! We have an application running .NET (C#) and Postgre on Windows 2003 with almost 1500 schemas which only 350 schemas are active daily (one schema, one customer). Lately, we've noticed that our server is getting slow. We also noticed loads of writes from postgresql even when less customers are using our software. My question is: What are the options to better monitor postgre on Windows? How can I see all the running sql commands on the server? Thanks a lot. Vitor
Re: [ADMIN] Postgresql shared_buffer and SHMMAX configuration
"Gnanakumar" wrote: > max_connection > From - 500 > To - 500 > > PGPOOL configuration > - > num_init_children > From - 150 > To - 420 > child_max_connections > From - 20 > To - 30 > We have more than 300 Users accessing our server concurrently so > we need around 300 concurrent connections. Not to the database itself, you don't; and it's probably killing performance for you to try to do that. You should use your connection pooler to funnel that many client-side connections down to a much smaller number of database connections. Your pgpool configuration doesn't seem to be doing that. > Recently we upgraded our production server RAM from 7.5GB to 15GB > Kernal.SHMMAX >From - 2147483648 >To - 19818063053 > 1) Is my tuning of PostgreSQL parameters to 15 GB RAM > configuration correct? No. For starters, you should not be configuring a shared memory maximum of over 18GB for your 15GB machine. More like (assuming your "given" settings): (250 kB) + (2304 MB * 1.025) + (14.2 kB * 500) = 2.48384348 * 10^9 bytes plus whatever shared memory you need for other processes and a little "slush". I might just go to something in the 4GB range, unless I know something else needs a lot. > 2) Will the above configuration improve the performance of the > database overall? I would expect these changes in the pgpool and PostgreSQL settings to actually make things worse, although the extra caching from the additional RAM may counteract that to some degree. Before I could venture a suggestion on what good settings might be, I would need to know more about the database server hardware. How many CPU cores? How many disks in what arrangement? -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Large files in main/base
Hello everyone, We had a problem with one of our servers and had noticed that the postgres/8.3/main folder had become quite large (>650mb). PostgreSQL confirmed this via a query but when I queried the size of the tables I was barely reaching 3mb. After looking around, I found one file with about 580 mb. From the contents I have the feeling it is some kind of changelog or similar. My Questions: What is it? How can I reduce it? How can I prevent it from reaching that size again? Thanks Frank DISCLAIMER: Unless indicated otherwise, the information contained in this message is privileged and confidential, and is intended only for the use of the addressee(s) named above and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this message and/or attachments is strictly prohibited. The company accepts no liability for any damage caused by any virus transmitted by this email. Furthermore, the company does not warrant a proper and complete transmission of this information, nor does it accept liability for any delays. If you have received this message in error, please contact the sender and delete the message. Thank you. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Postgresql shared_buffer and SHMMAX configuration.
Hi, Recently we upgraded our production server from 7.5GB RAM to 15GB RAM. 1. To solve performance issue. 2. Also the number of concurrent users increased from 150 to 300. Our production server is not dedicated server. We have also 1. Tomcat 2. Apache 3. Cron based scheduled programs 4. Also some thread based programs running in the same server. We are trying to configure postgresql parameters according to 15GB RAM. Linux --- Kernal.SHMMAX From - 2147483648 To - 19818063053 250kB + 8.2kB * shared_buffers + 14.2kB * max_connections (250kB + 8.2kB * 2359296kB(2304*1024) + 14.2kB * 500) = 19353577.2 * 1024 = 19818063052.8 postgresql.conf shared_buffers From - 1536MB To - 2304MB Since we have other application also running we have taken 15% of the RAM value for shared_buffers. max_connection From - 500 To - 500 PGPOOL configuration - num_init_children From - 150 To - 420 child_max_connections From - 20 To - 30 We have more than 300 peoples accessing our server concurrently so we need around 300 connections concurrently. Is this correct? Will the above configuration improve the performance? Thanks in advance Regards Ezhil -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Large files in main/base
On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank wrote: > Hello everyone, > > We had a problem with one of our servers and had noticed that the > postgres/8.3/main folder had become quite large (>650mb). > PostgreSQL confirmed this via a query but when I queried the size of the > tables I was barely reaching 3mb. > > After looking around, I found one file with about 580 mb. From the > contents I have the feeling it is some kind of changelog or similar. And it is? So close to telling us. > > My Questions: > What is it? Why don't you tell us? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Large files in main/base
Scott Marlowe writes: > On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank wrote: >> What is it? > Why don't you tell us? The exact name of the file might be useful information, too. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
On Fri, Jul 9, 2010 at 4:51 AM, Thomas Kellerer wrote: > Hi, > > we are contemplating the possibilities for a Postgres HA installation. > > As the rollout is targeted towards the end of the year, 9.0 and it's new > features might be an option for us. > > Now from a HA point of view, what is the major difference between 9.0's Hot > Standby and 8.x's Warm Standby? You can run queries on the standby... > > I am aware that I can use the 9.0 standby server for read only queries, but > that is (currently) not something we need > Taking SQL backups without impacting the master might be something to consider. > I'm wondering about the differences when the failover situation occurs. From > reading the docs, I get the impression that 9.0's streaming replication > might be faster than 8.4's WAL shipping, but otherwise offers the same level > of data protection. > > Is there a difference in how much data could potentially be lost in case of > a failover? 9.0 has streaming replication so much less data would likely be lost. WAL logs are generally 16 MB and often shipped when completed. > E.g. because 9.0 replicates the changes quicker than 8.4? > > If there is no (big) difference in reliability (or potential data loss) I > would rather go for 8.4 than 9.0 just because the feature is so new in 9.0 > and might not be 100% reliable at the beginning. > > Any input is highly appreciated. > > Thanks in advance > Thomas > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Hi, Rob Wultsch wrote on 09.07.2010 18:14: I am aware that I can use the 9.0 standby server for read only queries, but that is (currently) not something we need Taking SQL backups without impacting the master might be something to consider. Interesting point. Thanks for mentioning that. I'm wondering about the differences when the failover situation occurs. From reading the docs, I get the impression that 9.0's streaming replication might be faster than 8.4's WAL shipping, but otherwise offers the same level of data protection. Is there a difference in how much data could potentially be lost in case of a failover? 9.0 has streaming replication so much less data would likely be lost. WAL logs are generally 16 MB and often shipped when completed. So my assumption is correct that streaming replication does mean that in case of a failover less transactions are lost? Regards Thomas -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Thomas Kellerer wrote: > So my assumption is correct that streaming replication does mean > that in case of a failover less transactions are lost? Yes, that is correct. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
On Fri, 2010-07-09 at 18:31 +0200, Thomas Kellerer wrote: > Hi, > > Rob Wultsch wrote on 09.07.2010 18:14: > >> I am aware that I can use the 9.0 standby server for read only queries, but > >> that is (currently) not something we need > >> > > > > Taking SQL backups without impacting the master might be something to > > consider. > > Interesting point. Thanks for mentioning that. There is an issue with running your backup from a standby that you need to be aware of. There is potential that a long running query on the standby can conflict with the application of wal records. In this case, you have a choice to either terminate the query and let wal records continue to be applied, or delay the application of the wal until the query completes. Considering that you are looking at HA and asking about the difference in lost transactions between streaming replication and 8.4 PITR, I doubt that letting the standby lag for the duration of the pg_dump is going to be something that interests you. Full details are here: http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
Since the topic is very relevant to me right now I would like to ask if anyone is running a HA solution in the cloud? Thanks, Kasia -Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Brad Nicholson Sent: Friday, July 09, 2010 10:19 AM To: Thomas Kellerer Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby On Fri, 2010-07-09 at 18:31 +0200, Thomas Kellerer wrote: > Hi, > > Rob Wultsch wrote on 09.07.2010 18:14: > >> I am aware that I can use the 9.0 standby server for read only queries, but > >> that is (currently) not something we need > >> > > > > Taking SQL backups without impacting the master might be something to > > consider. > > Interesting point. Thanks for mentioning that. There is an issue with running your backup from a standby that you need to be aware of. There is potential that a long running query on the standby can conflict with the application of wal records. In this case, you have a choice to either terminate the query and let wal records continue to be applied, or delay the application of the wal until the query completes. Considering that you are looking at HA and asking about the difference in lost transactions between streaming replication and 8.4 PITR, I doubt that letting the standby lag for the duration of the pg_dump is going to be something that interests you. Full details are here: http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PgsqlODBC 8.1 and 8.4 side-by-side installation
On Wed, Jul 7, 2010 at 9:49 AM, Rob Richardson wrote: > > I wanted to try using the ODBC driver for PostgreSQL 8.4. But when I > installed it, I was surprised and dismayed to see that the old driver no > longer appeared in the list of available drivers when I tried to create a > new DSN. At first, I thought that the new driver was working, but any > update query gave me a "multiple-step operation raised errors. See error > list" error. > is this a 64bit machine? if so, you will need to run the 32bit odbc manager in order to find the pgodbc driver. > > Is it possible to have the 8.1 and 8.4 ODBC drivers available at the same > time? If so, how do I install the 8.4 driver so that the 8.1 driver is > still available? > > Thanks very much! > > RobR > -- David Bear College of Public Programs at ASU 602-494-0424
Re: [ADMIN] Large files in main/base
On Fri, 2010-07-09 at 15:31 +0200, Henry, Frank wrote: > Hello everyone, > > We had a problem with one of our servers and had noticed that the > postgres/8.3/main folder had become quite large (>650mb). > PostgreSQL confirmed this via a query but when I queried the size of the > tables I was barely reaching 3mb. > > After looking around, I found one file with about 580 mb. From the > contents I have the feeling it is some kind of changelog or similar. Name of file? Version of PostgreSQL? JD -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Large files in main/base
On Fri, Jul 9, 2010 at 7:31 AM, Henry, Frank wrote: > Hello everyone, > > We had a problem with one of our servers and had noticed that the > postgres/8.3/main folder had become quite large (>650mb). > PostgreSQL confirmed this via a query but when I queried the size of the > tables I was barely reaching 3mb. > > After looking around, I found one file with about 580 mb. From the > contents I have the feeling it is some kind of changelog or similar. pg_clog? If that's growing you've got a possible problem with a long running query. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
On Fri, 2010-07-09 at 10:25 -0700, Kasia Tuszynska wrote: > Since the topic is very relevant to me right now I would like to ask if > anyone is running a HA solution in the cloud? While running databases in a cloud does come with it's own set of issues/challenges, I don't see what would be materially different about implementation of (most) HA solutions for Postgres in the cloud vs standalone servers. If you have specific questions or concerns, perhaps we could be of assistance. > > -Original Message- > From: pgsql-admin-ow...@postgresql.org > [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Brad Nicholson > Sent: Friday, July 09, 2010 10:19 AM > To: Thomas Kellerer > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby > > On Fri, 2010-07-09 at 18:31 +0200, Thomas Kellerer wrote: > > Hi, > > > > Rob Wultsch wrote on 09.07.2010 18:14: > > >> I am aware that I can use the 9.0 standby server for read only queries, > > >> but > > >> that is (currently) not something we need > > >> > > > > > > Taking SQL backups without impacting the master might be something to > > > consider. > > > > Interesting point. Thanks for mentioning that. > > There is an issue with running your backup from a standby that you need > to be aware of. > > There is potential that a long running query on the standby can conflict > with the application of wal records. In this case, you have a choice to > either terminate the query and let wal records continue to be applied, > or delay the application of the wal until the query completes. > > Considering that you are looking at HA and asking about the difference > in lost transactions between streaming replication and 8.4 PITR, I doubt > that letting the standby lag for the duration of the pg_dump is going to > be something that interests you. > > Full details are here: > http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT > > -- > Brad Nicholson 416-673-4106 > Database Administrator, Afilias Canada Corp. > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
