Re: psql 9.3 automatic recovery in progress

[Periko Support <pheriko.support@xxxxxxxxx>]

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.stehule@xxxxxxxxx> wrote:

2016-10-10 21:43 GMT+02:00 Periko Support <pheriko.support@xxxxxxxxx>:

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.stehule@xxxxxxxxx> wrote:

2016-10-10 21:12 GMT+02:00 Periko Support <pheriko.support@xxxxxxxxx>:

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@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@xxxxxxxxxxxxx> wrote:

Periko Support <pheriko.support@xxxxxxxxx> 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