Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-27 Thread Jim C. Nasby
BTW, http://stats.distributed.net/~decibel/base.log is a test I ran; select count(*) was ~6x faster than explain analyze select *. On Tue, Apr 26, 2005 at 07:46:52PM -0700, Kevin Brown wrote: > Josh Berkus wrote: > > Jim, Kevin, > > > > > > Hrm... I was about to suggest that for timing just the q

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-26 Thread Kevin Brown
Josh Berkus wrote: > Jim, Kevin, > > > > Hrm... I was about to suggest that for timing just the query (and not > > > output/data transfer time) using explain analyze, but then I remembered > > > that explain analyze can incur some non-trivial overhead with the timing > > > calls. Is there a way to

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-25 Thread Christopher Browne
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Merlin Moncure") wrote: >> In practice, we have watched Windows evolve in such a fashion with >> respect to multiuser support, and, in effect, it has never really >> gotten it. Microsoft started by hacking something on top of MS-DOS,

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-25 Thread Merlin Moncure
> I am waiting to here back from Josh on using cursors and trying to flatten > long running views. > > I am a little disappointed I have not understood enough to get my analyzer > to use the proper plan, we had to set seqscan off to get the select from > response_line to work fast and I had to tur

Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-25 Thread Joel Fradkin
l 24, 2005 2:15 AM To: Joel Fradkin Cc: 'Mohan, Ross'; [EMAIL PROTECTED]; pgsql-performance@postgresql.org Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Here is, how you can receive all one billion rows with pieces of 2048 rows. This changes PostgreSQL

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-25 Thread Merlin Moncure
> In practice, we have watched Windows evolve in such a fashion with > respect to multiuser support, and, in effect, it has never really > gotten it. Microsoft started by hacking something on top of MS-DOS, > and by the time enough applications had enough dependancies on the way > that worked, it

Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-25 Thread Joel Fradkin
Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Here is, how you can receive all one billion rows with pieces of 2048 rows. This changes PostgreSQL and ODBC behaviour: Change ODBC data source configuration in the following way: Fetch = 2048 UseDeclareFetch = 1 It do

Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-24 Thread Marko Ristola
e any help Joel Fradkin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mohan, Ross Sent: Thursday, April 21, 2005 10:01 AM To: [EMAIL PROTECTED] Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Joel, thanks. A couple of t

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-23 Thread Josh Berkus
Jim, Kevin, > > Hrm... I was about to suggest that for timing just the query (and not > > output/data transfer time) using explain analyze, but then I remembered > > that explain analyze can incur some non-trivial overhead with the timing > > calls. Is there a way to run the query but have psql ig

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-23 Thread Christopher Browne
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Joel Fradkin") would write: > I am just testing the water so to speak, if it cant handle single > user tests then multiple user tests are kind of a waste of time. I would suggest that if multi-user functionality is needed, then starting

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-23 Thread brew
> I am just testing the water so to speak, if it cant handle single user > tests then multiple user tests are kind of a waste of time. At the risk of being even more pedantic, let me point out that if you are going to be running your application with multiple users the reverse is even more true,

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-23 Thread Joshua D. Drake
Joel Fradkin wrote: I would very, very strongly encourage you to run multi-user tests before deciding on mysql. Mysql is nowhere near as capable when it comes to concurrent operations as PostgreSQL is. From what others have said, it doesn't take many concurrent operations for it to just fall over.

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-23 Thread Joel Fradkin
I would very, very strongly encourage you to run multi-user tests before deciding on mysql. Mysql is nowhere near as capable when it comes to concurrent operations as PostgreSQL is. From what others have said, it doesn't take many concurrent operations for it to just fall over. I can't speak from e

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Kevin Brown
Jim C. Nasby wrote: > Hrm... I was about to suggest that for timing just the query (and not > output/data transfer time) using explain analyze, but then I remembered > that explain analyze can incur some non-trivial overhead with the timing > calls. Is there a way to run the query but have psql ign

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Joel Fradkin
I just finished testing Postgres, MYSQL, and MSSQL on my machine (2 gigs internal XP). I have adjusted the postgres config to what I think is an ok place and have mysql default and mssql default. Using Aqua studio a program that hooks to all three I have found: Initial exec Second exec R

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Bruce Momjian
Are you using 8.0.2? I hope so because there were some Win32 performance changes related to fsync() in that release. --- Joel Fradkin wrote: > I just finished testing Postgres, MYSQL, and MSSQL on my machine (2 gigs > inter

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Joel Fradkin
One further question is: is this really a meaningful test? I mean, in production are you going to query 30 rows regularly? It is a query snippet if you will as the view I posted for audit and case where tables are joined are more likely to be ran. Josh and I worked over this until we got ex

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Jim C. Nasby
On Fri, Apr 22, 2005 at 05:04:19PM -0400, Joel Fradkin wrote: > And is the system always going to be used by only one user? > No we have 400+ concurrent users > > I guess the question is if this big select is representative of the load you > expect in production. > Yes we see many time on the tw

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Alvaro Herrera
On Fri, Apr 22, 2005 at 01:51:08PM -0400, Joel Fradkin wrote: > I just finished testing Postgres, MYSQL, and MSSQL on my machine (2 gigs > internal XP). > > I have adjusted the postgres config to what I think is an ok place and have > mysql default and mssql default. > > Using Aqua studio a progra

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Jim C. Nasby
Hrm... I was about to suggest that for timing just the query (and not output/data transfer time) using explain analyze, but then I remembered that explain analyze can incur some non-trivial overhead with the timing calls. Is there a way to run the query but have psql ignore the output? If so, you c

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Mischa Sandberg
Quoting Alvaro Herrera <[EMAIL PROTECTED]>: > One further question is: is this really a meaningful test? I mean, in > production are you going to query 30 rows regularly? And is the > system always going to be used by only one user? I guess the question > is if this big select is representa

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Andreas Pflug
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andreas Pflug Sent: 21 April 2005 14:06 To: Joel Fradkin Cc: 'John A Meinel'; josh@agliodbs.com; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Joel's Perform

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-22 Thread Dave Page
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Andreas Pflug > Sent: 21 April 2005 14:06 > To: Joel Fradkin > Cc: 'John A Meinel'; josh@agliodbs.com; > pgsql-performance@postgresql.org > Subject: Re: [P

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Mohan, Ross
an, Ross Cc: [EMAIL PROTECTED]; PostgreSQL Perform Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Here is the connect string I am using. It could be horrid as I cut it from ODBC program. Session("StringConn") = "DRIVER={PostgreSQL};DATABASE=wazag

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Joel Fradkin
tableC ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS erverSidePrepare=0" Joel Fradkin -Original Message- From: Mohan, Ross [mailto:[EMAIL PROTECTED] Sent: Thursday, April 21, 2005 9:42 AM To: [EMAIL PROTECTED] Subject: RE: [PERFORM] Joel's Performance Iss

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Joel Fradkin
I suspect he's using pgadmin. Yup I was, but I did try running on the linux box in psql, but it was running to the screen and took forever because of that. The real issue is returning to my app using ODBC is very slow (Have not tested the ODBC for MYSQL, MSSQL is ok (the two proc dell is runnin

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Mohan, Ross
To: 'Tom Lane'; 'John A Meinel' Cc: 'Postgresql Performance' Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon I suspect he's using pgadmin. Yup I was, but I did try running on the linux box in psql, but it was running to the scree

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Joel Fradkin
Why is MYSQL returning 360,000 rows, while Postgres is only returning 330,000? This may not be important at all, though. I also assume you are selecting from a plain table, not a view. Yes plain table. Difference in rows is one of the datasets had sears data in it. It (speed differences found) is

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Andreas Pflug
Joel Fradkin wrote: I did think of something similar just loading the data tables with junk records and I may visit that idea with Josh. I did just do some comparisons on timing of a plain select * from tbl where indexed column = x and it was considerably slower then both MSSQL and MYSQL, so I am s

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-21 Thread Merlin Moncure
> John A Meinel <[EMAIL PROTECTED]> writes: > > Joel Fradkin wrote: > >> Postgres was on the second run > >> Total query runtime: 17109 ms. > >> Data retrieval runtime: 72188 ms. > >> 331640 rows retrieved. > > > How were you measuring "data retrieval time"? > > I suspect he's using pgadmin. We'

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Tom Lane
John A Meinel <[EMAIL PROTECTED]> writes: > Joel Fradkin wrote: >> Postgres was on the second run >> Total query runtime: 17109 ms. >> Data retrieval runtime: 72188 ms. >> 331640 rows retrieved. > How were you measuring "data retrieval time"? I suspect he's using pgadmin. We've seen reports befo

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Josh Berkus
Joel, Ok, please try this: ALTER TABLE tblresponseheader ALTER COLUMN clientnum SET STATISTICS 1000; ALTER TABLE tblresponseheader ALTER COLUMN locationid SET STATISTICS 1000; ALTER TABLE tbllocation ALTER COLUMN clientnum SET STATISTICS 1000; ALTER TABLE tbllocation ALTER COLUMN divisionid SET S

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread John A Meinel
Joel Fradkin wrote: I did think of something similar just loading the data tables with junk records and I may visit that idea with Josh. I did just do some comparisons on timing of a plain select * from tbl where indexed column = x and it was considerably slower then both MSSQL and MYSQL, so I am s

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Joel Fradkin
I did think of something similar just loading the data tables with junk records and I may visit that idea with Josh. I did just do some comparisons on timing of a plain select * from tbl where indexed column = x and it was considerably slower then both MSSQL and MYSQL, so I am still a bit confused

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Vivek Khera
On Apr 20, 2005, at 4:22 PM, Josh Berkus wrote: Realistically I don't think a 30k$ Dell is a something that needs to be junked. I am pretty sure if I got MSSQL running on it, it would outperform my two proc box. I can agree it may not have been the optimal platform. My decision is not based sole

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread John A Meinel
Joel Fradkin wrote: ... I would of spent more $ with Command, but he does need my data base to help me and I am not able to do that. ... What if someone were to write an anonymization script. Something that changes any of the "data" of the database, but leaves all of the relational information. It

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Anjan Dave
@postgresql.org Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Joel, > I have MSSQL running on a 2 proc dell which until my load has increased > (over aprx 2 years) it was just fine. I totally agree that there are better > solutions based on this lists comments,

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Josh Berkus
Joel, > I have MSSQL running on a 2 proc dell which until my load has increased > (over aprx 2 years) it was just fine. I totally agree that there are better > solutions based on this lists comments, but I have all Dell hardware now > and resist trying different vendors just to suit Postgres. I wa

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Joel Fradkin
ly on the performance on the 4 proc box. Joel Fradkin -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 20, 2005 1:54 PM To: Joel Fradkin Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon

2005-04-20 Thread Josh Berkus
Joel, > I did not see any marked improvement, but I don’t think my issues are > related to the hardware. If you won't believe it, then we certainly can't convince you. AFAIK your bad view is a bad query plan made worse by the Dell's hardware problems. > I am giving up on postgres and three dev