mySQL poor performance?

[B0b]

Hi everyone,

 

I'm trying to transfer a large .txt list of 2M+ entries into a mySQL databse.

 

Everything works fine until it reaches about 250K entries, from there, the performance dramatically drops and it takes ages to complete the rest.

Using top command, I may clearly see the CPU doesn't seem to work as much as it did for the first 250K.

Most of the time, it idles and the entries are very slowly transfered (about 10 per second), then it spins for a second to transfer at 10K+/sec.

 

What's going on?

 

$handle = fopen( 'largeFile.txt', 'r' );

$dbCon = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'database', $dbCon );

mysql_query( 
        "CREATE TABLE tableName
        (
            jack varchar( 255 ) Unique
        )" );
    
while ( !feof( $handle ) )
{
    $line = trim( fgets( $handle ) );
    
    if ( $line != '' )
    {
        mysql_query(
                "INSERT INTO tableName
                VALUES
                (
                    '$line'
                )" );
    }
} 

 

Thanks so much!

[Mchl]

Use extended inserts (a.k.a. multi-row inserts)

Drop the UNIQUE index and add it after data was loaded

 

Or if possible use LOAD DATA INFILE:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

[B0b]

Thank you Mchl.

 

Would this be the proper syntax for LOAD DATA INFILE?:

 

mysql_query( "LOAD DATA INFILE 'hugeFile.txt' INTO TABLE $tableName LINES TERMINATED BY" . "\r\n" );

 

Doesn't work?

[Mchl]

hugefile.txt myst be placed in MySQL's data directory or you have to supply a full path to this file.

LINES TERMINATED is optional, so you might skip it.

Use mysql_error to find what MySQL complains about.

[PFMaBiSmAd]

You are putting double-quotes inside of a php double-quoted string and probably getting a php parse/syntax error if full php error_reporting/display_errors were turned on.

[B0b]

Thanks guys.

 

The following error is returned:

 

Can't get stat of '...' (Errcode: 13)

 

I am using the absolute path to the file. Any clue?

[dabaR]

Show the code that produces the error?

[B0b]

$dbCon = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'database', $dbCon );

$tableName = 'tableName';
mysql_query( 
	"CREATE TABLE $tableName
	(
		columnName varchar( 255 )
	)" );


mysql_query( "LOAD DATA INFILE '/var/www/docs/myDomain/hugeFile.txt' INTO TABLE $tableName" );

echo mysql_error();

[Mchl]

Try LOAD DATA LOCAL INFILE

[B0b]

#1, thanks! 

[B0b]

Hum, I am having the exact same problem as I am trying to separate a table into parts:

 

for ( $i = 1; $i <= 20; $i++ ) // In 20 parts.
{
$TMPtableName = $i . '_' . $tableName;
mysql_query( 
		"CREATE TABLE $TMPtableName
		(
			ID int auto_increment not null,
			columnName varchar( 255 ) Unique,
			primary key ( ID )
		)" );
}

// Determine total of entries.
$total = mysql_query(
				"SELECT MAX( ID )
				FROM $tableName" );				
$total = mysql_fetch_row( $total );
$total = $total[0];

$counter = 1;
for ( $i = 1; $i <= $total; $i++ )
{
$tmpQuery = mysql_query(
					"SELECT columnName
					FROM $tableName
					WHERE ID = $i" );				
$tmpQuery = mysql_fetch_row( $tmpQuery );
$tmpQuery = $tmpQuery[0];

$TMPtableName = $counter . '_' . $ableName;
mysql_query(
		"INSERT INTO $TMPtableName
		(
			columnName
		)
		VALUES
		(
			'$$tmpQuery'
		)" );

$counter++;
if ( $counter > 20 )
{
	$counter = 1;
}
}

 

Any clue?

[B0b]

I am having the same problem as well with this:

 

CREATE TABLE $TMPtableName AS

SELECT DISTINCT columnName

FROM $tableName

 

If I set a limit to 250K, it will take few seconds while a limit to 500K will require about a minute. Not limit... errr.

 

Why is mySQL ALWAYS slowing down beyond ~250K?

 

[Mchl]

Why are you trying to separate a table into parts? Makes no sense.

 

You're moving records one row at a time. It's bound to be slow. You can use INSERT ... SELECT statement to do this in one query.

 

The slowing down might also be a result of how your MySQL server is configured. If it runs out of memory, it needs to start dumping data to disk, which slows things down considerably.

[B0b]

Mchl,

 

My goal is to transfer a .txt list to a mySQL database, then remove duplicate entries from it, shuffle the list and finally separate the table into 20 sub-tables.

 

I managed to separate it fairly quickly using:

 

SELECT * FROM tableName LIMIT ...

DELETE FROM tableName LIMIT ...

 

Where LIMIT split it in equal parts based on quantity of entries in the original table.

 

I then remove duplicate entries using a SELECT DISTINCT and shuffle using ORDER BY RAND() on each sub-table.

 

The issue with this method is that there may still be duplicate entries as only the sub-tables are cleared and the mixing isn't as good as it could be.

Also, an excessively large original table that would result in sub-tables greater than 250K would lead to a huge load time.

 

If you have any idea, please let me know.