Simon, In my experience load data infile is a lot faster than a sql file htrough the client. I would parse the sql file and create a csv file with just the columns of your table and then use load data infile using the created csv file
Olaf On 6/5/08 4:52 AM, "Simon Collins" <[EMAIL PROTECTED]> wrote: > I can do - if the load data infile command definitely improves > performance and splitting the file does the same I have no problem with > doing this. It just seems strange that it's problems with the way the > import file is configured. I thought the problem would be somehow with > the table getting bigger. > > Regards > > Simon > > > Ananda Kumar wrote: >> Simon, >> Why dont u split the file and use LOAD DATA INFILE command which would >> improve the performance while loading into an empty table with keys >> disabled. >> >> regards >> anandkl >> >> >> On 6/5/08, Simon Collins <[EMAIL PROTECTED]> wrote: >> >>> 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] >>> >>> >>> >> >> > ----------------------------------------- Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
