*BSD News Article 94952


Return to BSD News archive

Path: euryale.cc.adfa.oz.au!newshost.carno.net.au!harbinger.cc.monash.edu.au!news.mira.net.au!news.netspace.net.au!news.mel.connect.com.au!news.syd.connect.com.au!news.bri.connect.com.au!corolla.OntheNet.com.au!not-for-mail
From: Tony Griffiths <tonyg@OntheNet.com.au>
Newsgroups: comp.unix.bsd.freebsd.misc
Subject: Re: mSQL SLOOOOW???
Date: Thu, 08 May 1997 17:43:38 +1000
Organization: On the Net (ISP on the Gold Coast, Australia)
Lines: 28
Message-ID: <3371842A.3CCE@OntheNet.com.au>
References: <5krqn6$cm@ocean.silcom.com>
Reply-To: tonyg@OntheNet.com.au
NNTP-Posting-Host: swanee.nt.com.au
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 3.0 (WinNT; I)
To: David Carmean <dlc@silcom.com>
Xref: euryale.cc.adfa.oz.au comp.unix.bsd.freebsd.misc:40563

David Carmean wrote:
> 
> Got FreeBSD 2.1.7 running on a Pentium 166, with 64MB RAM, Micropolis
> 4221-09 AV SCSI drive, Bt958 SCSI controller.
> 
> mSQL 2.0 B5 (and B6); 124000 record table, no index, ~80MB resulting
> database file.  A simple exact query takes nearly four minutes to
> run; a little less than a minute when database is copied onto MFS
> filesystem.

At 4 minutes, thats 516 rows/second (~2 milliseconds/row).  At 1 minute,
you're looking a > 2000 rows/s or < 500 usec/row.

I find it a little curious that 3 out of the 4 minutes is I/O wait
state!?  Does a vmstat/iostat indicate that the system is 75% idle
during the query?

> 
> Should this *really* take this long to do a query on a 124000 row
> table?  Grepping a flat file is faster.

I would expect that grep would be considerably faster rushing through a
"flat" file that an query on a relational database table which is not
keyed, or is not being queried via a keyed field.  The whole idea is to
key the db such that frequent time-critical queries go fast.  Processing
every row in a table is definitely the "go-slow" option.

Tony