To sum it all up, I am trying to import data from text files into an
innobase table (about 12m rows of data that is stored in text files created
by doing a select into outfile). First I tried dropping the indexes before
doing load data infile, but I found that I didnt have enough table space to
recreate the indexes since apperently it makes a copy of the entire table.
So I decided to leave the indexes on the empty table and start loading the
text files. The first one loaded fine (4gb text file, loaded in 5 hours). I
noticed that the cpu usage from 14-20% for the first 4 hours, and then about
45 minutes of 99% cpu usage before it finished. The step that I am on now is
importing the largest file (12gb of text) and it has been going for about 26
hours now. The processor had been at 14-20% usage for most of the time and
for the last 5 hours or so its been at 99% usage. So I assume if it is
following the pattern that accured when I loaded the smaller file, it is
finishing up. But like I said, 26 hours have passed already. What if another
5/10/15 hours passes before it finally finishes? What if it is stuck in a
loop and is never going to finish?
The question is, when do you know when to give up, and when to keep on
trucking?
I would like to minimize downtime as much as possible (with limited
hardware, if i had more disk space it would be much easier!), but so far its
been 31 hours total. Does that seem long for importing 16gb of text files
into a table with 4 indexes (one primary, one unique, plus two more)? Is
there something else going on here or a better method of doing what I need
to do?
Thanks for any advice you give.
ryan
in case anyone cares to read more background on what I am trying to do, here
it is:
I have been using innobase tables with a 64kb page size in order to fit
around 30k per row into my table. As many of you know, recently Innobase 39b
was released with support for up to 4GB blob support. Naturaly I wanted to
upgrade to this code asap so I wouldnt have to worry about keeping my row
length under 30k or so. But because my innodb table space was creating with
a 64kb page size, I would have to dump the data out of the db to a file of
some sort, and then reimport it. This is where the trouble comes in.
I decided to split this 15gb table into three different text files using
select into outfile. This went without a hitch fairly quickly (12m rows or
so). I then deleted the old innodb files, upgraded the server, and recreated
the tablespace. So now the time comes to get the data back into the db.
Originaly I dropped all the indexes from the table, imported the old data
using load data infile (took around 4-5 hours), and started to recreate the
indexes. However I found that I didnt have enough table space to recreate an
index because it had to make another copy of table (and I dont have enough
disk space to increase the size of my table space that much). So that leads
me to the story above... imported the data with indexes on the table
already.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
