性能问题aftrer从7.1更新到7.4.2 [英] performance problem aftrer update from 7.1 to 7.4.2
问题描述
大家好，
i刚刚将我的旧postgres数据库从7.1版升级到7.4.2。
i从我的7.1数据库中删除了（带有pg_dump来自7.1）作为带有
复制命令的sql文件和使用insert-statements的一个文件。
在其他端口上进行初始化和启动postgres 7.4之后和
datadirectory，我试图用复制语句导入sql-dump。
这个导入失败，但导入带有插入的转储文件花了很长时间
时间但是成功了。
ok，起初我做了一个真空并分析并再次启动数据库。
现在我改变了我的php脚本使用新数据库作为数据源。
确定，事情看起来不错，但是当我测试一些php-sciripts时，我认为这些查询已经完成了大约2或5倍的时间:(
a测试一个脚本，从数据库读取很多东西，通常用
pgsql 7.1大约需要4秒才能显示t他的数据，但是
postgres 7.4花了大约25秒。
i启动7.4 pgsql与7.1相同的方式使用postmaster -D
/ xxx / xxx / ... -N512 -S -F -B2048 -i
i也试图杀死一些索引并重新创建它们，但这并不是
问题和查询花了相同的时间执行。我还测试了一些简单的选择查询，这需要更长的时间。
如果有人可以帮助我的话，还有b $ b，并暗示如何加快我的速度'新''
databse非常好:)
提前感谢
你的
volker
---------------------------（播出结束）---- -----------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令 ma*******@postgresql.org 以便您的
消息可以干净利落地到达邮件列表
Hello everyone,
i just upgraded my old postgres-database from version 7.1 to 7.4.2.
i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with
copy-commands and to one file using insert-statements.
after initalizing and starting postgres 7.4 on a different port and
datadirectory, i tried to import the sql-dump with the copy statements.
this import fails, but importing the dump-file with inserts took a long
time but was successfully.
ok, at first i do a vacuum and analyze and fire up the database again.
now i changed my php-scripts to use the new database as datasource.
ok, things look good, but as i was testing some php-sciripts, i
recognized that the querys took about 2 or 5 times longer :(
a test a script which reads a lot of stuff from database, normaly with
pgsql 7.1 it tooks about 4 seconds to display the data, but with
postgres 7.4 it tooks about 25 seconds.
i start the 7.4 pgsql the same way as 7.1 with postmaster -D
/xxx/xxx/... -N512 -S -F -B2048 -i
i also tried to kill some indexes and recreate them, but this doesnt
matter and the query took the same long time to execute. i also tested
some simple select querys, the will take longer time.
if somebody could help me, and hints on how i can speed up my ''new''
databse would be very nice :)
thanks in advance
yours
volker
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
推荐答案
您好，
你可能在某些地方错过了一步...我知道你有你的数据
导入但是你可以尝试使用7.4.2中的pg_dump来获得
抓住7.1数据库并从那里导入
。这听起来像你一样
缺少一个索引或什么。
如果我们新建你的数据结构，如果你可以发布
从7.1和7.4以及
解释您正在进行测试的查询。
此致，
Joshua D. Drake
开发 - multi.art.studio写道：
Hello,
You are probably missing a step some where... I know you got your data
imported but you might try
using the pg_dump from 7.4.2 to grab the 7.1 database and import from
there. It sounds to me like you
are missing an index or something.
Also it would help if we new your data structure, if you could post an
explain from 7.1 and from 7.4 and
possibly the queries that you are running your tests against.
Sincerely,
Joshua D. Drake
Development - multi.art.studio wrote:
大家好，
<我只是将我的旧postgres数据库从7.1版本升级到7.4.2。
我将7.1数据库（来自7.1的pg_dump）作为sql文件转储出来，并带有copy-commands和一个文件使用insert-statements。
在初始化并在另一个端口和
datadirectory上启动postgres 7.4之后，我尝试使用copy语句导入sql-dump。
这个导入失败，但导入带有插入的转储文件需要很长时间但是成功了。
好的，首先我做一个真空并分析并启动数据库再一次。
现在我改变了我的php脚本以使用新数据库作为数据源。
好的，事情看起来不错，但是当我测试一些php-sciripts时，我认识到查询花了大约2到5倍的时间:(测试一个脚本从数据库中读取很多东西，通常用pgsql 7.1来显示数据需要大约4秒钟，但是使用postgres 7.4花了大约25秒。
我以7.1的方式启动7.4 pgsql，使用postmaster -D
/ xxx / xxx / ... -N512 -S -F - B2048 -i
我也试图杀死一些索引并重新创建它们，但这并不重要，查询花了相同的长时间执行。我还测试了一些简单的选择查询，这将需要更长的时间。
如果有人可以帮助我，并提示我如何加快我的''新''
数据库会非常好:)
提前感谢
你的
volker
---------- -----------------（广播结束）--------------------------- <提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令到 ma ** *****@postgresql.org ，以便您的消息可以干净地通过邮件列表
Hello everyone,
i just upgraded my old postgres-database from version 7.1 to 7.4.2.
i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file
with copy-commands and to one file using insert-statements.
after initalizing and starting postgres 7.4 on a different port and
datadirectory, i tried to import the sql-dump with the copy statements.
this import fails, but importing the dump-file with inserts took a
long time but was successfully.
ok, at first i do a vacuum and analyze and fire up the database again.
now i changed my php-scripts to use the new database as datasource.
ok, things look good, but as i was testing some php-sciripts, i
recognized that the querys took about 2 or 5 times longer :(
a test a script which reads a lot of stuff from database, normaly with
pgsql 7.1 it tooks about 4 seconds to display the data, but with
postgres 7.4 it tooks about 25 seconds.
i start the 7.4 pgsql the same way as 7.1 with postmaster -D
/xxx/xxx/... -N512 -S -F -B2048 -i
i also tried to kill some indexes and recreate them, but this doesnt
matter and the query took the same long time to execute. i also tested
some simple select querys, the will take longer time.
if somebody could help me, and hints on how i can speed up my ''new''
databse would be very nice :)
thanks in advance
yours
volker
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
-
Command Prompt，Inc。，Mammoth PostgreSQL的主页 - S / ODBC和S / JDBC
Postgresql支持，编程共享主机和专用主机。
+ 1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator - PostgreSQL的生产质量复制
-------------------------- - （播出结束）---------------------------
提示8：解释分析是你的朋友
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
再次问好，
感谢您的帮助:)
i使用来自7.4.2的
pg_dump命令从postgres 7.1重新转储数据库（称为mcms09）并将其恢复为7.4.2
（数据库名为mcms，postgres 7.4.2在不同的端口上运行）
i将7.1到7.4.2的一些查询进行比较，并将
explain命令的所有输出都放到网站上，因为我认为它太多了，因为它在这里张贴了
。
i只看到大部分时间花在排序表之前（？）
创建过滤器。
7.1：
mcms09 =>解释select * from newsletter where site_id =''m000000-970''按日期排序desc，id desc limit 10;
注意：查询计划：
限制（成本= 9.26..9.26行= 7宽度= 84）
- >排序（成本= 9.26..9.26行= 8宽度= 84）
- >使用简报上的site_id_newsletter_key进行索引扫描（费用= 0.00..9.14行= 8宽度= 84）
EXPLAIN
7.4.2：
mcms =＃explain select * from newsletter where site_id =''m000000-970''按日期排序desc，id desc limit 10;
QUERY PLAN
----- -------------------------------------------------- --------------------------------------------------
限制（成本= 17.78..17.81行= 10宽= 610）
- >排序（成本= 17.78..17.81行= 11宽度= 610）
排序键：日期，ID
- >使用简报上的site_id_newsletter_key进行索引扫描（费用= 0.00..17.59行= 11宽度= 610）
索引条件：（（site_id）:: text =''m000000-970'':: text）
（5行）
但这个查询是第一个，只完成一次，然后另一个查询子集跟随10次，其他依赖于此第一个，在我看来这不是减缓dbs的重要因素
i还在新闻，时事通讯和
newsletter_send表中为这两个数据库添加了新索引，加快了速度因子10的查询:-)但是7.1是
总是更快....
你可以看到所有的东西，查询，表格布局和解释在
网站 http：// www .erdtrabant.de / index.php？i = 60500
提前感谢
volker
Joshua D. Drake写道：
hello again,
thanks for help :)
i re-dumped the database (called mcms09) from postgres 7.1 with the
pg_dump command from 7.4.2 successfully and restored it to 7.4.2
(database called mcms, postgres 7.4.2 running on a different port)
i compared some querys from 7.1 to 7.4.2 and put all the output of the
explain command to a website, because i think its too much for posting
it here.
i just see that most time is spent in sorting the tables before (?)
creating the filter.
7.1:
mcms09=> explain select * from newsletter where site_id=''m000000-970'' order by date desc,id desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=9.26..9.26 rows=7 width=84)
-> Sort (cost=9.26..9.26 rows=8 width=84)
-> Index Scan using site_id_newsletter_key on newsletter (cost=0.00..9.14 rows=8 width=84)
EXPLAIN
7.4.2:
mcms=# explain select * from newsletter where site_id=''m000000-970'' order by date desc,id desc limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Limit (cost=17.78..17.81 rows=10 width=610)
-> Sort (cost=17.78..17.81 rows=11 width=610)
Sort Key: date, id
-> Index Scan using site_id_newsletter_key on newsletter (cost=0.00..17.59 rows=11 width=610)
Index Cond: ((site_id)::text = ''m000000-970''::text)
(5 rows)
but this query is the first one, done only once, then another subset of querys follow 10 times , the others depend on this first one, in my opinion this cant be the big factor which slows down the dbs
i also added new indexes to both databases on news, newsletter and
newsletter_send table, speeding up the query by factor 10 :-) but 7.1 is
always faster ....
you can see all the stuff , query, table layout and explains on the
website http://www.erdtrabant.de/index.php?i=60500
thanks in advance
volker
Joshua D. Drake wrote:
你好，
你是可能的我错过了一些步骤...我知道你已经导入了你的数据了但是你可以尝试使用7.4.2中的pg_dump来获取7.1数据库并从那里导入
。对我来说听起来像是缺少一个索引或其他东西。
如果我们新建一个数据结构，如果你可以发布一个来自7.1和7.4和
可能是你正在进行测试的查询。
真诚的，
Joshua D. Drake
开发 - 多.art.studio写道：
Hello,
You are probably missing a step some where... I know you got your data
imported but you might try
using the pg_dump from 7.4.2 to grab the 7.1 database and import from
there. It sounds to me like you
are missing an index or something.
Also it would help if we new your data structure, if you could post an
explain from 7.1 and from 7.4 and
possibly the queries that you are running your tests against.
Sincerely,
Joshua D. Drake
Development - multi.art.studio wrote:
大家好，
我刚刚将我的旧postgres数据库从7.1版升级到7.4.2。
我使用copy-commands将我的7.1数据库（使用7.1中的pg_dump）作为sql文件转储到使用insert-statements的一个文件中。
在初始化后启动postgres 7.4 port和
datadirectory，我试图用复制语句导入sql-dump。
这个导入失败了，但导入带插入的dump-file花了很长时间但是成功了。好吧，首先我做了一个真空并分析并重新启动数据库。
现在我改变了我的php-scripts使用新数据库作为数据源。
好的，事情看起来不错，但是当我测试一些php-sciripts时，我认识到查询花了大概2到5次更长:(测试一个脚本从数据库中读取很多东西，通常用pgsql 7.1来显示数据花了大约4秒钟，但是使用了postgres 7.4它花了大约25个秒。
我以7.1的方式启动7.4 pgsql与postmaster -D
/ xxx / xxx / ... -N512 -S -F -B2048 -i
我也尝试过杀死一些索引并重新创建它们，但这并不重要，查询花了相同的时间来执行。我还测试了一些简单的选择查询，这将需要更长的时间。
如果有人可以帮助我，并暗示如何加速我的''新''
数据库会非常好:)
提前感谢
你的
volker
---------- -----------------（广播结束）--------------------------- <提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令到 ma ** *****@postgresql.org 以便您的消息可以干净地通过邮件列表
Hello everyone,
i just upgraded my old postgres-database from version 7.1 to 7.4.2.
i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file
with copy-commands and to one file using insert-statements.
after initalizing and starting postgres 7.4 on a different port and
datadirectory, i tried to import the sql-dump with the copy statements.
this import fails, but importing the dump-file with inserts took a
long time but was successfully.
ok, at first i do a vacuum and analyze and fire up the database again.
now i changed my php-scripts to use the new database as datasource.
ok, things look good, but as i was testing some php-sciripts, i
recognized that the querys took about 2 or 5 times longer :(
a test a script which reads a lot of stuff from database, normaly
with pgsql 7.1 it tooks about 4 seconds to display the data, but with
postgres 7.4 it tooks about 25 seconds.
i start the 7.4 pgsql the same way as 7.1 with postmaster -D
/xxx/xxx/... -N512 -S -F -B2048 -i
i also tried to kill some indexes and recreate them, but this doesnt
matter and the query took the same long time to execute. i also
tested some simple select querys, the will take longer time.
if somebody could help me, and hints on how i can speed up my ''new''
databse would be very nice :)
thanks in advance
yours
volker
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
2004年4月14日星期三01:56，开发 - multi.art.studio写道：
On Wednesday 14 April 2004 01:56, Development - multi.art.studio wrote:
我比较了从7.1到7.4.2的一些查询并将所有
解释命令输出到一个网站，因为我认为它太多了在这里张贴
。
这里有点搞笑 - 请注意旧版本中新闻稿（84）的宽度
与新版本中的宽度（610）。
7.1：
mcms09 =>解释select * from newsletter where site_id =''m000000-970''order
by date desc，id desc limit 10;注意：查询计划：
限制（成本= 9.26..9.26行= 7宽度= 84）
7.4.2：
mcms =＃explain select * from newsletter where site_id =''m000000 -970''命令
按日期desc，id desc limit 10; QUERY PLAN
-------------------------------------------- -------------------------------
--------------- ---------------限制（成本= 17.78..17.81行= 10宽度= 610）
- >排序（成本= 17.78..17.81行= 11宽= 610）
i compared some querys from 7.1 to 7.4.2 and put all the output of the
explain command to a website, because i think its too much for posting
it here.
Something funny here - notice the width of newsletter (84) in the old version
against the width in the new one (610).
7.1:
mcms09=> explain select * from newsletter where site_id=''m000000-970'' order
by date desc,id desc limit 10; NOTICE: QUERY PLAN:
Limit (cost=9.26..9.26 rows=7 width=84) 7.4.2:
mcms=# explain select * from newsletter where site_id=''m000000-970'' order
by date desc,id desc limit 10; QUERY PLAN
---------------------------------------------------------------------------
------------------------------ Limit (cost=17.78..17.81 rows=10 width=610)
-> Sort (cost=17.78..17.81 rows=11 width=610)
-
Richard Huxton
Archonet Ltd
---------------------------（播出结束）--- ------------------------
提示3：如果通过Usenet发布/阅读，请发送相应的
subscribe-nomail命令 ma*******@postgresql.org 以便
消息可以干净地通过邮件列表
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
这篇关于性能问题aftrer从7.1更新到7.4.2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
