List:General Discussion« Previous MessageNext Message »
From:Rolf Hopkins Date:February 28 2001 5:04am
Subject:Re: load large data files?
View as plain text  
You really should reply to the list and not just me.  I may not be able to
help you or I may not have the time.

----- Original Message -----
From: "Lisa M. FitzGerald" <lmfitzg@stripped>
To: "Rolf Hopkins" <rhopkins@stripped>
Sent: Wednesday, February 28, 2001 9:07
Subject: Re: load large data files?


> Rolf,
>
> -The file I'm loading is a text file with 18 tab-delimited fields.
> -I am not out of disk space or anywhere near.

I didn't think you would be.

> -All the columns match the type definitions for the table.  I admit
> that I haven't looked at every row in both data files, but
> the file was dumped (somehow) out of a mysql db by someone else
> and now I am trying to recreate the database on my server.  It
> came out of mysql, it should go back in.

That all depends on how it was dumped.  I do not know the answer to this but
what I suggest is that you check the whole file to see if there is some sort
of problem.  Check for things like use of reserved words for table/column
names, unescaped special characters, etc.

Now, it is a huge file, so what you may be able to do is break it in half
and continuously do that until you find the problem/s.


> -When the process 'hangs' mysqladmin says its still running
> even though the sizes of the table files haven't changed in hours.
>
> bin/mysqladmin -uroot -p process
> Enter password:
>
+----+------+-----------+-----+-----------+-------+-------+-----------------
------------------------------------------------------+
>
> | Id | User | Host      | db  | Command   | Time  | State |
> Info                                                                  |
>
+----+------+-----------+-----+-----------+-------+-------+-----------------
------------------------------------------------------+
>
> | 2  | root | localhost | hg5 | Query     | 55734 |       | LOAD DATA
INFILE
> "/opt/usr/local/mysql/mrna1.txt" INTO TABLE hg5.mrna |
> | 11 | root | localhost |     | Processes | 0     |
> |                                                                       |
>
+----+------+-----------+-----+-----------+-------+-------+-----------------
------------------------------------------------------+
>
> When it is in this state, 'running' but not actually doing anything, 1cpu
on
> my machine is full utilized and I can't get do anything else in any open
> mysql session I have running, and I can't open a new mysql session.
>
> The disk on my machine (where the mysql code lives but not the db)
> is also grinding away, but not seeming to do anything.
>
> If I kill it with mysqladmin kill 2, it shows up as killed in the
processlist,
> but never actually dies.  I still can't do anything else in any mysql
session.
>
> I then try to kill the server using mysqladmin -u root -p shutdown
> It says it did it, but it doesn't.  The server keeps running.  I can see
> it running on top, 1cpu is still fully utilized, and if I try to re-start
> the server it says the server is till running.
>
> The only way I have found to kill the server is to use UNIX kill for all
> the mysql processes.
>
> I have done this 6 or 7 times now, always with the same pattern as above.
> The load hangs, I can't kill the process, I cant shutdown the server
> gracefully.
>
> I've already loaded 338 tables with data from the same source and they all
> loaded fine.  Its just these last two that fail.  They sent individual
files
> with each of the 340 table definitions, and 340 files with the data for
> each table.
>
> I'm not sure what you meant about putting a symbolic link to the data in
> the database.  The whole database is already accessed by a
> symbolic link from the mysql/var directory to where I have built
> the database.  The individual table files built by mysql are binary,
> so clearly mysql is formatting them some how.  So if I just have a
> tab-delimited text file, can I just point to it rather than loading it in?

Sorry, maybe I wasn't clear.  I didn't mean a symbolic link.  For example if
your files were pictures  or music, just store the files on your hard drive
and store the URL/location in the database instead.  But obviously they're
not.

>
> Right now I've divided one of the files into many peices and am trying to
> load the parts, but I don't think this should be necessary and I'd rather
> not have to do this every time I reload data updates.

As mentioned above, a good idea.  But just be careful on how you dump the DB
in future.

> more observations:
> If I re-start the server and try to access the table its says there are no
> rows, eventhough the actual table files are quite large.
>
> database files
> -rw-rw----   1 lmfitzg  pronet   82500000 Feb 27 12:25
> /home/lmfitzg/MYSQL/db/hg5/mrna.ISD
> -rw-rw----   1 lmfitzg  pronet   144540672 Feb 27 14:33
> /home/lmfitzg/MYSQL/db/hg5/mrna.ISM
> -rw-rw----   1 lmfitzg  pronet      9073 Feb 27 10:49
> /home/lmfitzg/MYSQL/db/hg5/mrna.frm
>
> note time on ISM file keeps incrementing even though the file itself is
not
> changing size
> and the .ISD file is neither changing size nor timestamp.
>
> I ran myisamcheck on the .ISM file.

Ouch, this is a no no.  Use isamcheck on isam file and myisamcheck on myism
files.  What version of mysql do you have?  Should you do an upgrade and can
you upgrade your tables to myism?

>
>  bin/isamchk /home/lmfitzg/MYSQL/db/hg5/mrna.ISM
> Checking ISAM file: /home/lmfitzg/MYSQL/db/hg5/mrna.ISM
> Data records:       0   Deleted blocks:       0
> - check file-size
> bin/isamchk: warning: Size of indexfile is: 144540672      Should be: 2048
> bin/isamchk: warning: Size of datafile is: 82500000       Should be: 0
> - check delete-chain
> - check index reference
> - check data record references index: 1
> - check data record references index: 2
> - check data record references index: 3
> - check data record references index: 4
> - check data record references index: 5
> - check data record references index: 6
> - check data record references index: 7
> - check data record references index: 8
> - check data record references index: 9
> - check data record references index: 10
> - check data record references index: 11
> - check data record references index: 12
> - check data record references index: 13
> ISAM-table '/home/lmfitzg/MYSQL/db/hg5/mrna.ISM' is useable but should be
fixed
>
>
>
>
> Any ideas?
>
>
> Lisa
>
>
>  Hopkins wrote:
>
> > I'm presuming that hard drive space isn't a problem.
> >
> > What is it you're trying to import that's so large?  regular data?
graphics?
> > music?  Can you store the files on your hard drive instead and just
store
> > the link in the database? Are the fields, of the table you are importing
> > too, of an appropriate type?
> >
> > >
> > > I am trying to load two tables from space-delimited files.
> > > Both are quite large.
> > > Neither ever seems to finish. I have allowed them to run for days.
> > > The file sizes of the table files increase for a while and stop,
> > > but the mysql cursor never returns,  'mysqlamysqladmin processlist'
> > > says the process is still running, top says the process mysql is still
> > > taking up an entire CPU, but nothing is happening.  When this
> > > happens I can't do anything else in the database.
> > >
> > > I have tried LOAD DATA INFILE from the mysql prompt,
> > > and mysqlimport from the command line (which launches LOAD
> > > DATA INFILE), both with the same results.
> > >
> > >         file size
> > > file 1: 342580425 FAILS
> > > file 2: 233952880 FAILS
> > > file 3: 156121911 WORKS
> > >
> > > I am running Sun Solaris 8, which allows file sizes over 2gig. Is
there
> > > a file size limit for mysql?  Is there some setting I need to
> > > allow import of large files?  Is there some different/better way
> > > to import data than what I have tried?
> > >
> > > The only way I have found to stop the process is to kill the server.

Thread
load large data files?Lisa M. FitzGerald26 Feb
  • Re: load large data files?Rolf Hopkins27 Feb
    • Re: load large data files?S A27 Feb
  • Re: load large data files?Rolf Hopkins28 Feb
Re: load large data files?Chad Phillips28 Feb
  • Re: load large data files?Rolf Hopkins1 Mar