Thread: any proper benchmark scripts?
hello: im try to find out how mysql or postgresql perform i was wondering if there are any benchmarking scripts out there? if so please lemme see em try em etc!!
hello
on the modperl list a good thread was happening called
'fast db access'
find attached scripts used to do this
here are my results:
[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
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: 254943232 0 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
--
back in the day
we didn't have no
old school
-dr. frog
http://www.hyperbomb.com
it sells itself
Attachment
Hi Clayton, Was it opening a new connection to the database every time, or did it open one connection each time and pump multiple queries through it? It would be a good things to develop your script and benchmark this both ways. Could become a useful tool for sizing both of these databases. Out of curiosity do you have access to other databases such as Interbase, Oracle, DB2, Informix, and so forth? The more it can connect to, the better people will be able to understand each one's strengths and weaknesses, in terms of Perl's DB access. Regards and best wishes, Justin Clift Clayton Cottingham aka drfrog wrote: > > hello > > on the modperl list a good thread was happening called > 'fast db access' > find attached scripts used to do this > > here are my results: > > [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 > > 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: 254943232 0 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 > > -- > back in the day > we didn't have no > old school > -dr. frog > http://www.hyperbomb.com > it sells itself > > ------------------------------------------------------------------------ > Name: fast_db.pl > fast_db.pl Type: Perl Program (application/x-perl) > Encoding: base64 > > Name: benchmark.sql > benchmark.sql Type: application/x-unknown-content-type-sql_auto_file > Encoding: base64 > > ------------------------------------------------------------------------ > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On 18 Apr 2001, Clayton Cottingham aka drfrog wrote:
> i will recomp both the newest postgresql and mysql
> not using any optimizing techs at all i'll post the
> config scripts i use
Hiya Clayton,
Try it with a few hundred thousand tuples in a good size table with
say 300 users banging on the box. MySQL is a nice substitute for
Access but that's about it.
Not to mention missing features: views and foreign keys for
example...and have a look at the API...yuck. I always hear people
complain (MySQL fans) about Pg not having DROP COLUMN in its SQL
implementation. To me a VIEW is slightly more important. ;-)
(Trying hard not to be obnoxious here but failing. Comparing MySQL
to Pg is like comparing windows to BSD...and I just can't apologise
for calling it like I see it!)
Cheers!
Tom (the most obnoxious man in Staten Island?)
-------------------------------------------------------------------- SVCMC - Center for Behavioral Health
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island Region Mobile: 917-282-7359
--------------------------------------------------------------------
Powered by: PostgreSQL s l a c k w a r e FreeBSD: RDBMS |---------- linux The
PowerTo Serve
--------------------------------------------------------------------
/* We prefer linguistic vigor over mathematical rigor. */
Justin Clift wrote: > > Hi Clayton, > > Was it opening a new connection to the database every time, or did it > open one connection each time and pump multiple queries through it? > > It would be a good things to develop your script and benchmark this both > ways. Could become a useful tool for sizing both of these databases. > > Out of curiosity do you have access to other databases such as > Interbase, Oracle, DB2, Informix, and so forth? The more it can connect > to, the better people will be able to understand each one's strengths > and weaknesses, in terms of Perl's DB access. > > Regards and best wishes, > > Justin Clift > ok i looked over the script and the connections are opened before for loops and shuit down after for loops i dont have access to any other db's but another person on the modperl list said he would be try oracle check out that thread its been going since monday? perl.apache.org click through maillists and use mailarchive.com search for 'fast db access' thats the thread!!
On Wed, Apr 18, 2001 at 07:00:24PM -0700, Clayton Cottingham aka drfrog wrote: "Benchmarks" comparing MySQL to anything else usually make the worst and lowest denominator compromise (which obviously favours MySQL).- What table types did you use on MySQL? Did you use theBDB (or whatever the transaction tables are called) or ISAM tables? If you didn't use transactional tables, it's nota fair comparison. - In PostgreSQL, did you do the inserts in a single transaction block or just did what MySQL users usually do because theyknow what a transaction is, and left every INSERT on its own (effectively putting each on its transaction block)? If you didn't put the inserts into a single transaction block, then it's not a realistic test because that's not whatyou would do in real life in a real database system (pretty much anything besides MySQL and Access). > > correct me if im wrong but if fast_db.pl is > working right > first set is insert > second set is select Speed is good. But it's not everything. You should do it right, then make it fast. MySQL implementors don't care about doing it right. They just care about making it fast.My filesystem is much faster than the MySQL filesystem (that's what it is). UDP is faster than TCP, but TCP is better and more reliable. A skydiver without a parachute will always fall faster than the one with parachute. The skydiving school will say "we make your fall more exciting, and much faster than our competitors." and the dumb skydivers will reply "well, if you make my fall faster, then of course you're the best". Which one will survive the fall? Comparing MySQL to PostgreSQL, Oracle or any other real database is like comparing DOS with UNIX, or Emacs with an orange. -Roberto Mello -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I wonder what this button does? *&^(&^)#@$*&_% NO CARRIER
Thomas Good wrote: > > On 18 Apr 2001, Clayton Cottingham aka drfrog wrote: > > > i will recomp both the newest postgresql and mysql > > not using any optimizing techs at all i'll post the > > config scripts i use > > Hiya Clayton, > > Try it with a few hundred thousand tuples in a good size table with > say 300 users banging on the box. MySQL is a nice substitute for > Access but that's about it. > if you check the script youll see its fairly cool how they set it up > Not to mention missing features: views and foreign keys for > example...and have a look at the API...yuck. I always hear people > complain (MySQL fans) about Pg not having DROP COLUMN in its SQL > implementation. To me a VIEW is slightly more important. ;-) > yes all that was tested was inserts and selects check my prev post if you check out that thread on modperl youlll see why these where not included they were testing hashes versus Storable.pm versus postgres versus flatfile > (Trying hard not to be obnoxious here but failing. Comparing MySQL > to Pg is like comparing windows to BSD...and I just can't apologise > for calling it like I see it!) i agree > > Cheers! > Tom (the most obnoxious man in Staten Island?) >