[pgpool-general: 957] Re: Query Memory Cache goes OOM

[Tatsuo Ishii]

> I changed this, actual I test this with 20 loops. This seems to hold,
> but still uses lot of memory (and swap).
> 
> Maybe I just misunderstand the config values, is
> "memqcache_total_size" a per connection configuration or a total
> value?

No. Because it sits on shared memory, it is shared by all connections.
Are there any servers running on the machine other than pgpool?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> show pool_cache gives now:
> 
>  num_cache_hits | num_selects | cache_hit_ratio | num_hash_entries |
>  used_hash_entries | num_cache_entries | used_cache_enrties_size |
>  free_cache_entries_size | fragment_cache_entries_size
> ----------------+-------------+-----------------+------------------+-------------------+-------------------+-------------------------+----------------------
> ---+-----------------------------
>  137 | 17411 | 0.01 | 1048576 | 13026 | 13026 | 1512533 | 260121555
>    | 299816
> 
> With 20 selects per second (and 15 seconds until memqcache_expire) I
> would say there should be ~300 entries in the cache and not 13026.
> 
> With 31 selects per second 'top' looks like this:
> 
> Mem: 4194304k total, 4180556k used, 13748k free, 1144k buffers
> Swap: 1048568k total, 1048560k used, 8k free, 160984k cached
> 
> Thanks,
> 
> Sebastian
> 
> On 09/05/2012 10:56 AM, Tatsuo Ishii wrote:
>> Thanks, but I didn't find any evidence of actual memory leak.
>> BTW,
>>>>> memqcache_max_num_cache = 10
>> looks way too small.
>>
>> Can you set to default and try again?
>>
>> memqcache_max_num_cache = 1000000
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>> Hi,
>>>
>>> I attached the log file.
>>>
>>> Thanks for your Help!
>>>
>>> Sebastian
>>>
>>> On 09/05/2012 04:23 AM, Tatsuo Ishii wrote:
>>>> I couldn't reproduce your problem here.
>>>> Can you try to run with valgrind to detect memory leak?
>>>> Just in case how to use valgrind:
>>>>
>>>> 1) start pgpool by following command
>>>>      valgrind --trace-children=yes --leak-check=full pgpool [your pgpool
>>>>      options here...]
>>>>      Note that you need to -n option of pgpool and redirect pgpool log to
>>>>      somewhere.
>>>>
>>>> 2) stop pgpool.
>>>>
>>>> 3) send pgpool log.
>>>> --
>>>> Tatsuo Ishii
>>>> SRA OSS, Inc. Japan
>>>> English: http://www.sraoss.co.jp/index_en.php
>>>> Japanese: http://www.sraoss.co.jp
>>>>
>>>>> Hi all,
>>>>>
>>>>> I'm testing the new query cache (pgpool-II version 3.2.0) on CentOS 5.
>>>>>
>>>>> System Specs:
>>>>> 4 cores
>>>>> 4 GB Ram
>>>>>
>>>>> shmmax= 2147483648 (=2 GB)
>>>>>
>>>>> When I start pgpool, my memory usage looks like this:
>>>>>
>>>>> Mem: 4194304k total, 660016k used, 3534288k free, 62116k buffers
>>>>>
>>>>> My test scenario is the following:
>>>>>
>>>>> # Config:
>>>>>
>>>>> memory_cache_enabled = on
>>>>> memqcache_method = 'shmem'
>>>>> memqcache_total_size =  262144000 #250MB
>>>>> memqcache_max_num_cache = 10
>>>>> memqcache_expire = 15
>>>>> memqcache_auto_cache_invalidation = off
>>>>> memqcache_maxcache = 409600 #400kb
>>>>> memqcache_cache_block_size = 1048576 #1MB
>>>>> memqcache_oiddir = '/var/log/pgpool/oiddir'
>>>>>
>>>>> Table: data
>>>>> id integer pkey
>>>>> value character varying(255)
>>>>> [some more columns]
>>>>>
>>>>> SELECT value FROM data WHERE id =${randomNumber};
>>>>>
>>>>> so when I start the first
>>>>> while true; do psql -c "foobar ..." ; sleep 1; done
>>>>>
>>>>> everything looks good. When I start 9 more loops (= 10 selects per
>>>>> second), the used memory increases fast. After ~2 minutes it looks
>>>>> like this:
>>>>>
>>>>> Mem: 4194304k total, 2439012k used, 1755292k free, 62588k buffers
>>>>>
>>>>> after 2 more minutes:
>>>>>
>>>>> Mem: 4194304k total, 4053796k used, 140508k free, 62752k buffers
>>>>>
>>>>> When I add more clients, the machine begins to swap and dies.
>>>>>
>>>>> show pool_cache gives:
>>>>>
>>>>>    num_cache_hits | num_selects | cache_hit_ratio | num_hash_entries |
>>>>>    used_hash_entries | num_cache_entries | used_cache_enrties_size |
>>>>>    free_cache_entries_size | fragment_cache_entries_size
>>>>> ----------------+-------------+-----------------+------------------+-------------------+-------------------+-------------------------+----------------------
>>>>> ---+-----------------------------
>>>>>    0 | 1527 | 0.00 | 16 | 15 | 15 | 1746 | 262142254 | 0
>>>>> (1 row)
>>>>>
>>>>> So my question:
>>>>>
>>>>> What I'm doing wrong? The plan is to connect more Frontends with 300+
>>>>> statements per second.
>>>>> When I turn memory_cache_enabled off the problem does not occur.
>>>>>
>>>>> Thanks in advance!
>>>>>
>>>>> Sebastian
>>>>>
>>>>> _______________________________________________
>>>>> pgpool-general mailing list
>>>>> pgpool-general at pgpool.net
>>>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>