Received: from smartt.com (ktk6.smartt.com [209.52.5.253]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3J1xUu35181 for ; Wed, 18 Apr 2001 21:59:31 -0400 (EDT) (envelope-from drfrog@smartt.com) Received: from nomad.localdomain (alpnb13234.smarttadsl.com [209.52.13.234]) by smartt.com (8.11.1/8.11.1) with SMTP id f3J29Yj06260 for ; Wed, 18 Apr 2001 19:09:35 -0700 (PDT) Message-Id: <200104190209.f3J29Yj06260@smartt.com> Mime-Version: 1.0 From: "Clayton Cottingham aka drfrog" To: pgsql-sql@postgresql.org Subject: Re: any proper benchmark scripts? X-Mailer: Pronto v2.2.3 On linux/Pg Date: 18 Apr 2001 19:00:24 PDT Reply-To: "Clayton Cottingham aka drfrog" In-Reply-To: <3ADC8D97.23D5E339@smartt.com> References: <3ADC8D97.23D5E339@smartt.com> Content-Type: multipart/mixed; boundary="----------=_987645624-2268-1" X-Archive-Number: 200104/320 X-Sequence-Number: 2676 This is a multi-part message in MIME format... ------------=_987645624-2268-1 Content-Type: text/plain; charset="iso-8859-1" Content-Disposition: inline Content-Transfer-Encoding: 7bit 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 ------------=_987645624-2268-1 Content-Type: application/x-unknown; name="fast_db.pl" Content-Disposition: inline; filename="fast_db.pl" Content-Transfer-Encoding: base64 IyEvdXNyL2Jpbi9wZXJsIC13Cgp1c2Ugc3RyaWN0Owp1c2UgQmVuY2htYXJr Owp1c2UgREJJOwp1c2UgTUxEQk0gcXcoREJfRmlsZSBTdG9yYWJsZSk7CnVz ZSBTdG9yYWJsZTsKdXNlIEZjbnRsOwoKbXkoJGlfcHViLCAkaV9zaXplLCAk aV90eXBlLCAkaV9pcCk7Cm15KCVzX2NyciwgJHJzX3RtcCwgJXNfdG1wKTsK bXkoJXNfZGlzcF9oYXNoLCAkcl9oYXNoKTsKbXkoJHJfZmxhdGZpbGUpOwpt eSgkZGJoLCAkcl9wb3N0Z3JlcywgQHJvd19hcnkpOwpteSgkZGJoMiwgJHJf bXlzcWwsIEByb3dfYXJ5Mik7Cm15KCRyX3N0b3JhYmxlLCAlc19kaXNwX2hh c2hfc3RvcmFibGUpOwpteSgkcl9tbGRibSwgJXNfZGlzcF9oYXNoX21sZGJt KTsKCm15KCRpX2NvdW50KSA9IDIwOwpteSgkaV9pbm5lcl9sb29wKSA9IDI7 Cm15KCRzX2RlbGltaXRlcikgPSAnfCc7Cgolc19jcnIgPSAoIjAiLCAwLAoJ CSIxIiwgMSwKCQkiMiIsIDIsCgkJIjMiLCAzLAoJCSI0IiwgNCwKCQkiNSIs IDUsCgkJIjYiLCA2LAoJCSI3IiwgNywKCQkiOCIsIDgsCgkJIjkiLCA5ICk7 CgojLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KCiRkYmggPSBEQkkt PmNvbm5lY3QoImRiaTpQZzpkYm5hbWU9YmVuY2htYXJrIiwgImRyZnJvZyIs CgkJIiIsIHsgUmFpc2VFcnJvciA9PiAxLCBBdXRvQ29tbWl0ID0+IDEgfSk7 CSMgUG9zdGdyZXMKJGRiaDIgPSBEQkktPmNvbm5lY3QoImRiaTpteXNxbDpk Ym5hbWU9YmVuY2htYXJrIiwgImRyZnJvZyIsCgkJInRyYW5zMjIiLCB7IFJh aXNlRXJyb3IgPT4gMSwgQXV0b0NvbW1pdCA9PiAxIH0pOwkjIG15c3FsCgpm b3JlYWNoICRpX3B1YiAoMC4uOTkpIHsKCiMJbWtkaXIgImZsYXRmaWxlL3B1 YiRpX3B1YiIsIDA3Nzc7CSMgZmxhdCBmaWxlCQoJCglmb3JlYWNoICRpX3Np emUgKDAuLjIpIHsKCQojCQlta2RpciAiZmxhdGZpbGUvcHViJGlfcHViL3Np emUkaV9zaXplIiwgMDc3NzsJIyBmbGF0IGZpbGUKCQoJCWZvcmVhY2ggJGlf dHlwZSAoMC4uMykgewoJCQojCQkJbWtkaXIgImZsYXRmaWxlL3B1YiRpX3B1 Yi9zaXplJGlfc2l6ZS90eXBlJGlfdHlwZSIsIDA3Nzc7CgoJCQlmb3JlYWNo ICRpX2lwICgwLi4xOSkgewoKCQkJCSMtLS0tLSBmbGF0IGZpbGUgLS0tLS0K CiMJCQkJb3BlbihGSCwgIj5mbGF0ZmlsZS9wdWIkaV9wdWIvc2l6ZSRpX3Np emUvdHlwZSRpX3R5cGUvaXAkaV9pcCIpOwoKIwkJCQlwcmludCBGSCBxcSgi MCIsMCwiMSIsMSwiMiIsMiwiMyIsMywiNCIsNCwiNSIsNSwiNiIsNiwiNyIs NywiOCIsOCwiOSIsOSk7CgojCQkJCWNsb3NlKEZIKTsKCQkJCgkJCQkjLS0t LS0gaGFzaCAtLS0tLQoJCQkJCiMJCQkJJHNfZGlzcF9oYXNoe2pvaW4oJHNf ZGVsaW1pdGVyLAojCQkJCQkJInB1YiRpX3B1YiIsInNpemUkaV9zaXplIiwi dHlwZSRpX3R5cGUiLCJpcCRpX2lwIil9ID0KIwkJCQkJCVwlc19jcnI7CQoK CQkJCSMtLS0tLSBwb3N0Z3JlcyAtLS0tLQoJCQokZGJoLT5kbyhxcXtpbnNl cnQgaW50byBiZW5jaG1hcmsgdmFsdWVzICgncHViJGlfcHViJywJJ3NpemUk aV9zaXplJywgJ3R5cGUkaV90eXBlJywgJ2lwJGlfaXAnLAknIjAiLDAsIjEi LDEsIjIiLDIsIjMiLDMsIjQiLDQsIjUiLDUsIjYiLDYsIjciLDcsIjgiLDgs IjkiLDknKX0pOwoKJGRiaDItPmRvKHFxe2luc2VydCBpbnRvIGJlbmNobWFy ayB2YWx1ZXMgKCdwdWIkaV9wdWInLAknc2l6ZSRpX3NpemUnLCAndHlwZSRp X3R5cGUnLCAnaXAkaV9pcCcsCSciMCIsMCwiMSIsMSwiMiIsMiwiMyIsMywi NCIsNCwiNSIsNSwiNiIsNiwiNyIsNywiOCIsOCwiOSIsOScpfSk7CgoJCQkJ CQkKCQkJfQoJCX0KCX0KfQoKIy0tLS0tIHN0b3JhYmxlIC0tLS0tCgojc3Rv cmUgXCVzX2Rpc3BfaGFzaCwgJ3N0b3JhYmxlLmRhdCc7CgoKIy0tLS0tIE1M REJNIC0tLS0tCgojdGllICVzX2Rpc3BfaGFzaCwgJ01MREJNJywgIm1sZGJt LmRhdCIsIE9fQ1JFQVR8T19SRFdSLCAwMDY0NDsKI3VudGllICVzX2Rpc3Bf aGFzaDsKCiMtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQoKIy0tLS0t IHJfaGFzaCAtLS0tLQoKIyRyX2hhc2ggPSBzdWIgewojCWZvcmVhY2ggKDEu LiRpX2lubmVyX2xvb3ApIHsKIwkJJGlfcHViID0gaW50KHJhbmQoMTAwKSk7 CiMJCSRpX3NpemUgPSBpbnQocmFuZCgzKSk7CiMJCSRpX3R5cGUgPSBpbnQo cmFuZCg0KSk7CiMJCSRpX2lwID0gaW50KHJhbmQoMjApKTsKCiMJCSRyc190 bXAgPSAkc19kaXNwX2hhc2h7am9pbigkc19kZWxpbWl0ZXIsCiMJCQkJInB1 YiRpX3B1YiIsInNpemUkaV9zaXplIiwidHlwZSRpX3R5cGUiLCJpcCRpX2lw Iil9OwojCX0KI307CgojLS0tLS0gZmxhdCBmaWxlIC0tLS0tCgojJHJfZmxh dGZpbGUgPSBzdWIgewojCWZvcmVhY2ggKDEuLiRpX2lubmVyX2xvb3ApIHsK IwkJJGlfcHViID0gaW50KHJhbmQoMTAwKSk7CiMJCSRpX3NpemUgPSBpbnQo cmFuZCgzKSk7CiMJCSRpX3R5cGUgPSBpbnQocmFuZCg0KSk7CiMJCSRpX2lw ID0gaW50KHJhbmQoMjApKTsKCiMJCW9wZW4oRkgsICI8ZmxhdGZpbGUvcHVi JGlfcHViL3NpemUkaV9zaXplL3R5cGUkaV90eXBlL2lwJGlfaXAiKTsKIwkJ JXNfdG1wID0gKGV2YWwoPEZIPikpOwojCQljbG9zZShGSCk7CiMJfQojfTsK CiMtLS0tLSBQb3N0Z3JlcyAtLS0tLQoKJHJfcG9zdGdyZXMgPSBzdWIgewoJ Zm9yZWFjaCAoMS4uJGlfaW5uZXJfbG9vcCkgewoJCSRpX3B1YiA9IGludChy YW5kKDEwMCkpOwoJCSRpX3NpemUgPSBpbnQocmFuZCgzKSk7CgkJJGlfdHlw ZSA9IGludChyYW5kKDQpKTsKCQkkaV9pcCA9IGludChyYW5kKDIwKSk7CgoJ CUByb3dfYXJ5ID0gJGRiaC0+c2VsZWN0cm93X2FycmF5KCJzZWxlY3QgY3Jy IGZyb20gYmVuY2htYXJrIHdoZXJlCgkJCQlydHJpbShwdWIpID0gJ3B1YiRp X3B1YicgYW5kIHJ0cmltKHNpemUpID0gJ3NpemUkaV9zaXplJyBhbmQKCQkJ CXJ0cmltKHR5cGUpID0gJ3R5cGUkaV90eXBlJyBhbmQgcnRyaW0oaXApID0g J2lwJGlfaXAnIik7Cgl9Cn07CiMtLS0tLSBNeVNRTCAtLS0tLQokcl9teXNx bCA9IHN1YiB7Cglmb3JlYWNoICgxLi4kaV9pbm5lcl9sb29wKSB7CgkJJGlf cHViID0gaW50KHJhbmQoMTAwKSk7CgkJJGlfc2l6ZSA9IGludChyYW5kKDMp KTsKCQkkaV90eXBlID0gaW50KHJhbmQoNCkpOwoJCSRpX2lwID0gaW50KHJh bmQoMjApKTsKCgkJQHJvd19hcnkyID0gJGRiaDItPnNlbGVjdHJvd19hcnJh eSgic2VsZWN0IGNyciBmcm9tIGJlbmNobWFyayB3aGVyZQoJCQkJcnRyaW0o cHViKSA9ICdwdWIkaV9wdWInIGFuZCBydHJpbShzaXplKSA9ICdzaXplJGlf c2l6ZScgYW5kCgkJCQlydHJpbSh0eXBlKSA9ICd0eXBlJGlfdHlwZScgYW5k IHJ0cmltKGlwKSA9ICdpcCRpX2lwJyIpOwoJfQp9OwojLS0tLS0gU3RvcmFi bGUgLS0tLS0KCiMkcl9zdG9yYWJsZSA9IHN1YiB7CiMJZm9yZWFjaCAoMS4u JGlfaW5uZXJfbG9vcCkgewojICAgICAgICAkaV9wdWIgPSBpbnQocmFuZCgx MDApKTsKIwkgICAgJGlfc2l6ZSA9IGludChyYW5kKDMpKTsKIwkgICAgJGlf dHlwZSA9IGludChyYW5kKDQpKTsKIwkgICAgJGlfaXAgPSBpbnQocmFuZCgy MCkpOwoKIwkJJHJzX3RtcCA9IHJldHJpZXZlKCdzdG9yYWJsZS5kYXQnKTsK IwkJJXNfZGlzcF9oYXNoX3N0b3JhYmxlID0gJXskcnNfdG1wfTsgCgkJCiMJ CSRyc190bXAgPSAkc19kaXNwX2hhc2hfc3RvcmFibGV7am9pbigkc19kZWxp bWl0ZXIsCiMJCQkJInB1YiRpX3B1YiIsInNpemUkaV9zaXplIiwidHlwZSRp X3R5cGUiLCJpcCRpX2lwIil9OwojCX0JCiN9OwoKIy0tLS0tIE1MREJNIC0t LS0tCgojJHJfbWxkYm0gPSBzdWIgewojCWZvcmVhY2ggKDEuLiRpX2lubmVy X2xvb3ApIHsKIwkJJGlfcHViID0gaW50KHJhbmQoMTAwKSk7CiMJCSRpX3Np emUgPSBpbnQocmFuZCgzKSk7CiMJCSRpX3R5cGUgPSBpbnQocmFuZCg0KSk7 CiMJCSRpX2lwID0gaW50KHJhbmQoMjApKTsKCiMJCXRpZSglc19kaXNwX2hh c2hfbWxkYm0sICdNTERCTScsICdtbGRibS5kYXQnLCBPX1JET05MWSwKIwkJ CQkwMDQ0NCk7CgkJCQkKIwkJJHJzX3RtcCA9ICRzX2Rpc3BfaGFzaF9tbGRi bXtqb2luKCRzX2RlbGltaXRlciwKIwkJCQkicHViJGlfcHViIiwic2l6ZSRp X3NpemUiLCJ0eXBlJGlfdHlwZSIsImlwJGlfaXAiKX07CgojCQl1bnRpZSAl c19kaXNwX2hhc2hfbWxkYm07CiMJfQojfTsKCiMtLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLQoKZm9yZWFjaCAoMS4uMikgewojCXByaW50ICJoYXNo XG4iOwojCXByaW50IHRpbWVzdHIodGltZWl0KCRpX2NvdW50LCAkcl9oYXNo KSksICJcbiI7IAojCXByaW50ICJmbGF0ZmlsZVxuIjsKIwlwcmludCB0aW1l c3RyKHRpbWVpdCgkaV9jb3VudCwgJHJfZmxhdGZpbGUpKSwgIlxuIjsKCXBy aW50ICJwb3N0Z3Jlc1xuIjsKCXByaW50IHRpbWVzdHIodGltZWl0KCRpX2Nv dW50LCAkcl9wb3N0Z3JlcykpLCAiXG4iOwoJcHJpbnQgIm15c3FsXG4iOwoJ cHJpbnQgdGltZXN0cih0aW1laXQoJGlfY291bnQsICRyX215c3FsKSksICJc biI7CiMJcHJpbnQgInN0b3JhYmxlXG4iOwojCXByaW50IHRpbWVzdHIodGlt ZWl0KCRpX2NvdW50LCAkcl9zdG9yYWJsZSkpLCAiXG4iOwojCXByaW50ICJt bGRibVxuIjsKIwlwcmludCB0aW1lc3RyKHRpbWVpdCgkaV9jb3VudCwgJHJf bWxkYm0pKSwgIlxuIjsKfQoKJGRiaC0+ZGlzY29ubmVjdCgpOwokZGJoMi0+ ZGlzY29ubmVjdCgpOwo= ------------=_987645624-2268-1 Content-Type: application/x-unknown; name="benchmark.sql" Content-Disposition: inline; filename="benchmark.sql" Content-Transfer-Encoding: base64 Y3JlYXRlIHRhYmxlIGJlbmNobWFyayAocHViIGNoYXIoNykgbm90IG51bGws IHNpemUgY2hhcig3KSBub3QgbnVsbCx0eXBlIGNoYXIoNykgbm90IG51bGws aXAgY2hhcig3KSBub3QgbnVsbCwgY3JyIHZhcmNoYXIoMjU1KSwgcHJpbWFy eSBrZXkgKHB1Yiwgc2l6ZSwgdHlwZSwgaXApKTs= ------------=_987645624-2268-1--