Slow queries REST2 API

RT Users
1

Hi everyone,

I have a slow query problem with the RT REST2 API. So basically, I have a script to read the user data from the /REST/2.0/user/id route, and for some users the request goes for too long, and I get a timeout from the web server ( 500 ERROR ). But, the problem as I’ve discover from the logs, is with this query from db
SELECT main.* FROM Transactions main WHERE (main.ObjectId = 'USER_ID') AND (main.ObjectType = 'RT::User') ORDER BY main.id DESC; . This issues is only for some users, not all the users.

If I acces the same user data from the web interface with this route ( /Admin/Users/Modify.html?id=USER_ID ) , the request is complete successfully.

I have a solution in mind: is it possible to filter the fields I get from the API response ? In this script, I only need the Name of the user from the JSON that the API returns, and if I only get the Name I guess the query to the Transactions table will not be executed anymore. I’ve read the REST2 API docs from the metacpan.org but I had no success filtering the JSON fields with this specific API route.

Or maybe someone could help me with the slow queries from the Transactions table.

Thanks!

2

Hey,

For the slow queries, can you produce the slow results by running the SQL manually? Perhaps there is some index oddity going on.

With limiting the fields returned, you can’t do that when fetching individual objects, only with search results.

Cheers,
Andrew

3

Hi Andrew,

Meanwhile I’ve ran the query manually, and I found “the issue” . We use LDAP Auth in our RT instance, and we have some User CustomFields that we use to populate with some data from ActiveDirectory, and with every LDAP sync that we run, the rt-ldapimport script, writes some transactions for those field in Transactions table, but the data coming from ActiveDriectory is the same every time we run the rt-ldapimport script, and we have to run the script every day a few times a day.

Now I’m thinking if I delete all the “duplicate” data from Transactions table for those User CustomFields, it will cause any issues on the RT ? I have hundreds of transactions with the same value for old_cf_value and new_cf_value.

That’s why I have a timeout on the API call, because the query running time is very long

Thanks,
Jacob.

4

Hi Jacob,

Yeah, that’d certainly explain the slow response time!

Unfortunately I can’t say if it is safe to delete those records…

Sorry I can’t help more!

5

For anyone reading this in the future, the problem was something related to our company. Had nothing to do with RT. We found the source of the issue, and resolved it. We had like 1 milion transaction in DB for User CustomFilelds, because of a bug in other application, that is used to get data to AD.