Обсуждение: Re: Joel's Performance Issues WAS : Opteron vs Xeon

От:
"Dave Page"
Дата:


> -----Original Message-----
> From: 
> [mailto:] On Behalf Of
> Andreas Pflug
> Sent: 21 April 2005 14:06
> To: Joel Fradkin
> Cc: 'John A Meinel'; ;
> 
> Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
>
> Beware!
>  From the data, I can see that you're probably using pgAdmin3.
> The time to execute your query including transfer of all data to the
> client is 17s in this example, while displaying it (i.e. pure GUI and
> memory alloc stuff) takes 72s. Execute to a file to avoid this.

Perhaps we should add a guruhint there for longer runtimes?

Regards, dave

От:
Andreas Pflug
Дата:

Dave Page wrote:
>
>
>
>>-----Original Message-----
>>From: 
>>[mailto:] On Behalf Of
>>Andreas Pflug
>>Sent: 21 April 2005 14:06
>>To: Joel Fradkin
>>Cc: 'John A Meinel'; ;
>>
>>Subject: Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
>>
>>Beware!
>> From the data, I can see that you're probably using pgAdmin3.
>>The time to execute your query including transfer of all data to the
>>client is 17s in this example, while displaying it (i.e. pure GUI and
>>memory alloc stuff) takes 72s. Execute to a file to avoid this.
>
>
> Perhaps we should add a guruhint there for longer runtimes?

Yup, easily done as replacement for the "max rows exceeded" message box.
Added to TODO.txt.


Regards,
Andreas


От:
"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  Returning 331,640 records on all 3 database
MSSQL    468ms          16ms              2 mins 3  secs
MYSQL   14531ms       6625ms              2 mins 42 secs
Postgr  52120ms      11702ms              2 mins 15 secs

Not sure if this proves your point on PGadmin versus MYSQL query tool versus
MSSQL Query tool, but it certainly seems encouraging.

I am going to visit Josh's tests he wanted me to run on the LINUX server.

Joel Fradkin




От:
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
> 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  Returning 331,640 records on all 3 database
> MSSQL    468ms          16ms              2 mins 3  secs
> MYSQL   14531ms       6625ms              2 mins 42 secs
> Postgr  52120ms      11702ms              2 mins 15 secs
>
> Not sure if this proves your point on PGadmin versus MYSQL query tool versus
> MSSQL Query tool, but it certainly seems encouraging.
>
> I am going to visit Josh's tests he wanted me to run on the LINUX server.
>
> Joel Fradkin
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

От:
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 program that hooks to all three I have found:
>
>       Initial exec  Second exec  Returning 331,640 records on all 3 database
> MSSQL    468ms          16ms              2 mins 3  secs
> MYSQL   14531ms       6625ms              2 mins 42 secs
> Postgr  52120ms      11702ms              2 mins 15 secs

One further question is: is this really a meaningful test?  I mean, in
production are you going to query 300000 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 representative of the load you expect in
production.

What happens if you execute the query more times?  Do the times stay the
same as the second run?

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Use it up, wear it out, make it do, or do without"

От:
Mischa Sandberg
Дата:

Quoting Alvaro Herrera <>:

> One further question is: is this really a meaningful test?  I mean, in
> production are you going to query 300000 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 representative of the load you expect in
> production.

While there may be some far-out queries that nobody would try,
you might be surprised what becomes the norm for queries,
as soon as the engine feasibly supports them. SQL is used for
warehouse and olap apps, as a data queue, and as the co-ordinator
or bridge for (non-SQL) replication apps. In all of these,
you see large updates, large result sets and volatile tables
("large" to me means over 20% of a table and over 1M rows).

To answer your specific question: yes, every 30 mins,
in a data redistribution app that makes a 1M-row query,
and writes ~1000 individual update files, of overlapping sets of rows.
It's the kind of operation SQL doesn't do well,
so you have to rely on one big query to get the data out.

My 2c
--
"Dreams come true, not free." -- S.Sondheim, ITW


От:
"Joel Fradkin"
Дата:

One further question is: is this really a meaningful test?  I mean, in
production are you going to query 300000 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 explain analyze on the linux box to
1 sec. I was just using this as a test as I don't have my views set up on
MYSQL.

So many of my reports pull huge data sets (comprised of normalized joins).
I am thinking I probably have to modify to using an non normalized table,
and Josh is sending me information on using cursors instead of selects.

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 two processor box running MSSQL large return
sets using 100%cpu for 5-30 seconds.

What happens if you execute the query more times?  Do the times stay the
same as the second run?
I will definitely have to pressure testing prior to going live in
production. I have not done concurrent tests as honestly single user tests
are failing, so multiple user testing is not something I need yet.

Joel

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Use it up, wear it out, make it do, or do without"


От:
"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 two processor box running MSSQL large return
> sets using 100%cpu for 5-30 seconds.
>
> What happens if you execute the query more times?  Do the times stay the
> same as the second run?
> I will definitely have to pressure testing prior to going live in
> production. I have not done concurrent tests as honestly single user tests
> are failing, so multiple user testing is not something I need yet.

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 experience because I avoid mysql like the plague,
though. :)

Likewise, MSSQL will probably look better single-user than it will
multi-user. Unless you're going to only access the database single-user,
it's just not a valid test case (and by the way, this is true no matter
what database you're looking at. Multiuser access is where you uncover
your real bottlenecks.)
--
Jim C. Nasby, Database Consultant               
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

От:
"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 experience because I avoid mysql like the plague,
though. :)

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 am trying to de-normalize my view into a table to see if I can get my app
to work. It is a good idea anyway but raises a ton of questions about
dealing with the data post a case being closed etc; also on multiple child
relationships like merchandise and payments etc.

I did do a test of all three (MSSQL, MYSQL,and postgres) in aqua studio ,
all on the same machine running the servers and found postgres beat out
MYSQL, but like any other test it may have been an issue with aqua studio
and mysql in any case I have not made a decision to use mysql I am still
researching fixes for postgres.

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 turn off merge joins to get assoc
list to work fast. Once I am up I can try to learn more about it, I am so
glad there are so many folks here willing to take time to educate us newb's.



От:
"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. I
> can't speak from experience because I avoid mysql like the plague,
> though. :)
>
> 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.

Joel I think you are missing the point on the above comment. The above
comment as I read is, o.k. you are having problems with PostgreSQL BUT
MySQL isn't going to help you and you will see that in multi-user tests.

MySQL is known to work very well on small databases without a lot of
concurrent sessions. I don't think anybody here would argue that.

Where MySQL runs into trouble is larger databases with lots of
concurrent connections.

> 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 turn off merge joins to get assoc
> list to work fast. Once I am up I can try to learn more about it, I am so
> glad there are so many folks here willing to take time to educate us newb's.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.


>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


От:
brew@theMode.com
Дата:


> 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, 'If it can't handle multiple user tests then single user
tests are kind of a waste of time'.

brew

 ==========================================================================
                  Strange Brew   ()
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================


От:
Christopher Browne
Дата:

Centuries ago, Nostradamus foresaw when  ("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 with single user tests is a similar waste of time.

There's good reason to look at it this way...

It is all too common for people to try to start building things with
primitive functionality, and then try to "evolve" the system into what
they need.  It is possible for that to work, if the "base" covers
enough of the necessary functionality.

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 has essentially become impossible for them to migrate
properly to a multiuser model since applications are normally designed
with the myopic "this is MY computer!" model of the world.

You may not need _total_ functionality in the beginning, but,
particularly for multiuser support, which has deep implications for
applications, it needs to be there In The Beginning.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/lisp.html
A CONS is an object which cares.  -- Bernie Greenberg.