From 66eca9e7c18c3c62a056ccd27a626b2148945a7a Mon Sep 17 00:00:00 2001 From: Erich Eckner Date: Wed, 17 Oct 2018 15:03:00 +0200 Subject: bin/build-master-status: accellerate query by using "GROUP BY" --- bin/build-master-status | 285 ++++++++++++++++++++++-------------------------- 1 file changed, 128 insertions(+), 157 deletions(-) (limited to 'bin/build-master-status') diff --git a/bin/build-master-status b/bin/build-master-status index c38d491..16d6c5a 100755 --- a/bin/build-master-status +++ b/bin/build-master-status @@ -96,170 +96,141 @@ if [ -s "${tmp_dir}/todos" ]; then fi rm -f "${tmp_dir}/todos" -# TODO: accellerate this query by using "GROUP BY"? if [ ! -s "${work_dir}/build-master-sanity" ]; then - now=$( - date '+%F %H:%M:%S' | \ - base64 -w0 - ) # shellcheck disable=SC2016 { - printf 'SELECT `architectures`.`id`' - printf ' FROM `architectures`;\n' - } | \ - mysql_run_query 'unimportant' | \ - while read -r arch_id; do - printf 'INSERT IGNORE INTO `statistics` (' - printf '`%s`,' \ - 'stable_packages_count' \ - 'pending_tasks_count' \ - 'pending_packages_count' \ - 'staging_packages_count' \ - 'testing_packages_count' \ - 'tested_packages_count' \ - 'broken_tasks_count' \ - 'dependency_loops_count' \ - 'dependency_looped_tasks_count' \ - 'locked_tasks_count' \ - 'blocked_tasks_count' \ - 'next_tasks_count' \ - 'architecture' \ - 'date' | \ - sed 's/,$//' - printf ') VALUES (' - # stable_packages_count - printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM' - printf ' `binary_packages`' - mysql_join_binary_packages_binary_packages_in_repositories - mysql_join_binary_packages_in_repositories_repositories - # shellcheck disable=SC2154 - printf ' WHERE `repositories`.`stability`=%s' \ - "${repository_stability_ids__stable}" - printf ' AND `repositories`.`architecture`=%s),' \ - "${arch_id}" - # pending_tasks_count - printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM' - printf ' `build_assignments`' + printf 'INSERT IGNORE INTO `statistics` (' + printf '`%s`,' \ + 'pending_tasks_count' \ + 'pending_packages_count' \ + 'broken_tasks_count' \ + 'dependency_loops_count' \ + 'dependency_looped_tasks_count' \ + 'locked_tasks_count' \ + 'blocked_tasks_count' \ + 'next_tasks_count' \ + 'stable_packages_count' \ + 'staging_packages_count' \ + 'testing_packages_count' \ + 'tested_packages_count' \ + 'architecture' | \ + sed 's/,$//' + printf ') SELECT ' + printf '`ba_q`.`%s`,' \ + 'pending_tasks_count' \ + 'pending_packages_count' \ + 'broken_tasks_count' \ + 'dependency_loops_count' \ + 'dependency_looped_tasks_count' \ + 'locked_tasks_count' \ + 'blocked_tasks_count' \ + 'next_tasks_count' + printf '`bp_q`.`%s`,' \ + 'stable_packages_count' \ + 'staging_packages_count' \ + 'testing_packages_count' \ + 'tested_packages_count' + printf '`architectures`.`id`' + printf ' FROM `architectures`' + printf ' JOIN (' + printf 'SELECT' + printf ' COUNT(' + printf 'DISTINCT `build_assignments`.`id`' + printf ') AS `pending_tasks_count`,' + printf ' COUNT(' + printf 'DISTINCT `binary_packages`.`id`' + printf ') AS `pending_packages_count`,' + printf ' COUNT(' + printf 'DISTINCT IF(' + printf '`build_assignments`.`is_broken`,' + printf '`build_assignments`.`id`,' + printf 'NULL' + printf ')' + printf ') AS `broken_tasks_count`,' + printf ' COUNT(' + printf 'DISTINCT `build_dependency_loops`.`loop`' + printf ') AS `dependency_loops_count`,' + printf ' COUNT(' + printf 'DISTINCT `build_dependency_loops`.`build_assignment`' + printf ') AS `dependency_looped_tasks_count`,' + printf ' COUNT(' + printf 'DISTINCT `build_slaves`.`currently_building`' + printf ') AS `locked_tasks_count`,' + printf ' COUNT(' + printf 'DISTINCT IF(' + printf '`build_assignments`.`is_blocked` IS NULL,' + printf 'NULL,' + printf '`build_assignments`.`id`' + printf ')' + printf ') AS `blocked_tasks_count`,' + printf ' COUNT(' + printf 'DISTINCT IF(' + mysql_query_has_pending_dependencies \ + "$( + # shellcheck disable=SC2154 + printf 'IF(`build_assignments`.`architecture`=%s,%s,`build_assignments`.`architecture`)' \ + "${architecture_ids__any}" \ + "${architecture_ids__i686}" + )" \ + '`build_assignments`.`id`' + printf ',' + printf '`build_assignments`.`id`,' + printf 'NULL' + printf ')' + printf ') AS `next_tasks_count`,' + printf ' `build_assignments`.`architecture`' + printf ' FROM `build_assignments`' mysql_join_build_assignments_binary_packages mysql_join_binary_packages_binary_packages_in_repositories - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # pending_packages_count - printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM' - printf ' `binary_packages`' - mysql_join_binary_packages_binary_packages_in_repositories - mysql_join_binary_packages_build_assignments - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # staging_packages_count - printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM' - printf ' `binary_packages`' - mysql_join_binary_packages_binary_packages_in_repositories - mysql_join_binary_packages_in_repositories_repositories - # shellcheck disable=SC2154 - printf ' WHERE `repositories`.`stability`=%s' \ - "${repository_stability_ids__staging}" - printf ' AND `repositories`.`architecture`=%s),' \ - "${arch_id}" - # testing_packages_count - printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM' - printf ' `binary_packages`' - mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories - # shellcheck disable=SC2154 - printf ' WHERE `repositories`.`stability`=%s' \ - "${repository_stability_ids__testing}" - printf ' AND NOT `binary_packages`.`is_tested`' - printf ' AND `repositories`.`architecture`=%s),' \ - "${arch_id}" - # tested_packages_count - printf '(SELECT COUNT(DISTINCT `binary_packages`.`id`) FROM' - printf ' `binary_packages`' + printf ' LEFT' + mysql_join_build_assignments_build_slaves + printf ' LEFT' + mysql_join_build_assignments_build_dependency_loops + printf ') AS `ba_q`' + printf ' ON `ba_q`.`architecture`=`architectures`.`id`' + printf ' JOIN (' + printf 'SELECT' + printf ' SUM(' + printf 'IF(' + # shellcheck disable=SC2154 + printf '`repositories`.`stability`=%s,' \ + "${repository_stability_ids__stable}" + printf '1,0' + printf ')' + printf ') AS `stable_packages_count`,' + printf ' SUM(' + printf 'IF(' + # shellcheck disable=SC2154 + printf '`repositories`.`stability`=%s,' \ + "${repository_stability_ids__staging}" + printf '1,0' + printf ')' + printf ') AS `staging_packages_count`,' + printf ' SUM(' + printf 'IF(' + # shellcheck disable=SC2154 + printf '`repositories`.`stability`=%s' \ + "${repository_stability_ids__testing}" + printf ' AND NOT `binary_packages`.`is_tested`,' + printf '1,0' + printf ')' + printf ') AS `testing_packages_count`,' + printf ' SUM(' + printf 'IF(' + printf '`repositories`.`stability`=%s' \ + "${repository_stability_ids__testing}" + printf ' AND `binary_packages`.`is_tested`,' + printf '1,0' + printf ')' + printf ') AS `tested_packages_count`,' + printf ' `repositories`.`architecture`' + printf ' FROM `binary_packages`' mysql_join_binary_packages_binary_packages_in_repositories mysql_join_binary_packages_in_repositories_repositories - printf ' WHERE `repositories`.`stability`=%s' \ - "${repository_stability_ids__testing}" - printf ' AND `binary_packages`.`is_tested`' - printf ' AND `repositories`.`architecture`=%s),' \ - "${arch_id}" - # broken_tasks_count - printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM' - printf ' `build_assignments`' - mysql_join_build_assignments_binary_packages - mysql_join_binary_packages_binary_packages_in_repositories - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `build_assignments`.`is_broken`' - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # dependency_loops_count - printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`loop`) FROM' - printf ' `build_dependency_loops`' - mysql_join_build_dependency_loops_build_assignments - printf ' WHERE `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # dependency_looped_tasks_count - printf '(SELECT COUNT(DISTINCT `build_dependency_loops`.`build_assignment`) FROM' - printf ' `build_dependency_loops`' - mysql_join_build_dependency_loops_build_assignments - printf ' WHERE `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # locked_tasks_count - printf '(SELECT COUNT(DISTINCT `build_slaves`.`currently_building`) FROM' - printf ' `build_slaves`' - mysql_join_build_slaves_build_assignments - mysql_join_build_assignments_binary_packages - mysql_join_binary_packages_binary_packages_in_repositories - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # blocked_tasks_count - printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM' - printf ' `build_assignments`' - mysql_join_build_assignments_binary_packages - mysql_join_binary_packages_binary_packages_in_repositories - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `build_assignments`.`is_blocked` IS NOT NULL' - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # next_tasks_count - printf '(SELECT COUNT(DISTINCT `build_assignments`.`id`) FROM' - printf ' `build_assignments`' - mysql_join_build_assignments_binary_packages - mysql_join_binary_packages_binary_packages_in_repositories - printf ' WHERE `binary_packages_in_repositories`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND (' - printf 'NOT EXISTS (' - printf 'SELECT 1 FROM `dependencies`' - mysql_join_dependencies_dependency_types - printf ' AND `dependency_types`.`relevant_for_building`' - mysql_join_dependencies_install_target_providers_with_versions - mysql_join_install_target_providers_binary_packages '' 'prov_bp' - mysql_join_binary_packages_binary_packages_in_repositories 'prov_bp' 'prov_bpir' - printf ' WHERE `prov_bpir`.`repository`=%s' \ - "${repository_ids__any_build_list}" - printf ' AND `dependencies`.`dependent`=`binary_packages`.`id`' - printf ') OR EXISTS (' - printf 'SELECT 1 FROM `build_dependency_loops`' - printf ' WHERE `build_dependency_loops`.`build_assignment`=`build_assignments`.`id`' - printf ')' - printf ')' - printf ' AND `build_assignments`.`architecture`=%s),' \ - "${arch_id}" - # architecture - printf '%s,' \ - "${arch_id}" - # date - printf 'from_base64("%s")' \ - "${now}" - printf ');\n' - done | \ + printf ' GROUP BY `repositories`.`architecture`' + printf ') AS `bp_q`' + printf ' ON `bp_q`.`architecture`=`architectures`.`id`;\n' + } | \ mysql_run_query 'unimportant' fi -- cgit v1.2.3-70-g09d2