Search Postgresql Archives

Re: psql 9.3 automatic recovery in progress

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux