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