Re: psql 9.3 automatic recovery in progress

From: Periko Support <pheriko(dot)support(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: psql 9.3 automatic recovery in progress
Date: 2016-10-12 15:23:25
Message-ID: CAK2yrTbacjw=6b53u1HYW85QL_VSTMsY14CPhEenY7LfuU3Xjw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Just to inform u that my log Yesterday didn't show any 'recovery' which is
the main goal of this post.

Looks like the script was causing the issue, I test and change it to the
one from Scott I'.

On odoo I switch user sessions from default value 1 week(:-/) to 1 hour.

I found that option in the file: /opt/openerp/web/addons/web/http.py
google help.

def session_gc(session_store):
if random.random() < 0.001:
# we keep session one week
#last_week = time.time() - 60*60*24*7 <===== 1 week, don't know why
they chose this value.
last_week = time.time() - 3600 <====change it to 1 hour.
for fname in os.listdir(session_store.path):
path = os.path.join(session_store.path, fname)
try:
if os.path.getmtime(path) < last_week:
os.unlink(path)
except OSError:
pass

Other thing, my server was using swap memory+5MB, checking logs and system,
the task that was causing my server to eat swap, not much but don't want to
use this, the backup process at night is the one that
force the system to use swap, I increase my memory 20GB+ and today I just
see swap =0 :-).

I will continue monitoring my system, I still have other goals to finish,
but this error was a big one.

Thanks all for your help and contribution.

On Mon, Oct 10, 2016 at 6:50 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2016-10-10 21:43 GMT+02:00 Periko Support <pheriko(dot)support(at)gmail(dot)com>:
>
>> For the life time in odoo session, can u point me where I can manage that
>> setting?
>>
>> The configuration /etc/openerp-server.conf doesn't have any parameter for
>> that.
>>
>> That must be in a odoo file...?
>>
>
> https://www.odoo.com/forum/help-1/question/reduce-memory-usage-54636
> http://www.vionblog.com/openerp-server-conf-for-openerp-7-explained/
>
> Regards
>
> Pavel
>
>
>
>> Thanks.
>>
>> On Mon, Oct 10, 2016 at 12:25 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>>
>>>
>>> 2016-10-10 21:12 GMT+02:00 Periko Support <pheriko(dot)support(at)gmail(dot)com>:
>>>
>>>> Andreo u got a good observation here.
>>>>
>>>> I got a script that run every hour why?
>>>>
>>>> Odoo got some issues with IDLE connections, if we don't check our
>>>> current psql connections after a while the system eat all connections and a
>>>> lot of them are IDLE and stop answering users, we create a script that runs
>>>> every hour, this is:
>>>>
>>>> """ Script is used to kill database connection which are idle from last
>>>> 15 minutes """
>>>> #!/usr/bin/env python
>>>> import psycopg2
>>>> import sys
>>>> import os
>>>> from os.path import join, expanduser
>>>> import subprocess, signal, psutil
>>>> import time
>>>>
>>>> def get_conn():
>>>>
>>>> conn_string = "host='localhost' dbname='template1' user='openerp'
>>>> password='s$p_p(at)r70'"
>>>>
>>>> try:
>>>> # get a connection, if a connect cannot be made an exception
>>>> will be raised here
>>>> conn = psycopg2.connect(conn_string)
>>>> cursor = conn.cursor()
>>>> # print "successful Connection"
>>>> return cursor
>>>> except:
>>>> exceptionType, exceptionValue, exceptionTraceback =
>>>> sys.exc_info()
>>>> sys.exit("Database connection failed!\n ->%s" %
>>>> (exceptionValue))
>>>>
>>>>
>>>> def get_pid():
>>>>
>>>> SQL="select pid, datname, usename from pg_stat_activity where
>>>> usename = 'openerp' AND query_start < current_timestamp - INTERVAL '15'
>>>> MINUTE;"
>>>> cursor = get_conn()
>>>> cursor.execute(SQL)
>>>> idle_record = cursor.fetchall()
>>>> print "-----------------------------------------------------------
>>>> ----------------------------------------"
>>>> print "Date:",time.strftime("%d/%m/%Y")
>>>> print "idle record list: ", idle_record
>>>> print "-----------------------------------------------------------
>>>> ----------------------------------------"
>>>> for pid in idle_record:
>>>> try:
>>>> # print "process details",pid
>>>> # os.system("kill -9 %s" % (int(pid[0]), ))
>>>> os.kill(int(pid[0]), signal.SIGKILL)
>>>> except OSError as ex:
>>>> continue
>>>>
>>>> get_pid()
>>>>
>>>> I will move this to run not every hour and see the reaction.
>>>>
>>>> Is a easy move, about Tim, our current KVM server is good for me, see
>>>> picture please:
>>>>
>>>>
>>>> free
>>>> total used free shared buffers
>>>> cached
>>>> Mem: 181764228 136200312 45563916 468 69904
>>>> 734652
>>>> -/+ buffers/cache: 135395756 46368472
>>>> Swap: 261948 0 261948
>>>>
>>>> I got other vm but they are on other raid setup.
>>>>
>>>> Tim u mention that u recommend reduce memory pressure, u mean to lower
>>>> down my values like shared_buffers or increase memory?
>>>>
>>>
>>> try to decrease lifetime of odoo sessions - then memory will be returned
>>> back to system - set limit_memory_soft less in odoo config - I found
>>> some manuals on net with wrong settings on net.
>>>
>>> the odoo sessions should be refreshed more often.
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>
>>>>
>>>>
>>>> Melvin I try that value before but my server cry, I will add more
>>>> memory in a few weeks.
>>>>
>>>> Any comment I will appreciated, thanks.
>>>>
>>>> On Mon, Oct 10, 2016 at 11:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>
>>>>> Periko Support <pheriko(dot)support(at)gmail(dot)com> writes:
>>>>> > My current server has 82GB memory.
>>>>>
>>>>> You said this was running inside a VM, though --- maybe the VM is
>>>>> resource-constrained?
>>>>>
>>>>> In any case, turning off memory overcommit would be a good idea if
>>>>> you're not concerned about running anything but Postgres.
>>>>>
>>>>> regards, tom lane
>>>>>
>>>>
>>>>
>>>
>>
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message arnaud gaboury 2016-10-12 15:57:58 Re: confusion about user paring with pg_hba and pg_ident
Previous Message Kevin Grittner 2016-10-12 15:06:31 Re: SERIALIZABLE and INSERTs with multiple VALUES