From 2fbf50460bad59c280c0c5d36e6894fbefd78e9c Mon Sep 17 00:00:00 2001 From: Alexander Ebert Date: Mon, 3 Jun 2013 03:13:51 +0200 Subject: [PATCH] Trying sequential SQL imports to avoid hitting timeouts I've observed that adding the foreign keys is taking up ~70% of the entire time needed to import the SQL file. This work-around does not decrease the time, instead it splits it into big chunks processed in multiple requests. --- .../files/lib/system/WCFSetup.class.php | 67 +++++++++---------- .../system/database/util/SQLParser.class.php | 19 +----- wcfsetup/setup/db/install.sql | 6 ++ wcfsetup/setup/template/stepNext.tpl | 5 ++ 4 files changed, 45 insertions(+), 52 deletions(-) diff --git a/wcfsetup/install/files/lib/system/WCFSetup.class.php b/wcfsetup/install/files/lib/system/WCFSetup.class.php index 4c0627e1a8..2cc71b1a8a 100644 --- a/wcfsetup/install/files/lib/system/WCFSetup.class.php +++ b/wcfsetup/install/files/lib/system/WCFSetup.class.php @@ -719,28 +719,22 @@ class WCFSetup extends WCF { $this->initDB(); $start = microtime(true); - file_put_contents(WCF_DIR.'__wcfSetupPerformance.log', "Creating database ...", FILE_APPEND); + file_put_contents(WCF_DIR.'__wcfSetupPerformance.log', "Creating database: ", FILE_APPEND); // get content of the sql structure file $sql = file_get_contents(TMP_DIR.'setup/db/install.sql'); + // split by offsets + $sqlData = explode('/* SQL_PARSER_OFFSET */', $sql); + $offset = (isset($_POST['offset'])) ? intval($_POST['offset']) : 0; + if (!isset($sqlData[$offset])) { + throw new SystemException("Offset for SQL parser is out of bounds, ".$offset." was requested, but there are only ".count($sqlData)." sections"); + } + $sql = $sqlData[$offset]; + // installation number value 'n' (WCF_N) must be reflected in the executed sql queries $sql = StringUtil::replace('wcf1_', 'wcf'.WCF_N.'_', $sql); - $GLOBALS['__db'] = array( - '__construct' => 0, - 'parse' => 0, - 'modify' => 0, - 'insert' => 0, - 'tableCount' => 0, - 'table' => 0, - 'tableGlobal' => 0, - 'default' => 0, - 'defaultCount' => 0, - 'key' => 0, - 'keyCount' => 0 - ); - // execute sql queries $parser = new SQLParser($sql); $parser->execute(); @@ -749,7 +743,6 @@ class WCFSetup extends WCF { preg_match_all("~CREATE\s+TABLE\s+(\w+)~i", $sql, $matches); if (!empty($matches[1])) { - $s2 = microtime(true); $sql = "INSERT INTO wcf".WCF_N."_package_installation_sql_log (sqlTable) VALUES (?)"; @@ -757,26 +750,32 @@ class WCFSetup extends WCF { foreach ($matches[1] as $tableName) { $statement->execute(array($tableName)); } - $GLOBALS['__db']['insert'] = round(microtime(true) - $s2, 3); } - file_put_contents(WCF_DIR.'__sqlPerformance.log', print_r($GLOBALS['__db'], true)); - - /* - * Manually install PIPPackageInstallationPlugin since install.sql content is not escaped resulting - * in different behaviour in MySQL and MSSQL. You SHOULD NOT move this into install.sql! - */ - $sql = "INSERT INTO wcf".WCF_N."_package_installation_plugin - (pluginName, priority, className) - VALUES (?, ?, ?)"; - $statement = self::getDB()->prepareStatement($sql); - $statement->execute(array( - 'packageInstallationPlugin', - 1, - 'wcf\system\package\plugin\PIPPackageInstallationPlugin' - )); - - $this->gotoNextStep('logFiles'); + if ($offset < (count($sqlData) - 1)) { + WCF::getTPL()->assign(array( + 'offset' => $offset + 1 + )); + + $this->gotoNextStep('createDB'); + } + else { + /* + * Manually install PIPPackageInstallationPlugin since install.sql content is not escaped resulting + * in different behaviour in MySQL and MSSQL. You SHOULD NOT move this into install.sql! + */ + $sql = "INSERT INTO wcf".WCF_N."_package_installation_plugin + (pluginName, priority, className) + VALUES (?, ?, ?)"; + $statement = self::getDB()->prepareStatement($sql); + $statement->execute(array( + 'packageInstallationPlugin', + 1, + 'wcf\system\package\plugin\PIPPackageInstallationPlugin' + )); + + $this->gotoNextStep('logFiles'); + } $end = microtime(true); file_put_contents(WCF_DIR.'__wcfSetupPerformance.log', round($end - $start, 3) . "\n\n", FILE_APPEND); diff --git a/wcfsetup/install/files/lib/system/database/util/SQLParser.class.php b/wcfsetup/install/files/lib/system/database/util/SQLParser.class.php index 2b794cdf25..e27929997e 100644 --- a/wcfsetup/install/files/lib/system/database/util/SQLParser.class.php +++ b/wcfsetup/install/files/lib/system/database/util/SQLParser.class.php @@ -10,7 +10,7 @@ use wcf\util\StringUtil; * Given queries will be parsed, converted and executed in the active database. * * @author Marcel Werk - * @copyright 2001-2012 WoltLab GmbH + * @copyright 2001-2013 WoltLab GmbH * @license GNU Lesser General Public License * @package com.woltlab.wcf * @subpackage system.database.util @@ -30,14 +30,12 @@ class SQLParser { */ public function __construct($queries) { // delete comments - $start = microtime(true); $queries = preg_replace("~('[^'\\\\]*(?:\\\\.[^'\\\\]*)*')|(?:(?:--|#)[^\n]*|/\*.*?\*/)~s", '$1', $queries); // split queries by semicolon if (preg_match_all("~(?:[^;']+(?:'[^'\\\\]*(?:\\\\.[^'\\\\]*)*')*)*(?=;|\$)~s", $queries, $matches)) { $this->queryArray = ArrayUtil::trim($matches[0]); } - $GLOBALS['__db']['__construct'] = round(microtime(true) - $start, 3); } /** @@ -48,11 +46,7 @@ class SQLParser { if (preg_match('~^(ALTER\s+TABLE|CREATE\s+INDEX|CREATE\s+TABLE|DROP\s+INDEX|DROP\s+TABLE|INSERT|UPDATE|DELETE)~i', $query, $match)) { $statement = strtoupper(preg_replace('~\s+~', ' ', $match[0])); - $GLOBALS['__db']['parse']++; - - $s = microtime(true); $this->executeStatement($statement, $query); - $GLOBALS['__db']['modify'] += round(microtime(true) - $s, 3); } } } @@ -68,7 +62,6 @@ class SQLParser { case 'CREATE TABLE': // get table name if (preg_match('~^CREATE\s+TABLE\s+(\w+)\s*\(~i', $query, $match)) { - $startG = microtime(true); $tableName = $match[1]; $columns = $indices = array(); @@ -117,11 +110,7 @@ class SQLParser { } } - $s = microtime(true); $this->executeCreateTableStatement($tableName, $columns, $indices); - $GLOBALS['__db']['tableCount']++; - $GLOBALS['__db']['table'] += round(microtime(true) - $s, 3); - $GLOBALS['__db']['tableGlobal'] += round(microtime(true) - $startG, 3); } break; @@ -132,10 +121,7 @@ class SQLParser { } // add foreign key else if (preg_match('~^ALTER\s+TABLE\s+(\w+)\s+ADD\s+FOREIGN KEY\s+(?:(\w+)\s*)?\((\s*\w+\s*(?:,\s*\w+\s*)*)\)\s+REFERENCES\s+(\w+)\s+\((\s*\w+\s*(?:,\s*\w+\s*)*)\)(?:\s+ON\s+(UPDATE|DELETE)\s+(CASCADE|SET NULL|NO ACTION))?~is', $query, $match)) { - $s = microtime(true); $this->executeAddForeignKeyStatement($match[1], ($match[2] ?: self::getGenericIndexName($match[1], $match[3], 'fk')), array('columns' => $match[3], 'referencedTable' => $match[4], 'referencedColumns' => $match[5], 'operation' => $match[6], 'action' => $match[7])); - $GLOBALS['__db']['keyCount']++; - $GLOBALS['__db']['key'] += round(microtime(true) - $s, 3); } // add/change column else if (preg_match("~^ALTER\s+TABLE\s+(\w+)\s+(?:(ADD)\s+(?:COLUMN\s+)?|(CHANGE)\s+(?:COLUMN\s+)?(\w+)\s+)(\w+)\s+(\w+)(?:\s*\((\s*(?:\d+(?:\s*,\s*\d+)?|'[^']*'(?:\s*,\s*'[^']*')*))\))?(?:\s+UNSIGNED)?(?:\s+(NOT NULL|NULL))?(?:\s+DEFAULT\s+(-?\d+.\d+|-?\d+|NULL|'[^'\\\\]*(?:\\\\.[^'\\\\]*)*'))?(?:\s+(AUTO_INCREMENT))?(?:\s+(UNIQUE|PRIMARY)(?: KEY)?)?~is", $query, $match)) { @@ -211,10 +197,7 @@ class SQLParser { case 'INSERT': case 'UPDATE': case 'DELETE': - $s = microtime(true); $this->executeStandardStatement($query); - $GLOBALS['__db']['defaultCount']++; - $GLOBALS['__db']['default'] += round(microtime(true) - $s, 3); break; } } diff --git a/wcfsetup/setup/db/install.sql b/wcfsetup/setup/db/install.sql index 1c9be668ac..f3d518a35d 100644 --- a/wcfsetup/setup/db/install.sql +++ b/wcfsetup/setup/db/install.sql @@ -1275,6 +1275,8 @@ CREATE TABLE wcf1_user_to_language ( UNIQUE KEY userID (userID, languageID) ); +/* SQL_PARSER_OFFSET */ + /* foreign keys */ ALTER TABLE wcf1_acl_option ADD FOREIGN KEY (packageID) REFERENCES wcf1_package (packageID) ON DELETE CASCADE; ALTER TABLE wcf1_acl_option ADD FOREIGN KEY (objectTypeID) REFERENCES wcf1_object_type (objectTypeID) ON DELETE CASCADE; @@ -1408,6 +1410,8 @@ ALTER TABLE wcf1_user_group_option_category ADD FOREIGN KEY (packageID) REFERENC ALTER TABLE wcf1_user_group_option_value ADD FOREIGN KEY (groupID) REFERENCES wcf1_user_group (groupID) ON DELETE CASCADE; ALTER TABLE wcf1_user_group_option_value ADD FOREIGN KEY (optionID) REFERENCES wcf1_user_group_option (optionID) ON DELETE CASCADE; +/* SQL_PARSER_OFFSET */ + ALTER TABLE wcf1_user_option ADD FOREIGN KEY (packageID) REFERENCES wcf1_package (packageID) ON DELETE CASCADE; ALTER TABLE wcf1_user_option_category ADD FOREIGN KEY (packageID) REFERENCES wcf1_package (packageID) ON DELETE CASCADE; @@ -1523,6 +1527,8 @@ ALTER TABLE wcf1_poll_option_vote ADD FOREIGN KEY (pollID) REFERENCES wcf1_poll ALTER TABLE wcf1_poll_option_vote ADD FOREIGN KEY (optionID) REFERENCES wcf1_poll_option (optionID) ON DELETE CASCADE; ALTER TABLE wcf1_poll_option_vote ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE CASCADE; +/* SQL_PARSER_OFFSET */ + /* default inserts */ -- default user groups INSERT INTO wcf1_user_group (groupName, groupType) VALUES ('wcf.acp.group.group1', 1); diff --git a/wcfsetup/setup/template/stepNext.tpl b/wcfsetup/setup/template/stepNext.tpl index 8c1b098b66..aa6c6aff16 100644 --- a/wcfsetup/setup/template/stepNext.tpl +++ b/wcfsetup/setup/template/stepNext.tpl @@ -14,6 +14,11 @@ {foreach from=$selectedLanguages item=language} {/foreach} + {if $__additionalParameters|isset} + {foreach from=$__additionalParameters key=__name value=__value} + + {/foreach} + {/if} -- 2.20.1