Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nUUDJ-0000nJ-8E for pgsql-performance@arkaria.postgresql.org; Wed, 16 Mar 2022 14:01:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nUUDI-0000i6-5c for pgsql-performance@arkaria.postgresql.org; Wed, 16 Mar 2022 14:01:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nUUDH-0000hx-Js for pgsql-performance@lists.postgresql.org; Wed, 16 Mar 2022 14:01:19 +0000 Received: from mout.kundenserver.de ([212.227.126.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nUUDD-0007RN-NC for pgsql-performance@lists.postgresql.org; Wed, 16 Mar 2022 14:01:18 +0000 Received: from ANAT ([78.194.114.252]) by mrelayeu.kundenserver.de (mreue011 [212.227.15.168]) with ESMTPSA (Nemesis) id 1MTRAS-1ngTBD2IHM-00Tnp7; Wed, 16 Mar 2022 15:01:11 +0100 From: "Michel SALAIS" To: "'Luiz Felipph'" Cc: "'Pgsql Performance'" References: In-Reply-To: Subject: RE: Optimal configuration for server Date: Wed, 16 Mar 2022 15:01:10 +0100 Message-ID: <01de01d8393e$4b031b40$e10951c0$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01DF_01D83946.ACCB2CC0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJUMVJBNBG08/uHyjOnCqaWIJdFvwJDmUSJA0WZzsmrnc9u4A== Content-Language: fr X-Provags-ID: V03:K1:GH123Q3m23VHHwJvJLqsbqL8gZIfsvW3dNpUKDBD05GTp4l17S7 hvH3d43srNVHzUcT08a8CfGONLlFjoRONIxUbBpa+4tJZEErRotMFUKnJ28d1NyWISh1ptq bIUQIbLNCKtLf3iQcgn28fncn7ykXkYahercF9cjv6OHjQ2Kfi5kAJQr3wB8ZQ3lehc3USx qcAkHjcVgGcYBjWeX0sTA== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:8TdIznXyxf8=:AbhTDVHEsczJMnjmYDM/Hk wSgBkVs/nnr4IGkZl5jL4joLf5X3xSmvISj2TafFa4nskAiPzrcWbKb7MAqj0nShKIQ3g+4uq TG77ONg48NWOJiWhI1tOk7XvpmvMWKdARjt9V3fRcSCbMlxEz0znS38gzaEACiX45HogyZrCD Rd/xnwDi7MZQDm2LsjBHGJSPNQyOmAID8oglNsu0IOtSFtKbAb8RPegq2WqaFzamlU9+2mrX/ mbOCeE4mulRo+CyqkLdquY+buovW2No2v2EvdVSgNyw/CMSukxKvQhGC1Nf09EjK4/Bbxoe/S eNNh4zbii78zCHCOiHVStSVwdyWzNXHj3zMHEW6l5LKj9JvY1xdhm3iY/xhZxP5nRemAWqNwV t/AHPFFRV/rcE/2bA17b9iEHg5uGJn8Fi7RIC766dAYuWoQbWGarS6ftZ4QzIDI7HqPX8qXg1 e+L4r8OPywLVuRxFYAUNO/Aba80Rzuz6qzA+L6RwU/j61KRh9ok8Vv12My19M4xEy49us3Tor ZsgzPkVihKC6N+CvJhfF5mWx3Q5XeXxicy4YyLaQ3e6T6cRMaEs9xLkMTgptrmcm4KJUoiWHX iYMghYEejQ7NlTB2CGK58Wg3cZ5yzgjdj+hPheSWNBXPDXHqM6HGO3d+7Prf/t2KZZ3EPKWla 9dJjEV5sCbJqiIsrXQdqYyggRWzXiEwsa6g+5YrarfsZBmJENzCvYE9ex/1aCHR61F7M= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_01DF_01D83946.ACCB2CC0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi, =20 Another point to verify is idle_in_transaction_session_timeout What is the value of this parameter? =20 Regards =20 Michel SALAIS De : Luiz Felipph =20 Envoy=C3=A9 : lundi 7 mars 2022 22:07 =C3=80 : Tomas Vondra Cc : Pgsql Performance Objet : Re: Optimal configuration for server =20 Hi Tomas, =20 Thank you for your reply! =20 Thomas, =20 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. =20 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. =20 Thanks for you time! =20 Ranier, Are you using nested connections? =20 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) =20 =20 Felipph =20 =20 Em seg., 7 de mar. de 2022 =C3=A0s 15:07, Tomas Vondra = > = escreveu: On 3/7/22 12:51, Luiz Felipph wrote: > Hi everybody! >=20 > I have a big application running on premise. One of my main database > servers has the following configuration: >=20 > 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) >=20 > A consulting company recommended the following configuration for = theses > main servers(let me know if something important was left behind): >=20 > maxx_connections =3D 2000 > shared_buffers =3D 32GB > temp_buffers =3D 1024 > max_prepared_transactions =3D 3000 > work_men =3D 32MB > effective_io_concurrency =3D 200 > max_worker_processes =3D 24 > checkpoint_timeout =3D 15min > max_wal_size =3D 64GB > min_wall_size =3D 2GB > effective_cache_size =3D 96GB > (...) >=20 > 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) >=20 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: >=20 > under heavyload, i'm getting "connection closed" on the application > level(java-jdbc, jboss ds) >=20 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) >=20 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 --=20 Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ------=_NextPart_000_01DF_01D83946.ACCB2CC0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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=C3=A9 : lundi 7 mars = 2022 22:07
=C3=80 : 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 =C3=A0s 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 =3D 2000
> shared_buffers =3D 32GB
> = temp_buffers =3D 1024
> max_prepared_transactions =3D 3000
> = work_men =3D 32MB
> effective_io_concurrency =3D 200
> = max_worker_processes =3D 24
> checkpoint_timeout =3D 15min
> = max_wal_size =3D 64GB
> min_wall_size =3D 2GB
> = effective_cache_size =3D 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

------=_NextPart_000_01DF_01D83946.ACCB2CC0--