View Issue Details

IDProjectCategoryView StatusLast Update
0020900mantisbtdb mysqlpublic2016-06-12 00:46
ReporterUniversidad-ICESI Assigned Toatrol  
PriorityhighSeveritymajorReproducibilityalways
Status closedResolutionno change required 
Product Version1.2.19 
Summary0020900: Improvement in the database
Description

Greetings,

We have a problem with the time for database queries because it is a lot, we have arround of 280000 issues and 3000 users in the database,
Is there something that allows me to improve the database ? Can be index it ? and what impact can be high ? What could we suggest to improve performance ?

I hope to hear from somebody soon...

TagsNo tags attached.

Relationships

related to 0021038 closedatrol Improvement in the database - "My View" page is very slow. 

Activities

atrol

atrol

2016-05-11 17:14

developer   ~0053124

We have a problem with the time for database queries
which query does take how much time?

Universidad-ICESI

Universidad-ICESI

2016-05-11 17:22

reporter   ~0053125

Thank you very much for the reply.

when we open an issue, perform filters by users, in short is a general problem.

atrol

atrol

2016-05-11 17:48

developer   ~0053126

Last edited: 2016-05-11 17:48

Universidad-ICESI,

My question was:

which query does take how much time?
but you did not provide an answer for it.

you wrote

when we open an issue
Does it mean that it takes a long time to show the page after clicking "Report Issue" link?

The provided information is not sufficient to provide help in resolving the issue. A complete and detailed description is required for the support team to get a clear understanding of the problem.

Please explain what you do, what are the results you expect to get and what you actually get.

Also provide detailed, step-by-step instructions to reproduce the issue; the additional information listed below may also be useful:

  • Exact version of PHP, Database, Web server, Browser and Operating System
  • Relevant customizations (e.g. changes in config_inc.php, etc)
  • Installed plugins or custom functions ?
  • Was the MantisBT source code modified in any way ?
dregad

dregad

2016-05-12 05:47

developer   ~0053128

Database operations and optimization is on principle outside the scope of MantisBT support. That being said, if you can demonstrate that the poor performance is caused by our code or database design - or even better, propose solutions to improve things - then we'll see what we can do to address the issues.

To identify the root cause, you need to analyze the queries being executed in the slow-loading pages. To do so:

  • 1.3.x: set $g_log_level = LOG_DATABASE; and $g_log_destination as appropriate

  • 1.2.x: additionally, set $g_show_queries_count = $g_show_queries_list = ON; in config_inc.php. The queries will logged, as well as printed at the bottom of the page (by default only admins will see this output, to enable it for other users, set $g_show_queries_threshold)

Review the log file, it will display executed queries like this (Screen output has the same information, formatted in a slightly different way):

<pre>
2016-05-12 09:21 UTC database array (
0 => 'UPDATE mantis_user_table
SET last_visit= 1463044862
WHERE id=1',
1 => '0.0005',
2 => 'user_api.php:1158 user_update_last_visit()',
)
</pre>

In the above example, 0.0005 is the execution time (in seconds) - look for the queries that take longest to run.

atrol

atrol

2016-05-12 05:54

developer   ~0053129

Thanks @dregad for the note.

I thought it would be better to find out in a first step if this is a database issue or not.
It could also be our "typical email sending issue" (lots of unsent entries in email queue)

atrol

atrol

2016-05-22 16:14

developer   ~0053206

Universidad-ICESI,

You did not provide any feedback; I am therefore resolving this issue as "no change required".

Feel free to reopen the issue at a later time and provide the requested information.