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]