Обсуждение: Process in state BIND, authentication, PARSE
Hi, i want to know why in my database the process stay in BID, PARSE, autentication, startup by a couple minuts, generating slow in the process, perhaps tunning parameters? or configuration of operating system (Linux RHEL 6). Thanks by your help -- Atentamente, JEISON BEDOYA DELGADO Adm. Servidores y Comunicaciones AUDIFARMA S.A.
On 07/08/2013 12:22 PM, Jeison Bedoya wrote: > Hi, i want to know why in my database the process stay in BID, PARSE, > autentication, startup by a couple minuts, generating slow in the > process, perhaps tunning parameters? or configuration of operating > system (Linux RHEL 6). > > You haven't given us nearly enough information about your setup. We'd need to see your configuration settings and have some details of the machine and where connections are coming from to diagnose it further. cheers andrew
Hi, yeah i am sorry, i run the postgresql in a machine with this configuration Ram: 128GB cpu: 32 cores Disk: 400GB over SAN The database run an application web over glassfish, and have 2.000 users my database configuracion is this: max_connections = 900 shared_buffers = 4096MB temp_buffers = 128MB work_mem = 1024MB maintenance_work_mem = 1024MB wal_buffers = 256 checkpoint_segments = 103 effective_cache_size = 4096MB thanks Atentamente, JEISON BEDOYA DELGADO Adm. Servidores y Comunicaciones AUDIFARMA S.A. El 08/07/2013 11:40 a.m., Andrew Dunstan escribió: > > On 07/08/2013 12:22 PM, Jeison Bedoya wrote: >> Hi, i want to know why in my database the process stay in BID, PARSE, >> autentication, startup by a couple minuts, generating slow in the >> process, perhaps tunning parameters? or configuration of operating >> system (Linux RHEL 6). >> >> > > > You haven't given us nearly enough information about your setup. We'd > need to see your configuration settings and have some details of the > machine and where connections are coming from to diagnose it further. > > cheers > > andrew > > >
On Tue, Jul 9, 2013 at 2:01 AM, Jeison Bedoya <jeisonb@audifarma.com.co> wrote: > max_connections = 900 > work_mem = 1024MB > maintenance_work_mem = 1024MB Aren't work_mem and maintenance_work_mem too high? You need to keep in mind that those are per-operation settings, so for example if you have 100 clients performing queries, this could grow up to 100G. In your case you even have a maximum of 900 connections... Do you perform heavy sort operations with your application that could explain such an amount of memory needed? -- Michael
On Mon, Jul 8, 2013 at 5:35 PM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Tue, Jul 9, 2013 at 2:01 AM, Jeison Bedoya <jeisonb@audifarma.com.co> wrote: >> max_connections = 900 >> work_mem = 1024MB >> maintenance_work_mem = 1024MB > Aren't work_mem and maintenance_work_mem too high? You need to keep in > mind that those are per-operation settings, so for example if you have > 100 clients performing queries, this could grow up to 100G. In your > case you even have a maximum of 900 connections... Do you perform > heavy sort operations with your application that could explain such an > amount of memory needed? it's not at all unreasonable for maintenance_work_mem on a 128gb box. agree on work_mem though. If it was me, i'd set it to around 64mb and then locally set it for particular queries that need a lot of memory. merlin
Jeison Bedoya <jeisonb@audifarma.com.co> wrote: > Ram: 128GB > max_connections = 900 > temp_buffers = 128MB Besides the concerns already expressed about work_mem, temp_buffers could be a big problem. If a connection uses temp tables it acquires up to 128MB, *and holds on it reserved for caching temp tables for that connection for as long as the connection lasts*. So, for 900 connections, that could be 112.5 GB. I would expect to see performance decrease and eventually completely tank as more connections reserved memory for this purpose. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company