From 61988d1f36d3a99b815a19ce459e452524adfa1e Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Tue, 23 Jul 2019 14:03:49 +0200 Subject: buildmaster/build-list.php: use temporary tables to accellerate query --- buildmaster/build-list.php | 395 ++++++++++++++++++++++++++++----------------- 1 file changed, 246 insertions(+), 149 deletions(-) (limited to 'buildmaster') 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(" . - "\"\"," . - "`fail_reasons`.`name`," . - "\"\"" . - ")" . - " 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(' . + '"",' . + '`fail_reasons`.`name`,' . + '""' . + ')' . + ' 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 ); -- cgit v1.2.3-70-g09d2