pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Joel's Performance Issues WAS : Opteron vs Xeon
41+ messages / 19 participants
[nested] [flat]
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 18:53 Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 41+ messages in thread
From: Josh Berkus @ 2005-04-20 18:53 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-performance
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 developers two months of work and
> trying MYSQL.
I'd suggest testing your *whole* application and not just this one query. And
remember that you need to test InnoDB tables if you want transactions.
>
> I have posted several items and not got a response (not that I expect folks
> to drop everything). I want to thank everyone who has been of help and
> there are several.
Hmmm ... I see about 25 responses to some of your posts on this list.
Including ones by some of our head developers. That's more than you'd get
out of a paid MSSQL support contract, I know from experience.
If you want anything more, then you'll need a "do-or-die" contract with a
support company. If your frustration is because you can't find this kind of
help than I completely understand ... I have a waiting list for performance
contracts myself. (and, if you hired me the first thing I'd tell you is to
junk the Dell)
> I really like the environment and feel I have learned a lot in the past few
> months, but bottom line for me is speed. We bought a 30K Dell 6650 to get
> better performance.
Would have been smart to ask on this list *before* buying the Dell, hey? Even
a Google of this mailing list would have been informative.
> I chose PG because MSSQL was 70K to license. I believe
> the MYSQL will be 250.00 to license for us, but I may choose the 4k
> platinum support just to feel safe about having some one to touch base with
> in the event of an issue.
Hmmm ... you're willing to pay MySQL $4k but expect the PG community to solve
all your problems with free advice and a couple $100 with CMD? I sense an
apples vs. barca loungers comparison here ...
> I am not sure I am walking away feeling real good about
> postgres, because it just should not take a rocket scientist to get it to
> work, and I used to think I was fairly smart and could figure stuff out and
> I hate admitting defeat (especially since we have everything working with
> postgres now).
While I understand your frustration (I've been frustrated more than a few
times with issues that stump me on Linux, for example) it's extremely unfair
to lash out at a community that has provided you a lot of free advice because
the advice hasn't fixed everything yet. By my reading, you first raised your
query issue 6 days ago. 6 days is not a lot of time for getting *free*
troubleshooting help by e-mail. Certainly it's going to take more than 6 days
to port to MySQL.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 19:52 Joel Fradkin <jfradkin@wazagua.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-20 19:52 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
Sorry if you feel I am lashing out at a community.
Just to say it again, I am very appreciative of all the help everyone has
supplied.
I am running on more then just the 4 proc Dell (in fact my tests have been
mostly on desktops).
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 was under the
impression there were still issues with 64bit postgres and Linux (or at
least were when I purchased). I believed I could make my next aquistion a
opteron based hardware.
Again I am not at all trying to critasize any one, so please except my
apology if I some how came across with that attitude. I am very disappointed
at this point. My views may not be that great (although I am not saying that
either), but they run ok on MSSQL and appear to run ok on MYSQL.
I wish I did understand what I am doing wrong because I do not wish to
revisit engineering our application for MYSQL.
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.
I agree testing the whole app is the only way to see and unfortunately it is
a time consuming bit. I do not have to spend 4k on MYSQL, that is if I want
to have their premium support. I can spend $250.00 a server for the
commercial license if I find the whole app does run well. I just loaded the
data last night and only had time to convert one view this morning. I am
sure it is something I do not understand and not a problem with postgres. I
also am willing to take time to get more knowledgeable, but my time is
running out and I feel honestly stupid.
I have been in the process of converting for over two months and have said
several times these lists are a godsend.
It was never my intention to make you feel like I was flaming anyone
involved. On the contrary, I feel many have taken time to look at my
questions and given excellent advice. I know I check the archives so
hopefully that time will help others after me.
I may yet find that MYSQL is not a good fit as well. I have my whole app
converted at this point and find pg works well for a lot of my usage.
There are some key reporting views that need to retrieve many rows with many
joins that just take too long to pull the data. I told my boss just now that
if I try to de-normalize many of these data sets (like 6 main groups of data
that the reporting may work, but as is many of my web pages are timing out
(these are pages that still work on MSSQL and the 2 proc machine).
Thanks again for all the help and know I truly appreciate what time every
one has spent on my issues.
I may find that revisiting the datasets is a way to make PG work, or as you
mentioned maybe I can get some one with more knowledge to step in locally. I
did ask Tom if he knew of anyone, maybe some one else on the list is aware
of a professional in the Tampa FL area.
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 solely on the performance on the 4 proc box.
Joel Fradkin
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
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
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 developers two months of work and
> trying MYSQL.
I'd suggest testing your *whole* application and not just this one query.
And
remember that you need to test InnoDB tables if you want transactions.
>
> I have posted several items and not got a response (not that I expect
folks
> to drop everything). I want to thank everyone who has been of help and
> there are several.
Hmmm ... I see about 25 responses to some of your posts on this list.
Including ones by some of our head developers. That's more than you'd get
out of a paid MSSQL support contract, I know from experience.
If you want anything more, then you'll need a "do-or-die" contract with a
support company. If your frustration is because you can't find this kind of
help than I completely understand ... I have a waiting list for performance
contracts myself. (and, if you hired me the first thing I'd tell you is to
junk the Dell)
> I really like the environment and feel I have learned a lot in the past
few
> months, but bottom line for me is speed. We bought a 30K Dell 6650 to get
> better performance.
Would have been smart to ask on this list *before* buying the Dell, hey?
Even
a Google of this mailing list would have been informative.
> I chose PG because MSSQL was 70K to license. I believe
> the MYSQL will be 250.00 to license for us, but I may choose the 4k
> platinum support just to feel safe about having some one to touch base
with
> in the event of an issue.
Hmmm ... you're willing to pay MySQL $4k but expect the PG community to
solve
all your problems with free advice and a couple $100 with CMD? I sense an
apples vs. barca loungers comparison here ...
> I am not sure I am walking away feeling real good about
> postgres, because it just should not take a rocket scientist to get it to
> work, and I used to think I was fairly smart and could figure stuff out
and
> I hate admitting defeat (especially since we have everything working with
> postgres now).
While I understand your frustration (I've been frustrated more than a few
times with issues that stump me on Linux, for example) it's extremely unfair
to lash out at a community that has provided you a lot of free advice
because
the advice hasn't fixed everything yet. By my reading, you first raised
your
query issue 6 days ago. 6 days is not a lot of time for getting *free*
troubleshooting help by e-mail. Certainly it's going to take more than 6
days
to port to MySQL.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 20:22 Anjan Dave <adave@vantage.com>
0 siblings, 0 replies; 41+ messages in thread
From: Anjan Dave @ 2005-04-20 20:22 UTC (permalink / raw)
To: josh@agliodbs.com; Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-performance
He is running RHAS4, which is the latest 2.6.x kernel from RH. I believe
it should have done away with the RHAS3.0 Update 3 IO issue.
anjan
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, April 20, 2005 4:23 PM
To: Joel Fradkin
Cc: pgsql-performance@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, but I have all Dell hardware
now
> and resist trying different vendors just to suit Postgres. I was under
the
> impression there were still issues with 64bit postgres and Linux (or
at
> least were when I purchased). I believed I could make my next
aquistion a
> opteron based hardware.
Yeah, sorry, the Dell stuff is a sore point with me. You can't imagine
the
number of conversations I have that go like this:
"We're having a severe performance problem with PostgreSQL"
"What hardware/OS are you using?"
"Dell *650 with RHAS 3.0 ...."
BTW, which Update version is your RHAS? If you're on Update3, you can
grab
more performance right there by upgrading to Update4.
> Again I am not at all trying to critasize any one, so please except my
> apology if I some how came across with that attitude. I am very
> disappointed at this point. My views may not be that great (although I
am
> not saying that either), but they run ok on MSSQL and appear to run ok
on
> MYSQL.
Yeah. I think you'll find a few things that are vice-versa. For that
matter, I can point to a number of queries we run better than Oracle,
and a
number we don't.
Your particular query problem seems to stem from some bad estimates.
Can you
post an EXPLAIN ANALYZE based on all the advice people have given you so
far?
> I wish I did understand what I am doing wrong because I do not wish to
> revisit engineering our application for MYSQL.
I can imagine.
> 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.
Yes. For that matter, it'll take longer to troubleshoot on this list
because
of your security concerns.
> I agree testing the whole app is the only way to see and unfortunately
it
> is a time consuming bit. I do not have to spend 4k on MYSQL, that is
if I
> want to have their premium support. I can spend $250.00 a server for
the
> commercial license if I find the whole app does run well. I just
loaded the
> data last night and only had time to convert one view this morning. I
am
> sure it is something I do not understand and not a problem with
postgres. I
> also am willing to take time to get more knowledgeable, but my time is
> running out and I feel honestly stupid.
You're not. You have a real query problem and it will require further
troubleshooting to solve. Some of us make a pretty handsome living
solving
these kinds of problems, it take a lot of expert knowledge.
> It was never my intention to make you feel like I was flaming anyone
> involved. On the contrary, I feel many have taken time to look at my
> questions and given excellent advice. I know I check the archives so
> hopefully that time will help others after me.
Well, I overreacted too. Sorry!
> I may find that revisiting the datasets is a way to make PG work, or
as you
> mentioned maybe I can get some one with more knowledge to step in
locally.
> I did ask Tom if he knew of anyone, maybe some one else on the list is
> aware of a professional in the Tampa FL area.
Well, Robert Treat is in Florida but I'm pretty sure he's busy
full-time.
> 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 solely on the performance on the 4 proc box.
Oh, certainly it's too late to buy a Sunfire or eServer instead. You
just
could have gotten far more bang for the buck with some expert advice,
that's
all. But don't bother with Dell support any further, they don't really
have
the knowledge to help you.
So ... new EXPLAIN ANALYZE ?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 20:22 Josh Berkus <josh@agliodbs.com>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 1 reply; 41+ messages in thread
From: Josh Berkus @ 2005-04-20 20:22 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-performance
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 was under the
> impression there were still issues with 64bit postgres and Linux (or at
> least were when I purchased). I believed I could make my next aquistion a
> opteron based hardware.
Yeah, sorry, the Dell stuff is a sore point with me. You can't imagine the
number of conversations I have that go like this:
"We're having a severe performance problem with PostgreSQL"
"What hardware/OS are you using?"
"Dell *650 with RHAS 3.0 ...."
BTW, which Update version is your RHAS? If you're on Update3, you can grab
more performance right there by upgrading to Update4.
> Again I am not at all trying to critasize any one, so please except my
> apology if I some how came across with that attitude. I am very
> disappointed at this point. My views may not be that great (although I am
> not saying that either), but they run ok on MSSQL and appear to run ok on
> MYSQL.
Yeah. I think you'll find a few things that are vice-versa. For that
matter, I can point to a number of queries we run better than Oracle, and a
number we don't.
Your particular query problem seems to stem from some bad estimates. Can you
post an EXPLAIN ANALYZE based on all the advice people have given you so far?
> I wish I did understand what I am doing wrong because I do not wish to
> revisit engineering our application for MYSQL.
I can imagine.
> 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.
Yes. For that matter, it'll take longer to troubleshoot on this list because
of your security concerns.
> I agree testing the whole app is the only way to see and unfortunately it
> is a time consuming bit. I do not have to spend 4k on MYSQL, that is if I
> want to have their premium support. I can spend $250.00 a server for the
> commercial license if I find the whole app does run well. I just loaded the
> data last night and only had time to convert one view this morning. I am
> sure it is something I do not understand and not a problem with postgres. I
> also am willing to take time to get more knowledgeable, but my time is
> running out and I feel honestly stupid.
You're not. You have a real query problem and it will require further
troubleshooting to solve. Some of us make a pretty handsome living solving
these kinds of problems, it take a lot of expert knowledge.
> It was never my intention to make you feel like I was flaming anyone
> involved. On the contrary, I feel many have taken time to look at my
> questions and given excellent advice. I know I check the archives so
> hopefully that time will help others after me.
Well, I overreacted too. Sorry!
> I may find that revisiting the datasets is a way to make PG work, or as you
> mentioned maybe I can get some one with more knowledge to step in locally.
> I did ask Tom if he knew of anyone, maybe some one else on the list is
> aware of a professional in the Tampa FL area.
Well, Robert Treat is in Florida but I'm pretty sure he's busy full-time.
> 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 solely on the performance on the 4 proc box.
Oh, certainly it's too late to buy a Sunfire or eServer instead. You just
could have gotten far more bang for the buck with some expert advice, that's
all. But don't bother with Dell support any further, they don't really have
the knowledge to help you.
So ... new EXPLAIN ANALYZE ?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 20:42 John A Meinel <john@arbash-meinel.com>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 1 reply; 41+ messages in thread
From: John A Meinel @ 2005-04-20 20:42 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: josh@agliodbs.com; pgsql-performance
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 could turn all strings into some sort of
hashed version, so you don't give out any identifiable information.
It could even modify relational entries, as long as it updated both
ends, and this didn't affect the actual performance at all.
I don't think this would be very hard to write. Especially if you can
give a list of the tables, and what columns need to be modified.
Probably this would generally be a useful script to have for cases like
this where databases are confidential, but need to be tuned by someone else.
Would that be reasonable?
I would think that by renaming columns, hashing the data in the columns,
and renaming tables, most of the proprietary information is removed,
without removing the database information.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-20 21:01 Vivek Khera <vivek@khera.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 41+ messages in thread
From: Vivek Khera @ 2005-04-20 21:01 UTC (permalink / raw)
To: pgsql-performance
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 solely on the performance on the 4 proc box.
>
> Oh, certainly it's too late to buy a Sunfire or eServer instead. You
> just
> could have gotten far more bang for the buck with some expert advice,
> that's
> all. But don't bother with Dell support any further, they don't
> really have
> the knowledge to help you.
>
FWIW, I have a $20k Dell box (PE2650 with 14-disk external PowerVault
RAID enclosure) which I'm phasing out for a dual opteron box because it
can't handle the load. It will be re-purposed as a backup system.
Damn waste of money, but complaining customers can cost more...
Trust me, it is likely your Dell hardware, as moving to the Opteron
system has improved performance tremendously with fewer disks. Same
amount of RAM and other basic configurations. Both have LSI based RAID
cards, even.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 00:26 Joel Fradkin <jfradkin@wazagua.com>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 3 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-21 00:26 UTC (permalink / raw)
To: 'John A Meinel' <john@arbash-meinel.com>; +Cc: josh@agliodbs.com; pgsql-performance
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. This still might be configuration issue (I ran
on my 2gig desktop and the 8 gig Linux box comparisons were all ran on the
same machines as far MSSQL, MYSQL, and Postgres.
I turned off postgres when running MYSQL and turned off MYSQL when running
postgres, MSSQL had one of the two running while I tested it.
For the 360,000 records returned MYSQL did it in 40 seconds first run and 17
seconds second run.
MSSQL did it in 56 seconds first run and 16 seconds second run.
Postgres was on the second run
Total query runtime: 17109 ms.
Data retrieval runtime: 72188 ms.
331640 rows retrieved.
So like 89 on the second run.
The first run was 147 secs all told.
These are all on my 2 meg desktop running XP.
I can post the config. I noticed the postgres was using 70% of the cpu while
MSSQL was 100%.
Joel Fradkin
>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 could turn all strings into some sort of
hashed version, so you don't give out any identifiable information.
It could even modify relational entries, as long as it updated both
ends, and this didn't affect the actual performance at all.
I don't think this would be very hard to write. Especially if you can
give a list of the tables, and what columns need to be modified.
Probably this would generally be a useful script to have for cases like
this where databases are confidential, but need to be tuned by someone else.
Would that be reasonable?
I would think that by renaming columns, hashing the data in the columns,
and renaming tables, most of the proprietary information is removed,
without removing the database information.
John
=:->
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 00:45 John A Meinel <john@arbash-meinel.com>
parent: Joel Fradkin <jfradkin@wazagua.com>
2 siblings, 2 replies; 41+ messages in thread
From: John A Meinel @ 2005-04-21 00:45 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; pgsql-performance
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 still a bit confused. This still might be configuration issue (I ran
>on my 2gig desktop and the 8 gig Linux box comparisons were all ran on the
>same machines as far MSSQL, MYSQL, and Postgres.
>I turned off postgres when running MYSQL and turned off MYSQL when running
>postgres, MSSQL had one of the two running while I tested it.
>
>For the 360,000 records returned MYSQL did it in 40 seconds first run and 17
>seconds second run.
>
>MSSQL did it in 56 seconds first run and 16 seconds second run.
>
>Postgres was on the second run
>Total query runtime: 17109 ms.
>Data retrieval runtime: 72188 ms.
>331640 rows retrieved.
>
>So like 89 on the second run.
>The first run was 147 secs all told.
>
>These are all on my 2 meg desktop running XP.
>I can post the config. I noticed the postgres was using 70% of the cpu while
>MSSQL was 100%.
>
>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.
I suppose knowing your work_mem, and shared_buffers settings would be
useful.
How were you measuring "data retrieval time"? And how does this compare
to what you were measuring on the other machines? It might be possible
that what you are really measuring is just the time it takes psql to
load up all the data into memory, and then print it out. And since psql
defaults to measuring entry lengths for each column, this may not be
truly comparable.
It *looks* like it only takes 18s for postgres to get the data, but then
it is taking 72s to transfer the data to you. That would be network
latency, or something like that, not database latency.
And I would say that 18s is very close to 16 or 17 seconds.
I don't know what commands you were issuing, or how you measured,
though. You might be using some other interface (like ODBC), which I
can't say too much about.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 03:32 Josh Berkus <josh@agliodbs.com>
parent: Joel Fradkin <jfradkin@wazagua.com>
2 siblings, 0 replies; 41+ messages in thread
From: Josh Berkus @ 2005-04-21 03:32 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-performance
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 STATISTICS 1000;
ALTER TABLE tbllocation ALTER COLUMN regionid SET STATISTICS 1000;
ANALYZE tblresponseheader;
ANALYZE tbllocation;
Then run the EXPLAIN ANALYZE again. (on Linux)
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 04:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: John A Meinel <john@arbash-meinel.com>
1 sibling, 1 reply; 41+ messages in thread
From: Tom Lane @ 2005-04-21 04:35 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: Joel Fradkin <jfradkin@wazagua.com>; pgsql-performance
John A Meinel <john@arbash-meinel.com> 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 before suggesting that
pgadmin can be amazingly slow, eg here
http://archives.postgresql.org/pgsql-performance/2004-10/msg00427.php
where the *actual* data retrieval time as shown by EXPLAIN ANALYZE
was under three seconds, but pgadmin claimed the query runtime was 22
sec and data retrieval runtime was 72 sec.
I wouldn't be too surprised if that time was being spent formatting
the data into a table for display inside pgadmin. It is a GUI after
all, not a tool for pushing vast volumes of data around.
It'd be interesting to check the runtimes for the same query with
LIMIT 3000, ie, see if a tenth as much data takes a tenth as much
processing time or not. The backend code should be pretty darn
linear in this regard, but maybe pgadmin isn't.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 12:56 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 41+ messages in thread
From: Merlin Moncure @ 2005-04-21 12:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance; Joel Fradkin <jfradkin@wazagua.com>
> John A Meinel <john@arbash-meinel.com> 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 before suggesting
that
> pgadmin can be amazingly slow, eg here
> http://archives.postgresql.org/pgsql-performance/2004-10/msg00427.php
> where the *actual* data retrieval time as shown by EXPLAIN ANALYZE
> was under three seconds, but pgadmin claimed the query runtime was 22
> sec and data retrieval runtime was 72 sec.
The problem is that pgAdmin takes your query results and puts it in a
grid. The grid is not designed to be used in that way for large
datasets. The time complexity is not linear and really breaks down
around 10k-100k rows depending on various factors. pgAdmin users just
have to become used to it and use limit or the filter feature at
appropriate times.
The ms sql enterprise manager uses cursors which has its own set of
nasty issues (no mvcc).
In fairness, unless you are running with \a switch, psql adds a fair
amount of time to the query too.
Joel:
"Postgres was on the second run
Total query runtime: 17109 ms.
Data retrieval runtime: 72188 ms.
331640 rows retrieved."
The Data retrieval runtime is time spend by pgAdmin formatting, etc.
The query runtime is the actual timing figure you should be concerned
with (you are not comparing apples to apples). I can send you a utility
I wrote in Delphi which adds only a few seconds overhead for 360k result
set. Or, go into psql, throw \a switch, and run query.
or:
psql -A -c "select * from myview where x" > output.txt
it should finish the above in 16-17 sec plus the time to write out the
file.
Joel, I have a lot of experience with all three databases you are
evaluating and you are making a huge mistake switching to mysql. you
can make a decent case for ms sql, but it's quite expensive at your
level of play as you know.
Merlin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 13:05 Andreas Pflug <pgadmin@pse-consulting.de>
parent: Joel Fradkin <jfradkin@wazagua.com>
2 siblings, 0 replies; 41+ messages in thread
From: Andreas Pflug @ 2005-04-21 13:05 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: John A Meinel <john@arbash-meinel.com>; josh@agliodbs.com; pgsql-performance
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 still a bit confused. This still might be configuration issue (I ran
> on my 2gig desktop and the 8 gig Linux box comparisons were all ran on the
> same machines as far MSSQL, MYSQL, and Postgres.
> I turned off postgres when running MYSQL and turned off MYSQL when running
> postgres, MSSQL had one of the two running while I tested it.
>
> For the 360,000 records returned MYSQL did it in 40 seconds first run and 17
> seconds second run.
>
> MSSQL did it in 56 seconds first run and 16 seconds second run.
>
> Postgres was on the second run
> Total query runtime: 17109 ms.
> Data retrieval runtime: 72188 ms.
> 331640 rows retrieved.
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.
Regards,
Andreas
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 13:53 Joel Fradkin <jfradkin@wazagua.com>
parent: John A Meinel <john@arbash-meinel.com>
1 sibling, 1 reply; 41+ messages in thread
From: Joel Fradkin @ 2005-04-21 13:53 UTC (permalink / raw)
To: 'John A Meinel' <john@arbash-meinel.com>; pgsql-performance
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 much worse on some of my views, which is
what forced me to start looking at other options.
I suppose knowing your work_mem, and shared_buffers settings would be
useful. I have posted my configs, but will add the Tampa to the bottom
again. My desktop has
# - Memory -
shared_buffers = 8000 # min 16, at least max_connections*2, 8KB
each
work_mem = 8000#1024 # min 64, size in KB
maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 30000#20000 # min max_fsm_relations*16, 6 bytes
each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Planner Cost Constants -
effective_cache_size = 80000#1000 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
How were you measuring "data retrieval time"? And how does this compare
to what you were measuring on the other machines? It might be possible
that what you are really measuring is just the time it takes psql to
load up all the data into memory, and then print it out. And since psql
defaults to measuring entry lengths for each column, this may not be
truly comparable.
It *looks* like it only takes 18s for postgres to get the data, but then
it is taking 72s to transfer the data to you. That would be network
latency, or something like that, not database latency.
And I would say that 18s is very close to 16 or 17 seconds.
This was ran on the machine with database (as was MYSQL and MSSQL).
The PG timing was from PGADMIN and the 18 secs was second run, first run was
Same time to return the data and 70 secs to do the first part like 147 secs
all told, compared to the 40 seconds first run of MYSQL and 56 Seconds
MSSQL. MYSQL was done in their query tool, it returns the rows as well and
MSSQL was done in their query analyzer. All three tools appear to use a
similar approach. Just an FYI doing an explain analyze of my problem view
took much longer then actually returning the data in MSSQL and MYSQL. I have
done extensive testing with MYSQL (just this table and two of my problem
views). I am not using the transactional version, because I need the best
speed.
I don't know what commands you were issuing, or how you measured,
though. You might be using some other interface (like ODBC), which I
can't say too much about.
John
=:->
This is the Linux box config.
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the postmaster.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload". Some settings, such as listen_address, require
# a postmaster shutdown and restart to take effect.
#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.
# data_directory = 'ConfigDir' # use data in another directory
#data_directory = '/pgdata/data'
# hba_file = 'ConfigDir/pg_hba.conf' # the host-based authentication file
# ident_file = 'ConfigDir/pg_ident.conf' # the IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
# external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
listen_addresses = '*'
port = 5432
max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from
shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 12288 #5000 min 16, at least max_connections*2, 8KB each
#work_mem = 1024 # min 64, size in KB
work_mem = 16384 # 8192
#maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 100000 #30000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1500 #1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_pagE_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits
# - Background writer -
#bgwriter_delay = 200 # 10-10000 milliseconds between rounds
#bgwriter_percent = 1 # 0-100% of dirty buffers in each round
#bgwriter_maxpages = 100 # 0-1000 buffers max per round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
wal_sync_method = open_sync# fsync # the default varies across
platforms:
# fsync, fdatasync, open_sync, or
open_datasync
wal_buffers = 2048#8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 100 #3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
# - Archiving -
#archive_command = '' # command to use to archive a logfile
segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
enable_mergejoin = false
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 262144 #40000 typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
random_page_cost = 2
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
default_statistics_target = 250#10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
redirect_stderr = true # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
log_directory = 'pg_log' # Directory where log files are written.
# May be specified absolute or relative to
PGDATA
log_filename = 'postgresql-%a.log' # Log file name pattern.
# May include strftime() escapes
log_truncate_on_rotation = true # If true, any existing log file of the
# same name as the new log file will be
truncated
# rather than appended to. But such truncation
# only occurs on time-driven rotation,
# not on restarts or size-driven rotation.
# Default is false, meaning append to existing
# files in all cases.
log_rotation_age = 1440 # Automatic rotation of logfiles will happen
after
# so many minutes. 0 to disable.
log_rotation_size = 0 # Automatic rotation of logfiles will happen
after
# so many kilobytes of log output. 0 to
disable.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log,
fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error,
panic(off)
#log_min_duration_statement = -1 # -1 is disabled, in milliseconds.
#silent_mode = false # DO NOT USE without syslog or
redirect_stderr
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_disconnections = false
#log_duration = false
#log_line_prefix = '' # e.g. '<%u%%%d> '
# %u=user name %d=database name
# %r=remote host and port
# %p=PID %t=timestamp %i=command tag
# %c=session id %l=session line number
# %s=session start timestamp %x=transaction
id
# %q=stop here in non-session processes
# %%='%'
#log_statement = 'none' # none, mod, ddl, all
#log_hostname = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false
# - Query/Index Statistics Collector -
#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, or '' for default
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they might be changed
lc_messages = 'en_US.UTF-8' # locale for system error message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
#default_with_oids = true
# - Other Platforms & Clients -
#transform_null_equals = false
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 14:36 Joel Fradkin <jfradkin@wazagua.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-21 14:36 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; 'John A Meinel' <john@arbash-meinel.com>; +Cc: pgsql-performance
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 running out of
steam but been good until this year when we about doubled our demand by
adding sears as a client).
Using odbc to postgres on some of the views (Josh from Command is having me
do some very specific testing) is timing out with a 10 minute time limit.
These are pages that still respond using MSSQL (this is wehere production is
using the duel proc and the test is using the 4 proc).
I have a tool that hooks to all three databases so I can try it with that
and see if I get different responses.
Joel
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 14:42 Mohan, Ross <RMohan@arbinet.com>
0 siblings, 1 reply; 41+ messages in thread
From: Mohan, Ross @ 2005-04-21 14:42 UTC (permalink / raw)
To: pgsql-performance
FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the like.
Maybe one of the ODBC cognoscenti here can chime in more concretely....
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joel Fradkin
Sent: Thursday, April 21, 2005 10:36 AM
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 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 running out of steam but been good until this year when we about doubled our demand by adding sears as a client).
Using odbc to postgres on some of the views (Josh from Command is having me do some very specific testing) is timing out with a 10 minute time limit. These are pages that still respond using MSSQL (this is wehere production is using the duel proc and the test is using the 4 proc).
I have a tool that hooks to all three databases so I can try it with that and see if I get different responses.
Joel
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 14:53 Joel Fradkin <jfradkin@wazagua.com>
parent: Mohan, Ross <RMohan@arbinet.com>
0 siblings, 0 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-21 14:53 UTC (permalink / raw)
To: 'Mohan, Ross' <RMohan@arbinet.com>; +Cc: pgsql-odbc@postgresql.org; pgsql-performance
Here is the connect string I am using.
It could be horrid as I cut it from ODBC program.
Session("StringConn") =
"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar
charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;Us
eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Pa
rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableC
ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS
erverSidePrepare=0"
Joel Fradkin
-----Original Message-----
From: Mohan, Ross [mailto:RMohan@arbinet.com]
Sent: Thursday, April 21, 2005 9:42 AM
To: jfradkin@wazagua.com
Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the
like.
Maybe one of the ODBC cognoscenti here can chime in more concretely....
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-21 15:01 Mohan, Ross <RMohan@arbinet.com>
0 siblings, 1 reply; 41+ messages in thread
From: Mohan, Ross @ 2005-04-21 15:01 UTC (permalink / raw)
To: pgsql-odbc@postgresql.org
Joel, thanks. A couple of things jump out there for
me, not a problem for a routine ODBC connection, but
perhaps in the "lotsa stuff" context of your current
explorations, it might be relevant?
I am completely shooting from the hip, here, but...if
it were my goose to cook, I'd be investigating
Session("StringConn") = "DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;
|| Protocol? Is this related to version? is the driver waaaay old?
FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;
|| Fetch great for OLTP, lousy for batch?
Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
|| what ARE the datatypes and sizes in your particular case?
Debug=0;
|| a run with debug=1 probably would spit up something interesting....
CommLog=0;Optimizer=1;
|| Optimizer? that's a new one on me....
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0"
|| that's about all I can see, prima facie. I'll be very curious to know if ODBC is
any part of your performance equation.
HTH,
Ross
-----Original Message-----
From: Joel Fradkin [mailto:jfradkin@wazagua.com]
Sent: Thursday, April 21, 2005 10:54 AM
To: Mohan, Ross
Cc: pgsql-odbc@postgresql.org; 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=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar
charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;Us
eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Pa
rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableC
ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS
erverSidePrepare=0"
Joel Fradkin
-----Original Message-----
From: Mohan, Ross [mailto:RMohan@arbinet.com]
Sent: Thursday, April 21, 2005 9:42 AM
To: jfradkin@wazagua.com
Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the like.
Maybe one of the ODBC cognoscenti here can chime in more concretely....
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 08:08 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 1 reply; 41+ messages in thread
From: Dave Page @ 2005-04-22 08:08 UTC (permalink / raw)
To: Andreas Pflug <pgadmin@pse-consulting.de>; Joel Fradkin <jfradkin@wazagua.com>; +Cc: John A Meinel <john@arbash-meinel.com>; josh@agliodbs.com; pgsql-performance
> -----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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 09:47 Andreas Pflug <pgadmin@pse-consulting.de>
parent: Dave Page <dpage@vale-housing.co.uk>
0 siblings, 1 reply; 41+ messages in thread
From: Andreas Pflug @ 2005-04-22 09:47 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 17:51 Joel Fradkin <jfradkin@wazagua.com>
parent: Andreas Pflug <pgadmin@pse-consulting.de>
0 siblings, 2 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-22 17:51 UTC (permalink / raw)
To: 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; +Cc: pgsql-performance; ac@wazagua.com; Joshua D. Drake <jd@commandprompt.com>; wheyliger@wazagua.com; 'Steve Hatt' <shatt@wazagua.com>
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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 18:03 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 0 replies; 41+ messages in thread
From: Bruce Momjian @ 2005-04-22 18:03 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-performance@postgresql.org, ac@wazagua.com, "Joshua D. Drake" <jd@commandprompt.com>; wheyliger@wazagua.com, "'Steve Hatt'" <shatt@wazagua.com>
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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 18:30 Alvaro Herrera <alvherre@dcc.uchile.cl>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 2 replies; 41+ messages in thread
From: Alvaro Herrera @ 2005-04-22 18:30 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-performance@postgresql.org, ac@wazagua.com, "Joshua D. Drake" <jd@commandprompt.com>; wheyliger@wazagua.com, "'Steve Hatt'" <shatt@wazagua.com>
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"
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 20:53 Mischa Sandberg <mischa.sandberg@telus.net>
parent: Alvaro Herrera <alvherre@dcc.uchile.cl>
1 sibling, 0 replies; 41+ messages in thread
From: Mischa Sandberg @ 2005-04-22 20:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: Joel Fradkin <jfradkin@wazagua.com>; Andreas Pflug <pgadmin@pse-consulting.de>; Dave Page <dpage@vale-housing.co.uk>; pgsql-performance@postgresql.org, ac@wazagua.com, "Joshua D. Drake" <jd@commandprompt.com>; wheyliger@wazagua.com, 'Steve Hatt' <shatt@wazagua.com>
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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-22 21:04 Joel Fradkin <jfradkin@wazagua.com>
parent: Alvaro Herrera <alvherre@dcc.uchile.cl>
1 sibling, 1 reply; 41+ messages in thread
From: Joel Fradkin @ 2005-04-22 21:04 UTC (permalink / raw)
To: 'Alvaro Herrera' <alvherre@dcc.uchile.cl>; +Cc: 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-performance; ac@wazagua.com; 'Joshua D. Drake' <jd@commandprompt.com>; wheyliger@wazagua.com; 'Steve Hatt' <shatt@wazagua.com>
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"
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 02:01 Jim C. Nasby <decibel@decibel.org>
parent: Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 1 reply; 41+ messages in thread
From: Jim C. Nasby @ 2005-04-23 02:01 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: John A Meinel <john@arbash-meinel.com>; pgsql-performance
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 could use \timing.
In any case, it's not valid to use pgadmin to time things.
--
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?"
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 02:11 Jim C. Nasby <decibel@decibel.org>
parent: Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 1 reply; 41+ messages in thread
From: Jim C. Nasby @ 2005-04-23 02:11 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Andreas Pflug <pgadmin@pse-consulting.de>; Dave Page <dpage@vale-housing.co.uk>; pgsql-performance@postgresql.org, ac@wazagua.com, "'Joshua D. Drake'" <jd@commandprompt.com>; wheyliger@wazagua.com, 'Steve Hatt' <shatt@wazagua.com>
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?"
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 06:30 Kevin Brown <kevin@sysexperts.com>
parent: Jim C. Nasby <decibel@decibel.org>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Brown @ 2005-04-23 06:30 UTC (permalink / raw)
To: pgsql-performance
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 ignore the output?
> If so, you could use \timing.
Would timing "SELECT COUNT(*) FROM (query)" work?
--
Kevin Brown kevin@sysexperts.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 14:11 Joel Fradkin <jfradkin@wazagua.com>
parent: Jim C. Nasby <decibel@decibel.org>
0 siblings, 2 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-23 14:11 UTC (permalink / raw)
To: 'Jim C. Nasby' <decibel@decibel.org>; +Cc: 'Alvaro Herrera' <alvherre@dcc.uchile.cl>; 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-performance; ac@wazagua.com; 'Joshua D. Drake' <jd@commandprompt.com>; wheyliger@wazagua.com; 'Steve Hatt' <shatt@wazagua.com>
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.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 14:18 Joel Fradkin <jfradkin@wazagua.com>
parent: Mohan, Ross <RMohan@arbinet.com>
0 siblings, 1 reply; 41+ messages in thread
From: Joel Fradkin @ 2005-04-23 14:18 UTC (permalink / raw)
To: 'Mohan, Ross' <RMohan@arbinet.com>; pgsql-odbc@postgresql.org
Hate to be dumb, but unfortunately I am.
Could you give me an idea what I should be using, or is there a good
resource for me to check out.
I have been spending so much time with config and moving data, converting
etc, I never looked at the odbc settings (didn't even think about it until
Josh brought it up). I did ask him for his advice, but would love a second
opinion.
Our data is a bit of a mixture, some records have text items most are
varchars and integers with a bit of Booleans mixed in.
I am running 8.0.2 so not sure if the protocol is ODBC or Postgres?
Thanks for responding I appreciate any help
Joel Fradkin
-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mohan, Ross
Sent: Thursday, April 21, 2005 10:01 AM
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs
Xeon
Joel, thanks. A couple of things jump out there for
me, not a problem for a routine ODBC connection, but
perhaps in the "lotsa stuff" context of your current
explorations, it might be relevant?
I am completely shooting from the hip, here, but...if
it were my goose to cook, I'd be investigating
Session("StringConn") =
"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;
|| Protocol? Is this related to version? is the driver waaaay old?
FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;
|| Fetch great for OLTP, lousy for batch?
Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
|| what ARE the datatypes and sizes in your particular case?
Debug=0;
|| a run with debug=1 probably would spit up something interesting....
CommLog=0;Optimizer=1;
|| Optimizer? that's a new one on me....
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAs
Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;Up
datableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinar
y=0;UseServerSidePrepare=0"
|| that's about all I can see, prima facie. I'll be very curious to know
if ODBC is
any part of your performance equation.
HTH,
Ross
-----Original Message-----
From: Joel Fradkin [mailto:jfradkin@wazagua.com]
Sent: Thursday, April 21, 2005 10:54 AM
To: Mohan, Ross
Cc: pgsql-odbc@postgresql.org; 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=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar
charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;Us
eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Pa
rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableC
ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS
erverSidePrepare=0"
Joel Fradkin
-----Original Message-----
From: Mohan, Ross [mailto:RMohan@arbinet.com]
Sent: Thursday, April 21, 2005 9:42 AM
To: jfradkin@wazagua.com
Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the
like.
Maybe one of the ODBC cognoscenti here can chime in more concretely....
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 15:35 Joshua D. Drake <jd@commandprompt.com>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 1 reply; 41+ messages in thread
From: Joshua D. Drake @ 2005-04-23 15:35 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Jim C. Nasby' <decibel@decibel.org>; 'Alvaro Herrera' <alvherre@dcc.uchile.cl>; 'Andreas Pflug' <pgadmin@pse-consulting.de>; 'Dave Page' <dpage@vale-housing.co.uk>; pgsql-performance@postgresql.org, ac@wazagua.com, wheyliger@wazagua.com, "'Steve Hatt'" <shatt@wazagua.com>
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
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 16:03 brew@theMode.com
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 41+ messages in thread
From: brew@theMode.com @ 2005-04-23 16:03 UTC (permalink / raw)
To: pgsql-performance
> 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
==========================================================================
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 18:27 Christopher Browne <cbbrowne@acm.org>
parent: Joel Fradkin <jfradkin@wazagua.com>
1 sibling, 0 replies; 41+ messages in thread
From: Christopher Browne @ 2005-04-23 18:27 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-23 18:27 Josh Berkus <josh@agliodbs.com>
parent: Kevin Brown <kevin@sysexperts.com>
0 siblings, 1 reply; 41+ messages in thread
From: Josh Berkus @ 2005-04-23 18:27 UTC (permalink / raw)
To: Kevin Brown <kevin@sysexperts.com>; +Cc: pgsql-performance
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 ignore the output?
> > If so, you could use \timing.
>
> Would timing "SELECT COUNT(*) FROM (query)" work?
Just \timing would work fine; PostgreSQL doesn't return anything until it has
the whole result set. That's why MSSQL vs. PostgreSQL timing comparisons are
deceptive unless you're careful: MSSQL returns the results on block at a
time, and reports execution time as the time required to return the *first*
block, as opposed to Postgres which reports the time required to return the
whole dataset.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-24 07:15 Marko Ristola <marko.ristola@kolumbus.fi>
parent: Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 2 replies; 41+ messages in thread
From: Marko Ristola @ 2005-04-24 07:15 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: 'Mohan, Ross' <RMohan@arbinet.com>; pgsql-odbc@postgresql.org; pgsql-performance
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 does not create core dumps with 32 bit computers with billions of rows!
This is a bit slower than fetching all rows at once. Scalability means
sometimes
a bit less speed :(
With UseDeclareFetch=1 you might get even 150 thousands rows per second.
With UseDeclareFetch=0 the backend might be able to send about 200
thousands rows per
second.
So, these high numbers come, if all the results are already in memory,
and no disc
accesses are needed. These are about the peak speeds with VARCHAR,
without Unicode,
with Athlon64 home computer.
With sequential disc scan, more typical fetching
speed is about 50-100 thousands rows per second.
PostgreSQL ODBC row fetching speed is very good.
Perhaps with better discs, with RAID10, the current upper limit about
200 thousands
rows per second could be achieved??
So the in memory examples show, that the hard disc is normally
the bottleneck. It is on the server side.
My experiments are done in Linux. In Windows, the speed might be a bit
different
by a constant factor (algorithmically).
These speeds depend on very many factos even on sequential scan.
ODBC speed is affected by the number of columns fetched and the types of
the columns.
Integers are processed faster than textual or date columns.
The network latency is decreased with UseDeclareFetc=1 by increasing the
Fetch=2048
parameter: With Fetch=1 you get a bad performance with lots of rows, but
if you fetch
more data from the server once per 2048 rows, the network latency
affects only once for
the 2048 row block.
Regards,
Marko Ristola
Joel Fradkin wrote:
>Hate to be dumb, but unfortunately I am.
>
>Could you give me an idea what I should be using, or is there a good
>resource for me to check out.
>I have been spending so much time with config and moving data, converting
>etc, I never looked at the odbc settings (didn't even think about it until
>Josh brought it up). I did ask him for his advice, but would love a second
>opinion.
>
>Our data is a bit of a mixture, some records have text items most are
>varchars and integers with a bit of Booleans mixed in.
>
>I am running 8.0.2 so not sure if the protocol is ODBC or Postgres?
>
>Thanks for responding I appreciate any help
>
>Joel Fradkin
>
>-----Original Message-----
>From: pgsql-odbc-owner@postgresql.org
>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mohan, Ross
>Sent: Thursday, April 21, 2005 10:01 AM
>To: pgsql-odbc@postgresql.org
>Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs
>Xeon
>
>Joel, thanks. A couple of things jump out there for
>me, not a problem for a routine ODBC connection, but
>perhaps in the "lotsa stuff" context of your current
>explorations, it might be relevant?
>
>I am completely shooting from the hip, here, but...if
>it were my goose to cook, I'd be investigating
>
>Session("StringConn") =
>"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
>PWD=;ReadOnly=0;Protocol=6.4;
>
>|| Protocol? Is this related to version? is the driver waaaay old?
>
>
>FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;
>
>|| Fetch great for OLTP, lousy for batch?
>
>
>Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
>
>|| what ARE the datatypes and sizes in your particular case?
>
>Debug=0;
>
>|| a run with debug=1 probably would spit up something interesting....
>
>CommLog=0;Optimizer=1;
>
>|| Optimizer? that's a new one on me....
>
>Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAs
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;Up
>datableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinar
>y=0;UseServerSidePrepare=0"
>
>
>|| that's about all I can see, prima facie. I'll be very curious to know
>if ODBC is
> any part of your performance equation.
>
>
>HTH,
>
>Ross
>
>-----Original Message-----
>From: Joel Fradkin [mailto:jfradkin@wazagua.com]
>Sent: Thursday, April 21, 2005 10:54 AM
>To: Mohan, Ross
>Cc: pgsql-odbc@postgresql.org; 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=wazagua;SERVER=192.168.123.252;PORT=5432;UID=;
>PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVar
>charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;Us
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Pa
>rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableC
>ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseS
>erverSidePrepare=0"
>
>Joel Fradkin
>
>
>-----Original Message-----
>From: Mohan, Ross [mailto:RMohan@arbinet.com]
>Sent: Thursday, April 21, 2005 9:42 AM
>To: jfradkin@wazagua.com
>Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
>
>FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the
>like.
>
>Maybe one of the ODBC cognoscenti here can chime in more concretely....
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-25 13:08 Joel Fradkin <jfradkin@wazagua.com>
parent: Marko Ristola <marko.ristola@kolumbus.fi>
1 sibling, 0 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-25 13:08 UTC (permalink / raw)
To: 'Marko Ristola' <marko.ristola@kolumbus.fi>; +Cc: 'Mohan, Ross' <RMohan@arbinet.com>; pgsql-odbc@postgresql.org; pgsql-performance; Steve Hatt <shatt@wazagua.com>; ac@wazagua.com
Thanks we will try that, we are working on a test suit for the way our app
gets data (ODBC).
we plan to include updates, inserts, and selects and all three at once with
a log of the results.
Then we should use a stress test tool to see how it works with multiple
instances (I used Microsoft's tool last time I did stress testing).
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-----Original Message-----
From: Marko Ristola [mailto:marko.ristola@kolumbus.fi]
Sent: Sunday, April 24, 2005 2:15 AM
To: Joel Fradkin
Cc: 'Mohan, Ross'; pgsql-odbc@postgresql.org;
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 and ODBC behaviour:
Change ODBC data source configuration in the following way:
Fetch = 2048
UseDeclareFetch = 1
It does not create core dumps with 32 bit computers with billions of rows!
This is a bit slower than fetching all rows at once. Scalability means
sometimes
a bit less speed :(
With UseDeclareFetch=1 you might get even 150 thousands rows per second.
With UseDeclareFetch=0 the backend might be able to send about 200
thousands rows per
second.
So, these high numbers come, if all the results are already in memory,
and no disc
accesses are needed. These are about the peak speeds with VARCHAR,
without Unicode,
with Athlon64 home computer.
With sequential disc scan, more typical fetching
speed is about 50-100 thousands rows per second.
PostgreSQL ODBC row fetching speed is very good.
Perhaps with better discs, with RAID10, the current upper limit about
200 thousands
rows per second could be achieved??
So the in memory examples show, that the hard disc is normally
the bottleneck. It is on the server side.
My experiments are done in Linux. In Windows, the speed might be a bit
different
by a constant factor (algorithmically).
These speeds depend on very many factos even on sequential scan.
ODBC speed is affected by the number of columns fetched and the types of
the columns.
Integers are processed faster than textual or date columns.
The network latency is decreased with UseDeclareFetc=1 by increasing the
Fetch=2048
parameter: With Fetch=1 you get a bad performance with lots of rows, but
if you fetch
more data from the server once per 2048 rows, the network latency
affects only once for
the 2048 row block.
Regards,
Marko Ristola
Joel Fradkin wrote:
>Hate to be dumb, but unfortunately I am.
>
>Could you give me an idea what I should be using, or is there a good
>resource for me to check out.
>I have been spending so much time with config and moving data, converting
>etc, I never looked at the odbc settings (didn't even think about it until
>Josh brought it up). I did ask him for his advice, but would love a second
>opinion.
>
>Our data is a bit of a mixture, some records have text items most are
>varchars and integers with a bit of Booleans mixed in.
>
>I am running 8.0.2 so not sure if the protocol is ODBC or Postgres?
>
>Thanks for responding I appreciate any help
>
>Joel Fradkin
>
>-----Original Message-----
>From: pgsql-odbc-owner@postgresql.org
>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mohan, Ross
>Sent: Thursday, April 21, 2005 10:01 AM
>To: pgsql-odbc@postgresql.org
>Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs
>Xeon
>
>Joel, thanks. A couple of things jump out there for
>me, not a problem for a routine ODBC connection, but
>perhaps in the "lotsa stuff" context of your current
>explorations, it might be relevant?
>
>I am completely shooting from the hip, here, but...if
>it were my goose to cook, I'd be investigating
>
>Session("StringConn") =
>"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=
;
>PWD=;ReadOnly=0;Protocol=6.4;
>
>|| Protocol? Is this related to version? is the driver waaaay old?
>
>
>FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;
>
>|| Fetch great for OLTP, lousy for batch?
>
>
>Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
>
>|| what ARE the datatypes and sizes in your particular case?
>
>Debug=0;
>
>|| a run with debug=1 probably would spit up something interesting....
>
>CommLog=0;Optimizer=1;
>
>|| Optimizer? that's a new one on me....
>
>Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsA
s
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;U
p
>datableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBina
r
>y=0;UseServerSidePrepare=0"
>
>
>|| that's about all I can see, prima facie. I'll be very curious to know
>if ODBC is
> any part of your performance equation.
>
>
>HTH,
>
>Ross
>
>-----Original Message-----
>From: Joel Fradkin [mailto:jfradkin@wazagua.com]
>Sent: Thursday, April 21, 2005 10:54 AM
>To: Mohan, Ross
>Cc: pgsql-odbc@postgresql.org; 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=wazagua;SERVER=192.168.123.252;PORT=5432;UID=
;
>PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0
;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVa
r
>charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;U
s
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;P
a
>rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;Updatable
C
>ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;Use
S
>erverSidePrepare=0"
>
>Joel Fradkin
>
>
>-----Original Message-----
>From: Mohan, Ross [mailto:RMohan@arbinet.com]
>Sent: Thursday, April 21, 2005 9:42 AM
>To: jfradkin@wazagua.com
>Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
>
>FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the
>like.
>
>Maybe one of the ODBC cognoscenti here can chime in more concretely....
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-25 13:52 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 41+ messages in thread
From: Merlin Moncure @ 2005-04-25 13:52 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
> 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.
Completely false. NT was a complete rewrite (1993ish) and was
inherently multi-user with even the GDI running as a user level process
(no longer however). The NT kernel was scalable and portable, running
on the Alpha, MIPS, etc.
However, you do have a point with applications...many win32 developers
have a very bad habit about expecting their apps to install and run as
root. However, this is generally not a problem with Microsoft stuff.
In short, the problem is really people, not the technology.
Merlin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-25 14:07 Joel Fradkin <jfradkin@wazagua.com>
parent: Marko Ristola <marko.ristola@kolumbus.fi>
1 sibling, 0 replies; 41+ messages in thread
From: Joel Fradkin @ 2005-04-25 14:07 UTC (permalink / raw)
To: 'Marko Ristola' <marko.ristola@kolumbus.fi>; +Cc: 'Mohan, Ross' <RMohan@arbinet.com>; pgsql-odbc@postgresql.org; pgsql-performance
Tried changing the settings and saw no change in a test using asp.
The test does several selects on views and tables.
It actually seemed to take a bit longer.
Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel. 941-753-7111 ext 305
jfradkin@wazagua.com
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete and destroy
all copies of the original message, including attachments.
-----Original Message-----
From: Marko Ristola [mailto:marko.ristola@kolumbus.fi]
Sent: Sunday, April 24, 2005 2:15 AM
To: Joel Fradkin
Cc: 'Mohan, Ross'; pgsql-odbc@postgresql.org;
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 and ODBC behaviour:
Change ODBC data source configuration in the following way:
Fetch = 2048
UseDeclareFetch = 1
It does not create core dumps with 32 bit computers with billions of rows!
This is a bit slower than fetching all rows at once. Scalability means
sometimes
a bit less speed :(
With UseDeclareFetch=1 you might get even 150 thousands rows per second.
With UseDeclareFetch=0 the backend might be able to send about 200
thousands rows per
second.
So, these high numbers come, if all the results are already in memory,
and no disc
accesses are needed. These are about the peak speeds with VARCHAR,
without Unicode,
with Athlon64 home computer.
With sequential disc scan, more typical fetching
speed is about 50-100 thousands rows per second.
PostgreSQL ODBC row fetching speed is very good.
Perhaps with better discs, with RAID10, the current upper limit about
200 thousands
rows per second could be achieved??
So the in memory examples show, that the hard disc is normally
the bottleneck. It is on the server side.
My experiments are done in Linux. In Windows, the speed might be a bit
different
by a constant factor (algorithmically).
These speeds depend on very many factos even on sequential scan.
ODBC speed is affected by the number of columns fetched and the types of
the columns.
Integers are processed faster than textual or date columns.
The network latency is decreased with UseDeclareFetc=1 by increasing the
Fetch=2048
parameter: With Fetch=1 you get a bad performance with lots of rows, but
if you fetch
more data from the server once per 2048 rows, the network latency
affects only once for
the 2048 row block.
Regards,
Marko Ristola
Joel Fradkin wrote:
>Hate to be dumb, but unfortunately I am.
>
>Could you give me an idea what I should be using, or is there a good
>resource for me to check out.
>I have been spending so much time with config and moving data, converting
>etc, I never looked at the odbc settings (didn't even think about it until
>Josh brought it up). I did ask him for his advice, but would love a second
>opinion.
>
>Our data is a bit of a mixture, some records have text items most are
>varchars and integers with a bit of Booleans mixed in.
>
>I am running 8.0.2 so not sure if the protocol is ODBC or Postgres?
>
>Thanks for responding I appreciate any help
>
>Joel Fradkin
>
>-----Original Message-----
>From: pgsql-odbc-owner@postgresql.org
>[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mohan, Ross
>Sent: Thursday, April 21, 2005 10:01 AM
>To: pgsql-odbc@postgresql.org
>Subject: Re: [ODBC] [PERFORM] Joel's Performance Issues WAS : Opteron vs
>Xeon
>
>Joel, thanks. A couple of things jump out there for
>me, not a problem for a routine ODBC connection, but
>perhaps in the "lotsa stuff" context of your current
>explorations, it might be relevant?
>
>I am completely shooting from the hip, here, but...if
>it were my goose to cook, I'd be investigating
>
>Session("StringConn") =
>"DRIVER={PostgreSQL};DATABASE=wazagua;SERVER=192.168.123.252;PORT=5432;UID=
;
>PWD=;ReadOnly=0;Protocol=6.4;
>
>|| Protocol? Is this related to version? is the driver waaaay old?
>
>
>FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
>ShowSystemTables=0;ConnSettings=;Fetch=100;
>
>|| Fetch great for OLTP, lousy for batch?
>
>
>Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;
>
>|| what ARE the datatypes and sizes in your particular case?
>
>Debug=0;
>
>|| a run with debug=1 probably would spit up something interesting....
>
>CommLog=0;Optimizer=1;
>
>|| Optimizer? that's a new one on me....
>
>Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsA
s
>Char=1;Parse=0;CancelAsFreeStmt=;ExtraSysTablePrefixes=dd_;LFConversion=1;U
p
>datableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBina
r
>y=0;UseServerSidePrepare=0"
>
>
>|| that's about all I can see, prima facie. I'll be very curious to know
>if ODBC is
> any part of your performance equation.
>
>
>HTH,
>
>Ross
>
>-----Original Message-----
>From: Joel Fradkin [mailto:jfradkin@wazagua.com]
>Sent: Thursday, April 21, 2005 10:54 AM
>To: Mohan, Ross
>Cc: pgsql-odbc@postgresql.org; 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=wazagua;SERVER=192.168.123.252;PORT=5432;UID=
;
>PWD=;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0
;
>ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVa
r
>charSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;Ksqo=1;U
s
>eDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;P
a
>rse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;Updatable
C
>ursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;Use
S
>erverSidePrepare=0"
>
>Joel Fradkin
>
>
>-----Original Message-----
>From: Mohan, Ross [mailto:RMohan@arbinet.com]
>Sent: Thursday, April 21, 2005 9:42 AM
>To: jfradkin@wazagua.com
>Subject: RE: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon
>
>FWIW, ODBC has variables to tweak, as well. fetch/buffer sizes, and the
>like.
>
>Maybe one of the ODBC cognoscenti here can chime in more concretely....
>
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-25 14:13 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 41+ messages in thread
From: Merlin Moncure @ 2005-04-25 14:13 UTC (permalink / raw)
To: Joel Fradkin <jfradkin@wazagua.com>; +Cc: pgsql-performance
> 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.
I am not a big fan of tweaking the optimizer because you are robbing
Peter to pay Paul, so to speak. pg 8.1 may come out with new optimizer
tweaks and you'll have to do it all over again. If the optimizer is not
'getting' your view, there are a few different approaches to fixing the
problem.
I am also not a big fan of de-normalizing your database. Essentially
you are lighting a fuse that may blow up later. Here are some general
approaches to planner optimization that can help out in tricky
situations.
1. Split up views. Often overlooked but can provide good enhancements.
If your view is based on 3 or more tables, has left/right joins,
consider breaking it up into two or more views. Views can be based on
views and it is easier to force the planner to pick good plans this way.
If you can find other uses for component views in other queries, so much
the better.
2. Materialize your view. Use lazy materialization, i.e. you query the
view into a table at scheduled times. Now we are trading disk spaces
and coherence for performance...this may not fit your requirements but
the nice thing about it is that it will help give us the 'ideal plan'
running time which we are shooting for.
3. pl/pgsql. Using combinations of loops, refcursors, and queries, you
can cut code that should give you comparable performance to the ideal
plan. If you can do the actual work here as well (no data returned to
client), you get a tremendous win. Also pl/pgsql works really well for
recursive sets and other things that are difficult to run in the context
of a single query. Just be aware of the disadvantages:
a. not portable
b. maintenance overhead
c. require relatively high developer skill set
I will go out on a limb and say that mastering the above approaches can
provide the solution to virtually any performance problem within the
limits of your hardware and the problem complexity.
Based on your questions, it sounds to me like your #1 problem is your
developer skillset relative to your requirements. However, this is
easily solvable...just keep attacking the problem and don't be afraid to
bring in outside help (which you've already done, that's a start!).
Merlin
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-25 22:03 Christopher Browne <cbbrowne@acm.org>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 41+ messages in thread
From: Christopher Browne @ 2005-04-25 22:03 UTC (permalink / raw)
To: pgsql-performance
Martha Stewart called it a Good Thing when merlin.moncure@rcsonline.com ("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,
>> 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.
>
> Completely false. NT was a complete rewrite (1993ish) and was
> inherently multi-user with even the GDI running as a user level
> process (no longer however). The NT kernel was scalable and
> portable, running on the Alpha, MIPS, etc.
Completely irrelevant. When Win32 was deployed, the notion that more
than a tiny fraction of the users would be running Win32 apps on
multiuser platforms was absolutely laughable. It continued to be
laughable until well into this century, when Microsoft ceased to sell
systems based on MS-DOS.
> However, you do have a point with applications...many win32 developers
> have a very bad habit about expecting their apps to install and run as
> root. However, this is generally not a problem with Microsoft stuff.
> In short, the problem is really people, not the technology.
Reality is that it is all about the applications.
Microsoft spent _years_ pushing people from MS-DOS to Windows 3.1 to
WfW to Windows 95, and had to do a lot of hard pushing.
The result of that was that a lot of vendors built Win32 applications
for Windows 95.
None of those systems supported multiple users, so the usage and
experience with Win32 pointed everyone to the construction of single
user applications.
At that point, whether Windows NT did or didn't support multiple users
became irrelevant. Usage patterns had to be oriented towards single
user operation because that's all Win32 could be used to support for
the vast majority that _weren't_ running Windows NT.
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/x.html
But what can you do with it? -- ubiquitous cry from Linux-user
partner. -- Andy Pearce, <ajp@hpopd.pwd.hp.com>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-27 02:46 Kevin Brown <kevin@sysexperts.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 41+ messages in thread
From: Kevin Brown @ 2005-04-27 02:46 UTC (permalink / raw)
To: pgsql-performance
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 run the query but have psql ignore the output?
> > > If so, you could use \timing.
> >
> > Would timing "SELECT COUNT(*) FROM (query)" work?
>
> Just \timing would work fine; PostgreSQL doesn't return anything until it has
> the whole result set.
Hmm...does \timing show the amount of elapsed time between query start
and the first results handed to it by the database (even if the
database itself has prepared the entire result set for transmission by
that time), or between query start and the last result handed to it by
the database?
Because if it's the latter, then things like server<->client network
bandwidth are going to affect the results that \timing shows, and it
won't necessarily give you a good indicator of how well the database
backend is performing. I would expect that timing SELECT COUNT(*)
FROM (query) would give you an idea of how the backend is performing,
because the amount of result set data that has to go over the wire is
trivial.
Each is, of course, useful in its own right, and you want to be able
to measure both (so, for instance, you can get an idea of just how
much your network affects the overall performance of your queries).
> That's why MSSQL vs. PostgreSQL timing comparisons are
> deceptive unless you're careful: MSSQL returns the results on block at a
> time, and reports execution time as the time required to return the *first*
> block, as opposed to Postgres which reports the time required to return the
> whole dataset.
Interesting. I had no idea MSSQL did that, but I can't exactly say
I'm surprised. :-)
--
Kevin Brown kevin@sysexperts.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Joel's Performance Issues WAS : Opteron vs Xeon
@ 2005-04-27 21:01 Jim C. Nasby <decibel@decibel.org>
parent: Kevin Brown <kevin@sysexperts.com>
0 siblings, 0 replies; 41+ messages in thread
From: Jim C. Nasby @ 2005-04-27 21:01 UTC (permalink / raw)
To: Kevin Brown <kevin@sysexperts.com>; pgsql-performance
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 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 could use \timing.
> > >
> > > Would timing "SELECT COUNT(*) FROM (query)" work?
> >
> > Just \timing would work fine; PostgreSQL doesn't return anything until it has
> > the whole result set.
>
> Hmm...does \timing show the amount of elapsed time between query start
> and the first results handed to it by the database (even if the
> database itself has prepared the entire result set for transmission by
> that time), or between query start and the last result handed to it by
> the database?
>
> Because if it's the latter, then things like server<->client network
> bandwidth are going to affect the results that \timing shows, and it
> won't necessarily give you a good indicator of how well the database
> backend is performing. I would expect that timing SELECT COUNT(*)
> FROM (query) would give you an idea of how the backend is performing,
> because the amount of result set data that has to go over the wire is
> trivial.
>
> Each is, of course, useful in its own right, and you want to be able
> to measure both (so, for instance, you can get an idea of just how
> much your network affects the overall performance of your queries).
>
>
> > That's why MSSQL vs. PostgreSQL timing comparisons are
> > deceptive unless you're careful: MSSQL returns the results on block at a
> > time, and reports execution time as the time required to return the *first*
> > block, as opposed to Postgres which reports the time required to return the
> > whole dataset.
>
> Interesting. I had no idea MSSQL did that, but I can't exactly say
> I'm surprised. :-)
>
>
> --
> Kevin Brown kevin@sysexperts.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
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?"
^ permalink raw reply [nested|flat] 41+ messages in thread
end of thread, other threads:[~2005-04-27 21:01 UTC | newest]
Thread overview: 41+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-20 18:53 Re: Joel's Performance Issues WAS : Opteron vs Xeon Josh Berkus <josh@agliodbs.com>
2005-04-20 19:52 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-20 20:22 ` Josh Berkus <josh@agliodbs.com>
2005-04-20 21:01 ` Vivek Khera <vivek@khera.org>
2005-04-20 20:42 ` John A Meinel <john@arbash-meinel.com>
2005-04-21 00:26 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-21 00:45 ` John A Meinel <john@arbash-meinel.com>
2005-04-21 04:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-21 14:36 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-21 13:53 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-23 02:01 ` Jim C. Nasby <decibel@decibel.org>
2005-04-23 06:30 ` Kevin Brown <kevin@sysexperts.com>
2005-04-23 18:27 ` Josh Berkus <josh@agliodbs.com>
2005-04-27 02:46 ` Kevin Brown <kevin@sysexperts.com>
2005-04-27 21:01 ` Jim C. Nasby <decibel@decibel.org>
2005-04-21 03:32 ` Josh Berkus <josh@agliodbs.com>
2005-04-21 13:05 ` Andreas Pflug <pgadmin@pse-consulting.de>
2005-04-20 20:22 Re: Joel's Performance Issues WAS : Opteron vs Xeon Anjan Dave <adave@vantage.com>
2005-04-21 12:56 Re: Joel's Performance Issues WAS : Opteron vs Xeon Merlin Moncure <merlin.moncure@rcsonline.com>
2005-04-21 14:42 Re: Joel's Performance Issues WAS : Opteron vs Xeon Mohan, Ross <RMohan@arbinet.com>
2005-04-21 14:53 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-21 15:01 Re: [PERFORM] Joel's Performance Issues WAS : Opteron vs Xeon Mohan, Ross <RMohan@arbinet.com>
2005-04-23 14:18 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-24 07:15 ` Marko Ristola <marko.ristola@kolumbus.fi>
2005-04-25 13:08 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-25 14:07 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-22 08:08 Re: Joel's Performance Issues WAS : Opteron vs Xeon Dave Page <dpage@vale-housing.co.uk>
2005-04-22 09:47 ` Andreas Pflug <pgadmin@pse-consulting.de>
2005-04-22 17:51 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-22 18:03 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-04-22 18:30 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-04-22 20:53 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-04-22 21:04 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-23 02:11 ` Jim C. Nasby <decibel@decibel.org>
2005-04-23 14:11 ` Joel Fradkin <jfradkin@wazagua.com>
2005-04-23 15:35 ` Joshua D. Drake <jd@commandprompt.com>
2005-04-23 16:03 ` brew@theMode.com
2005-04-23 18:27 ` Christopher Browne <cbbrowne@acm.org>
2005-04-25 13:52 Re: Joel's Performance Issues WAS : Opteron vs Xeon Merlin Moncure <merlin.moncure@rcsonline.com>
2005-04-25 22:03 ` Christopher Browne <cbbrowne@acm.org>
2005-04-25 14:13 Re: Joel's Performance Issues WAS : Opteron vs Xeon Merlin Moncure <merlin.moncure@rcsonline.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox