Re: [PERFORM] Database size
Hello group, Moreover a reindex (REINDEX name of your database while in pgsql) followed by an ANALYZE will claim more space. Regards J6M - Original Message - From: Francisco Reyes [EMAIL PROTECTED] To: choksi [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Friday, June 15, 2007 7:15 PM Subject: Re: [PERFORM] Database size choksi writes: I had a database which uses to hold some 50 Mill records and disk space used was 103 GB. I deleted around 34 Mill records but still the disk size is same. Can some on please shed some light on this. When records are deleted they are only marked in the database. When you run vacuum in the database that space will be marked so new data can use the space. To lower the space used you need to run vacuum full. That however can take a while and I think it will lock the database for some operations. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Parsing VACUUM VERBOSE
Guillaume Smet [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Sabin,
On 6/14/07, Sabin Coanda [EMAIL PROTECTED] wrote:
I'd like to understand completely the report generated by VACUUM VERBOSE.
Please tell me where is it documented ?
You can take a look to what I did for pgFouine:
http://pgfouine.projects.postgresql.org/vacuum.html
Hi Guillaume,
I tried pgFouine.php app on a sample log file but it reports me some errors.
Could you give me some startup support, please ?
I attach the log here to find what's wrong.
Regards,
Sabin
begin 666 postgresql-2007-06-18_160048.log
M,C P-RTP-BTQ. Q-CHP,#HT.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @
M9%T86)AV4@WES=5M('=AR!S:'5T([EMAIL PROTECTED]@,C P-RTP-BTQ. Q
M-CHP,#HT-R!%15-4C(P,#M,#8M,3@@,38Z,# [EMAIL PROTECTED]!;,3DP-SE=
M.B!;+3%=($Q/1SH@(-H96-K]I;G0@F5C;W)D(ES(%T(#,W+T$S-SDX
M-C8TC(P,#M,#8M,3@@,38Z,# [EMAIL PROTECTED]!;,3DP-SE=.B!;+3%=($Q/
M1SH@(')E9\@F5C;W)D(ES(%T(#,W+T$S-SDX-C8T.R!U;F1O(')E8V]R
M9!IR!A= P+S [('-H=71D;W=N(%12544*,C P-RTP-BTQ. Q-CHP,#HT
M.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @;F5X=!TF%NV%C=EO;B!)
M1#H@,[EMAIL PROTECTED],C$[(YE'[EMAIL PROTECTED] V-C,W.#*,C P-RTP-BTQ.
Q-CHP
M,#HT.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @;F5X=!-=6QT:5AA8W1)
M9#H@,S8[(YE'[EMAIL PROTECTED]E886-T3V9FV5T.B W,0HR,# W+3 V+3$X(#$V
M.C P.C0Y([EMAIL PROTECTED],#[EMAIL PROTECTED],3TZ(!D871A8F%S92!S7-T
M96T@:7,@F5A9'D*,C P-RTP-BTQ. Q-CHP,3HR-R!%15-4(%LQ.3 Y-UTZ
M(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ([EMAIL PROTECTED]!MR
@W1A=5M96YT.B!3
[EMAIL PROTECTED]%T95-T6QE/4E33SM314Q%0U0@;VED+!P9U]E;F-O9EN9U]T;U]C
M:%R*5N8V]D:6YG*2!!4R!E;F-O9[EMAIL PROTECTED]%T;%S='-YV]I9 H)(!
M4D]-('!G7V1A=%B87-E(%=(15)%(]I9 ](#0V.30R,PHR,# W+3 V+3$X
M(#$V.C Q.C(W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A=EO;CH@
M,XP.3$@;7,@('-T871E;65N=#H@V5T(-L:65N=%]E;F-O9EN9R!T;R G
M54Y)0T]$12*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM
M,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(#DX+C8S-R!MR @W1A=5M96YT.B!314Q%
[EMAIL PROTECTED]4D]-()T8D-O;QE8W1I;VYS(@HR,# W+3 V+3$X(#$V.C Q.C,W
M([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A=EO;[EMAIL PROTECTED](@;7,@
M('-T871E;[EMAIL PROTECTED],14-4(9OFUA=%]T7!E*]I9PM,[EMAIL PROTECTED],@='EP
M;F%M92!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED(#T@,C,*,C P-RTP-BTQ. Q
M-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(#0N
M,SQ(US(!S=%T96UE;G0Z(%-%3$5#5!#05-%(%=([EMAIL PROTECTED]'EP8F%S971Y
M4],!42$5.(]I9!E;'-E('1Y)AV5T7!E($5.1!!4R!B87-E='EP
M90H)(!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED/3(SC(P,#M,#8M,3@@,38Z
M,#$Z,S@14535!;,3DP.3==.B!;+3%=($Q/1SH@(1UF%T:6]N.B P+C0S
M,B!MR @W1A=[EMAIL PROTECTED];6%T7W1Y4H;VED+#$P-D@
M87,@='EP;F%M92!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED(#T@,3 T,PHR,# W
M+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A
M=EO;CH@,XU,C,@;7,@('-T871E;[EMAIL PROTECTED],14-4([EMAIL PROTECTED]
M7!B87-E='EP93TP(%1(14X@;VED(5L[EMAIL PROTECTED]'EP8F%S971Y[EMAIL
PROTECTED]($%3
M()AV5T7!E@D@($923TT@=?='EP92!72$5212!O:60],3 T,PHR,# W
M+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A
M=EO;CH@,XS-S$@;7,@('-T871E;[EMAIL PROTECTED],14-4(9OFUA=%]T7!E
M*]I9PM,[EMAIL PROTECTED],@='EP;F%M92!4D]-('!G7W1Y[EMAIL
PROTECTED]@;VED(#T@
M,C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,[EMAIL PROTECTED]'
M.B @9'5R871I;VXZ(# N-3$T(US(!S=%T96UE;G0Z(%-%3$5#5!#05-%
M(%=([EMAIL PROTECTED]'EP8F%S971Y4],!42$5.(]I9!E;'-E('1Y)AV5T7!E
M($5.1!!4R!B87-E='EP90H)(!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED/3(S
MC(P,#M,#8M,3@@,38Z,#$Z,S@14535!;,3DP.3==.B!;+3%=($Q/1SH@
M(1UF%T:6]N.B P+C,W.2!MR @W1A=[EMAIL PROTECTED];6%T
M7W1Y4H;VED+TQ*2!AR!T7!N86UE($923TT@=?='EP92!72$5212!O
M:60@/2 R,PHR,# W+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED]
M72!,3TZ(!D=7)A=EO;CH@,XU,3@;7,@('-T871E;[EMAIL PROTECTED],14-4
M([EMAIL PROTECTED]7!B87-E='EP93TP(%1(14X@;VED(5L[EMAIL PROTECTED]'EP8F%S
M971Y[EMAIL PROTECTED]($%3()AV5T7!E@D@($923TT@=?='EP92!72$5212!O
M:60],C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,5T@
M3$]'.B @9'5R871I;VXZ(# N,SQ(US(!S=%T96UE;G0Z(%-%3$5#5!F
M;W)M871?='EP92AO:60L+3$I(%S('1YYA;[EMAIL PROTECTED])/32!P9U]T7!E(%=(
M15)%(]I9 ](#(SC(P,#M,#8M,3@@,38Z,#$Z,S@14535!;,3DP.3==
M.B!;+3%=($Q/1SH@(1UF%T:6]N.B P+C4Q,!MR @W1A=5M96YT.B!3
[EMAIL PROTECTED]('1Y)AV5T7!E/3 @5$A%3B!O:[EMAIL PROTECTED]
M7!B87-E='[EMAIL PROTECTED],@8F%S971Y4*2 @1E)/32!P9U]T7!E(%=(
M15)%(]I9#TR,PHR,# W+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],#DW73H@
M6RTQ72!,3TZ(!D=7)A=EO;CH@,XS-S$@;7,@('-T871E;[EMAIL PROTECTED],
M14-4(9OFUA=%]T7!E*]I9PM,[EMAIL PROTECTED],@='EP;F%M92!4D]-('!G7W1Y
M[EMAIL PROTECTED]@;VED(#T@,C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ
M.3 Y-UTZ(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(# N-3$Y(US(!S=%T96UE
M;G0Z(%-%3$5#5!#05-%(%=([EMAIL PROTECTED]'EP8F%S971Y4],!42$5.(]I9!E
M;'-E('1Y)AV5T7!E($5.1!!4R!B87-E='EP90H)(!4D]-('!G7W1Y
0[EMAIL PROTECTED]@;VED/3(S@``
`
end
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Volunteer to build a configuration tool
I am a Java Software architect, DBA, and project manager for the University of Illinois, Department of Web Services. We use PostgreSQL to serve about 2 million pages of dynamic content a month; everything from calendars, surveys, forms, discussion boards, RSS feeds, etc. I am really impressed with this tool. The only major problem area I have found where PostgreSQL is really lacking is in what should my initial configuration settings be? I realize that there are many elements that can impact a DBA's specific database settings but it would be nice to have a configuration tool that would get someone up and running better in the beginning. This is my idea: A JavaScript HTML page that would have some basic questions at the top: 1) How much memory do you have? 2) How many connections will be made to the database? 3) What operating system do you use? 4) Etc... Next the person would press a button, generate, found below the questions. The JavaScript HTML page would then generate content for two Iframes at the bottom on the page. One Iframe would contain the contents of the postgresql.conf file. The postgresql.conf settings would be tailored more to the individuals needs than the standard default file. The second Iframe would contain the default settings one should consider using with their operating system. My web team would be very happy to develop this for the PostgreSQL project. It would have saved us a lot of time by having a configuration tool in the beginning. I am willing to make this a very high priority for my team. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, David Tokmatchi [EMAIL PROTECTED] wrote: Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Aside from the Wikipedia database comparison, I'm not aware of any direct PostgreSQL-to-Oracle comparison. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It's even harder, as Oracle disallows publishing benchmark figures in their license. As a cynic, I might ask, what Oracle is fearing? Andreas Jonah H. Harris wrote: On 6/18/07, David Tokmatchi [EMAIL PROTECTED] wrote: Scalability ? Performance? Benchmark ? Availability ? Architecture ? Limitation : users, volumes ? Resouces needed ? Support ? Aside from the Wikipedia database comparison, I'm not aware of any direct PostgreSQL-to-Oracle comparison. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdrfHHJdudm4KnO0RAqKQAJ96t7WkLG/VbqkWTW60g6QC5eU4HgCfShNd o3+YPVnPJ2nwXcpi4ow28nw= =1CwN -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Andreas Kostyrka [EMAIL PROTECTED] wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-database flamewar-starting question? -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Jonah H. Harris wrote: On 6/18/07, Andreas Kostyrka [EMAIL PROTECTED] wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-database flamewar-starting question? Depends? How many times are you going to antagonize the people that ask? 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. 2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Oracle also fears benchmarks made by people who don't know how to tune Oracle properly... ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [DOCS] Volunteer to build a configuration tool
Mario, The JavaScript configuration tool I proposed would not be in the install of PostgreSQL. It would be an HTML page. It would be part of the HTML documentation or it could be a separate HTML page that would be linked from the HTML documentation. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Mario Gonzalez [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:16 AM To: Campbell, Lance Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org Subject: Re: [DOCS] Volunteer to build a configuration tool On 18/06/07, Campbell, Lance [EMAIL PROTECTED] wrote: Next the person would press a button, generate, found below the questions. The JavaScript HTML page would then generate content for two Iframes at the bottom on the page. One Iframe would contain the contents of the postgresql.conf file. The postgresql.conf settings would be tailored more to the individuals needs than the standard default file. The second Iframe would contain the default settings one should consider using with their operating system. I think it could be a great help to newbies. IMVHO a bash script in dialog could be better than a javascript file. There are many administrators with no graphics navigator or with no javascript. -- http://www.advogato.org/person/mgonzalez/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
PFC wrote: 2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Oracle also fears benchmarks made by people who don't know how to tune Oracle properly... Yes that is one argument that is made (and a valid one) but it is assuredly not the only one that can be made, that would be legitimate. Joshua D. Drake ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Depends? How many times are you going to antagonize the people that ask? As many times as necessary. Funny how the anti-proprietary-database arguments can continue forever and no one brings up the traditional RTFM-like response of, hey, this was already discussed in thread XXX, read that before posting again. 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. As long as closed-mindedness is legitimate, sure. 2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. They may well have a lot to fear, but that doesn't mean they do; anything statement in that area is pure assumption. I'm in no way saying we can't compete, I'm just saying that the continued closed-mindedness and inside-the-box thinking only serves to perpetuate malcontent toward the proprietary vendors by turning personal experiences into sacred-mailing-list gospel. All of us have noticed the anti-MySQL bashing based on problems with MySQL 3.23... Berkus and others (including yourself, if I am correct), have corrected people on not making invalid comparisons against ancient versions. I'm only doing the same where Oracle, IBM, and Microsoft are concerned. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Replication
Hi, Joshua D. Drake wrote: Slony-II Seems brilliant, a solid theoretical foundation, at the forefront of computer science. But can't find project status -- when will it be available? Is it a pipe dream, or a nearly-ready reality? Dead Not quite... there's still Postgres-R, see www.postgres-r.org And I'm continuously working on it, despite not having updated the website for almost a year now... I planned on releasing the next development snapshot together with 8.3, as that seems to be delayed, that seems realistic ;-) Regards Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: On 6/18/07, Andreas Kostyrka [EMAIL PROTECTED] wrote: As a cynic, I might ask, what Oracle is fearing? As a realist, I might ask, how many times do we have to answer this type of anti-commercial-database flamewar-starting question? Well, my experience when working with certain DBs is much like I had some years ago, when I was forced to work with different SCO Unix legacy boxes. Why do I have to put up with this silliness?, and with databases there is no way to get a sensible tool set by shopping around and installing GNU packages en masse :( Furthermore not being allowed to talk about performance is a real hard misfeature, like DRM. Consider: 1.) Performance is certainly an important aspect of my work as a DBA. 2.) Gaining experience as a DBA is not trivial, it's clearly a discipline that cannot be learned from a book, you need experience. As a developer I can gain experience on my own. As a DBA, I need some nice hardware and databases that are big enough to be nontrivial. 3.) The above points make it vital to be able to discuss my experiences. 4.) Oracle's license NDA makes exchanging experience harder. So as an endeffect, the limited number of playing grounds (#2 above) keeps hourly rates for DBAs high. Oracle's NDA limits secondary knowledge effects, so in effect it keeps the price for Oracle knowhow potentially even higher. Or put bluntly, the NDA mindset benefits completly and only Oracle, and is a clear drawback for customers. It makes Oracle-supplied consultants gods, no matter how much hot air they produce. They've got the benefit of having internal peer knowledge, and as consumer there is not much that I can do counter it. I'm not even allowed to document externally the pitfalls and experiences I've made, so the next poor sob will walk on the same landmine. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsT5HJdudm4KnO0RAoASAJ9b229Uhsuxn9qGfU5I0QUfTC/dqQCfZK/b 65XQFcc0aRBVptxW5uzLejY= =UIF6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 PFC wrote: 2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Oracle also fears benchmarks made by people who don't know how to tune Oracle properly... Well, bad results are as interesting as good results. And this problems applies to all other databases. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsXdHJdudm4KnO0RArTkAKCZs6ht4z0lb2zHtr5MfXj8CsTZdQCgmwE5 JAD6Hkul1iIML42GO1vAM0c= =FMRt -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Jonah H. Harris wrote: On 6/18/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Depends? How many times are you going to antagonize the people that ask? As many times as necessary. Funny how the anti-proprietary-database arguments can continue forever and no one brings up the traditional RTFM-like response of, hey, this was already discussed in thread XXX, read that before posting again. Yeah funny how you didn't do that ;) (of course neither did I). 1. It has *nothing* to do with anti-commercial. It is anti-proprietary which is perfectly legitimate. As long as closed-mindedness is legitimate, sure. It isn't closed minded to consider anti-proprietary a bad thing. It is an opinion and a valid one. One that many have made part of their lives in a very pro-commercial and profitable manner. 2. Oracle, Microsoft, and IBM have a lot to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. They may well have a lot to fear, but that doesn't mean they do; anything statement in that area is pure assumption. 95% of life is assumption. Some of it based on experience, some of it based on pure conjecture, some based on all kinds of other things. I'm in no way saying we can't compete, I'm just saying that the continued closed-mindedness and inside-the-box thinking only serves to perpetuate malcontent toward the proprietary vendors by turning personal experiences into sacred-mailing-list gospel. It is amazing how completely misguided you are in this response. I haven't said anything closed minded. I only responded to your rather antagonistic response to a reasonably innocuous question of: As a cynic, I might ask, what Oracle is fearing? It is a good question to ask, and a good question to discuss. All of us have noticed the anti-MySQL bashing based on problems with MySQL 3.23... Berkus and others (including yourself, if I am correct), have corrected people on not making invalid comparisons against ancient versions. I'm only doing the same where Oracle, IBM, and Microsoft are concerned. I haven't seen any bashing going on yet. Shall we start with the closed mindedness and unfairness of per cpu license and support models? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Yeah funny how you didn't do that ;) (of course neither did I). I agree, an oops on my part :) It is amazing how completely misguided you are in this response. I haven't said anything closed minded. I only responded to your rather antagonistic response to a reasonably innocuous question of: As a cynic, I might ask, what Oracle is fearing? I wasn't responding to you, just to the seemingly closed-mindedness of the original question/statement. We're all aware of the reasons, for and against, proprietary system licenses prohibiting benchmarking. It is a good question to ask, and a good question to discuss. Certainly, but can one expect to get a realistic answer to an, is Oracle fearing something question on he PostgreSQL list? Or was it just a backhanded attempt at pushing the topic again? My vote is for the latter; it served no purpose other than to push the competitiveness topic again. I haven't seen any bashing going on yet. Shall we start with the closed mindedness and unfairness of per cpu license and support models? Not preferably, you make me type too much :) -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: All of us have noticed the anti-MySQL bashing based on problems with MySQL 3.23... Berkus and others (including yourself, if I am correct), have corrected people on not making invalid comparisons against ancient versions. I'm only doing the same where Oracle, IBM, and Microsoft are concerned. My, my, I fear my asbestos are trying to feel warm inside ;) Well, there is not much MySQL bashing going around. And MySQL 5 has enough features and current MySQL AB support for it is so good, that there is no need to bash MySQL based on V3 problems. MySQL5 is still a joke, and one can quite safely predict the answers to tickets, with well over 50% guess rate. (Hint: I don't consider the answer: Redo your schema to be a satisfactory answer. And philosophically, the query optimizer in MySQL is near perfect. OTOH, considering the fact that many operations in MySQL still have just one way to execute, it's easy to choose the fastest plan, isn't it *g*) Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGdsgCHJdudm4KnO0RAg2oAKCdabTyQCcK8eC0+ErVJLlX59nNjgCfQjaO hhfSxBoESyCU/mTQo3gbQRM= =RqB7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Parsing VACUUM VERBOSE
On 6/18/07, Sabin Coanda [EMAIL PROTECTED] wrote:
Guillaume Smet [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Sabin,
On 6/14/07, Sabin Coanda [EMAIL PROTECTED] wrote:
I'd like to understand completely the report generated by VACUUM
VERBOSE.
Please tell me where is it documented ?
You can take a look to what I did for pgFouine:
http://pgfouine.projects.postgresql.org/vacuum.html
Hi Guillaume,
I tried pgFouine.php app on a sample log file but it reports me some
errors.
Could you give me some startup support, please ?
I attach the log here to find what's wrong.
Regards,
Sabin
begin 666 postgresql-2007-06-18_160048.log
M,C P-RTP-BTQ. Q-CHP,#HT.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @
M9%T86)AV4@WES=5M('=AR!S:'5T([EMAIL PROTECTED]@,C P-RTP-BTQ. Q
M-CHP,#HT-R!%15-4C(P,#M,#8M,3@@,38Z,# [EMAIL PROTECTED]!;,3DP-SE=
M.B!;+3%=($Q/1SH@(-H96-K]I;G0@F5C;W)D(ES(%T(#,W+T$S-SDX
M-C8TC(P,#M,#8M,3@@,38Z,# [EMAIL PROTECTED]!;,3DP-SE=.B!;+3%=($Q/
M1SH@(')E9\@F5C;W)D(ES(%T(#,W+T$S-SDX-C8T.R!U;F1O(')E8V]R
M9!IR!A= P+S [('-H=71D;W=N(%12544*,C P-RTP-BTQ. Q-CHP,#HT
M.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @;F5X=!TF%NV%C=EO;B!)
M1#H@,[EMAIL PROTECTED],C$[(YE'[EMAIL PROTECTED] V-C,W.#*,C P-RTP-BTQ.
Q-CHP
M,#HT.2!%15-4(%LQ.3 W.5TZ(%LM,[EMAIL PROTECTED]'.B @;F5X=!-=6QT:5AA8W1)
M9#H@,S8[(YE'[EMAIL PROTECTED]E886-T3V9FV5T.B W,0HR,# W+3 V+3$X(#$V
M.C P.C0Y([EMAIL PROTECTED],#[EMAIL PROTECTED],3TZ(!D871A8F%S92!S7-T
M96T@:7,@F5A9'D*,C P-RTP-BTQ. Q-CHP,3HR-R!%15-4(%LQ.3 Y-UTZ
M(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ([EMAIL PROTECTED]!MR
@W1A=5M96YT.B!3
[EMAIL PROTECTED]%T95-T6QE/4E33SM314Q%0U0@;VED+!P9U]E;F-O9EN9U]T;U]C
M:%R*5N8V]D:6YG*2!!4R!E;F-O9[EMAIL PROTECTED]%T;%S='-YV]I9 H)(!
M4D]-('!G7V1A=%B87-E(%=(15)%(]I9 ](#0V.30R,PHR,# W+3 V+3$X
M(#$V.C Q.C(W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A=EO;CH@
M,XP.3$@;7,@('-T871E;65N=#H@V5T(-L:65N=%]E;F-O9EN9R!T;R G
M54Y)0T]$12*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM
M,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(#DX+C8S-R!MR @W1A=5M96YT.B!314Q%
[EMAIL PROTECTED]4D]-()T8D-O;QE8W1I;VYS(@HR,# W+3 V+3$X(#$V.C Q.C,W
M([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A=EO;[EMAIL PROTECTED](@;7,@
M('-T871E;[EMAIL PROTECTED],14-4(9OFUA=%]T7!E*]I9PM,[EMAIL PROTECTED],@='EP
M;F%M92!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED(#T@,C,*,C P-RTP-BTQ. Q
M-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(#0N
M,SQ(US(!S=%T96UE;G0Z(%-%3$5#5!#05-%(%=([EMAIL PROTECTED]'EP8F%S971Y
M4],!42$5.(]I9!E;'-E('1Y)AV5T7!E($5.1!!4R!B87-E='EP
M90H)(!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED/3(SC(P,#M,#8M,3@@,38Z
M,#$Z,S@14535!;,3DP.3==.B!;+3%=($Q/1SH@(1UF%T:6]N.B P+C0S
M,B!MR @W1A=[EMAIL PROTECTED];6%T7W1Y4H;VED+#$P-D@
M87,@='EP;F%M92!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED(#T@,3 T,PHR,# W
M+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A
M=EO;CH@,XU,C,@;7,@('-T871E;[EMAIL PROTECTED],14-4([EMAIL PROTECTED]
M7!B87-E='EP93TP(%1(14X@;VED(5L[EMAIL PROTECTED]'EP8F%S971Y[EMAIL
PROTECTED]($%3
M()AV5T7!E@D@($923TT@=?='EP92!72$5212!O:60],3 T,PHR,# W
M+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED],3TZ(!D=7)A
M=EO;CH@,XS-S$@;7,@('-T871E;[EMAIL PROTECTED],14-4(9OFUA=%]T7!E
M*]I9PM,[EMAIL PROTECTED],@='EP;F%M92!4D]-('!G7W1Y[EMAIL
PROTECTED]@;VED(#T@
M,C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,[EMAIL PROTECTED]'
M.B @9'5R871I;VXZ(# N-3$T(US(!S=%T96UE;G0Z(%-%3$5#5!#05-%
M(%=([EMAIL PROTECTED]'EP8F%S971Y4],!42$5.(]I9!E;'-E('1Y)AV5T7!E
M($5.1!!4R!B87-E='EP90H)(!4D]-('!G7W1Y[EMAIL PROTECTED]@;VED/3(S
MC(P,#M,#8M,3@@,38Z,#$Z,S@14535!;,3DP.3==.B!;+3%=($Q/1SH@
M(1UF%T:6]N.B P+C,W.2!MR @W1A=[EMAIL PROTECTED];6%T
M7W1Y4H;VED+TQ*2!AR!T7!N86UE($923TT@=?='EP92!72$5212!O
M:60@/2 R,PHR,# W+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],[EMAIL PROTECTED]
M72!,3TZ(!D=7)A=EO;CH@,XU,3@;7,@('-T871E;[EMAIL PROTECTED],14-4
M([EMAIL PROTECTED]7!B87-E='EP93TP(%1(14X@;VED(5L[EMAIL PROTECTED]'EP8F%S
M971Y[EMAIL PROTECTED]($%3()AV5T7!E@D@($923TT@=?='EP92!72$5212!O
M:60],C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ.3 Y-UTZ(%LM,5T@
M3$]'.B @9'5R871I;VXZ(# N,SQ(US(!S=%T96UE;G0Z(%-%3$5#5!F
M;W)M871?='EP92AO:60L+3$I(%S('1YYA;[EMAIL PROTECTED])/32!P9U]T7!E(%=(
M15)%(]I9 ](#(SC(P,#M,#8M,3@@,38Z,#$Z,S@14535!;,3DP.3==
M.B!;+3%=($Q/1SH@(1UF%T:6]N.B P+C4Q,!MR @W1A=5M96YT.B!3
[EMAIL PROTECTED]('1Y)AV5T7!E/3 @5$A%3B!O:[EMAIL PROTECTED]
M7!B87-E='[EMAIL PROTECTED],@8F%S971Y4*2 @1E)/32!P9U]T7!E(%=(
M15)%(]I9#TR,PHR,# W+3 V+3$X(#$V.C Q.C,W([EMAIL PROTECTED],#DW73H@
M6RTQ72!,3TZ(!D=7)A=EO;CH@,XS-S$@;7,@('-T871E;[EMAIL PROTECTED],
M14-4(9OFUA=%]T7!E*]I9PM,[EMAIL PROTECTED],@='EP;F%M92!4D]-('!G7W1Y
M[EMAIL PROTECTED]@;VED(#T@,C,*,C P-RTP-BTQ. Q-CHP,3HS-R!%15-4(%LQ
M.3 Y-UTZ(%LM,[EMAIL PROTECTED]'.B @9'5R871I;VXZ(# N-3$Y(US(!S=%T96UE
M;G0Z(%-%3$5#5!#05-%(%=([EMAIL PROTECTED]'EP8F%S971Y4],!42$5.(]I9!E
M;'-E('1Y)AV5T7!E($5.1!!4R!B87-E='EP90H)(!4D]-('!G7W1Y
0[EMAIL PROTECTED]@;VED/3(S@``
`
end
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Guillaume and
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
All, On Mon, Jun 18, 2007 at 07:50:22PM +0200, Andreas Kostyrka wrote: [something] It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [DOCS] Volunteer to build a configuration tool
On 6/18/07, Campbell, Lance [EMAIL PROTECTED] wrote: Mario, The JavaScript configuration tool I proposed would not be in the install of PostgreSQL. It would be an HTML page. It would be part of the HTML documentation or it could be a separate HTML page that would be linked from the HTML documentation. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Mario Gonzalez [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:16 AM To: Campbell, Lance Cc: [EMAIL PROTECTED]; pgsql-performance@postgresql.org Subject: Re: [DOCS] Volunteer to build a configuration tool On 18/06/07, Campbell, Lance [EMAIL PROTECTED] wrote: Next the person would press a button, generate, found below the questions. The JavaScript HTML page would then generate content for two Iframes at the bottom on the page. One Iframe would contain the contents of the postgresql.conf file. The postgresql.conf settings would be tailored more to the individuals needs than the standard default file. The second Iframe would contain the default settings one should consider using with their operating system. I think it could be a great help to newbies. IMVHO a bash script in dialog could be better than a javascript file. There are many administrators with no graphics navigator or with no javascript. -- http://www.advogato.org/person/mgonzalez/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster EXCELLENT idea Lance. -- Yudhvir Singh Sidhu 408 375 3134 cell
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On 6/18/07, Andrew Sullivan [EMAIL PROTECTED] wrote: It would appear that this was the flame-fest that was predicted. Particularly as this has been copied to five lists. If you all want to have an argument about what Oracle should or should not do, could you at least limit it to one list? Yeah, Josh B. asked it to be toned down to the original list which should've been involved. Which I think should be pgsql-admin or pgsql-advocacy... your thoughts? I think the Oracle discussion is over, David T. just needs URL references IMHO. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Replication
Markus Schiltknecht wrote: Not quite... there's still Postgres-R, see www.postgres-r.org And I'm continuously working on it, despite not having updated the website for almost a year now... I planned on releasing the next development snapshot together with 8.3, as that seems to be delayed, that seems realistic ;-) Is Postgres-R the same thing as Slony-II? There's a lot of info and news around about Slony-II, but your web page doesn't seem to mention it. While researching replication solutions, I had a heck of a time sorting out the dead or outdated web pages (like the stuff on gborg) from the active projects. Either way, it's great to know you're working on it. Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jonah H. Harris wrote: Certainly, but can one expect to get a realistic answer to an, is Oracle fearing something question on he PostgreSQL list? Or was it just a backhanded attempt at pushing the topic again? My vote is for the latter; it served no purpose other than to push the competitiveness topic again. Well, I'm a cynic at heart, really. So there was no bad intend behind it. And it was a nice comment, because I would base it on my personal experiences with certain vendors, it wouldn't be near as nice. The original question was about comparisons between PG and Oracle. Now, I could answer this question from my personal experiences with the product and support. That would be way more stronger worded than my small cynic question. Another thing, Joshua posted a guesstimate that PG can compete in 90-95% cases with Oracle. Because Oracle insists on secrecy, I'm somehow inclined to believe the side that talks openly. And while I don't like to question Joshua's comment, I think he overlooked one set of problems, namely the cases where Oracle is not able to compete with PG. It's hard to quantify how many of these cases there are performance-wise, well, because Oracle insists on that silly NDA, but there are clearly cases where PG is superior. Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGds8WHJdudm4KnO0RAvb0AJ4gBec4yikrAOvDi5C3kc5NLGYteACghewU PkfrnXgCRfZlEdeMA2DZGTE= =BpUw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:16:56PM -0400, Jonah H. Harris wrote: pgsql-advocacy... your thoughts? I've picked -advocacy. I think the Oracle discussion is over, David T. just needs URL references IMHO. I don't think we can speak about Oracle; if we were licenced, we'd be violating it, and since we're not, we can't possibly know about it, right ;-) But there are some materials about why to use Postgres on the website: http://www.postgresql.org/about/advantages A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
On Mon, Jun 18, 2007 at 02:38:32PM -0400, Andrew Sullivan wrote: I've picked -advocacy. Actually, I _had_ picked advocacy, but had an itchy trigger finger. Apologies, all. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Replication
Hi, Craig James wrote: Is Postgres-R the same thing as Slony-II? There's a lot of info and news around about Slony-II, but your web page doesn't seem to mention it. Hm... true. Good point. Maybe I should add a FAQ: Postgres-R has been the name of the research project by Bettina Kemme et al. Slony-II was the name Neil and Gavin gave their attempt to continue that project. I've based my work on the old (6.4.2) Postgres-R source code - and I'm still calling it Postgres-R, probably Postgres-R (8) to distinguish it from the original one. But I'm thinking about changing the name completely... however, I'm a developer, not a marketing guru. While researching replication solutions, I had a heck of a time sorting out the dead or outdated web pages (like the stuff on gborg) from the active projects. Yeah, that's one of the main problems with replication for PostgreSQL. I hope Postgres-R (or whatever name I'll come up with in the future) can change that. Either way, it's great to know you're working on it. Maybe you want to join its mailing list [1]? I'll try to get some discussion going there in the near future. Regards Markus [1]: Postgres-R on gborg: http://pgfoundry.org/projects/postgres-r/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] Performance query about large tables, lots of concurrent access
Hi, I have an application which really exercises the performance of postgresql in a major way, and I am running into a performance bottleneck with Postgresql 8.1 that I do not yet understand. Here are the details: - There is a primary table, with some secondary tables - The principle transaction consists of a SELECT...FOR UPDATE, followed by either an INSERT or an UPDATE on the primary table - INSERTs, DELETEs, and UPDATEs may occur on the secondary table depending on what happens with the primary table, for any given transaction. The secondary table has about 10x the number of rows as the primary. - All operations are carefully chosen so that highly discriminatory indexes are used to locate the record(s) in question. The execution plans show INDEX SCAN operations being done in all cases. - At any given time, there are up to 100 of these operations going on at once against the same database. What I am seeing: - In postgresql 7.4, the table activity seems to be gated by locks, and runs rather slowly except when the sizes of the tables are small. - In postgresql 8.1, locks do not seem to be an issue, and the activity runs about 10x faster than for postgresql 7.4. - For EITHER database version, the scaling behavior is not the log(n) behavior I'd expect (where n is the number of rows in the table), but much more like linear performance. That is, as the tables grow, performance drops off precipitously. For a primary table size up to 100,000 rows or so, I get somewhere around 700 transactions per minute, on average. Between 100,000 and 1,000,000 rows I got some 150 transactions per minute. At about 1,500,000 rows I get about 40 transactions per minute. - Access to a row in the secondary table (which right now has 13,000,000 rows in it) via an index that has extremely good discriminatory ability on a busy machine takes about 90 seconds elapsed time at the moment - which I feel is pretty high. I tried increasing the shared_buffers parameter to see if it had any impact on overall throughput. It was moderately helpful going from the small default value up to 8192, but less helpful when I increased it beyond that. Currently I have it set to 131072. Question: Does anyone have any idea what bottleneck I am hitting? An index's performance should in theory scale as the log of the number of rows - what am I missing here? Thanks very much! Karl ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Volunteer to build a configuration tool
This is my idea: A JavaScript HTML page that would have some basic questions at the top: 1) How much memory do you have? 2) How many connections will be made to the database? 3) What operating system do you use? 4) Etc… Next the person would press a button, “generate”, found below the questions. The JavaScript HTML page would then generate content for two Iframes at the bottom on the page. One Iframe would contain the contents of the postgresql.conf file. The postgresql.conf settings would be tailored more to the individuals needs than the standard default file. The second Iframe would contain the default settings one should consider using with their operating system. My web team would be very happy to develop this for the PostgreSQL project. It would have saved us a lot of time by having a configuration tool in the beginning. I am willing to make this a very high priority for my team. Hi Lance, I agree that having a page that can assist in generating a base configuration file is an excellent way to start off with a good configuration that can assist a system administrator in getting half way to a good configuration. We've recently gone through a process of configuring a machine and it is a time consuming task of testing and benchmarking various configuration details. My thoughts: Using the browser is a great idea as a universal platform. I can foreseen a problem in that some users won't have GUI access to the machine that they are setting up. I don't have much direct experience in this field, but I suspect that a great number of installations happen 'headless'? This can easily be circumvented by hosting the configuration builder on a public internet site, possibly postgresql.org? Also, Javascript isn't the easiest language to use to get all the decisions that need to be made for various configuration options. Would it not be a better idea to host a configuration builder centrally, possible on postgresql.org and have the documentation reference it, including the docs that come packaged with postgresql (README, INSTALL documentation?). This would mean that you wouldn't be able to package the configuration builder, but you would be able to implement more application logic and more complex decision making in a hosted application. Of course, I have no idea of the skills that your team already have :) To add ideas: perhaps a more advanced tool would be able to add comment indicating a suggested range for the particular setting. For example, with 2Gb of RAM, it chooses a workmem of, say, 768Mb, with a comment indicating a suggested range of 512Mb - 1024Mb. Thanks for taking the time to put this together and for offering the services of your team. Kind regards, James begin:vcard fn:James Neethling n:Neethling;James org:Silver Sphere Business Solutions adr:Centurion Business Park A2;;25633 Democracy Way;Prosperity Park;Milnerton;Cape Town;7441 email;internet:[EMAIL PROTECTED] title:Managing Member tel;work:27 21 552 7108 tel;fax:27 21 552 7106 tel;cell:27 83 399 2799 x-mozilla-html:FALSE url:http://www.silversphere.co.za version:2.1 end:vcard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Karl Wright wrote: Hi, I have an application which really exercises the performance of postgresql in a major way, and I am running into a performance bottleneck with Postgresql 8.1 that I do not yet understand. Here are the details: - There is a primary table, with some secondary tables - The principle transaction consists of a SELECT...FOR UPDATE, followed by either an INSERT or an UPDATE on the primary table - INSERTs, DELETEs, and UPDATEs may occur on the secondary table depending on what happens with the primary table, for any given transaction. The secondary table has about 10x the number of rows as the primary. - All operations are carefully chosen so that highly discriminatory indexes are used to locate the record(s) in question. The execution plans show INDEX SCAN operations being done in all cases. - At any given time, there are up to 100 of these operations going on at once against the same database. What I am seeing: - In postgresql 7.4, the table activity seems to be gated by locks, and runs rather slowly except when the sizes of the tables are small. - In postgresql 8.1, locks do not seem to be an issue, and the activity runs about 10x faster than for postgresql 7.4. - For EITHER database version, the scaling behavior is not the log(n) behavior I'd expect (where n is the number of rows in the table), but much more like linear performance. That is, as the tables grow, performance drops off precipitously. For a primary table size up to 100,000 rows or so, I get somewhere around 700 transactions per minute, on average. Between 100,000 and 1,000,000 rows I got some 150 transactions per minute. At about 1,500,000 rows I get about 40 transactions per minute. - Access to a row in the secondary table (which right now has 13,000,000 rows in it) via an index that has extremely good discriminatory ability on a busy machine takes about 90 seconds elapsed time at the moment - which I feel is pretty high. I tried increasing the shared_buffers parameter to see if it had any impact on overall throughput. It was moderately helpful going from the small default value up to 8192, but less helpful when I increased it beyond that. Currently I have it set to 131072. Question: Does anyone have any idea what bottleneck I am hitting? An index's performance should in theory scale as the log of the number of rows - what am I missing here? Thanks very much! Karl I suppose I should also have noted that the postgresql processes that are dealing with the transactions seem to be CPU bound. Here's a top from the running system: top - 15:58:50 up 4 days, 4:45, 1 user, load average: 17.14, 21.05, 22.46 Tasks: 194 total, 15 running, 177 sleeping, 0 stopped, 2 zombie Cpu(s): 98.4% us, 1.5% sy, 0.0% ni, 0.0% id, 0.1% wa, 0.0% hi, 0.0% si Mem: 16634256k total, 16280244k used, 354012k free, 144560k buffers Swap: 8008360k total, 56k used, 8008304k free, 15071968k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 15966 postgres 18 0 1052m 1.0g 1.0g R 66.5 6.3 0:18.64 postmaster 14683 postgres 17 0 1053m 1.0g 1.0g R 54.9 6.3 0:17.90 postmaster 17050 postgres 15 0 1052m 93m 90m S 50.3 0.6 0:06.42 postmaster 16816 postgres 18 0 1052m 166m 162m R 46.3 1.0 0:04.80 postmaster 16697 postgres 18 0 1052m 992m 988m R 42.3 6.1 0:15.49 postmaster 17272 postgres 16 0 1053m 277m 273m S 30.8 1.7 0:09.91 postmaster 16659 postgres 16 0 1052m 217m 213m R 29.8 1.3 0:06.60 postmaster 15509 postgres 18 0 1052m 1.0g 1.0g R 23.2 6.4 0:26.72 postmaster 16329 postgres 18 0 1052m 195m 191m R 16.9 1.2 0:05.54 postmaster 14019 postgres 20 0 1052m 986m 983m R 16.5 6.1 0:16.50 postmaster 17002 postgres 18 0 1052m 38m 35m R 12.6 0.2 0:02.98 postmaster 16960 postgres 15 0 1053m 453m 449m S 3.3 2.8 0:10.39 postmaster 16421 postgres 15 0 1053m 1.0g 1.0g S 2.3 6.2 0:23.59 postmaster 13588 postgres 15 0 1052m 1.0g 1.0g D 0.3 6.4 0:47.89 postmaster 24708 root 15 0 2268 1136 836 R 0.3 0.0 0:05.92 top 1 root 15 0 1584 520 452 S 0.0 0.0 0:02.08 init Karl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Parsing VACUUM VERBOSE
On 6/18/07, Sabin Coanda [EMAIL PROTECTED] wrote: Hi Guillaume, I tried pgFouine.php app on a sample log file but it reports me some errors. Could you give me some startup support, please ? I attach the log here to find what's wrong. Sorry for the delay. I answered to your private email this evening. -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Parsing VACUUM VERBOSE
On 6/18/07, Y Sidhu [EMAIL PROTECTED] wrote: I am following this discussion with great interest. I have PG running on FreeBSD and am forced to run pgFouine on a separate Linux box. I am hoping I can create a log file. and then copy that over and have pgFouine analyze it on the Linux box. a. I created a log file out of vacuum verbose, is that right? It is not complete because I don't know how to dump it into a file in some sort of autmoated fashion. So, I have to take what is on the screen and copy it off. If you want to analyze a VACUUM log, just run vacuumdb with the option you need (for example -a -z -v -f for a vacuum full analyze verbose). # vacuumdb -a -z -v -f your_log_file.log Then analyze this log file as explained on the pgFouine website. b. I can also set a variable log_min_duration_statement in pgsql.conf I guess I am like Sabin,, and need some hand holding to get started. This is completely different and it's useful for query log analysis. So you don't care if you just want to analyze your vacuum behaviour. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Volunteer to build a configuration tool
On Mon, 18 Jun 2007, Campbell, Lance wrote: The postgresql.conf settings would be tailored more to the individuals needs than the standard default file. The second Iframe would contain the default settings one should consider using with their operating system. I'd toyed with making a Javascript based tool for this but concluded it wasn't ever going to be robust enough for my purposes. It wouldn't hurt to have it around through, as almost anything is an improvement over the current state of affairs for new users. As far as prior art goes here, there was an ambitious tool driven by Josh Berkus called Configurator that tried to address this need but never got off the ground, you might want to swipe ideas from it. See http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/configurator/configurator/ for some documents/code and http://pgfoundry.org/docman/index.php?group_id=1000106 for a handy Open-Office spreadsheet. If you want this to take off as a project, make sure you can release the code under a free software license compatible with the PostgreSQL project, so others can contribute to it and it can be assimilated by the core project if it proves helpful. I know I wouldn't spend a minute working on this if that's not the case. I'd suggest you try and get the basic look fleshed out with some reasonable values for the parameters, then release the source and let other people nail down the parts you're missing. Don't get stressed about making sure you have a good value to set for everything before releasing a beta, it's a lot easier for others to come in and help fix a couple of parameters once the basic framework is in place. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Volunteer to build a configuration tool
On Jun 18, 2007, at 4:09 PM, [EMAIL PROTECTED] wrote: one thing to point out to people about this idea is that nothing says that this page needs to be served via a webserver. If all the calculations are done in javascript this could be a local file that you open with a browser. do any of the text-mode browsers implement javascript? if so then you have an answer even for the deeply buried isolated headless servers. It doesn't really matter. The implementation is likely to be trivial, and could be independently knocked out by anyone in their favorite language in a few hours. The tricky bits are going to be defining the problem and creating the alogrithm to do the maths from input to output. If that's so, the language or platform the proof-of-concept code is written for isn't that important, as it's likely to be portable to anything else without too much effort. But the tricky bits seem quite tricky (and the first part, defining the problem, is something where someone developing it on their own, without some discussion with other users and devs could easily end up way off in the weeds). Cheers, Steve David Lang On Mon, 18 Jun 2007, Campbell, Lance wrote: I am a Java Software architect, DBA, and project manager for the University of Illinois, Department of Web Services. We use PostgreSQL to serve about 2 million pages of dynamic content a month; everything from calendars, surveys, forms, discussion boards, RSS feeds, etc. I am really impressed with this tool. The only major problem area I have found where PostgreSQL is really lacking is in what should my initial configuration settings be? I realize that there are many elements that can impact a DBA's specific database settings but it would be nice to have a configuration tool that would get someone up and running better in the beginning. This is my idea: A JavaScript HTML page that would have some basic questions at the top: 1) How much memory do you have? 2) How many connections will be made to the database? 3) What operating system do you use? 4) Etc... ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Volunteer to build a configuration tool
On Mon, 18 Jun 2007, [EMAIL PROTECTED] wrote: do any of the text-mode browsers implement javascript? http://links.twibright.com/ -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Karl Wright [EMAIL PROTECTED] writes: - At any given time, there are up to 100 of these operations going on at once against the same database. It sounds like your hardware is far past maxed out. Which is odd since tables with a million or so rows are pretty small for modern hardware. What's the CPU and disk hardware here, exactly? What do you see when watching vmstat or iostat (as appropriate for OS, which you didn't mention either)? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
