Slow performance: select DISTINCT

Get help from other users here.

Moderators: Developer, Contributor

Post Reply
plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Slow performance: select DISTINCT

Post by plume7 » Sep 24, 2007 6:58 am

Hello,

I use Mantis 1.0.0a2, I have less than 2000 issues in my DB.
I have very slow performance: it could be linked to select DISTINCT requests that slow the application.
I recently imported around 1200 and the problem appeared after that, when the new users of this DB began to work with Mantis.
Has anybody an idea to solve that?
Thanks
:cry:

the Mysql shoq process list :
Command Time State Info
Query 1035 Copying to tmp table SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_bug_text_table, mantis_project_table, ma
Query 941 Copying to tmp table SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_bug_text_table, mantis_project_table, ma
Query 918 Copying to tmp table SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_bug_text_table, mantis_project_table, ma
Query 908 Locked UPDATE mantis_bug_table SET last_updated= '2007-09-20 10:18:26' WHERE id='867'
Query 869 Locked SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_bug_text_table, mantis_project_table, ma

vboctor
Site Admin
Posts: 1286
Joined: Feb 13, 2005 5:11 pm
Location: Redmond, Washington
Contact:

Post by vboctor » Sep 26, 2007 1:21 am

This may be related to the following issue:
http://www.mantisbt.org/bugs/view.php?id=7516

Please test the patch and let us know the results.
Migrate your MantisBT to the MantisHub Cloud

plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Still problems

Post by plume7 » Oct 02, 2007 7:51 am

Thanks but the issue 0007516 brings no workaround.
I am facing a great problem: select distinct queries create a bottleneck and the users can no more work.
The only solution for me continously watch to the queries and kill too long queries and I can keep on doing that all my life :x
Please help.

vboctor
Site Admin
Posts: 1286
Joined: Feb 13, 2005 5:11 pm
Location: Redmond, Washington
Contact:

Post by vboctor » Oct 02, 2007 9:21 am

Did you try rebuilding the indices? Sometimes rebuilding the indices in MySQL fixes this performance issue. For the number of issues you have, this doesn't seem to be a query problem.

How many users you have? How many are concurrently logged in?
Migrate your MantisBT to the MantisHub Cloud

plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Post by plume7 » Oct 03, 2007 2:35 am

Hello,
I have 116 users, 30-40 should be connected as the same time.
BR

plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Post by plume7 » Oct 04, 2007 3:40 am

Hello Vboctor,
I have interesting screen copies of the MySQL administrator tool showing what happens to the Mantis DB.
How can I send you this powerpoint file?
BR

plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Post by plume7 » Oct 08, 2007 6:17 am

Hello,

We will try to rebuild the indexes on Thursday.
In the meanwhile I can precise what happens:
Sometimes, one or more select distinct mantis_bug_table.id AS is from mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN queries do not finished when copying to tmp table.
Then if an insert query or an update come, its locks the table
and all next queries (eg.select) are locked too.
All is blocked.
The only way to keep on working is to delete all select distinct queries that have troubles to finish.
BR

plume7
Posts: 28
Joined: Sep 28, 2006 5:03 am

Post by plume7 » Oct 25, 2007 2:27 am

The index rebuild was done.

The situation is better but the same blocking situations continue, less frequently, but still appear.
If someone has any other idea to solve that, I should appreciate.

Post Reply