Обсуждение: High-end PG database configuration help
Hello,
I am working to configure high end PostgreSQL database, and need some help.
Please can you suggest me best and optimal configuration for “postgresql.conf” I should use.
DB server details:
· PostgreSQL Version: 9.5.3
· Disc: RAID 10 (1 TB)
· DB Size: 250GB
· RAM: 256GB
· CPU(s): 48
· Max Connection: 2000
· OS: CentOS release 6.6 (Final)
Many thanks in advanced.
Best Regards,
Amar
Confidentiality Notice
This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure.
If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message.
I would appreciate, if anyone provide me some guidance
Best,
Amar
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gupta, Amar Nath
Sent: 20 July 2016 11:45
To: pgsql-admin@postgresql.org
Subject: [ADMIN] High-end PG database configuration help
Hello,
I am working to configure high end PostgreSQL database, and need some help.
Please can you suggest me best and optimal configuration for “postgresql.conf” I should use.
DB server details:
· PostgreSQL Version: 9.5.3
· Disc: RAID 10 (1 TB)
· DB Size: 250GB
· RAM: 256GB
· CPU(s): 48
· Max Connection: 2000
· OS: CentOS release 6.6 (Final)
Many thanks in advanced.
Best Regards,
Amar
Confidentiality Notice
This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure.
If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message.
Confidentiality Notice
This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure.
If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message.
-------- Original message --------
From: "Gupta, Amar Nath" <angupta@corbus.com>
Date: 07/21/2016 4:03 PM (GMT-06:00)
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] High-end PG database configuration help
I would appreciate, if anyone provide me some guidance
Best,
Amar
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Gupta, Amar Nath
Sent: 20 July 2016 11:45
To: pgsql-admin@postgresql.org
Subject: [ADMIN] High-end PG database configuration help
Hello,
I am working to configure high end PostgreSQL database, and need some help.
Please can you suggest me best and optimal configuration for “postgresql.conf” I should use.
DB server details:
· PostgreSQL Version: 9.5.3
· Disc: RAID 10 (1 TB)
· DB Size: 250GB
· RAM: 256GB
· CPU(s): 48
· Max Connection: 2000
· OS: CentOS release 6.6 (Final)
Many thanks in advanced.
Best Regards,
Amar
Confidentiality Notice
This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure.
If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message.
Confidentiality Notice
This message is intended exclusively for the individual or entity to which it is addressed. This communication may contain information that is proprietary, privileged, confidential or otherwise legally exempt from disclosure.
If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724) or reply to this e-mail and delete all copies of this message.
Austin, TX 78757
www.journyx.com
I would appreciate, if anyone provide me some guidance
On Wed, Jul 20, 2016 at 4:45 AM, Gupta, Amar Nath <angupta@corbus.com> wrote: > Hello, > > I am working to configure high end PostgreSQL database, and need some help. > > Please can you suggest me best and optimal configuration for > “postgresql.conf” I should use. > > DB server details: > > · PostgreSQL Version: 9.5.3 > > · Disc: RAID 10 (1 TB) > > · DB Size: 250GB > > · RAM: 256GB > > · CPU(s): 48 > > · Max Connection: 2000 > > · OS: CentOS release 6.6 (Final) A few quick pointers. 1: describe what your workload looks like. How you configure a server can vary quite a bit depending on what it's doing. 2: If that RAID-10 is spinning disks pull them, throw them in the trash, and buy some SSDs. A pair of 800GB Intels costs $1000 total, and you don't even need the RAID controller with them to be fast. 3: Pick a distro that can run the 3.11 or higher kernels. IO is MUCH MUCH faster in the later model kernels than in the older 2.6.32 kernel Centos 6 uses. Ubuntu 12.04 can easily run a 3.11 or 3.13 kernel from the standard repos. Not sure about Centos 6, but I'm pretty sure it takes more than a simple rpm command to get a later model kernel into it. 4: Look into connection pooling. NO database is gonna be fast if it has 2,000 connections all active at once. I've got 80 core machines with 3 super fast 1TB SSD cards in them that would fall over under the load of 2,000 active connections. 5: Don't go crazy on shared_buffers. A few gig is usually plenty, let the OS do the majority of the heavy lifting when it comes to caching data. There's more to discuss, but we don't know what you're trying to do yet, so I'll wait to hear back from you (Please keep it on the list so others can benefit)
Many thanks for your reply Scott, Point#1: we have two DB servers (web app and reporting database). Web app does simple read and insert query + some complexqueries. And reporting DB is used for heave queries Point#2: will do in next update ;) Point#3: I'll check for that. Point#4: Agreed, opening 2000 connection is not a good idea. Somehow my application is setup in this way I cannot use pgpool,but working on it in long term. Point#5: I have setup 32GB for that. Server current configuration is: max_connections = 2000 shared_buffers = 32GB work_mem = 128MB synchronous_commit = off effective_cache_size = 192GB rest settings are on default Regards, Amar -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: 21 July 2016 22:36 To: Gupta, Amar Nath <angupta@corbus.com> Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] High-end PG database configuration help On Wed, Jul 20, 2016 at 4:45 AM, Gupta, Amar Nath <angupta@corbus.com> wrote: > Hello, > > I am working to configure high end PostgreSQL database, and need some help. > > Please can you suggest me best and optimal configuration for > “postgresql.conf” I should use. > > DB server details: > > · PostgreSQL Version: 9.5.3 > > · Disc: RAID 10 (1 TB) > > · DB Size: 250GB > > · RAM: 256GB > > · CPU(s): 48 > > · Max Connection: 2000 > > · OS: CentOS release 6.6 (Final) A few quick pointers. 1: describe what your workload looks like. How you configure a server can vary quite a bit depending on what it's doing. 2: If that RAID-10 is spinning disks pull them, throw them in the trash, and buy some SSDs. A pair of 800GB Intels costs$1000 total, and you don't even need the RAID controller with them to be fast. 3: Pick a distro that can run the 3.11 or higher kernels. IO is MUCH MUCH faster in the later model kernels than in the older2.6.32 kernel Centos 6 uses. Ubuntu 12.04 can easily run a 3.11 or 3.13 kernel from the standard repos. Not sure aboutCentos 6, but I'm pretty sure it takes more than a simple rpm command to get a later model kernel into it. 4: Look into connection pooling. NO database is gonna be fast if it has 2,000 connections all active at once. I've got 80core machines with 3 super fast 1TB SSD cards in them that would fall over under the load of 2,000 active connections. 5: Don't go crazy on shared_buffers. A few gig is usually plenty, let the OS do the majority of the heavy lifting when itcomes to caching data. There's more to discuss, but we don't know what you're trying to do yet, so I'll wait to hear back from you (Please keepit on the list so others can benefit) Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed. This communication may containinformation that is proprietary, privileged, confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or anypart of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724)or reply to this e-mail and delete all copies of this message.
On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@corbus.com> wrote: > Many thanks for your reply Scott, > > Point#1: we have two DB servers (web app and reporting database). Web app does simple read and insert query + some complexqueries. And reporting DB is used for heave queries > Point#2: will do in next update ;) > Point#3: I'll check for that. > Point#4: Agreed, opening 2000 connection is not a good idea. Somehow my application is setup in this way I cannot use pgpool,but working on it in long term. > Point#5: I have setup 32GB for that. > > Server current configuration is: > max_connections = 2000 > shared_buffers = 32GB > work_mem = 128MB > synchronous_commit = off > effective_cache_size = 192GB > > rest settings are on default Which of your two dbs has 2,000 connections and needs tuning, the reporting db or the web db? Two points: 1: Rarely if ever is 32GB needed for shared_buffers. There's a cost to have more shared_buffers and it's pretty rare that anything over 1 or 2GB is needed. If you have a performance test showing you need more than a few G then yes, go for it. 2: You've got to get max_connections down to something reasonable. Look at pgbouncer. It's super easy to setup and will allow you to drop the # connections to 100 or so even for busy dbs with lots of users etc.
> On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@corbus.com> > wrote: >> Many thanks for your reply Scott, >> >> Point#1: we have two DB servers (web app and reporting database). Web >> app does simple read and insert query + some complex queries. And >> reporting DB is used for heave queries >> Point#2: will do in next update ;) >> Point#3: I'll check for that. >> Point#4: Agreed, opening 2000 connection is not a good idea. Somehow my >> application is setup in this way I cannot use pgpool, but working on it >> in long term. >> Point#5: I have setup 32GB for that. >> >> Server current configuration is: >> max_connections = 2000 >> shared_buffers = 32GB >> work_mem = 128MB >> synchronous_commit = off >> effective_cache_size = 192GB >> >> rest settings are on default > > Which of your two dbs has 2,000 connections and needs tuning, the > reporting db or the web db? > > Two points: > > 1: Rarely if ever is 32GB needed for shared_buffers. There's a cost to > have more shared_buffers and it's pretty rare that anything over 1 or > 2GB is needed. If you have a performance test showing you need more > than a few G then yes, go for it. > > 2: You've got to get max_connections down to something reasonable. > Look at pgbouncer. It's super easy to setup and will allow you to drop > the # connections to 100 or so even for busy dbs with lots of users > etc. > > Maybe may use pg_tunning in you server, following your recomendation. Other Case, Use pgbounce for those conection. -- Saludos, Gilberto Castillo ETECSA, La Habana, Cuba
Thanks Scott, Which of your two dbs has 2,000 connections and needs tuning, the reporting db or the web db? Amar>> Web DB has 2000 connection. But would be great if you can also suggest tuning for reporting DB. 1: Rarely if ever is 32GB needed for shared_buffers. There's a cost to have more shared_buffers and it's pretty rare thatanything over 1 or 2GB is needed. If you have a performance test showing you need more than a few G then yes, go forit. Amar>> reduced it to 16GB. Honestly speaking I haven’t noticed any difference in DB performance since I reduced memory from64G to 32GB to 16GB. 2: You've got to get max_connections down to something reasonable. Look at pgbouncer. It's super easy to setup and will allowyou to drop the # connections to 100 or so even for busy dbs with lots of users etc. Amar>> will look into this. Regards, Amar -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: 22 July 2016 17:13 To: Gupta, Amar Nath <angupta@corbus.com> Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] High-end PG database configuration help On Fri, Jul 22, 2016 at 1:57 AM, Gupta, Amar Nath <angupta@corbus.com> wrote: > Many thanks for your reply Scott, > > Point#1: we have two DB servers (web app and reporting database). Web > app does simple read and insert query + some complex queries. And > reporting DB is used for heave queries > Point#2: will do in next update ;) > Point#3: I'll check for that. > Point#4: Agreed, opening 2000 connection is not a good idea. Somehow my application is setup in this way I cannot use pgpool,but working on it in long term. > Point#5: I have setup 32GB for that. > > Server current configuration is: > max_connections = 2000 > shared_buffers = 32GB > work_mem = 128MB > synchronous_commit = off > effective_cache_size = 192GB > > rest settings are on default Which of your two dbs has 2,000 connections and needs tuning, the reporting db or the web db? Two points: 1: Rarely if ever is 32GB needed for shared_buffers. There's a cost to have more shared_buffers and it's pretty rare thatanything over 1 or 2GB is needed. If you have a performance test showing you need more than a few G then yes, go forit. 2: You've got to get max_connections down to something reasonable. Look at pgbouncer. It's super easy to setup and will allow you to drop the # connections to 100 or so even for busy dbs withlots of users etc. Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed. This communication may containinformation that is proprietary, privileged, confidential or otherwise legally exempt from disclosure. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or anypart of it. If you have received this message in error, please notify the sender immediately either by phone (937-226-7724)or reply to this e-mail and delete all copies of this message.