Chinaunix

标题: 要死人了，postgresql数据库突然变得很慢，总是执行一些奇怪的查询 [打印本页]

作者: popety 时间: 2007-09-04 17:41
标题: 要死人了，postgresql数据库突然变得很慢，总是执行一些奇怪的查询
我用postgresql一直还算稳定，性能也可以，不过最近不知什么原因，突然变的很慢，而且不断执行如下的很奇怪的查询语句：

3931 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
3066 | <IDLE>
4017 | <IDLE>
4007 | SELECT 1
4018 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
4019 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
3957 | SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2
4008 | <IDLE>
3958 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
3979 | <IDLE>
4009 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
4022 | <BIND>
3976 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
4006 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
4011 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
3981 | SELECT def.adsrc FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) WHERE c.oid = $1 and a.attnum = $2 AND def.adsrc LIKE '%nextval(%'
4021 | <IDLE>
3964 | <IDLE>
4013 | SELECT attnotnull FROM pg_catalog.pg_attribute WHERE attrelid = $1 AND attnum = $2

大概数据库不断执行上面这些语句影响了性能，有达人知道这是什么原因吗，请指教，千恩万谢呀

作者: popety 时间: 2007-09-05 16:23
没有人遇到过这种问题吗？急呀

作者: panafrio 时间: 2007-09-08 01:10
我的也有這個問題.......我只能每天用CRONTAB 把它重裝就好....

作者: popety 时间: 2007-09-08 09:17
你是说你重新安装的数据库吗？

作者: joint 时间: 2007-09-08 09:20
版本？数据量？

作者: SkyBelieve 时间: 2007-09-08 11:59
pg_class 表记载表和几乎所有有字段或者是那些类似表的东西。包括索引（不过还要参阅 pg_index），序列，视图，复合类型和一些特殊关系类型；参阅 relkind。在下面，当我们指所有这些对象的时候我们说 "relations"（关系）。不是所有字段对所有关系类型都有意义。

pg_attribute 表存储关于表的字段的信息。数据库里每个表的每个字段都在 pg_attribute 里有一行。（还有用于索引，以及所有在 pg_class 里有记录的对象。）

pg_attrdef 表存储字段缺省值。字段的主要信息存放在 pg_attribute （见下文）。只有明确声明一个缺省值（该表何时创建或字段何时增加）的字段在这里有元组。

作者: popety 时间: 2007-09-08 12:15
我现在用的postgresql 是8.1.4 , 系统是centos 4 ，数据库里有几十个表，大的表有几十万行记录吧，原来一直运行比较平稳，最近才突然变得异常起来，执行大量上面列出的查询。这几天我又 vacuum,vacuum full, reindex都做过，但问题仍然存在，我重新建了一个数据库，然后把备份数据导入，发现还是有这些查询存在，现在我实在是束手无策了

作者: crykun 时间: 2007-09-10 11:58
很同情你,可我才刚刚开始学习使用postgresql,才入门,帮不了你.
帮你问问,有消息给你留言.

作者: SkyBelieve 时间: 2007-09-10 12:21
最近对数据库做过什么更改啊！！

作者: leeshasr 时间: 2007-09-10 20:41
到http://bbs.pgsqldb.com 问问！

作者: popety 时间: 2007-09-12 08:06

原帖由 leeshasr 于 2007-9-10 20:41 发表
到http://bbs.pgsqldb.com 问问！

这个网站根本就发不了贴子，一点发贴就出错，晕

作者: SkyBelieve 时间: 2007-09-12 10:44

原帖由 popety 于 2007-9-12 08:06 发表

这个网站根本就发不了贴子，一点发贴就出错，晕

更晕

我也想知道原因，所以我帮你发了！

http://bbs.pgsqldb.com/index.php ... p;start=0#msg_41090

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)