Re: psql 9.3 automatic recovery in progress

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
>>
>
>