注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: 业务框架SQL导致 PostgreSQL 负载高一例  

2011-12-10 15:40:27|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

 

         最近刚上了新业务,在业务前期业务量不是很大, 但数据库负载有点高,达到 3 到 4左右,接着到数据库里
查询一番,发现业务的慢SQL 很少,奇怪的是有个非常大的SQL在跑,之所以奇怪是因为这个SQL 查的是 PostgreSQL
的系统表,显然不是业务系统发起,而且这个SQL每天执行的量比较大,下面是详细信息。


--1 问题 SQL
 SELECT conname, consrc, contype, indkey FROM (                                                                           
         SELECT                                                                                                           
                 conname,                                                                                                 
                 CASE WHEN contype='f' THEN                                                                               
                         pg_catalog.pg_get_constraintdef(oid)                                                             
                 ELSE                                                                                                     
                         'CHECK (' || consrc || ')'                                                                       
                 END AS consrc,                                                                                           
                 contype,                                                                                                 
                 conrelid AS relid,                                                                                       
                 NULL AS indkey                                                                                           
         FROM                                                                                                             
                 pg_catalog.pg_constraint                                                                                 
         WHERE                                                                                                            
                 contype IN ('f', 'c')                                                                                    
         UNION ALL                                                                                                        
         SELECT                                                                                                           
                 pc.relname,                                                                                              
                 NULL,                                                                                                    
                 CASE WHEN indisprimary THEN                                                                              
                                 'p'                                                                                      
                 ELSE                                                                                                     
                                 'u'                                                                                      
                 END,                                                                                                     
                 pi.indrelid,                                                                                             
                 indkey                                                                                                   
         FROM                                                                                                             
                 pg_catalog.pg_class pc,                                                                                  
                 pg_catalog.pg_index pi                                                                                   
         WHERE                                                                                                            
                 pc.oid=pi.indexrelid                                                                                     
                 AND EXISTS (                                                                                             
                         SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c                             
                         ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)                                            
                         WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
         )                                                                                                                
 ) AS sub                                                                                                                 
 WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='coin_businesses'                                       
         AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace                                                      
         WHERE nspname='freecoin'))
        
  备注:根据 pg_stat_activity 的 client_addr 字段,知道了发起这个 SQL的源IP。       
        
 
 
--2 上面语句的结果如下
        conname        | consrc | contype | indkey
----------------------+--------+---------+--------
 coin_businesses_pkey |        | p       | 1
 
 
  备注:于是找来开发人员,和开发人员沟通后,原来这个语句是他们用的一个框架发起,会不停地查询数据库
        结构。
       
       
--3 解决方法
  
    后来开发人员将这个查询做到缓存,不需要时时查询数据库,这个SQL解决之后,数据库平均负载下降
    40% 左右。      

  评论这张
 
阅读(25890)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016