summaryrefslogtreecommitdiff
path: root/bin/build-master-status
diff options
context:
space:
mode:
authorErich Eckner <git@eckner.net>2018-10-17 15:03:00 +0200
committerErich Eckner <git@eckner.net>2018-10-17 15:03:00 +0200
commit66eca9e7c18c3c62a056ccd27a626b2148945a7a (patch)
tree9bdd68cc9ad55fbc2e345ae9ef9b5585a35dc756 /bin/build-master-status
parente708441a8c782c9304d2e1ab97fce320a7f09a2c (diff)
downloadbuilder-66eca9e7c18c3c62a056ccd27a626b2148945a7a.tar.xz
bin/build-master-status: accellerate query by using "GROUP BY"
Diffstat (limited to 'bin/build-master-status')
-rwxr-xr-xbin/build-master-status285
1 files changed, 128 insertions, 157 deletions
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