Hi Simon, what kind of table you are using. If it is myisam you can increase the max size of table by changing the following variable
myisam_data_pointer_size = 7 as default it should be 6. Please let me know if that helps you. Thanks, Saravanan --- On Thu, 6/5/08, Simon Collins <[EMAIL PROTECTED]> wrote: From: Simon Collins <[EMAIL PROTECTED]> Subject: Re: Large import into MYISAM - performance problems To: mysql@lists.mysql.com Date: Thursday, June 5, 2008, 3:05 PM I'm loading the data through the command below mysql -f -u root -p enwiki < enwiki.sql The version is MySQL 5.0.51a-community I've disabled the primary key, so there are no indexes. The CPU has 2 cores and 2 Gigs memory. The import fell over overnight with a "table full" error as it hit 1T (I think this may be a file system problem). As it's not importing before anymore show status isn't going to provide any interesting info however, I did notice that mysql was not consuming much CPU time ~ 10%. I wouldn't like to split the data up into separate tables as it would change the schema and I'm not in charge of the schema design - just the DBA at the backend. Cheers Simon mos wrote: > Simon, > As someone else mentioned, how are you loading the data? Can you post > the SQL? > > You have an Id field, so is that not the primary key? If so, the > slowdown could be maintaining the index. If so, add up to 30% of your > available ram to your key_bufer_size in your my.cnf file and restart > the server. How much RAM do you have on your machine and how many > CPU's do you have? What version of MySQL are you using? Also can you > post your "Show Status" output after it has started to slow down? How > much CPU is being used after the import slows down? > > Now from what you've said, it looks like you are using this table as a > lookup table, so if it just has an id and a blob field, you probably > return the blob field for a given id, correct? If it were up to me, I > would break the data into more manageable tables. If you have 100 > million rows, then I'd break it into 10x10 million row tables. Table_1 > would have id's from 1 to 9,999,999, and table_2 with id's from 10 > million to 10,999,999 etc. Your lookup would call a stored procedure > which determines which table to use based on the Id it was given. If > you really had to search all the tables you can then use a Merge table > based on those 10 tables. I use Merge tables quite a bit and the > performance is quite good. > > Mike > > At 11:42 AM 6/4/2008, you wrote: >> Dear all, >> >> I'm presently trying to import the full wikipedia dump for one of our >> research users. Unsurprisingly it's a massive import file (2.7T) >> >> Most of the data is importing into a single MyISAM table which has an >> id field and a blob field. There are no constraints / indexes on this >> table. We're using an XFS filesystem. >> >> The import starts of quickly but gets increasingly slower as it >> progresses, starting off at about 60 G per hour but now the MyISAM >> table is ~1TB it's slowed to a load of about 5G per hour. At this >> rate the import will not finish for a considerable time, if at all. >> >> Can anyone suggest to me why this is happening and if there's a way >> to improve performance. If there's a more suitable list to discuss >> this, please let me know. >> >> Regards >> >> Simon > > -- Dr Simon Collins Data Grid Consultant National Grid Service University of Manchester Research Computing Services Kilburn Building Oxford Road Manchester M13 9PL Tel 0161 275 0604 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
