Thread: Re: Joel's Performance Issues WAS : Opteron vs Xeon
> -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] 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 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
Dave Page wrote: > > > >>-----Original Message----- >>From: pgsql-performance-owner@postgresql.org >>[mailto:pgsql-performance-owner@postgresql.org] 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 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
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
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 pgman@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
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"
Quoting Alvaro Herrera <alvherre@dcc.uchile.cl>:
> 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
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"
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 decibel@decibel.org 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?"
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.
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
> 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 (brew@theMode.com)
Check out my Stock Option Covered Call website http://www.callpix.com
and my Musician's Online Database Exchange http://www.TheMode.com
==========================================================================
Centuries ago, Nostradamus foresaw when jfradkin@wazagua.com ("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.