Mailing List Archive


Back to the month index Back to the list index
Eric Wenger (ewenger@scl.ameslab.gov)
Mon, 02 Sep 1996 15:29:18 +0000 

Message-Id: <322AFD4E.41C6@scl.ameslab.gov>
Date: Mon, 02 Sep 1996 15:29:18 +0000
From: Eric Wenger <ewenger@scl.ameslab.gov>
Subject: [mSQL] slow mSQL inserts (AGAIN!)

Hi, I'm back again. I have looked at many suggestions and I thank the
people who have suggested possible solutions to my problem, but I
haven't found a fix or an answer yet. So here it is again.

I am inserting about 26000 records out of a flat file into an msql
database. The database contains about 15 tables. 3 of these tables get
data for each record and 1 of the 12 other tables gets data for each
record. The 12 other tables are used to emulate a variable length
record. My inserting program checks the size of the record in the flat
file and then decides which of these 12 tables to insert the record
into. There are no primary keys in this database.

I am running this process on an SGI Onyx with 2 R4400 processors at
250Mhz, 256 megs of memory and 30 gig of fast-wide scsi disks attached
so I don't think the problem is in the size of the machine. Below is a
paste from top while the process is running. addrecords is the name of
the insertion program.

This program is designed to check for a record's existence before
inserting, so it can start from where it left off on the last run. The
interesting thing that is happening is that after inserting 1500
records, the process has slowed down to 3-4 seconds per insert and msqld
is consuming a great deal of cpu. When I kill the program and restart
it, it still takes 3-4 seconds per insert. I have used the SGI profiler
to check my code and it confirms that all of this time is spent in a
read by the msql query call. As I write this message, the time has just
crept over 4 seconds per insert.

The time required for selects has not changed, I have been watching that
because the program does a select to see if the record has already been
inserted.

IRIX64 gemini 6.2 03131016 IP19 Load[1.66,1.25,0.74] 15:15:32 146 procs
    user pid pgrp %cpu proc pri size rss time
command
    root 8288 8288 88.45 1 100 153 68 50:50 msqld
    todi 25963 25963 6.69 * 66 155 68 533:55 counter
    root 14 0 3.48 * +39 0 0 443:05 rtnetd
 ewenger 26449 26449 1.29 * 61 970 738 0:55 netscape30b8
 ewenger 26724 26724 0.81 0 61 111 25 0:06 top
    root 15 0 0.62 * +39 0 0 292:20 sockd
    root 7967 7966 0.50 * 60 919 583 407:34 dataserver
 ewenger 26624 26624 0.45 * 60 763 536 0:31 xemacs-19.14
 ewenger 26711 26711 0.20 * 60 109 31 0:04 addrecords
    root 451 0 0.18 * 60 209 36 98:26 netlsd
    root 444 0 0.07 * 60 111 21 37:06 llbd
    root 1390 340 0.06 * 60 184 87 31:17 fam
    root 26713 340 0.06 * 60 118 39 0:00 klogi 

-- 
-------------------------------------------
Eric Wenger
Scalable Computing Lab /
   Ames Laboratory, Dept. of Energy
335 Wilhelm Hall
Ames, IA 50012
--------------------------------------------------------------------------
To remove yourself from the Mini SQL mailing list send a message containing
"unsubscribe" to "unsubscribe" to msql-list-request@bunyip.com.  Send a message containing
"info msql-list" to majordomo@bunyip.com for info on monthly archives of
the list. For more help, mail owner-msql-list@bunyip.com NOT the msql-list!