Topics

Tribe Support

Tribe Support's Topics

Nick Swinford

Nick Swinford

November 30 2017

Assignment query is slow

So I'm in the process of migrating over to a new host and after testing the migration, I've run into an issue. There's one query that's taking about 3-5 seconds and slowing down my TTFB.

This is the assignments query from here (https://github.com/anahitasocial/anahita/blob/master/src/components/com_actors/domains/entitysets/component.php#L38).

Loading ...

Here's the EXPLAIN:

From what it looks like, it's checking every row in the database to see if it matches the criteria which is taking a long time as my nodes table has over 100k records.

Any ideas on how to speed it up? I've checked that my table looks okay and tried some basic ways to speed it up, but not sure where to continue.
Rastin Mehr
Rastin Mehr
November 30 2017 Permalink
Are you using entities in your migration script or it is happening in regular operation of Anahita? Normally if we use domain entities the generated queries aren't the most efficient, but they make app development way easier. It is a tradeoff. For specific case scenarios, we can write custom queries that are more efficient and bypass the domain entities generated queries. In migration scripts it is always better to use raw php and mysql queries instead of the domain entities.
Nick Swinford
Nick Swinford
November 30 2017 Permalink
Nah, this is just a basic page load of the front page while not logged in.
Rastin Mehr
Rastin Mehr
November 30 2017 Permalink
Or logged in? Why are assignments being checked for guest viewers? What do you display on that page?
Nick Swinford
Nick Swinford
December 01 2017 Permalink
Nothing's on the page but the default in shiraz as I switch back to the default template to see if my template was causing the issue.

When you're logged in, as you know, it becomes the dashboard. Maybe that's why it's looking for app assignments? It's slow even when you log in too. Dashboard takes about ~5 seconds for TTFB.

I checked mysql query logged and it's the same query causing this issue. The query is called on many pages, even the /groups page. Any ideas on how to optimize it abit?
Rastin Mehr
Rastin Mehr
December 01 2017 Permalink
Yes, it looks for the viewers assignments to load all the app gadgets. The dashboard queries are the heaviest in Anahita. The story feed also has to check for all the leader's assignments and privacy settings.

Right now the best way to make it faster is to have APC and OPCache enabled and have healthy amount of RAM. On Anahitapolis we have about 60K nodes and 46K edges. We use 4GB of RAM on our server. The load time for dashboard is between 3 to 4.5 seconds depending on the type of nodes being displayed.

I'd like to get rid of gadgets for the other apps and instead focus on improving the story feed. Right now all the focus is on the React App and maintaining the 4.3 codebase.

If you found ways to improve the query without breaking the logic, do a PR.
Nick Swinford
Nick Swinford
December 01 2017 Permalink
I have OPCache and APC installed.

This is with MySQL 5.7.20 and may just be another way it was designed to make our lives difficult. I don't have this issue at my current host.
Rastin Mehr
Rastin Mehr
December 01 2017 Permalink
Do you think MySql 5.7 could be configured differently? What makes it work slower than the older version on your current host?
Nick Swinford
Nick Swinford
December 01 2017 Permalink
Not sure about configuration or why it's slower. I'm not getting any errors or notices in the MySQL log. It just is for some reason.

I'm going to try to decipher the EXPLAIN and see if I can figure out why it's slow.
Nick Swinford
Nick Swinford
December 01 2017 Permalink
So I ran the same query on my old server to check it out and it's decently fast, about .3 seconds to run and then I did an EXPLAIN to see if there was any reason why it's faster there.



On older MySQL, the query uses the type key as an index to speed things up, but for some reason it's not working on newer MySQL.
Rastin Mehr
Rastin Mehr
December 01 2017 Permalink
Does the index even exists? Can you try adding it manually and see if is is working?
Nick Swinford
Nick Swinford
December 01 2017 Permalink
Yes, the indexes exist.
Rastin Mehr
Rastin Mehr
December 01 2017 Permalink
Is MySql working on fully non restricted mode?
Nick Swinford
Nick Swinford
December 01 2017 Permalink
From what I can tell, it looks like having the ON ... AND is what's causing the issue. If I move the AND condition into the where it's fast again, but not sure how to accomplish that with the Anahita's query.

I've posted a full workup here (https://stackoverflow.com/questions/47599033/index-stops-working-after-upgrade) in hopes of getting some outside help, but haven't gotten any responses yet. Might need to move the question the Database Administrator Exchange.
Rastin Mehr liked this
Rastin Mehr
Rastin Mehr
December 01 2017 Permalink
The type comparison already exists in the WHERE clause. What happens when you remove the one in the ON condition?
Nick Swinford
Nick Swinford
December 01 2017 Permalink
I disabled NO_ENGINE_SUBSTITUTION and STRICT_TRANS_TABLES, but the query is still slow.

Removing that constraint from the join speeds it up.
Rastin Mehr
Rastin Mehr
December 02 2017 Permalink
Can you give me some clues on where I can output that query in error_log please? I want to see how much work is involving the type comparison in the join on clause and whether it will break the logic.
Rastin Mehr
Rastin Mehr
December 02 2017 Permalink
Yes I figured it out. Looks like the $query->link method is the one that uses two conditions for joining assignments
Rastin Mehr
Rastin Mehr
December 02 2017 Permalink
Let's see what stackoverflow answers
Rastin Mehr
Rastin Mehr
December 02 2017 Permalink
Can you try something else please. Remove the nested SELECT statement in the WHERE. That select statement returns the components_set_to_never and it is being executed every time. Let me know if the performance time improves. I want to make sure that is the join statement conditions that are slowing down the query.

Powered by Anahita