-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";
}
  • Dumb question incoming, but have you compared all 3 environments and looked for the possible differences in setup? – Kevin Jan 2 '19 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 '19 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 '19 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 '19 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 '19 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();
}
| improve this answer | |
  • Thanks for sharing this. This will definitely come in handy for me in the future. – albertski Jan 3 '19 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.

| improve this answer | |

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