Обсуждение: Optimal configuration for server
72 CPUs(2 chips, 18 physical cores per chip, 2 threads) Xeon Gold 6240
1TB of ram or 786GB (5 servers at all)
Вложения
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 = 2000shared_buffers = 32GBtemp_buffers = 1024max_prepared_transactions = 3000work_men = 32MBeffective_io_concurrency = 200max_worker_processes = 24checkpoint_timeout = 15minmax_wal_size = 64GBmin_wall_size = 2GBeffective_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)
Em seg., 7 de mar. de 2022 às 08:54, Luiz Felipph <luizfelipph@gmail.com> 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 = 2000shared_buffers = 32GBtemp_buffers = 1024max_prepared_transactions = 3000work_men = 32MBeffective_io_concurrency = 200max_worker_processes = 24checkpoint_timeout = 15minmax_wal_size = 64GBmin_wall_size = 2GBeffective_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
Greatings Ranieri,Server logs I need ask to someone to get itRedhat EL 7Postgres 12Humm.. I will find out were I should put keep Alive setting
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
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.
Are you using nested connections?
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
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)
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
Hi,
Another point to verify is idle_in_transaction_session_timeout
What is the value of this parameter?
Regards
Michel SALAIS
De : Luiz Felipph <luizfelipph@gmail.com>
Envoyé : lundi 7 mars 2022 22:07
À : Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc : Pgsql Performance <pgsql-performance@lists.postgresql.org>
Objet : 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.vondra@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