postgresql 锁的定位

今天碰到了一个问题，锁定穷根追底把postgresql的锁研究了一番。

数据库查看锁

可以通过表 pg_locks来查看有哪些锁。sql如下：

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid  and b.relname not like 'pg%';

 

可以通过pg_stat_query表找到具体的sql，根据pid，正如文档2里面所说，很鸡肋，木有神马作用。顶多知道出于神马状态。

 

 锁的定位一般步骤

鉴于这次的问题发生在“idle in transaction”， 因此针对这个具体做了下攻关：(参考文档2)

1. 设置PostgreSQL\x.x\data\下的postgresql.conf文件中的两个参数：

   log_line_prefix ， 日志行的前缀（支持比较多的参数，具体看conf文件后面的说明。修改为 '%m %u@%d %p'。

   log_min_duration_statement ， 是否记录所有的sql，默认为-1，即不记录，0则为记录。修改为0

 

2. 这一步可以不做。 清空原来的日志，位于PostgreSQL\x.x\data\pg_log下面。需要先停掉postgres然后再开启。便于我们观察不受影响。

3. 重复你产生问题的操作。

4. 定位哪些sql操作时间比较长。（可能需要制作一个工具分析下日志得到报表）

5. 定位具体有问题的代码。

   

 

参考：

文档1：http://blog.sina.com.cn/s/blog_79d599dc0100qpra.html

文档2：http://www.depesz.com/2008/08/28/hunting-idle-in-transactions/

文档3：http://blog.sina.com.cn/s/blog_544a710b01019u1f.html  (pg_stat_query表结构参考）

 

附录：

python+django.db快速将日志导入数据表中源码：

sqldb.py

#coding=utf-8
__author__ = 'tommy.yu'

from django.db import connection
from django.conf import settings

settings.configure(
    DATABASES = { 'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'django',
        'USER': 'openerp',
        'PASSWORD': '1',
        'HOST': 'localhost',
        'PORT': '5432',
        }, },
    TIME_ZONE = 'Hongkong'
)

class cursor(object):
    def __init__(self):
        self.cr =  connection.cursor()

    def query(self,sql):
        self.cr.execute(sql)
        res = self.cr.fetchall()
        self.cr.close()
        return res

    def un_query(self,sql):
        return self.cr.execute(sql)

main.py

#coding=utf-8
__author__ = 'tommy.yu'
from sqldb import cursor
import os

class odoo_sql_log2db(object):
    def __init__(self,log_dir, delimiter, db_table):
        self.table=db_table
        self.delimiter = delimiter
        self.log_dir = log_dir
        self.cr = cursor()
        # self.cr.un_query("""
        # create table %s (
        # sql text,
        # dura float
        # );
        # """%self.table)

    def _sync_line(self, line):
        res = {}
        #FORMAT EG.
        #2014-11-17 16:19:27.965 HKT openerp@ES_IT_TEST 3376日志:  执行时间: 0.000 ms 语句: SELECT nextval('mail_message_id_seq')
        try:
            t = line.split(': ',3)
            if t:
                res.update(sql = t[-1])
                res.update(dura = t[-2].split(' ')[0])
                res.update(ret = self.cr.un_query("""insert into %s(sql, dura) values ('%s', %s)"""%(self.table, res.get('sql').replace('\'','‘'), float(res.get('dura')))))
            return res
        except Exception,ex:
            print ex.message

    def parse_log(self, file_name):
        data = []
        line = ''
        with open(file_name) as f:
            for temp_line in f:
                if not temp_line.startswith(self.delimiter):
                    line += ' '+ temp_line
                else:
                    if line:
                        data.append(self._sync_line(line))
                    line = temp_line
        return data

    def parse_logs(self):
        #os.path.dirname(self.log_dir)
        for (cur, dirs, files) in os.walk(self.log_dir):
            for fname in files:
                log_path = os.path.join(self.log_dir,cur,fname)
                print('begin parsing data for file %s .................................'%fname)
                res= self.parse_log(log_path)
                print('job done. %s records of log file %s have been inserted to the db'%(len(res),fname))
                #print log_path

o = odoo_sql_log2db(r'D:\Program Files\PostgreSQL\9.3\data\pg_log','2014-11-17', 'x_sql_log')
o.parse_logs()

　

转载请注明本文来自：http://www.cnblogs.com/Tommy-Yu/p/4103289.html，谢谢！