Optimal configuration for server
Hi everybody! I have a big application running on premise. One of my main database servers has the following configuration: 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 1TB of ram or 786GB (5 servers at all) A huge storage( I don't know for sure what kind is, but is very powerful) A consulting company recommended the following configuration for theses main servers(let me know if something important was left behind): maxx_connections = 2000 shared_buffers = 32GB temp_buffers = 1024 max_prepared_transactions = 3000 work_men = 32MB effective_io_concurrency = 200 max_worker_processes = 24 checkpoint_timeout = 15min max_wal_size = 64GB min_wall_size = 2GB effective_cache_size = 96GB (...) I Think this is too low memory setting for de size of server... The number of connections, I'm still measuring to reduce this value( I think it's too high for the needs of application, but untill hit a value too high to justfy any memory issue, I think is not a problem) My current problem: under heavyload, i'm getting "connection closed" on the application level(java-jdbc, jboss ds) The server never spikes more the 200GB of used ram(that's why I thing the configuration is too low) This is the output of free command: [image: image.png] Thanks in advance! Felipph
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 08:54, Luiz Felipph escreveu: > Hi everybody! > > I have a big application running on premise. One of my main database > servers has the following configuration: > > 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 > 1TB of ram or 786GB (5 servers at all) > A huge storage( I don't know for sure what kind is, but is very powerful) > > A consulting company recommended the following configuration for theses > main servers(let me know if something important was left behind): > > maxx_connections = 2000 > shared_buffers = 32GB > temp_buffers = 1024 > max_prepared_transactions = 3000 > work_men = 32MB > effective_io_concurrency = 200 > max_worker_processes = 24 > checkpoint_timeout = 15min > max_wal_size = 64GB > min_wall_size = 2GB > effective_cache_size = 96GB > (...) > > I Think this is too low memory setting for de size of server... The number > of connections, I'm still measuring to reduce this value( I think it's too > high for the needs of application, but untill hit a value too high to > justfy any memory issue, I think is not a problem) > > My current problem: > > under heavyload, i'm getting "connection closed" on the application > level(java-jdbc, jboss ds) > Server logs? What OS (version) What Postgres version. Keep-alive may not be configured at the client side? regards, Ranier Vilela >
Re: Optimal configuration for server
Greatings Ranieri, Server logs I need ask to someone to get it Redhat EL 7 Postgres 12 Humm.. I will find out were I should put keep Alive setting Em seg., 7 de mar. de 2022 13:51, Ranier Vilela escreveu: > Em seg., 7 de mar. de 2022 às 08:54, Luiz Felipph > escreveu: > >> Hi everybody! >> >> I have a big application running on premise. One of my main database >> servers has the following configuration: >> >> 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 >> 1TB of ram or 786GB (5 servers at all) >> A huge storage( I don't know for sure what kind is, but is very powerful) >> >> A consulting company recommended the following configuration for theses >> main servers(let me know if something important was left behind): >> >> maxx_connections = 2000 >> shared_buffers = 32GB >> temp_buffers = 1024 >> max_prepared_transactions = 3000 >> work_men = 32MB >> effective_io_concurrency = 200 >> max_worker_processes = 24 >> checkpoint_timeout = 15min >> max_wal_size = 64GB >> min_wall_size = 2GB >> effective_cache_size = 96GB >> (...) >> >> I Think this is too low memory setting for de size of server... The >> number of connections, I'm still measuring to reduce this value( I think >> it's too high for the needs of application, but untill hit a value too high >> to justfy any memory issue, I think is not a problem) >> >> My current problem: >> >> under heavyload, i'm getting "connection closed" on the application >> level(java-jdbc, jboss ds) >> > Server logs? > What OS (version) > What Postgres version. > Keep-alive may not be configured at the client side? > > regards, > Ranier Vilela > >>
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 14:18, Luiz Felipph escreveu: > Greatings Ranieri, > > Server logs I need ask to someone to get it > > Redhat EL 7 > > Postgres 12 > > Humm.. I will find out were I should put keep Alive setting > Are you using nested connections? regards, Ranier Vilela >
Re: Optimal configuration for server
On 3/7/22 12:51, Luiz Felipph wrote: > Hi everybody! > > I have a big application running on premise. One of my main database > servers has the following configuration: > > 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 > 1TB of ram or 786GB (5 servers at all) > A huge storage( I don't know for sure what kind is, but is very powerful) > > A consulting company recommended the following configuration for theses > main servers(let me know if something important was left behind): > > maxx_connections = 2000 > shared_buffers = 32GB > temp_buffers = 1024 > max_prepared_transactions = 3000 > work_men = 32MB > effective_io_concurrency = 200 > max_worker_processes = 24 > checkpoint_timeout = 15min > max_wal_size = 64GB > min_wall_size = 2GB > effective_cache_size = 96GB > (...) > > I Think this is too low memory setting for de size of server... The > number of connections, I'm still measuring to reduce this value( I think > it's too high for the needs of application, but untill hit a value too > high to justfy any memory issue, I think is not a problem) > Hard to judge, not knowing your workload. We don't know what information was provided to the consulting company, you'll have to ask them for justification of the values they recommended. I'd say it looks OK, but max_connections/max_prepared_transactions are rather high, considering you only have 72 threads. But it depends ... > My current problem: > > under heavyload, i'm getting "connection closed" on the application > level(java-jdbc, jboss ds) > Most likely a java/jboss connection pool config. The database won't just arbitrarily close connections (unless there are timeouts set, but you haven't included any such info). > The server never spikes more the 200GB of used ram(that's why I thing > the configuration is too low) > Unlikely. If needed, the system would use memory for page cache, to cache filesystem data. So most likely this is due to the database not being large enough to need more memory. You're optimizing the wrong thing - the goal is not to use as much memory as possible. The goal is to give good performance given the available amount of memory. You need to monitor shared buffers cache hit rate (from pg_stat_database view) - if that's low, increase shared buffers. Then monitor and tune slow queries - if a slow query benefits from higher work_mem values, do increase that value. It's nonsense to just increase the parameters to consume more memory. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Optimal configuration for server
Hi Tomas, Thank you for your reply! Thomas, > You need to monitor shared buffers cache hit rate (from pg_stat_database > view) - if that's low, increase shared buffers. Then monitor and tune > slow queries - if a slow query benefits from higher work_mem values, do > increase that value. It's nonsense to just increase the parameters to > consume more memory. Makes perfect sense! The system is a OLTP and unfortunately has some issues about how big the single lines are(too many colunms). In some cases I have to bring to app 150k lines(in some not so rare cases, 200k ~300k) to process in a single transaction, then update and insert new rows. It's works fine, except when eventually start to outOfMemory or Connection has been closed forcing us to restart the application cluster. Finally I'll have access to a performance environment to see how is configured(they promised me a production mirror) and then get back to you to provide more detailed information. Thanks for you time! Ranier, > Are you using nested connections? What do you mean with "nested connections"? If you are talking about nested transactions, then yes, and I'm aware of subtransaction problem but I think this is not the case right now (we had, removed multiple points, some other points we delivered to God's hands(joking), but know I don't see this issue) Felipph Em seg., 7 de mar. de 2022 às 15:07, Tomas Vondra < tomas.von...@enterprisedb.com> escreveu: > > > On 3/7/22 12:51, Luiz Felipph wrote: > > Hi everybody! > > > > I have a big application running on premise. One of my main database > > servers has the following configuration: > > > > 72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240 > > 1TB of ram or 786GB (5 servers at all) > > A huge storage( I don't know for sure what kind is, but is very powerful) > > > > A consulting company recommended the following configuration for theses > > main servers(let me know if something important was left behind): > > > > maxx_connections = 2000 > > shared_buffers = 32GB > > temp_buffers = 1024 > > max_prepared_transactions = 3000 > > work_men = 32MB > > effective_io_concurrency = 200 > > max_worker_processes = 24 > > checkpoint_timeout = 15min > > max_wal_size = 64GB > > min_wall_size = 2GB > > effective_cache_size = 96GB > > (...) > > > > I Think this is too low memory setting for de size of server... The > > number of connections, I'm still measuring to reduce this value( I think > > it's too high for the needs of application, but untill hit a value too > > high to justfy any memory issue, I think is not a problem) > > > > Hard to judge, not knowing your workload. We don't know what information > was provided to the consulting company, you'll have to ask them for > justification of the values they recommended. > > I'd say it looks OK, but max_connections/max_prepared_transactions are > rather high, considering you only have 72 threads. But it depends ... > > > My current problem: > > > > under heavyload, i'm getting "connection closed" on the application > > level(java-jdbc, jboss ds) > > > > Most likely a java/jboss connection pool config. The database won't just > arbitrarily close connections (unless there are timeouts set, but you > haven't included any such info). > > > The server never spikes more the 200GB of used ram(that's why I thing > > the configuration is too low) > > > > Unlikely. If needed, the system would use memory for page cache, to > cache filesystem data. So most likely this is due to the database not > being large enough to need more memory. > > You're optimizing the wrong thing - the goal is not to use as much > memory as possible. The goal is to give good performance given the > available amount of memory. > > You need to monitor shared buffers cache hit rate (from pg_stat_database > view) - if that's low, increase shared buffers. Then monitor and tune > slow queries - if a slow query benefits from higher work_mem values, do > increase that value. It's nonsense to just increase the parameters to > consume more memory. > > > regards > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: Optimal configuration for server
Em seg., 7 de mar. de 2022 às 18:10, Luiz Felipph escreveu: > Hi Tomas, > > Thank you for your reply! > > Thomas, > >> You need to monitor shared buffers cache hit rate (from pg_stat_database >> view) - if that's low, increase shared buffers. Then monitor and tune >> slow queries - if a slow query benefits from higher work_mem values, do >> increase that value. It's nonsense to just increase the parameters to >> consume more memory. > > > Makes perfect sense! The system is a OLTP and unfortunately has some > issues about how big the single lines are(too many colunms). In some cases > I have to bring to app 150k lines(in some not so rare cases, 200k ~300k) to > process in a single transaction, then update and insert new rows. It's > works fine, except when eventually start to outOfMemory or Connection has > been closed forcing us to restart the application cluster. Finally I'll > have access to a performance environment to see how is configured(they > promised me a production mirror) and then get back to you to provide more > detailed information. > > Thanks for you time! > > Ranier, > >> Are you using nested connections? > > > What do you mean with "nested connections"? If you are talking about > nested transactions, then yes, and I'm aware of subtransaction problem but > I think this is not the case right now (we had, removed multiple points, > some other points we delivered to God's hands(joking), but know I don't see > this issue) > I mean "nested", even. Two or more connections opened by app. If this is case, is need processing the second connection first, before the first connection. Just a guess. regards, Ranier Vilela
Re: Optimal configuration for server
On Mon, Mar 07, 2022 at 08:51:24AM -0300, Luiz Felipph wrote:
> My current problem:
>
> under heavyload, i'm getting "connection closed" on the application
> level(java-jdbc, jboss ds)
Could you check whether the server is crashing ?
If you run "ps -fu postgres", you can compare the start time ("STIME") of the
postmaster parent process with that of the persistent, auxilliary, child
processes like the checkpointer. If there was a crash, the checkpointer will
have restarted more recently than the parent process.
The SQL version of that is like:
SELECT date_trunc('second', pg_postmaster_start_time() - backend_start) FROM
pg_stat_activity ORDER BY 1 DESC LIMIT 1;
--
Justin
