Transitions execution time - Oops

General questions
1

Hi,

We have a high traffic site, with plenty of work done to make use of queued tracking and cron-based archiving. Now we’re hitting the point where transitions are giving us an Oops message later in the day.

I understand these are live queries, rather than archiving-based, and from the code, it looks like the queries themselves are governed by the ‘live_query_max_execution_time’ setting.

When I change this setting to 300, I can see the hint be included in the query to the database.

However, we still get an Oops message on the UI (behind the pop-up) after 30s, and no change to the foreground popup after any amount of time.

How do I change the 30s timeout in the UI?

2

I’ve also been paying attention to the FAQ on coping with transitions on high-traffic sites at https://matomo.org/faq/how-to/faq_161/

We implemented the suggested “ADD INDEX” commands, and that had the desired impact when viewing transitions from the Pages screen (ie via URLs), but it doesn’t help when viewing transitions from the Page Titles screen. In the DB, the query times are as long as before.

The SQL looks slightly different, so I guess an EXPLAIN will show a different index dependency list

3

I’ve confirmed with EXPLAIN that the query doesn’t use the new indexes.

Is the solution to add yet more indexes? Something like this:

ALTER TABLE  `log_link_visit_action` ADD INDEX `transitions_name` (  `idaction_name` ,  `idsite` ,  `server_time` ), ADD INDEX `transitions_name_ref` (  `idaction_name_ref` ,  `idsite` ,  `server_time` );

ALTER TABLE  `log_visit` ADD INDEX `transitions_name` (  `visit_entry_idaction_name` ,  `idsite` ,  `visit_last_action_time` );

I’m a little wary, on reading the message in this post, that having more indexes will slow down tracking: https://github.com/matomo-org/matomo/issues/14762

“I would definitely not want to see these indexes there as it makes things so much slower in the critical tracker part.”