I'm working with TMQ on a large repo (about 10 millions of docs) on a community 5.1.g.
When running a batch of queries using SearchService API (with transactional queryConsistency), at a regular interval the response time is beyond 3 mn (sometimes 10mn or higher!).
All other queries return almost immediately.
All queries are strictly identical, just parameters (property values) change.
We tried to run SearchService queries starting each time a new RO transactions (retryingtransactionhelper) without any positive effect.
This behavior is easily reproductible, even with no other activity on the repo (just run a batch of queries, and every 5-6 queries one is 'stuck' during minutes).
We can see the long duration SELECT query in pg_stat_activity in postgres DB.
- run exactly the same batch of queries directly in SQL using psql client -> performances are very good. (SQL SELECT produced by SearchService have been captured and rerun with psql script)
- write an alf webscript that connect directly in SQL (instead of using SearchService), and run exactly the same batch of queries -> performances are very good.
It means that when using SearchService, SQL queries context have something that limit in anyway the query performances...
We did the basics maintenance operations on DB (index, vacuum, analyse...), and the native SQL query performances means that the DB itself is OK.
We checked this link, but I'm still out of ideas
Any advice ?