Re: cache Memory of server - Mailing list pgsql-admin

Please keep the list in copy.

Look at the log_ configuration lines in postgresql.conf, especially log_temp_files. 2GB shared buffers seems more
reasonableto me. The max_connection setting seems like a waste of resources to me, but I need to reread the pgpool
manual.But maybe your num_init_children is too high in the first place? You start at 350 initial sessions? 

Von meinem iPad gesendet

> Am 09.06.2015 um 10:29 schrieb AL-Temimi, Muthana <muthana.al-temimi@tu-harburg.hamburg.de>:
>
> the max_connection=2800 because requiered configurations from pgpools see below:
>
> max_pool*num_init_children <= (max_connections - superuser_reserved_connections) (no query canceling needed)
> max_pool*num_init_children*2 <= (max_connections - superuser_reserved_connections) (query canceling needed)
>
> and here is the link for it: http://www.pgpool.net/docs/latest/pgpool-en.html
>
> should I increase the shared_buffer of the postgresql to 2GB instead of 1GB?
> Work_mem is just for sort the result. What should be the expacted value of work_mem?
> I don't know how to log the usage of temp files?
>
> Regards
> Muthana
>
> -----Ursprüngliche Nachricht-----
> Von: Jan Lentfer [mailto:Jan.Lentfer@web.de]
> Gesendet: Dienstag, 9. Juni 2015 10:16
> An: AL-Temimi, Muthana
> Cc: pgsql-admin
> Betreff: Re: AW: [ADMIN] cache Memory of server
>
> Shared buffers seem too low to me, usually you go for 20 - 25% of RAM for a dedicated DB server. work_mem also seems
low,but that depends on your actual queries. You should at least log the usage of temp files and maybe look into
pgbadgerto analyze your logs. 
> Max_connections is way too high - espcially when you use a pooler. I get along using ca. 100 connections serving
severalhundreds of users using jdbc pooling. 
> You might want to take a look at pgtune.
>
> Von meinem iPad gesendet
>
>> Am 09.06.2015 um 10:07 schrieb AL-Temimi, Muthana <muthana.al-temimi@tu-harburg.hamburg.de>:
>>
>> Hello Jan,
>>
>> The shared_buffers ist 1024MB of the postgresql database and the kernel.shmmax=2147483648 (2GB) of linux OS.
>>
>> And here is the some postgresql configurations:
>>
>> work_mem=4MB
>> max_connections=2800
>> shared_buffers=1024MB
>>
>> and the configuration of pgpool
>> init_childern=350
>> max_pool=4
>>
>> Regards
>> Muthana
>>
>> -----Ursprüngliche Nachricht-----
>> Von: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] Im Auftrag von Jan Lentfer
>> Gesendet: Montag, 8. Juni 2015 16:23
>> An: pgsql-admin@postgresql.org
>> Betreff: Re: [ADMIN] cache Memory of server
>>
>> Am 2015-06-08 15:53, schrieb AL-Temimi, Muthana:
>>> See the free command:
>>>
>>> am 08.06.2015 um 15:13 Uhr: --active connection: 305
>>>
>>> srvpgsql1:/opt/pgsql_data # free
>>>
>>> total used free shared buffers cached
>>>
>>> Mem: 12199684 8758400 3441284 1269784 231324 7139400
>>>
>>> -/+ buffers/cache: 1387676 10812008
>>>
>>> Swap: 6289404 0 6289404
>>>
>>>
>>> ----------------------------------------------------------------------
>>>
>>>
>>> am 08.06.2015 um 15:53 Uhr: --active connection: 278
>>>
>>> srvpgsql1:/opt/pgsql_data # free
>>>
>>> total used free shared buffers cached
>>>
>>> Mem: 12199684 8686228 3513456 1269784 232164 7164288
>>>
>>> -/+ buffers/cache: 1289776 10909908
>>>
>>> Swap: 6289404 0 6289404
>>
>>
>> That is basically what Scott said: you are watching the Kernel FS cache. It may only be a coincidence that it
increasedtogether with the postgres sessions. A high number here is usually somehting good, because a lot of your
filesystemsreads will be served from RAM. Looking at your numbers, I would say you are "all good" (except as Scott
said,mabye try to reduce number of parallel sessions) - big fs cache and still free RAM. 
>> What are your settings for shared buffers btw?
>>
>> Jan
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin