Increase PHP / MySQL script performance

Question

Increase PHP / MySQL script performance

Navigation

#1 by (4 votes)

1

Good Afternoon

Currently, I have a script in PHP that leads a CSV file of 6801 lines, and performs an INSERT as long as there are records. I'm currently able to insert 19 records per second.

In addition to this INSERT routine that runs every 5 minutes, users will connect to the database and so on.

I would like somehow to increase the 19reg / sec rate and still make simultaneous connections also perform well.

The source of the reading function follows:

 error_reporting(0);
 date_default_timezone_set('America/Sao_Paulo');
 set_time_limit(600);
$server = "localhost";
$username = "root";
$password = "";
$database = "datab";

$conecta = mysql_connect($server, $username, $password) or print (mysql_error()); 
mysql_select_db($database, $conecta) or print(mysql_error()); 

function lerCsv(){



$handle = fopen("csv.csv", "r");
$num = 1;
$inicio = date("Y-m-d H:i:s");
Logger("Inicio da insercao -> ".$inicio);
while (!feof($handle) ) {
    $linhas = fgetcsv($handle, 0);       
    $a= $linhas[0];
    $b= $linhas[1];
    $c= $linhas[2];
    $d= $linhas[3];
    $e= $linhas[4];
    $f= $linhas[5];


    $sql = "insert into bldatant values(null, '".$a."', '".$b."','".$c."','".$d."','".$e."','".$f."');";
    if($query = mysql_query($sql)){
        Logger("Registro ".$num." inserido com sucesso.".$sql);
        $num++;

    }
    else{
        Logger("Erro ao inserir registro".$num.". Erro->".mysql_error());
    }

    $sql="update blparams set valorparam=NOW() where nomeparam='BP_LASTUPD'";
    $exc = mysql_query($sql);


}

fclose($handle);
mysql_close($conecta);
}

php mysql desempenho

asked by anonymous 17.07.2015 / 21:44

1 answer

Responsive horizontal list that fits 100% width of the parent div Tag code recognize language (Wordpress)?

score 4 · Accepted Answer

Since you do not do any processing of the file data, I see no need to process the file sequentially.

So, in my opinion, you can remove the loop (loop) and process the file at once with LOAD DATA INFILE . For example:

$sql = "LOAD DATA INFILE 'csv.csv'
        INTO TABLE bldatant
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\r\n'
        (a, b, c, d, d, f)
        SET ID=NULL"

You just have to replace (a, b, c, d, d, f) with the column names of your table and ID with the name of the column to which you are assigning NULL.

If you still encounter performance issues, you can change the settings for bulk insert . For example:

set bulk_insert_buffer_size = 1024 * 1024 * 256;