Return to BSD News archive
Path: euryale.cc.adfa.oz.au!newshost.carno.net.au!harbinger.cc.monash.edu.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 18:49:49 +1000
Organization: On the Net (ISP on the Gold Coast, Australia)
Lines: 58
Message-ID: <337193AD.368D@OntheNet.com.au>
References: <5krqn6$cm@ocean.silcom.com> <3371842A.3CCE@OntheNet.com.au>
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: dlc@avtel.net
Xref: euryale.cc.adfa.oz.au comp.unix.bsd.freebsd.misc:40567
On Thu, 8 May 1997, David Carmean wrote:
> Worse:
>
> tty sd0 fd0 cpu
> tin tout sps tps msps sps tps msps us ni sy in id
> 0 791405 44 0.0 0 0 0.0 0 0 1 0 98
> 0 271558 49 0.0 0 0 0.0 1 0 0 0 98
> 0 271422 45 0.0 0 0 0.0 1 0 1 0 97
>
Hmmm... These numbers are NOT good. It sort of looks like you disk is
MAXED-OUT although I've seen my SCSI disks going at 70-80 transfers/sec.
I can think of a number of reasons why the disks may not be running at
full speed. Consider the following-
(a) Read a block from disk.
(b) Process said block taking only a fraction of a millisecond.
(c) Read next sequential block from disk. Oops, we're past it. Twiddle
thumbs while disk does a rotation to position heads then repeat from
(a).
Basically, you only need to be 1 bit past the beginning of the sector
header and you have to wait for a FULL rotation of the disk (say 16ms
.v. << 1ms to process each block once in memory which is why SO little
cpu is being used!).
Also, a 64MB memory system with 80MBytes of data read sequentially
doesn't WORK. As you read in data the buffer cache will fill but since
you are finished with a block once it has been processed the next read
will not find the desired block in the bcache so will have to read from
disk. The bcache is not big enough to hold the entire db so older
blocks will be flushed as newer blocks are read in. A second pass
through the db will not find ANY blocks in the bcache that it wants and
so the flushing process continues.
Btw, cpu caches work the same way in general.
I think that is why terabyte memory systems + Alpha cpus are so much
faster at "data mining" than equally fast (or almost so) cpu but smaller
memory systems. You really need to keep a large portion of the db in
memory to get any performance out of sequential searches!
I would advise at least another 64MB of RAM, and if the db is going to
grow larger, even more RAM.
Also, you say that the queries are 'ad hoc' but it still might help to
key certain fields such as title, topic, classification, author, ...
Even a "contains"-type query (classification='%sf%' for science-fiction)
might do a lot better if the field is keyed. At least only a much
smaller amount of data (the key buckets) needs to be read to determine
is a row matches the query.
As for it being 1 AM, I hope you are a night owl or have a good supply
of caffeine at hand! ;-))
Tony