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? 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 <t...@sss.pgh.pa.us> wrote: > Periko Support <pheriko.supp...@gmail.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 >