Database Slow Queries caused by dynamic fields

[estoque]

Im using OTRS 4.0.6 and I'm having problems with slow query congestion which will then need database restart. I am using around 10 dynamic field values on ticket creation.

This is the query in Kernel/System/DynamicFieldValue.pm which registers most of the slow queries.
"SELECT DISTINCT($ValueType) FROM dynamic_field_value WHERE field_id = ?"

On which specific part of OTRS is this used? and is there any way to optimize it?

[jojo]

you should upgrade to the latest versionto get bugfixes etc. Also it seems that your database server sizing does not match so enhance the database server with more cpu and memory

[estoque]

Thanks for the inputs. However upgrade is out of the options at the moment due to the numerous customization done. I would just like to know on which specific part of OTRS requires a query that SELECT all values on dynamic_field_value. If you can help me identify where is that module then I will either hide it or modify the query(if possible).

Im using a virtualized DB server, 32 Cores with 64GB RAM on a system with 20000 open tickets

[root]

You should enable the slow query log of MySQL and try to create custom indexes. You DBA will be your best friend for this.

[EXG133]

I think those are mostly used for reports. I hope you're not using the built in reporting for such a large system?

[estoque]

I already enabled slow query log of mysql thats why I identified the queries causing the congestion. I traced it to function HistoricalValueGet of Kernel/System/DynamicFieldValue.pm(?), and some certain scripts at kernel/System/DynamicField/Driver. My problem now is where are the users navigating on the web for OTRS to use HistoricalValueGet function.

[EXG133]

That's what I said: reports.
If you create a report it's possible to filter on Dynamic Field values, even ones that are no longer active in the Dynamic Field configuration.

Dynamic Field has two values A and B. You create 10 tickets with value A and 10 with value B.
At some point your Dynamic Field is updated: you remove values A and B in the AdminDynamicField and you add values D and E.
In the frontend A and B will not be visible anymore, but when you create a statistic it will check the entire Dynamic Field table for all values every used to present it in a dropdown field -> expensive query!

I don't know of any other case where this is used, but I might be wrong.

[estoque]

EXG133 I already disabled the statistics module however i'm still seeing these queries. I'm still tracing where are the users navigating to execute that query.

[EXG133]

Out of curiosity, what does

select count(*) from dynamic_field_value

return for you? I get 528 846 and I haven't received any complaint from the DBA's yet

It looks like it can also be triggered by CustomerSearch or AgentSearch, can you test that?

[estoque]

i got 4229819, for the first few months there are no problems.

As of the moment there are no dynamic fields on AgentTicketSearch and I can't think of any relationship between the attributes on search with dynamic fields.

Although don't like my temporary crude fix, I implemented it anyway. I added a condition on the SQL query, id > 4000000, this way the query will only go through 200k+ records.

I'm still working on a permanent fix.