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

Sebastian Kornehl webmaster at sourcebase.org
Wed Sep 5 19:24:02 JST 2012


It's a fresh set up just for playing around with pgpool, the Database 
and pgpool have their own machines with no other services.

-Sebastian

On 09/05/2012 12:16 PM, Tatsuo Ishii wrote:
>> 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



More information about the pgpool-general mailing list