Mailing List Archive


Back to the month index Back to the list index
Carlos M. Gutierrez (carlos@gutierrez.com)
Mon, 2 Sep 1996 16:38:21 -0400 (AST) 

Date: Mon, 2 Sep 1996 16:38:21 -0400 (AST)
From: "Carlos M. Gutierrez" <carlos@gutierrez.com>
Subject: Re: [mSQL] slow mSQL inserts (AGAIN!)
Message-Id: <Pine.BSI.3.91.960902162554.24621A-100000@gaia.gutierrez.com>

On Mon, 2 Sep 1996, Eric Wenger wrote:

> 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.

If it checks for the record existence before inserting, and there is no
primary key... then it seems like mSQL has to do a sequential search for
each record, each SELECT. (it has to be slow)

1) It would speed things up to make the table a keyed table.

2) The other possibility, if you have lots of memory, is to make a memory
file system and place the database files in there.

I would do 1) & 2) if possible.

Another thing you might study is re-organizing your database. If you have
variable-length records, you might be better off putting these on
individual files. On your database you only keep a pointer (the filename,
or even an URL) to the variable-length data. Naming the files uniquely
involves some work, true, but your database will work much faster and
will also be smaller in size.

Hope this helps...

Carlos M. Gutierrez
Puerto Rico Internet Corp.
carlos@ipr.net

> 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!
>
--------------------------------------------------------------------------
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!