Re: Database performance

["Christopher L. Everett" <ceverett@ceverett.com>]

Kent West wrote:

> Christopher L. Everett wrote:
>
> > Hi,
> >
> > I do a lot of database work.  Sometimes I must do massive batch jobs 
> > on my box
> > such as:
> >
> >  -- multi-gigabyte database dumps and restores
> >  -- tests over millions of records, searching for overlooked cases
> >  -- one-off queries for sales & marketing typs that join 8 or 9 tables
> >
> > The problem is that these things often take 10 to 30 minutes to run 
> > on my box.
> > When I use the GNU time utility, I see a low PCPU number, typically 
> > between 15
> > and 25%.  CPU utilization viewed through top remains at 35% or so, 
> > and I never
> > go deeper than a few tens of kilobytes into swap, even though the 1 
> > minute load
> > average climbs to 2 and higher (I've seen peak numbers around 6).
> >
> > I'm using a single Seagate 40GB ATA-133 as my sole hard drive, and my 
> > system has
> > an Athlon 2600 processor and 1 GB of RAM.  Am I correct in thinking 
> > that the
> > bottleneck lies in the HD subsystem?
> That would be my guess, but I'm no expert. I'd try using "hdparm" to 
> turn on 32-bit addressing and DMA, to see if that makes a difference.
Gosh I should have mentioned my hdparm output.  32-bit addressing and
DMA are already on:

# hdparm -tT /dev/hda
/dev/hda:
Timing buffer-cache reads:   1400 MB in  2.00 seconds = 698.71 MB/sec
Timing buffered disk reads:  120 MB in  3.01 seconds =  39.93 MB/sec
# hdparm -tT /dev/hda
/dev/hda:
Timing buffer-cache reads:   1396 MB in  2.00 seconds = 697.06 MB/sec
Timing buffered disk reads:  122 MB in  3.04 seconds =  40.12 MB/sec
# hdparm -tT /dev/hda
/dev/hda:
Timing buffer-cache reads:   1404 MB in  2.01 seconds = 700.01 MB/sec
Timing buffered disk reads:  120 MB in  3.01 seconds =  39.89 MB/sec
# hdparm -I /dev/hda
ATA device, with non-removable media
   Model Number:       ST340016A
   Serial Number:      3HS29P4S
   Firmware Revision:  3.10
Standards:
   Supported: 5 4 3 2
   Likely used: 6
Configuration:
   Logical        max    current
   cylinders    16383    16383
   heads        16    16
   sectors/track    63    63
   --
   CHS current addressable sectors:   16514064
   LBA    user addressable sectors:   78165360
   device size with M = 1024*1024:       38166 MBytes
   device size with M = 1000*1000:       40020 MBytes (40 GB)
Capabilities:
   LBA, IORDY(can be disabled)
   bytes avail on r/w long: 4    Queue depth: 1
   Standby timer values: spec'd by Standard
   R/W multiple sector transfer: Max = 16    Current = ?
   Recommended acoustic management value: 128, current value: 0
   DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 *udma5
        Cycle time: min=120ns recommended=120ns
   PIO: pio0 pio1 pio2 pio3 pio4
        Cycle time: no flow control=240ns  IORDY flow control=120ns
Commands/features:
   Enabled    Supported:
      *    READ BUFFER cmd
      *    WRITE BUFFER cmd
      *    Host Protected Area feature set
      *    Look-ahead
      *    Write cache
      *    Power Management feature set
       Security Mode feature set
       SMART feature set
       Device Configuration Overlay feature set
       Automatic Acoustic Management feature set
       SET MAX security extension
      *    DOWNLOAD MICROCODE cmd
Security:
   Master password revision code = 65534
       supported
   not    enabled
   not    locked
   not    frozen
   not    expired: security count
   not    supported: enhanced erase
HW reset results:
   CBLID- above Vih
   Device num = 1
Checksum: correct

As far as I can tell, I need buffered disk read numbers about 2.5 to 3
times to take full advantage of my processor.  If I were to up my
processor to a Athlon 3400, I would need to add another 35% of
disk throughput.

What kind of rig would people suggest, that wouldn't break the bank?

--
Christopher L. Everett

Chief Technology Officer                               www.medbanner.com
MedBanner, Inc.                                          www.physemp.com