Hi David,
We're running price tapestry for quite some time on InnoDB now and pretty satisfied with it. However as the table grows during the import process the INSERTs become very slow, I guess also because the table uses quite some indexes.
Do you have any suggestions regarding improvements on an InnoDB table for the cron.php import process? I guess transactions would do some good but I'm not quite sure where exactly to implement it.
Please let me know if you have any other suggestions or advice on how to implement transactions :)
Thanks in advance!
Hi David,
I have tried your suggestion but unfortunately I can not convert the table to MyISAM because of indexes being too long (MySQL #1071).
I'm now trying to use transactions on the InnoDB table, however if that doesn't work I guess I'll have to go back to the MyISAM engine for the products table. The import now takes at least 14 hours to complete for around 1.5M records..
In case you have any other suggestion please let me know, I'll update this thread whenever the import finishes again.
Thanks!
Hi,
Another option to speed up import is to use Prepared Statements, which avoids the calls to escape content to make it SQL safe (as this is handled by the database engine). If you wanted to try this, edit includes/admin.php and look for the original INSERT code beginning at line 486:
/* create product record */
$importRecordSQL = "";
foreach($importRecord as $field => $v)
{
$importRecordSQL .= "`".$field."`='".database_safe($v)."',";
}
if (isset($admin_importAll))
{
$table = "products_import";
}
else
{
$table = "products";
}
$sql = sprintf("INSERT IGNORE INTO `".$config_databaseTablePrefix.$table."` SET
filename='%s',
%s
search_name='%s',
normalised_name='%s',
dupe_hash='%s'
",
database_safe($admin_importFeed["filename"]),
$importRecordSQL,
database_safe($searchName),
database_safe($normalisedName),
$dupe_hash
);
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}...and REPLACE with:
/* create product record */
global $database_link;
global $database_stmt;
global $database_types;
$importRecord["filename"] = $admin_importFeed["filename"];
$importRecord["search_name"] = $searchName;
$importRecord["normalised_name"] = $normalisedName;
$importRecord["dupe_hash"] = $dupe_hash;
if (!isset($importRecord["categoryid"])) $importRecord["categoryid"] = 0;
if (!isset($importRecord["voucher_code"])) $importRecord["voucher_code"] = "";
if (!isset($database_stmt) || !mysqli_ping($database_link))
{
$sets = array();
$database_types = "";
foreach($importRecord as $field => $v)
{
$sets[] = "`".$field."`= ? ";
$database_types .= "s";
}
$set = implode(",",$sets);
if (isset($admin_importAll))
{
$table = "products_import";
}
else
{
$table = "products";
}
$sql = "INSERT IGNORE INTO `".$config_databaseTablePrefix.$table."` SET ".$set;
$database_stmt = mysqli_prepare(database_link(),$sql);
}
$params = array();
$params[] = $database_stmt;
$params[] = $database_types;
foreach($importRecord as $field => $v)
{
$params[] = &$importRecord[$field];
}
call_user_func_array("mysqli_stmt_bind_param",$params);
$database_stmt->execute();
$admin_importProductCount += $database_stmt->affected_rows;Cheers,
David.
--
PriceTapestry.com
Hi David,
We just changed our database engine back to MyISAM and the performance is now way better! We went from around 30qps (insert) to over 2000 qps. The import now finishes within around 30 minutes for 2.5M records which is perfectly fine.
Once again thanks for your help!
Cheers,
Hi,
InnoDB doesn't support ENABLE / DISABLE KEYS so you are probably correct that the index construction is the reason for the import becoming slower as you increase the number of products.
If you are not otherwise using any InnoDB specific features, what about making the temporary table products_import (created by scripts/cron.php) MyISAM, and then changing it back to InnoDB just before the switch to become the live table?
If you'd like to try this (make sure you have good backups of everything of course) look for the following code beginning at line 81:
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` DISABLE KEYS";database_queryModify($sql,$result);
...and REPLACE with:
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENGINE=MyISAM";database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` DISABLE KEYS";
database_queryModify($sql,$result);
And then the following code beginning at line 97:
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";database_queryModify($sql,$result);
...and REPLACE with:
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENGINE=InnoDB";
database_queryModify($sql,$result);
Hope this helps!
Cheers,
David.
--
PriceTapestry.com