Обсуждение: 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

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?)
>