Send patches - preferably formatted by git format-patch - to patches at archlinux32 dot org.
summaryrefslogtreecommitdiff
path: root/buildmaster
diff options
context:
space:
mode:
authorErich Eckner <git@eckner.net>2019-07-23 14:03:49 +0200
committerErich Eckner <git@eckner.net>2019-07-23 14:03:49 +0200
commit61988d1f36d3a99b815a19ce459e452524adfa1e (patch)
treefab97a165d05789c8adacf302365f9750707fa28 /buildmaster
parentf89e099311ed91489b83d84ed2ed6b8194ecc408 (diff)
buildmaster/build-list.php: use temporary tables to accellerate query
Diffstat (limited to 'buildmaster')
-rw-r--r--buildmaster/build-list.php395
1 files changed, 246 insertions, 149 deletions
diff --git a/buildmaster/build-list.php b/buildmaster/build-list.php
index a574d39..1962c07 100644
--- a/buildmaster/build-list.php
+++ b/buildmaster/build-list.php
@@ -265,158 +265,255 @@ function combine_fields($cln) {
return $cln["mysql_query"] . " AS `" . $cln["mysql_name"] . "`";
}
-$result = mysql_run_query(
- "SELECT " .
- implode(",",array_map("combine_fields",$columns)) .
- " FROM" .
- " (" .
- "SELECT DISTINCT " .
- "`build_assignments`.`id`," .
- "`build_assignments`.`is_blocked`," .
- "`build_assignments`.`is_broken`," .
- "`build_assignments`.`priority`," .
- "`build_assignments`.`currently_blocking`," .
- "`package_sources`.`pkgbase`," .
- "`package_sources`.`git_revision`," .
- "`package_sources`.`mod_git_revision`," .
- "`package_sources`.`uses_upstream`," .
- "`package_sources`.`uses_modification`," .
- "`package_sources`.`commit_time`," .
- "`upstream_repositories`.`name` AS `package_repository`," .
- "`git_repositories`.`name` AS `git_repository`," .
- "`architectures`.`name` AS `arch`" .
- " FROM `build_assignments`" .
- mysql_join_build_assignments_architectures() .
- mysql_join_build_assignments_package_sources() .
- mysql_join_package_sources_upstream_repositories() .
- mysql_join_upstream_repositories_git_repositories() .
- mysql_join_build_assignments_binary_packages() .
- mysql_join_binary_packages_binary_packages_in_repositories() .
- mysql_join_binary_packages_in_repositories_repositories() .
- " WHERE `repositories`.`name`=\"build-list\"" .
- ") AS `ba_q`".
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`dependent_bp`.`build_assignment`," .
- "COUNT(DISTINCT `dependency_bp`.`build_assignment`) AS `run_dependencies_pending`" .
- " FROM `binary_packages` AS `dependent_bp`" .
- mysql_join_binary_packages_dependencies('dependent_bp') .
- mysql_join_dependencies_dependency_types() .
- mysql_join_dependencies_install_target_providers() .
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `ba_q`(' .
+ '`id` BIGINT,' .
+ '`is_blocked` BIT,' .
+ '`is_broken` BIT,' .
+ '`priority` SMALLINT,' .
+ '`currently_blocking` MEDIUMINT,' .
+ '`pkgbase` VARCHAR(64),' .
+ '`git_revision` VARCHAR(40),' .
+ '`mod_git_revision` VARCHAR(40),' .
+ '`uses_upstream` BIT,' .
+ '`uses_modification` BIT,' .
+ '`commit_time` TIMESTAMP,' .
+ '`package_repository` VARCHAR(64),' .
+ '`git_repository` VARCHAR(64),' .
+ '`arch` VARCHAR(16),' .
+ 'PRIMARY KEY `id`(`id`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `ba_q`' .
+ ' SELECT DISTINCT' .
+ ' `build_assignments`.`id`,' .
+ '`build_assignments`.`is_blocked`,' .
+ '`build_assignments`.`is_broken`,' .
+ '`build_assignments`.`priority`,' .
+ '`build_assignments`.`currently_blocking`,' .
+ '`package_sources`.`pkgbase`,' .
+ '`package_sources`.`git_revision`,' .
+ '`package_sources`.`mod_git_revision`,' .
+ '`package_sources`.`uses_upstream`,' .
+ '`package_sources`.`uses_modification`,' .
+ '`package_sources`.`commit_time`,' .
+ '`upstream_repositories`.`name`,' .
+ '`git_repositories`.`name`,' .
+ '`architectures`.`name`' .
+ ' FROM `build_assignments`' .
+ mysql_join_build_assignments_architectures() .
+ mysql_join_build_assignments_package_sources() .
+ mysql_join_package_sources_upstream_repositories() .
+ mysql_join_upstream_repositories_git_repositories() .
+ mysql_join_build_assignments_binary_packages() .
+ mysql_join_binary_packages_binary_packages_in_repositories() .
+ mysql_join_binary_packages_in_repositories_repositories() .
+ ' WHERE `repositories`.`name`="build-list"'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `rd_q`(' .
+ '`build_assignment` BIGINT,' .
+ '`run_dependencies_pending` MEDIUMINT,' .
+ 'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `rd_q`' .
+ 'SELECT' .
+ ' `dependent_bp`.`build_assignment`,' .
+ 'COUNT(DISTINCT `dependency_bp`.`build_assignment`)' .
+ ' FROM `binary_packages` AS `dependent_bp`' .
+// only consider ba_q build assignments!
+ mysql_join_binary_packages_build_assignments('dependent_bp','ba_q') .
+ mysql_join_binary_packages_dependencies('dependent_bp') .
+ mysql_join_dependencies_dependency_types() .
+ mysql_join_dependencies_install_target_providers() .
+ mysql_join_install_target_providers_binary_packages('','dependency_bp') .
+ mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp') .
+ mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+ mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
+ ' JOIN `architecture_compatibilities` AS `ac_a`'.
+ ' ON `ac_a`.`fully_compatible`'.
+ ' AND `ac_a`.`built_for`=`dependency_ba`.`architecture`'.
+ ' JOIN `architecture_compatibilities` AS `ac_b`'.
+ ' ON `ac_b`.`fully_compatible`'.
+ ' AND `ac_b`.`built_for`=`dependent_ba`.`architecture`'.
+ ' AND `ac_b`.`runs_on`=`ac_a`.`runs_on`'.
+ mysql_join_binary_packages_in_repositories_repositories() .
+ ' WHERE `dependency_bp`.`build_assignment` != `dependent_bp`.`build_assignment`' .
+ ' AND `dependency_types`.`relevant_for_building`' .
+ ' AND `dependency_types`.`relevant_for_binary_packages`' .
+ ' AND `repositories`.`name`="build-list"' .
+ ' GROUP BY `dependent_bp`.`build_assignment`'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `md_q`(' .
+ '`build_assignment` BIGINT,' .
+ '`make_dependencies_pending` MEDIUMINT,' .
+ 'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `md_q`' .
+ ' SELECT' .
+ ' `dependent_bp`.`build_assignment`,' .
+ 'COUNT(DISTINCT `dependencies`.`id`)' .
+ ' FROM `binary_packages` AS `dependent_bp`' .
+// only consider ba_q build assignments!
+ mysql_join_binary_packages_build_assignments('dependent_bp','ba_q') .
+ mysql_join_binary_packages_dependencies('dependent_bp') .
+ mysql_join_dependencies_dependency_types() .
+ mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+ ' JOIN `architecture_compatibilities` AS `ac_b`' .
+ ' ON `ac_b`.`fully_compatible`' .
+ ' AND `ac_b`.`built_for`=`dependent_ba`.`architecture`' .
+ ' WHERE NOT EXISTS(' .
+ 'SELECT 1 FROM `install_target_providers`' .
mysql_join_install_target_providers_binary_packages('','dependency_bp') .
- mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp') .
- mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
+ mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp','dependency_bpir') .
+ mysql_join_binary_packages_in_repositories_repositories('dependency_bpir') .
mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
- " JOIN `architecture_compatibilities` AS `ac_a`".
- " ON `ac_a`.`fully_compatible`".
- " AND `ac_a`.`built_for`=`dependency_ba`.`architecture`".
- " JOIN `architecture_compatibilities` AS `ac_b`".
- " ON `ac_b`.`fully_compatible`".
- " AND `ac_b`.`built_for`=`dependent_ba`.`architecture`".
- " AND `ac_b`.`runs_on`=`ac_a`.`runs_on`".
- mysql_join_binary_packages_in_repositories_repositories() .
- " WHERE `dependency_bp`.`build_assignment` != `dependent_bp`.`build_assignment`" .
- " AND `dependency_types`.`relevant_for_building`" .
- " AND `dependency_types`.`relevant_for_binary_packages`" .
- " AND `repositories`.`name`=\"build-list\"" .
- " GROUP BY `dependent_bp`.`build_assignment`" .
- ") AS `rd_q` ON `rd_q`.`build_assignment`=`ba_q`.`id`" .
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`dependent_bp`.`build_assignment`," .
- "COUNT(DISTINCT `dependencies`.`id`) AS `make_dependencies_pending`" .
- " FROM `binary_packages` AS `dependent_bp`" .
- mysql_join_binary_packages_dependencies('dependent_bp') .
- mysql_join_dependencies_dependency_types() .
- mysql_join_binary_packages_build_assignments('dependent_bp','dependent_ba') .
- " JOIN `architecture_compatibilities` AS `ac_b`".
- " ON `ac_b`.`fully_compatible`".
- " AND `ac_b`.`built_for`=`dependent_ba`.`architecture`".
- " WHERE NOT EXISTS(" .
- "SELECT 1 FROM `install_target_providers`" .
- mysql_join_install_target_providers_binary_packages('','dependency_bp') .
- mysql_join_binary_packages_binary_packages_in_repositories('dependency_bp','dependency_bpir') .
- mysql_join_binary_packages_in_repositories_repositories('dependency_bpir') .
- mysql_join_binary_packages_build_assignments('dependency_bp','dependency_ba') .
- " JOIN `architecture_compatibilities` AS `ac_a`".
- " ON `ac_a`.`fully_compatible`".
- " AND `ac_a`.`built_for`=`dependency_ba`.`architecture`".
- " WHERE `install_target_providers`.`install_target` = `dependencies`.`depending_on`" .
- " AND `repositories`.`is_on_master_mirror`" .
- " AND `ac_b`.`runs_on`=`ac_a`.`runs_on`".
- ")" .
- " AND `dependency_types`.`relevant_for_building`" .
- " AND NOT `dependency_types`.`relevant_for_binary_packages`" .
- " GROUP BY `dependent_bp`.`build_assignment`" .
- ") AS `md_q` ON `md_q`.`build_assignment`=`ba_q`.`id`" .
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`build_dependency_loops`.`build_assignment`," .
- "COUNT(1) AS `loops`" .
- " FROM `build_dependency_loops`" .
- " GROUP BY `build_dependency_loops`.`build_assignment`" .
- ") AS `l_q` ON `l_q`.`build_assignment`=`ba_q`.`id`" .
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`rfb`.`build_assignment`," .
- "GROUP_CONCAT(" .
- "CONCAT(" .
- "\"<a href=\\\"https://buildmaster.archlinux32.org/build-logs/error/\"," .
- mysql_url_encode("`rfb`.`log_file`") . "," .
- "\"\\\">\"," .
- "`fail_reasons`.`name`," .
- "\"</a>\"" .
- ")" .
- " ORDER BY `fail_reasons`.`name`" .
- ") AS `fail_reasons_print`," .
- "CONCAT(" .
- "\",\"," .
- "GROUP_CONCAT(" .
- "`fail_reasons`.`name`" .
- ")," .
- "\",\"" .
- ") AS `fail_reasons_raw`" .
- " FROM (" .
- "SELECT " .
- "`failed_builds`.`build_assignment`," .
- "`failed_builds`.`reason`," .
- "MAX(`failed_builds`.`date`) AS `max_date`" .
- " FROM `failed_builds`" .
- " GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`" .
- ") AS `cfb`" .
- " JOIN" .
- " (" .
- "SELECT DISTINCT " .
- "`failed_builds`.*" .
- " FROM `failed_builds`" .
- " GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`,`failed_builds`.`date`" .
- ") AS `rfb`" .
- " ON `cfb`.`build_assignment`=`rfb`.`build_assignment`" .
- " AND `cfb`.`reason`=`rfb`.`reason`" .
- " AND `cfb`.`max_date`=`rfb`.`date`" .
+ ' JOIN `architecture_compatibilities` AS `ac_a`' .
+ ' ON `ac_a`.`fully_compatible`' .
+ ' AND `ac_a`.`built_for`=`dependency_ba`.`architecture`' .
+ ' WHERE `install_target_providers`.`install_target` = `dependencies`.`depending_on`' .
+ ' AND `repositories`.`is_on_master_mirror`' .
+ ' AND `ac_b`.`runs_on`=`ac_a`.`runs_on`' .
+ ')' .
+ ' AND `dependency_types`.`relevant_for_building`' .
+ ' AND NOT `dependency_types`.`relevant_for_binary_packages`' .
+ ' GROUP BY `dependent_bp`.`build_assignment`'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `l_q`(' .
+ '`build_assignment` BIGINT,' .
+ '`loops` MEDIUMINT,' .
+ 'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `l_q`' .
+ ' SELECT' .
+ ' `build_dependency_loops`.`build_assignment`,' .
+ 'COUNT(1)' .
+ ' FROM `build_dependency_loops`' .
+// no need to filter for ba_q build assignments: only *those* can be in loops anyways
+ ' GROUP BY `build_dependency_loops`.`build_assignment`'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `fr_q`(' .
+ '`build_assignment` BIGINT,' .
+ '`fail_reasons_print` TEXT,' .
+ '`fail_reasons_raw` TEXT,' .
+ 'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `fr_q`' .
+ ' SELECT' .
+ ' `rfb`.`build_assignment`,' .
+ 'GROUP_CONCAT(' .
+ 'CONCAT(' .
+ '"<a href=\"https://buildmaster.archlinux32.org/build-logs/error/",' .
+ mysql_url_encode('`rfb`.`log_file`') . ',' .
+ '"\">",' .
+ '`fail_reasons`.`name`,' .
+ '"</a>"' .
+ ')' .
+ ' ORDER BY `fail_reasons`.`name`' .
+ '),' .
+ 'CONCAT(' .
+ '",",' .
+ 'GROUP_CONCAT(' .
+ '`fail_reasons`.`name`' .
+ '),' .
+ '","' .
+ ')' .
+ ' FROM (' .
+ 'SELECT ' .
+ '`failed_builds`.`build_assignment`,' .
+ '`failed_builds`.`reason`,' .
+ 'MAX(`failed_builds`.`date`) AS `max_date`' .
+ ' FROM `failed_builds`' .
+// no need to filter for ba_q build assignments: only *those* can be failed anyways
+ ' GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`' .
+ ') AS `cfb`' .
+ ' JOIN' .
+ ' (' .
+ 'SELECT DISTINCT ' .
+ '`failed_builds`.*' .
+ ' FROM `failed_builds`' .
+ ' GROUP BY `failed_builds`.`build_assignment`,`failed_builds`.`reason`,`failed_builds`.`date`' .
+ ') AS `rfb`' .
+ ' ON `cfb`.`build_assignment`=`rfb`.`build_assignment`' .
+ ' AND `cfb`.`reason`=`rfb`.`reason`' .
+ ' AND `cfb`.`max_date`=`rfb`.`date`' .
mysql_join_failed_builds_fail_reasons('rfb') .
- " GROUP BY `rfb`.`build_assignment`" .
- ") AS `fr_q` ON `fr_q`.`build_assignment`=`ba_q`.`id`" .
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`failed_builds`.`build_assignment`," .
- "COUNT(`failed_builds`.`id`) AS `trials`" .
- " FROM `failed_builds`" .
- " GROUP BY `failed_builds`.`build_assignment`" .
- ") AS `t_q` ON `t_q`.`build_assignment`=`ba_q`.`id`" .
- " LEFT JOIN" .
- " (" .
- "SELECT " .
- "`build_slaves`.`currently_building`," .
- "GROUP_CONCAT(`build_slaves`.`name`) AS `build_slave`" .
- " FROM `build_slaves`" .
- " GROUP BY `build_slaves`.`currently_building`" .
- ") AS `bs_q` ON `bs_q`.`currently_building`=`ba_q`.`id`" .
+ ' GROUP BY `rfb`.`build_assignment`'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `t_q`(' .
+ '`build_assignment` BIGINT,' .
+ '`trials` MEDIUMINT,' .
+ 'PRIMARY KEY `build_assignment`(`build_assignment`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `t_q`' .
+ ' SELECT' .
+ ' `failed_builds`.`build_assignment`,' .
+ 'COUNT(`failed_builds`.`id`)' .
+ ' FROM `failed_builds`' .
+// no need to filter for ba_q build assignments: only *those* can be failed anyways
+ ' GROUP BY `failed_builds`.`build_assignment`'
+);
+
+mysql_run_query(
+ 'CREATE TEMPORARY TABLE `bs_q` (' .
+ '`currently_building` BIGINT,' .
+ '`build_slave` VARCHAR(32),' .
+ 'PRIMARY KEY `currently_building`(`currently_building`)' .
+ ')'
+);
+
+mysql_run_query(
+ 'INSERT INTO `bs_q`' .
+ ' SELECT' .
+ ' `build_slaves`.`currently_building`,' .
+ 'GROUP_CONCAT(`build_slaves`.`name`) AS `build_slave`' .
+ ' FROM `build_slaves`' .
+ ' WHERE NOT `build_slaves`.`currently_building` IS NULL' .
+ ' GROUP BY `build_slaves`.`currently_building`'
+);
+
+$result = mysql_run_query(
+ 'SELECT ' .
+ implode(',', array_map('combine_fields', $columns)) .
+ ' FROM `ba_q`'.
+ ' LEFT JOIN `rd_q`' .
+ ' ON `rd_q`.`build_assignment`=`ba_q`.`id`' .
+ ' LEFT JOIN `md_q`' .
+ ' ON `md_q`.`build_assignment`=`ba_q`.`id`' .
+ ' LEFT JOIN `l_q`' .
+ ' ON `l_q`.`build_assignment`=`ba_q`.`id`' .
+ ' LEFT JOIN `fr_q`' .
+ ' ON `fr_q`.`build_assignment`=`ba_q`.`id`' .
+ ' LEFT JOIN `t_q`' .
+ ' ON `t_q`.`build_assignment`=`ba_q`.`id`' .
+ ' LEFT JOIN `bs_q`' .
+ ' ON `bs_q`.`currently_building`=`ba_q`.`id`' .
$filter .
" ORDER BY " . $order . "`trials` " . $direction . ",`dependencies_pending` " . $direction . ",`is_blocked` " . $direction . ",`pkgbase` " . $direction
);