Versions:
Ubuntu 7.10
phpbt-1.0.4.tgz
mysql Ver 14.12 Distrib 5.0.45
With 25 bug reports filed in 2 projects, a simple query of type "all-but-closed" takes 15 seconds. This time is consumed by mysqld which causes a CPU load of 100%. The host is a "Intel Core2 Duo @ 2.40GHz", 2GB RAM.
The problem may have showed up when creating the 2nd project, not quite sure.
This renders phpBugTracker completely useless because there is no confidence that PHPBT will last...
I'm aware that this problem might be a MySQL-one. "bug_tracker" is the only DB and the SQL dump (ASCII) is 60kB.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here's the "slow query log" output and the query is a heavy one, but should not overtax a Core2 with 2GB RAM and just 33 bug reports filed:
/usr/sbin/mysqld, Version: 5.0.45-Debian_1ubuntu3.4-log (Debian etch distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 090527 11:56:50
# User@Host: root[root] @ localhost []
# Query_time: 16 Lock_time: 0 Rows_sent: 10 Rows_examined: 588
use bug_tracker;
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;
---------------------------
I confess that I have removed some bugs entirely from the DB with this SQL script and I hope that these "missing bugs" aren't the cause:
use bug_tracker
DELETE FROM phpbt_attachment WHERE bug_id = $1;
DELETE FROM phpbt_bookmark WHERE bug_id = $1;
DELETE FROM phpbt_bug WHERE bug_id = $1;
DELETE FROM phpbt_bug_cc WHERE bug_id = $1;
DELETE FROM phpbt_bug_dependency WHERE bug_id = $1;
DELETE FROM phpbt_bug_group WHERE bug_id = $1;
DELETE FROM phpbt_bug_history WHERE bug_id = $1;
DELETE FROM phpbt_bug_vote WHERE bug_id = $1;
DELETE FROM phpbt_comment WHERE bug_id = $1;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
But I think your solution is to upgrade to the version from CVS, which will build a new index for mysql tables:
log_query("create INDEX bug_id_attachment ON phpbt_attachment (bug_id);");
log_query("create INDEX bug_id_bookmark ON phpbt_bookmark (bug_id);");
log_query("create INDEX bug_id_comment ON phpbt_comment (bug_id);");
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Wowowoh, my High-Score now increased to 32 secondes per query with 42 bugs filed.
PhpBugTracker definitely is evil SCRAP! This is quite evident having a look at this braindead SQL query (keep in mind: only 42 bugs filed!):
# Time: 090604 9:14:23
# User@Host: root[root] @ localhost []
# Query_time: 32 Lock_time: 0 Rows_sent: 9 Rows_examined: 773
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
# Time: 090604 9:14:23
# User@Host: root[root] @ localhost []
# Query_time: 32 Lock_time: 0 Rows_sent: 9 Rows_examined: 773
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Versions:
Ubuntu 7.10
phpbt-1.0.4.tgz
mysql Ver 14.12 Distrib 5.0.45
With 25 bug reports filed in 2 projects, a simple query of type "all-but-closed" takes 15 seconds. This time is consumed by mysqld which causes a CPU load of 100%. The host is a "Intel Core2 Duo @ 2.40GHz", 2GB RAM.
The problem may have showed up when creating the 2nd project, not quite sure.
This renders phpBugTracker completely useless because there is no confidence that PHPBT will last...
I'm aware that this problem might be a MySQL-one. "bug_tracker" is the only DB and the SQL dump (ASCII) is 60kB.
To track this down, enable the "slow query log" in mysql, and post the result.
phpBT is very fast on mysql, as long as "query caching" is enabled.
Here's the "slow query log" output and the query is a heavy one, but should not overtax a Core2 with 2GB RAM and just 33 bug reports filed:
/usr/sbin/mysqld, Version: 5.0.45-Debian_1ubuntu3.4-log (Debian etch distribution). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 090527 11:56:50
# User@Host: root[root] @ localhost []
# Query_time: 16 Lock_time: 0 Rows_sent: 10 Rows_examined: 588
use bug_tracker;
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;
---------------------------
I confess that I have removed some bugs entirely from the DB with this SQL script and I hope that these "missing bugs" aren't the cause:
use bug_tracker
DELETE FROM phpbt_attachment WHERE bug_id = $1;
DELETE FROM phpbt_bookmark WHERE bug_id = $1;
DELETE FROM phpbt_bug WHERE bug_id = $1;
DELETE FROM phpbt_bug_cc WHERE bug_id = $1;
DELETE FROM phpbt_bug_dependency WHERE bug_id = $1;
DELETE FROM phpbt_bug_group WHERE bug_id = $1;
DELETE FROM phpbt_bug_history WHERE bug_id = $1;
DELETE FROM phpbt_bug_vote WHERE bug_id = $1;
DELETE FROM phpbt_comment WHERE bug_id = $1;
I think the deletes are fine.
You can use "explain" to analyze the above query.
But I think your solution is to upgrade to the version from CVS, which will build a new index for mysql tables:
log_query("create INDEX bug_id_attachment ON phpbt_attachment (bug_id);");
log_query("create INDEX bug_id_bookmark ON phpbt_bookmark (bug_id);");
log_query("create INDEX bug_id_comment ON phpbt_comment (bug_id);");
Wowowoh, my High-Score now increased to 32 secondes per query with 42 bugs filed.
PhpBugTracker definitely is evil SCRAP! This is quite evident having a look at this braindead SQL query (keep in mind: only 42 bugs filed!):
# Time: 090604 9:14:23
# User@Host: root[root] @ localhost []
# Query_time: 32 Lock_time: 0 Rows_sent: 9 Rows_examined: 773
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;
Yes, I know, thats not funny!
# Time: 090604 9:14:23
# User@Host: root[root] @ localhost []
# Query_time: 32 Lock_time: 0 Rows_sent: 9 Rows_examined: 773
select b.bug_id as bug_link_id, severity.severity_color, priority.priority_color, b.bug_id, title, status.status_name, resolution_name, reporter.login as reporter, owner.login as owner, component.component_name from phpbt_bug b left join phpbt_auth_user owner on b.assigned_to = owner.user_id left join phpbt_auth_user reporter on b.created_by = reporter.user_id left join phpbt_auth_user lastmodifier on b.last_modified_by = lastmodifier.user_id left join phpbt_comment comment on b.bug_id = comment.bug_id left join phpbt_attachment attachment on b.bug_id = attachment.bug_id left join phpbt_bug_vote vote on b.bug_id = vote.bug_id left join phpbt_bookmark bookmark on b.bug_id = bookmark.bug_id left join phpbt_resolution resolution on b.resolution_id = resolution.resolution_id left join phpbt_database_server on b.database_id = phpbt_database_server.database_id left join phpbt_version version2 on b.to_be_closed_in_version_id = version2.version_id left join phpbt_version version3 on b.closed_in_version_id = version3.version_id, phpbt_severity severity, phpbt_status status, phpbt_os os, phpbt_site site, phpbt_version version, phpbt_component component, phpbt_project project, phpbt_priority priority where b.severity_id = severity.severity_id and b.priority = priority.priority_id and b.status_id = status.status_id and b.os_id = os.os_id and b.site_id = site.site_id and b.version_id = version.version_id and b.component_id = component.component_id and b.project_id = project.project_id and (b.status_id in (1,2,8,5)) group by b.bug_id order by priority.sort_order asc, b.bug_id asc LIMIT 0, 20;