Re: [OT] Re: Fast DB access
Thanks for pointing out the mistake in postgres. Your Advice makes lots of sense. V Murali - Original Message - From: Cees Hek [EMAIL PROTECTED] To: Murali V [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 20, 2001 1:45 AM Subject: [OT] Re: Fast DB access On Thu, 19 Apr 2001, Murali V wrote: Hi, If you read the code more deeply, you'll find that the timeit is only wrapped around select and not around insert. We've written the insert code so that in the first round you can populate the database. You comment out the insert code after the first round and run the benchmark several times. This would only do select and time select. Hi Murali, OK, to start off, I was not specifically aiming my rant at you, I was replying to someone who had modified your code and was now comparing MySQL and PostgreSQL, and he was implying that the timings were for inserts and selects. I took this at face value, and didn't check the code close enough which I really should have done in the first place. Connecting this error to an axiom that Benchmarks are useless is bad indeed. Shouldn't we be ironing out errors and runing benchmarks which are good. Perhaps I should have said published benchmarks. In your case, you are using benchmarks for exactly what they are intended for... Creating a system that closely resembles your application and putting it through it's paces. What I find dangerous about publishing benchmarks, is that they are almost always heavily swayed to a specific application, and most of the time they show what the user wants them to show. In your original message, you clain to have a bias against Postgres, and your benchmark shows that bias. I however am a happy user of postgres, and am therefore biased towards it. I modified your benchmark script slightly, and I got the following results (I have include a diff of my changes at the bottom): postgres 0 wallclock secs ( 0.02 usr + 0.01 sys = 0.03 CPU) postgres 0 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU) Whereas if I run it with your version I get the following: postgres 27 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) postgres 27 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU) So what does that tell you about the benchmark? that the postgres part of this benchmark is useless... It may have given you the answer that you wanted, but it is misleading to anyone else out there. This is why there are always flame wars about benchmarking databases (by the way I think this whole thread has been very civilized and i hope is stays that way). Invariably the benchmark has missed some critical idea or optimization which drastically skew the results. Your recommendation is to pick a DB best suited to your app. But How ?? a) Either by hiring a guru who has seen all kinds of apps with different DBs who can give you the answer with which we can run b) Run a benchmark on critical programs which represent you app across databases and find what performs best. I've read too much literature on DB features. All DBs have all features (except MySQL which does not have commit ) You can't make a thing out of DB literature. What I would recommend is exactly what you have done in this case. Get access to any and all the systems that you feel may do the job for you , and try them out. Browse the web for other users experiences, but don't use other peoples benchmarks, because the odds are good that they are wrong... Create your own, or modify an existing one, and scrutinize exactly what it is doing. And if you want to share your results with anyone else, tell them what you choose in the end, and why. Tell them you choose database x because it did this and this for you. Don't say database y is a piece of crap, so we went with database x. But whatever you do, don't choose your database based on other peoples benchmarks (that is all I'm trying to say, and I guess I didn't say it clearly enough) When I first read your message, I tucked it away somewhere, so I could reference it again in the future, because I was interested in the MLDBM work that you had done, and I thank you for that. But it also made me think that maybe I shouldn't be using Postgres, because your results were so poor (only for a second or too though :). But I'll bet that a lot of people who have never used postgres before are now less likely to download it and try it out for themself, because a benchmark swayed them away from it. That sounds like a good closer, so I'll stop it there :-) Cees Here is the diff of my changes and a quick comment on why your way kills the performance of postgres *** *** 124,131 $i_ip = int(rand(20)); @row_ary = $dbh-selectrow_array(select crr from benchmark where ! rtrim(pub) = 'pub$i_pub' and rtrim(size) = 'size$i_size
[OT] Re: Fast DB access
On Thu, 19 Apr 2001, Murali V wrote:
Hi,
If you read the code more deeply, you'll find that the timeit is only
wrapped around select and not around insert.
We've written the insert code so that in the first round you can populate
the database.
You comment out the insert code after the first round and run the benchmark
several times. This would only do select and time select.
Hi Murali,
OK, to start off, I was not specifically aiming my rant at you, I was
replying to someone who had modified your code and was now comparing MySQL
and PostgreSQL, and he was implying that the timings were for inserts and
selects. I took this at face value, and didn't check the code close
enough which I really should have done in the first place.
Connecting this error to an axiom that "Benchmarks are useless" is bad
indeed. Shouldn't we be ironing out errors and runing benchmarks which are
good.
Perhaps I should have said published benchmarks. In your case, you are
using benchmarks for exactly what they are intended for... Creating a
system that closely resembles your application and putting it through it's
paces. What I find dangerous about publishing benchmarks, is that they
are almost always heavily swayed to a specific application, and most of
the time they show what the user wants them to show.
In your original message, you clain to have a bias against Postgres, and
your benchmark shows that bias. I however am a happy user of postgres,
and am therefore biased towards it. I modified your benchmark script
slightly, and I got the following results (I have include a diff of my
changes at the bottom):
postgres
0 wallclock secs ( 0.02 usr + 0.01 sys = 0.03 CPU)
postgres
0 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU)
Whereas if I run it with your version I get the following:
postgres
27 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU)
postgres
27 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU)
So what does that tell you about the benchmark? that the postgres part of
this benchmark is useless... It may have given you the answer that you
wanted, but it is misleading to anyone else out there.
This is why there are always flame wars about benchmarking databases (by
the way I think this whole thread has been very civilized and i hope is
stays that way). Invariably the benchmark has missed some critical idea
or optimization which drastically skew the results.
Your recommendation is to pick a DB best suited to your app. But How ??
a) Either by hiring a guru who has seen all kinds of apps with different DBs
who can give you the answer with which we can run
b) Run a benchmark on critical programs which represent you app across
databases and find what performs best.
I've read too much literature on DB features. All DBs have all features
(except MySQL which does not have commit )
You can't make a thing out of DB literature.
What I would recommend is exactly what you have done in this case. Get
access to any and all the systems that you feel may do the job for you ,
and try them out. Browse the web for other users experiences, but don't
use other peoples benchmarks, because the odds are good that they are
wrong... Create your own, or modify an existing one, and scrutinize
exactly what it is doing. And if you want to share your results with
anyone else, tell them what you choose in the end, and why. Tell them you
choose database x because it did this and this for you. Don't say
database y is a piece of crap, so we went with database x.
But whatever you do, don't choose your database based on other peoples
benchmarks (that is all I'm trying to say, and I guess I didn't
say it clearly enough)
When I first read your message, I tucked it away somewhere, so I could
reference it again in the future, because I was interested in the MLDBM
work that you had done, and I thank you for that. But it also made me
think that maybe I shouldn't be using Postgres, because your results were
so poor (only for a second or too though :). But I'll bet that a lot of
people who have never used postgres before are now less likely to download
it and try it out for themself, because a benchmark swayed them away from
it. That sounds like a good closer, so I'll stop it there :-)
Cees
Here is the diff of my changes and a quick comment on why your way kills
the performance of postgres
***
*** 124,131
$i_ip = int(rand(20));
@row_ary = $dbh-selectrow_array("select crr from benchmark where
! rtrim(pub) = 'pub$i_pub' and rtrim(size) =
'size$i_size' and
! rtrim(type) = 'type$i_type' and rtrim(ip) =
'ip$i_ip'");
}
};
--- 124,131
$i_ip = int(rand(20));
@row_ary = $dbh-selectrow_array("select crr from benchmark where
! pub = 'pub$i_pub' and size = 'size$i_size' and
! type =
Fw: [OT] Re: Fast DB access
Thanks for pointing out the mistake in postgres. Your Advice makes lots of sense. We will recreate the benchmark and post the results V Murali Differentiated Software Solutions Pvt. Ltd., 90, 3rd Cross,2nd Main, Ganga Nagar, Bangalore - 560 032 Phone : 91 80 3631445, 3431470 Visit us at www.diffsoft.com - Original Message - From: Cees Hek [EMAIL PROTECTED] To: Murali V [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, April 20, 2001 1:45 AM Subject: [OT] Re: Fast DB access On Thu, 19 Apr 2001, Murali V wrote: Hi, If you read the code more deeply, you'll find that the timeit is only wrapped around select and not around insert. We've written the insert code so that in the first round you can populate the database. You comment out the insert code after the first round and run the benchmark several times. This would only do select and time select. Hi Murali, OK, to start off, I was not specifically aiming my rant at you, I was replying to someone who had modified your code and was now comparing MySQL and PostgreSQL, and he was implying that the timings were for inserts and selects. I took this at face value, and didn't check the code close enough which I really should have done in the first place. Connecting this error to an axiom that "Benchmarks are useless" is bad indeed. Shouldn't we be ironing out errors and runing benchmarks which are good. Perhaps I should have said published benchmarks. In your case, you are using benchmarks for exactly what they are intended for... Creating a system that closely resembles your application and putting it through it's paces. What I find dangerous about publishing benchmarks, is that they are almost always heavily swayed to a specific application, and most of the time they show what the user wants them to show. In your original message, you clain to have a bias against Postgres, and your benchmark shows that bias. I however am a happy user of postgres, and am therefore biased towards it. I modified your benchmark script slightly, and I got the following results (I have include a diff of my changes at the bottom): postgres 0 wallclock secs ( 0.02 usr + 0.01 sys = 0.03 CPU) postgres 0 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU) Whereas if I run it with your version I get the following: postgres 27 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) postgres 27 wallclock secs ( 0.02 usr + 0.00 sys = 0.02 CPU) So what does that tell you about the benchmark? that the postgres part of this benchmark is useless... It may have given you the answer that you wanted, but it is misleading to anyone else out there. This is why there are always flame wars about benchmarking databases (by the way I think this whole thread has been very civilized and i hope is stays that way). Invariably the benchmark has missed some critical idea or optimization which drastically skew the results. Your recommendation is to pick a DB best suited to your app. But How ?? a) Either by hiring a guru who has seen all kinds of apps with different DBs who can give you the answer with which we can run b) Run a benchmark on critical programs which represent you app across databases and find what performs best. I've read too much literature on DB features. All DBs have all features (except MySQL which does not have commit ) You can't make a thing out of DB literature. What I would recommend is exactly what you have done in this case. Get access to any and all the systems that you feel may do the job for you , and try them out. Browse the web for other users experiences, but don't use other peoples benchmarks, because the odds are good that they are wrong... Create your own, or modify an existing one, and scrutinize exactly what it is doing. And if you want to share your results with anyone else, tell them what you choose in the end, and why. Tell them you choose database x because it did this and this for you. Don't say database y is a piece of crap, so we went with database x. But whatever you do, don't choose your database based on other peoples benchmarks (that is all I'm trying to say, and I guess I didn't say it clearly enough) When I first read your message, I tucked it away somewhere, so I could reference it again in the future, because I was interested in the MLDBM work that you had done, and I thank you for that. But it also made me think that maybe I shouldn't be using Postgres, because your results were so poor (only for a second or too though :). But I'll bet that a lot of people who have never used postgres before are now less likely to download it and try it out for themself, because a benchmark swayed them away from it. That sounds like a good closer, so I'll stop it there :-) Cees Here is the diff of my changes a
(OT) Re: Fast DB access
On 18 Apr 2001 08:49:38 -0700, clayton cottingham wrote:
Matthew Kennedy wrote:
On 17 Apr 2001 18:24:43 -0700, clayton wrote:
i wanted a good benchmark for postgres and mysql
{i hope to transpose the sql properly!}
This is a good comparison of MySQL and PostgreSQL 7.0:
"Open Source Databases: As The Tables Turn" --
http://www.phpbuilder.com/columns/tim20001112.php3
very nice not all the info i was looking for but some solid answers
This might help too:
http://www.angelfire.com/nv/aldev/pgsql/GreatBridge.html
Of course benchmarks are so debatable anyway..
Matt
Re: (OT) Re: Fast DB access
Matthew Kennedy wrote: This might help too: http://www.angelfire.com/nv/aldev/pgsql/GreatBridge.html Of course benchmarks are so debatable anyway.. Matt i saw those they are pretty good but greatbridge is tied into postgres somehow im looking for impartial benchmarks nonetheless i think the other article said they used mysql 3.23.26a and a pre postgresql 7.1
[OT] Re: Fast DB access
On 18 Apr 2001, Clayton Cottingham aka drfrog wrote:
[drfrog]$ perl fast_db.pl
postgres
16 wallclock secs ( 0.05 usr +0.00 sys = 0.05 CPU) @ 400.00/s (n=20)
mysql
3 wallclock secs ( 0.07 usr +0.00 sys = 0.07 CPU) @ 285.71/s (n=20)
postgres
17 wallclock secs ( 0.06 usr +0.00 sys = 0.06 CPU) @ 333.33/s (n=20)
mysql
3 wallclock secs ( 0.01 usr +0.01 sys = 0.02 CPU) @ 1000.00/s (n=20)
correct me if im wrong but if fast_db.pl is
working right
first set is insert
second set is select
I am mad at myself for getting dragged into this, but I couldn't help
myself...
You are crippling postgreSQL by doing a tonne of inserts with a commit
after each statement. This completely misses the fact that postgreSQL is
transaction based whereas MySQL is not. Turn off AutoCommit and do a
commit at the end of the insert loop.
Also, if your selects are taking just as long as your inserts then you
must have other problems as well. Did you set up any indeces for the
columns of your table, or is that considered "optimizing the database" and
therefore not valid in your benchmark?
Benchmarks like this are pretty much useless (actually 99% of all
benchmarks are useless).
Use the database that best fits your needs based on the features it
supports, and the experience you have using it. If you find your database
is too slow, look into optimizing it because there are usually hundreds of
things you can do to make a database faster (faster disks, more ram,
faster CPU, fixing indeces, optimizing queries, etc...).
Don't pick a database because a benchmark on the web somewhere says it's
the fastest...
Sorry for the rant, I'll go back to sleep now...
Cees
find attached the modified ver of fast_db.pl
i sued to conduct this test
comp stats
running stock rpms from mandrake 7.2 for both
postgresql and mysql
3.23.23-beta of mysql and
7.02 of postgresql
[drfrog@nomad desktop]$ uname -a
Linux nomad.localdomain 2.2.18 #2 Tue Apr 17 22:55:04 PDT 2001 i686 unknown
[drfrog]$ cat /proc/meminfo
total:used:free: shared: buffers: cached:
Mem: 257511424 170409984 87101440 24219648 96067584 44507136
Swap: 2549432320 254943232
MemTotal:251476 kB
MemFree: 85060 kB
MemShared:23652 kB
Buffers: 93816 kB
Cached: 43464 kB
SwapTotal: 248968 kB
SwapFree:248968 kB
[drfrog]$ cat /proc/cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family: 6
model : 3
model name: AMD Duron(tm) Processor
stepping : 1
cpu MHz : 697.535
cache size: 64 KB
fdiv_bug : no
hlt_bug : no
sep_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp: yes
flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat
pse36 psn mmxext mmx fxsr 3dnowext 3dnow
bogomips : 1392.64
i will recomp both the newest postgresql and mysql
not using any optimizing techs at all i'll post the
config scripts i use
On Tue, 17 Apr 2001 18:24:43 -0700, clayton said:
Matt Sergeant wrote:
On Tue, 17 Apr 2001, Differentiated Software Solutions Pvt. Ltd., wrote:
H/W : Celeron 433 with 64 MB RAM, IDE HDD using RH 6.1, perl 5.005,
Postgres 6.5.3
This is a very very old version of postgresql. Try it again with 7.1 for
more respectable results.
im very glad to see this thread
i wanted a good benchmark for postgres and mysql
{i hope to transpose the sql properly!}
i do have 7.1 installed and it is very sweet
ill report back when i rerun under postgresql at the very least
--
Cees Hek
SiteSuite Corporation
[EMAIL PROTECTED]
