-1

I recently upgraded the PHP version to 7.1 on my production server. I also updated my local to 7.1 and staging to 7.1. Only on production, I am seeing a slow down when I import a csv file and insert records into the database:

$fp = fopen($file_path, 'r');
if ($fp === FALSE) {
  // Failed to open file.
  watchdog('ea_test', 'Failed to open %file_path', array('%file_path' => $file_path));
  $context['finished'] = TRUE;
  return;
}
fseek($fp, $context['sandbox']['offset']);

for ($i = 0; $i < $limit; $i++) {
  $line = fgetcsv($fp);

  if ($line == FALSE) {
    $done = TRUE;
  }
  // If first line just skipping ahead.
  elseif ($context['sandbox']['records'] < 1) {
    $context['sandbox']['records']++;
  }
  else {
    try {
      db_insert('ea_csv_test')
        ->fields(array(
          'agent_number' => $line[0],
          'total_signatures' => $line[1],
          'template_number' => $line[2],
          'photo' => $line[3] ? 1 : 0,
          'total_views' => $csv_line[4],
          'total_clicks' => $csv_line[5],
          'annual_cost' => $csv_line[7],
          'monthly_cost' => $csv_line[8],
          'popular_button' => $csv_line[9],
          'month' => $csv_line[10],
          'asof' => date('Y-m-d 00:00:00'),
        ))->execute();
    }
    catch (Exception $e) {
      // By setting the databse error it will fail and display the last bad
      // record. Normally I would log/email this but I think it
      // will send too many. More than likely if one is bad they all
      // will be bad.
      $database_insert_error = $e->getMessage();
      $error = TRUE;
    }

    // Set the current position of the file so it starts from there.
    $context['sandbox']['offset'] = ftell($fp);

    $context['sandbox']['records']++;
  }
}

$eof = feof($fp);

On local / staging it takes 9.51 seconds to process a csv file. On production, that same exact file takes 166 seconds.

When I comment out the db_insert, it runs the code in 1.34 seconds so I would say the problem is with the database insert.

Also, I import the CSV file directly with a SQL query and it runs extremely fast so I don't think it is the database itself.

Environment Differences

Mariadb

Locally: 5.5.5-10.2.20-MariaDB-log

Production: 5.5.5-10.2.20-MariaDB-log

Edit

Just to make sure tried using mysqli_query() (Set up connection once above the for loop) but it is still slower:

$sql = "INSERT INTO ea_csv_test (
agent_number,
total_signatures,
template_number,
photo,
total_views,
total_clicks,
annual_cost,
monthly_cost,
popular_button,
month,
asof)
VALUES (
'{$line[0]}',
'{$line[1]}',
'{$line[2]}',
1,
100,
2000,
'{$line[7]}',
'{$line[8]}',
'{$line[9]}',
'{$line[10]}',
'2019-12-12 02:02:01'
)";

if (mysqli_query($conn, $sql)) {
  echo "New record created successfully\n";
}
else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn) . "\n";
}

closed as off-topic by No Sssweat, mradcliffe, leymannx, Clive Jan 3 at 11:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions on programming, PHP, SQL, etc. that do not relate directly to Drupal are off-topic here, but can be asked on Stack Overflow." – Clive
If this question can be reworded to fit the rules in the help center, please edit the question.

  • Dumb question incoming, but have you compared all 3 environments and looked for the possible differences in setup? – Kevin Jan 2 at 18:13
  • Yes. It's hard to tell what the culprit is because there are differences on the environments (php settings). One thing I just noticed is that the production has the latest 10.2.x version of Mariadb and locally I have a couple versions older on local. Production 5.5.5-10.2.20-MariaDB-log Locally 5.5.5-10.2.14-MariaDB. I wonder what the -log means. – albertski Jan 2 at 18:23
  • @albertski A quick search for “5.5.5-10.2.20-MariaDB-log” produces only Polish results - perhaps it’s a Polish version of the distribution – Clive Jan 2 at 19:04
  • I upgraded my local to be 5.5.5-10.2.20-MariaDB and it is still fast locally. Going to check with my Hosting company if they know what -log means. – albertski Jan 2 at 19:21
  • 2
    Ah, this is the -log bit: mariadb.com/kb/en/library/version/#examples. It simply means that General logging, slow logging or binary (replication) logging is enabled.. Which of course could be the culprit; I'd ask your sysadmin/hosting provider to disable it temporarily so you can test – Clive Jan 2 at 19:56
1

You know you can run a LOAD DATA (LOCAL) INFILE query through Drupal too, right?

As Clive stated in his comment, it is possible, I have done it in the past. You can avert the performance issue here for a while, or use this to come up with perhaps a better more performant solution.

https://gist.github.com/kevinquillen/45fb33206559449e9cb2

The key is this function:

/**
 * This takes the provided uploaded file and imports it directly into a temporary table in the database.
 * Drupal has some issues executing the statement, so the PDO class is leveraged with some different options to allow it without error.
 * @param $table_name
 * @param $uri
 * @param $message
 * @param $context
 */
function populate_mysql_table_with_infile($table_name, $uri) {
  $database = Database::getConnectionInfo()['default'];
  $data_source = 'mysql:host=' . $database['host'] . ';dbname=' . $database['database'];
  $db_user = $database['username'];
  $db_password = $database['password'];
  $connection = new PDO($data_source, $db_user, $db_password,
    array(
      PDO::ATTR_EMULATE_PREPARES => TRUE,
      PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_PERSISTENT
    )
  );
  $statement = $connection->prepare("LOAD DATA LOCAL INFILE '" . drupal_realpath($uri) . "' INTO TABLE " . $table_name . "
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES");
  $statement->execute();
  $statement->closeCursor();
}
  • Thanks for sharing this. This will definitely come in handy for me in the future. – albertski Jan 3 at 3:18
0

I was able to figure out the problem. I started to compare my innodb settings and I noticed that innodb_flush_log_at_trx_commit was set to 2 on local and staging and 1 on production. I changed it to 2 and it fixed the issue.

I read a couple of posts (for example this one) and it seems like it is pretty safe to do it unless you are doing something super mission critical like bank transactions.

Not the answer you're looking for? Browse other questions tagged or ask your own question.