search

specify / specify7

Specify 7
https://www.specifysoftware.org/products/specify-7/
GNU General Public License v2.0
60 stars 36 forks source link

Auto-run tree queries are causing massive issues due to their inefficiency #3821

Closed grantfitzsimmons closed 8 months ago

grantfitzsimmons commented 11 months ago

This has been an issue for years. It's time to fix it!

This issue serves as a place for me to make a case for why we should prioritize solving this in production.

See https://github.com/specify/specify7/issues/496, which this issue will supersede.

A brief (internal) history

@benanhalt on April 19, 2019:

In large databases the requests for tree node stats can be long running and tie up all the server threads, especially in situations where the tree is redisplayed multiple times such as doing tree merge operations.

@benanhalt on March 25, 2020:

A work around is to adjust the tree level that Specify starts computing those counts to be lower (away from the root). There is a remote pref called TreeEditor.Rank.Threshold.Geography that controls the level. You can set it in Specify 6 under the "Trees" preferences. It is the "Show Counts Below:" setting.

@timo11 on June 18, 2020 (email):

This query should find them: select t.taxonid, t.parentid, t.name, t.rankid, p.name, p.rankid from taxon t join taxon p on t.parentid = p.taxonid where t.rankid <= p.rankid;

@maxpatiiuk reported this as a bug again on August 29, 2021: https://github.com/specify/specify7/issues/912

@maxpatiiuk on Jan 14, 2022:

As a temporary fix, I made failed tree stats request fail silently, rather than crash the entire UI

This is something we've had many discussions about, specifically with @realVinayak. This should be addressed very soon. It is causing quite a headache for some users.

External User Reports

Here is just one of the many reports we have had about this issue:

Madrid (CSIC) (1 June 2023)

Since almost 2 weeks (maybe from the last Specify docker update to v 7.8.11) every Specify7 taxon search, directly over the tree or through queries, is throwing and overload error and gateway time-out.

image

I've found this morning that our database server was working permanently at 394% cpu

image

After restarting mysql everything is ok. Using Sp6 taxon tree and queries everything works properly (even if the %cpu highs at 200% for some seconds). Watching processes at mysql, the taxon queries reached from sp6 are opened, the cpu is high for some seconds and then down to normal % again and the query is closed (not keeping at "sending data" state).

But accesing from Specify7 to taxon tree searches or queries the % cpu is even higher (almost 300%) not only for seconds but forever and the query request at mysql keeps alive as "Sending data" and "Creating sort index" state. This state keeps from the begining, when showing results at client side, and after a second or third taxon request that finally generates the overload error.

Our mysql is running on a server with 4 CPUs (2399.998 MHz and 16384 KB cache size ) and 16 GB RAM.

Our specify7 web is runnig on another server with 6CPUs and 31 GB RAM and not showing performance problems.

Obviously the database server is being small for Specify7.

The database size is 3.2 GB and taxon table has 136401 records

Why these requests work ok from Sp6 but doesn't work from Sp7? Is the small number of cpu's the reason why taxon requestes are opened for so long when are done from Specify7?

Would it be enough using a 8 CPUs server for our database? do we need 12 if % cpu highs to 300%? Maybe the problem is not only related to dataserver capacity?

QUERIES THAT KEEP AS "Sending data" status forever

SELECT taxon_1.`TaxonID` AS `taxon_1_TaxonID`, count(DISTINCT determination_1.`DeterminationID`) AS count_1, count(DISTINCT determination_1.`DeterminationID`) + count(DISTINCT determination_2.`DeterminationID`) + count(DISTINCT determination_3.`DeterminationID`) + count(DISTINCT determination_4.`DeterminationID`) + count(DISTINCT determination_5.`DeterminationID`) + count(DISTINCT determination_6.`DeterminationID`) + count(DISTINCT determination_7.`DeterminationID`) + count(DISTINCT determination_8.`DeterminationID`) + count(DISTINCT determination_9.`DeterminationID`) + count(DISTINCT determination_10.`DeterminationID`) AS anon_1
FROM taxon AS taxon_1 LEFT OUTER JOIN taxon AS taxon_2 ON taxon_2.`ParentID` = taxon_1.`TaxonID` LEFT OUTER JOIN taxon AS taxon_3 ON taxon_3.`ParentID` = taxon_2.`TaxonID` LEFT OUTER JOIN taxon AS taxon_4 ON taxon_4.`ParentID` = taxon_3.`TaxonID` LEFT OUTER JOIN taxon AS taxon_5 ON taxon_5.`ParentID` = taxon_4.`TaxonID` LEFT OUTER JOIN taxon AS taxon_6 ON taxon_6.`ParentID` = taxon_5.`TaxonID` LEFT OUTER JOIN taxon AS taxon_7 ON taxon_7.`ParentID` = taxon_6.`TaxonID` LEFT OUTER JOIN taxon AS taxon_8 ON taxon_8.`ParentID` = taxon_7.`TaxonID` LEFT OUTER JOIN taxon AS taxon_9 ON taxon_9.`ParentID` = taxon_8.`TaxonID` LEFT OUTER JOIN taxon AS taxon_10 ON taxon_10.`ParentID` = taxon_9.`TaxonID` LEFT OUTER JOIN determination AS determination_1 ON determination_1.`IsCurrent` AND determination_1.`CollectionMemberID` = 4 AND determination_1.`PreferredTaxonID` = taxon_1.`TaxonID` LEFT OUTER JOIN determination AS determination_2 ON determination_2.`IsCurrent` AND determination_2.`CollectionMemberID` = 4 AND determination_2.`PreferredTaxonID` = taxon_2.`TaxonID` LEFT OUTER JOIN determination AS determination_3 ON determination_3.`IsCurrent` AND determination_3.`CollectionMemberID` = 4 AND determination_3.`PreferredTaxonID` = taxon_3.`TaxonID` LEFT OUTER JOIN determination AS determination_4 ON determination_4.`IsCurrent` AND determination_4.`CollectionMemberID` = 4 AND determination_4.`PreferredTaxonID` = taxon_4.`TaxonID` LEFT OUTER JOIN determination AS determination_5 ON determination_5.`IsCurrent` AND determination_5.`CollectionMemberID` = 4 AND determination_5.`PreferredTaxonID` = taxon_5.`TaxonID` LEFT OUTER JOIN determination AS determination_6 ON determination_6.`IsCurrent` AND determination_6.`CollectionMemberID` = 4 AND determination_6.`PreferredTaxonID` = taxon_6.`TaxonID` LEFT OUTER JOIN determination AS determination_7 ON determination_7.`IsCurrent` AND determination_7.`CollectionMemberID` = 4 AND determination_7.`PreferredTaxonID` = taxon_7.`TaxonID` LEFT OUTER JOIN determination AS determination_8 ON determination_8.`IsCurrent` AND determination_8.`CollectionMemberID` = 4 AND determination_8.`PreferredTaxonID` = taxon_8.`TaxonID` LEFT OUTER JOIN determination AS determination_9 ON determination_9.`IsCurrent` AND determination_9.`CollectionMemberID` = 4 AND determination_9.`PreferredTaxonID` = taxon_9.`TaxonID` LEFT OUTER JOIN determination AS determination_10 ON determination_10.`IsCurrent` AND determination_10.`CollectionMemberID` = 4 AND determination_10.`PreferredTaxonID` = taxon_10.`TaxonID`

WHERE taxon_1.`ParentID` = 26 AND taxon_1.`TaxonTreeDefID` = 1 GROUP BY taxon_1.`TaxonID`

I'm pretty sure that is the reason for our problems with the taxon tree. Actually the users at MA Herbaria reported taxon tree problems since we began to use Sp7. Navigation was very slow and after a while an error related with "request timeout" was generated. So I tunned a bit the config nginx to avoid the error, but the problem was there somehow and users preferred to work with that tree on Sp6, so till two weeks ago the problem was almost hidden. Two weeks ago when even Sp6 was really slow I checked the cpu load and remove the tunning at nginx to see what happened and the overload errors were there. So you're right, it's not related to code changes between versions.

The only problem now is that we are working with mysql 5.7 and this version doesn't support the CTE WITH clause. I've checked a Mariadb server on ubuntu with 8 cores running with our current database and the CTE works very well and fast. So the solutions probably comes from both sides: changing our database engine to Mariadb on out side and using a new release with that issue solved. But I am wondering if anyone else is using as we do the mysql 5.7 + Sp7. The sql change won't work for them if they keep using mysql 5.7. The upgrade from 4 to 8 cores was made on Friday but the queries was working like before so in the short term we should avoid to work with taxon tree on Sp7.

NOU herbarium (Nouvelle Calédonie) (21 July 2023)

I deployed both docker containers "all-in-one" and "just-specify7" to give a try to Specify 7 on the NOU database. It launches fine but then I want to display Taxon trees and I notice that two CPU's just go full on and the process hangs and eventually fails :

image

At first I thought that in the all-in-one container, something was maybe limiting memory of CPU power. That is why I switched to just-specify-7 and used local mariadb server (just as Specify 6). But I do have the same error. And mariadb server keeps boiling for ever and I have to kill it manually.

I must also say that I first ran both containers (all-in-one and just-specify-7) on a remote server that will be dedicated to running specify for CAY and NOU herbarium. I had this error and though that maybe the server was not powerful enough. So I switch to my local computer that is quite powerful (20 CPUs and 34Go RAM), but I have exactly the same issue.

Here is an exemple of QUERY that is triggered when I open the tree from SPECIFY7:

SELECT taxon_1.`TaxonID` AS `taxon_1_TaxonID`, count(DISTINCT determination_1.`DeterminationID`) AS count_1, count(DISTINCT determination_1.`DeterminationID`) + count(DISTINCT determination_2.`DeterminationID`) + count(DISTINCT determination_3.`DeterminationID`) + count(DISTINCT determination_4.`DeterminationID`) + count(DISTINCT determination_5.`DeterminationID`) + count(DISTINCT determination_6.`DeterminationID`) + count(DISTINCT determination_7.`DeterminationID`) + count(DISTINCT determination_8.`DeterminationID`) + count(DISTINCT determination_9.`DeterminationID`) + count(DISTINCT determination_10.`DeterminationID`) + count(DISTINCT determination_11.`DeterminationID`) + count(DISTINCT determination_12.`DeterminationID`) + count(DISTINCT determination_13.`DeterminationID`) AS anon_1
FROM taxon AS taxon_1 LEFT OUTER JOIN taxon AS taxon_2 ON taxon_2.`ParentID` = taxon_1.`TaxonID` LEFT OUTER JOIN taxon AS taxon_3 ON taxon_3.`ParentID` = taxon_2.`TaxonID` LEFT OUTER JOIN taxon AS taxon_4 ON taxon_4.`ParentID` = taxon_3.`TaxonID` LEFT OUTER JOIN taxon AS taxon_5 ON taxon_5.`ParentID` = taxon_4.`TaxonID` LEFT OUTER JOIN taxon AS taxon_6 ON taxon_6.`ParentID` = taxon_5.`TaxonID` LEFT OUTER JOIN taxon AS taxon_7 ON taxon_7.`ParentID` = taxon_6.`TaxonID` LEFT OUTER JOIN taxon AS taxon_8 ON taxon_8.`ParentID` = taxon_7.`TaxonID` LEFT OUTER JOIN taxon AS taxon_9 ON taxon_9.`ParentID` = taxon_8.`TaxonID` LEFT OUTER JOIN taxon AS taxon_10 ON taxon_10.`ParentID` = taxon_9.`TaxonID` LEFT OUTER JOIN taxon AS taxon_11 ON taxon_11.`ParentID` = taxon_10.`TaxonID` LEFT OUTER JOIN taxon AS taxon_12 ON taxon_12.`ParentID` = taxon_11.`TaxonID` LEFT OUTER JOIN taxon AS taxon_13 ON taxon_13.`ParentID` = taxon_12.`TaxonID` LEFT OUTER JOIN determination AS determination_1 ON determination_1.`IsCurrent` AND determination_1.`CollectionMemberID` = 4 AND determination_1.`PreferredTaxonID` = taxon_1.`TaxonID` LEFT OUTER JOIN determination AS determination_2 ON determination_2.`IsCurrent` AND determination_2.`CollectionMemberID` = 4 AND determination_2.`PreferredTaxonID` = taxon_2.`TaxonID` LEFT OUTER JOIN determination AS determination_3 ON determination_3.`IsCurrent` AND determination_3.`CollectionMemberID` = 4 AND determination_3.`PreferredTaxonID` = taxon_3.`TaxonID` LEFT OUTER JOIN determination AS determination_4 ON determination_4.`IsCurrent` AND determination_4.`CollectionMemberID` = 4 AND determination_4.`PreferredTaxonID` = taxon_4.`TaxonID` LEFT OUTER JOIN determination AS determination_5 ON determination_5.`IsCurrent` AND determination_5.`CollectionMemberID` = 4 AND determination_5.`PreferredTaxonID` = taxon_5.`TaxonID` LEFT OUTER JOIN determination AS determination_6 ON determination_6.`IsCurrent` AND determination_6.`CollectionMemberID` = 4 AND determination_6.`PreferredTaxonID` = taxon_6.`TaxonID` LEFT OUTER JOIN determination AS determination_7 ON determination_7.`IsCurrent` AND determination_7.`CollectionMemberID` = 4 AND determination_7.`PreferredTaxonID` = taxon_7.`TaxonID` LEFT OUTER JOIN determination AS determination_8 ON determination_8.`IsCurrent` AND determination_8.`CollectionMemberID` = 4 AND determination_8.`PreferredTaxonID` = taxon_8.`TaxonID` LEFT OUTER JOIN determination AS determination_9 ON determination_9.`IsCurrent` AND determination_9.`CollectionMemberID` = 4 AND determination_9.`PreferredTaxonID` = taxon_9.`TaxonID` LEFT OUTER JOIN determination AS determination_10 ON determination_10.`IsCurrent` AND determination_10.`CollectionMemberID` = 4 AND determination_10.`PreferredTaxonID` = taxon_10.`TaxonID` LEFT OUTER JOIN determination AS determination_11 ON determination_11.`IsCurrent` AND determination_11.`CollectionMemberID` = 4 AND determination_11.`PreferredTaxonID` = taxon_11.`TaxonID` LEFT OUTER JOIN determination AS determination_12 ON determination_12.`IsCurrent` AND determination_12.`CollectionMemberID` = 4 AND determination_12.`PreferredTaxonID` = taxon_12.`TaxonID` LEFT OUTER JOIN determination AS determination_13 ON determination_13.`IsCurrent` AND determination_13.`CollectionMemberID` = 4 AND determination_13.`PreferredTaxonID` = taxon_13.`TaxonID`
WHERE taxon_1.`ParentID` = 105 AND taxon_1.`TaxonTreeDefID` = 1 GROUP BY taxon_1.`TaxonID`

On the other hand it is handled in a very different way in SPECIFY6, with way less JOINs in the request and none on the determination table.

University of Massachusetts (6 March 2020)

We are having ongoing problems getting Specify version 7 fully functional. I am not sure exactly why it is, but the Herbarium Collections Manager, Roberta Lombardi, has a couple of time repeated a request for assistance with getting results from a query. She has been asked to send a screen shot, which she has done, and after weeks when she has tried to repeat the request, simply gets the same set of directions, which did not work before, and with another request for a screen shot. It seems that either her attachment giving a screen shot is ignored, or never reaches the help person. The query does not complete. If that is a server-side issue (as seems possible) I'd appreciate getting at least a suggestion for what we ought to be looking into. (Server side issue are on my plate).

Logs indicate this is caused by the same lock issue by the trees.

California Academy of Sciences (18 October 2021)

This issue is a blocker for us; we can’t deploy if a single user clicking can bring down the system.

We’re trying out specify7 against our existing databases, and we found three copies of the below query that ran all weekend. I suspect this is either a s7 bug or there’s something special about our schema. If we can identify what generated this query on the server side we can probably debug from there. Any insight welcome!

SELECT taxon_1.`TaxonID` AS `taxon_1_TaxonID`, count(DISTINCT determination_1.`DeterminationID`) AS count_1, count(DISTINCT determination_1.`DeterminationID`) + count(DISTINCT determination_2.`DeterminationID`) + count(DISTINCT determination_3.`DeterminationID`) + count(DISTINCT determination_4.`DeterminationID`) + count(DISTINCT determination_5.`DeterminationID`) + count(DISTINCT determination_6.`DeterminationID`) + count(DISTINCT determination_7.`DeterminationID`) + count(DISTINCT determination_8.`DeterminationID`) + count(DISTINCT determination_9.`DeterminationID`) + count(DISTINCT determination_10.`DeterminationID`) + count(DISTINCT determination_11.`DeterminationID`) + count(DISTINCT determination_12.`DeterminationID`) + count(DISTINCT determination_13.`DeterminationID`) + count(DISTINCT determination_14.`DeterminationID`) + count(DISTINCT determination_15.`DeterminationID`) + count(DISTINCT determination_16.`DeterminationID`) AS anon_1 
FROM taxon AS taxon_1 LEFT OUTER JOIN taxon AS taxon_2 ON taxon_2.`ParentID` = taxon_1.`TaxonID` LEFT OUTER JOIN taxon AS taxon_3 ON taxon_3.`ParentID` = taxon_2.`TaxonID` LEFT OUTER JOIN taxon AS taxon_4 ON taxon_4.`ParentID` = taxon_3.`TaxonID` LEFT OUTER JOIN taxon AS taxon_5 ON taxon_5.`ParentID` = taxon_4.`TaxonID` LEFT OUTER JOIN taxon AS taxon_6 ON taxon_6.`ParentID` = taxon_5.`TaxonID` LEFT OUTER JOIN taxon AS taxon_7 ON taxon_7.`ParentID` = taxon_6.`TaxonID` LEFT OUTER JOIN taxon AS taxon_8 ON taxon_8.`ParentID` = taxon_7.`TaxonID` LEFT OUTER JOIN taxon AS taxon_9 ON taxon_9.`ParentID` = taxon_8.`TaxonID` LEFT OUTER JOIN taxon AS taxon_10 ON taxon_10.`ParentID` = taxon_9.`TaxonID` LEFT OUTER JOIN taxon AS taxon_11 ON taxon_11.`ParentID` = taxon_10.`TaxonID` LEFT OUTER JOIN taxon AS taxon_12 ON taxon_12.`ParentID` = taxon_11.`TaxonID` LEFT OUTER JOIN taxon AS taxon_13 ON taxon_13.`ParentID` = taxon_12.`TaxonID` LEFT OUTER JOIN taxon AS taxon_14 ON taxon_14.`ParentID` = taxon_13.`TaxonID` LEFT OUTER JOIN taxon AS taxon_15 ON taxon_15.`ParentID` = taxon_14.`TaxonID` LEFT OUTER JOIN taxon AS taxon_16 ON taxon_16.`ParentID` = taxon_15.`TaxonID` LEFT OUTER JOIN determination AS determination_1 ON determination_1.`IsCurrent` AND determination_1.`CollectionMemberID` = 4 AND determination_1.`PreferredTaxonID` = taxon_1.`TaxonID` LEFT OUTER JOIN determination AS determination_2 ON determination_2.`IsCurrent` AND determination_2.`CollectionMemberID` = 4 AND determination_2.`PreferredTaxonID` = taxon_2.`TaxonID` LEFT OUTER JOIN determination AS determination_3 ON determination_3.`IsCurrent` AND determination_3.`CollectionMemberID` = 4 AND determination_3.`PreferredTaxonID` = taxon_3.`TaxonID` LEFT OUTER JOIN determination AS determination_4 ON determination_4.`IsCurrent` AND determination_4.`CollectionMemberID` = 4 AND determination_4.`PreferredTaxonID` = taxon_4.`TaxonID` LEFT OUTER JOIN determination AS determination_5 ON determination_5.`IsCurrent` AND determination_5.`CollectionMemberID` = 4 AND determination_5.`PreferredTaxonID` = taxon_5.`TaxonID` LEFT OUTER JOIN determination AS determination_6 ON determination_6.`IsCurrent` AND determination_6.`CollectionMemberID` = 4 AND determination_6.`PreferredTaxonID` = taxon_6.`TaxonID` LEFT OUTER JOIN determination AS determination_7 ON determination_7.`IsCurrent` AND determination_7.`CollectionMemberID` = 4 AND determination_7.`PreferredTaxonID` = taxon_7.`TaxonID` LEFT OUTER JOIN determination AS determination_8 ON determination_8.`IsCurrent` AND determination_8.`CollectionMemberID` = 4 AND determination_8.`PreferredTaxonID` = taxon_8.`TaxonID` LEFT OUTER JOIN determination AS determination_9 ON determination_9.`IsCurrent` AND determination_9.`CollectionMemberID` = 4 AND determination_9.`PreferredTaxonID` = taxon_9.`TaxonID` LEFT OUTER JOIN determination AS determination_10 ON determination_10.`IsCurrent` AND determination_10.`CollectionMemberID` = 4 AND determination_10.`PreferredTaxonID` = taxon_10.`TaxonID` LEFT OUTER JOIN determination AS determination_11 ON determination_11.`IsCurrent` AND determination_11.`CollectionMemberID` = 4 AND determination_11.`PreferredTaxonID` = taxon_11.`TaxonID` LEFT OUTER JOIN determination AS determination_12 ON determination_12.`IsCurrent` AND determination_12.`CollectionMemberID` = 4 AND determination_12.`PreferredTaxonID` = taxon_12.`TaxonID` LEFT OUTER JOIN determination AS determination_13 ON determination_13.`IsCurrent` AND determination_13.`CollectionMemberID` = 4 AND determination_13.`PreferredTaxonID` = taxon_13.`TaxonID` LEFT OUTER JOIN determination AS determination_14 ON determination_14.`IsCurrent` AND determination_14.`CollectionMemberID` = 4 AND determination_14.`PreferredTaxonID` = taxon_14.`TaxonID` LEFT OUTER JOIN determination AS determination_15 ON determination_15.`IsCurrent` AND determination_15.`CollectionMemberID` = 4 AND determination_15.`PreferredTaxonID` = taxon_15.`TaxonID` LEFT OUTER JOIN determination AS determination_16 ON determination_16.`IsCurrent` AND determination_16.`CollectionMemberID` = 4 AND determination_16.`PreferredTaxonID` = taxon_16.`TaxonID` 
WHERE taxon_1.`ParentID` = 2 AND taxon_1.`TaxonTreeDefID` = 1 GROUP BY taxon_1.`TaxonID`

Reported By:

This was just what I could return by doing some light searching + my experience.

grantfitzsimmons commented 11 months ago

3613 may solve/improve this

grantfitzsimmons commented 10 months ago

Happened in all hosted servers in the past 10 days, NA, CA, and EU Specify Cloud.

Example from this morning on the NA DB server:

SELECT
    taxon_1.`TaxonID` AS `taxon_1_TaxonID`,
    COUNT(DISTINCT determination_1.`DeterminationID`) AS count_1,
    COUNT(DISTINCT determination_1.`DeterminationID`)
        + COUNT(DISTINCT determination_2.`DeterminationID`)
        + COUNT(DISTINCT determination_3.`DeterminationID`)
        + COUNT(DISTINCT determination_4.`DeterminationID`)
        + COUNT(DISTINCT determination_5.`DeterminationID`) AS anon_1
FROM
    taxon AS taxon_1
    LEFT OUTER JOIN taxon AS taxon_2 ON taxon_2.`ParentID` = taxon_1.`TaxonID`
    LEFT OUTER JOIN taxon AS taxon_3 ON taxon_3.`ParentID` = taxon_2.`TaxonID`
    LEFT OUTER JOIN taxon AS taxon_4 ON taxon_4.`ParentID` = taxon_3.`TaxonID`
    LEFT OUTER JOIN taxon AS taxon_5 ON taxon_5.`ParentID` = taxon_4.`TaxonID`
    LEFT OUTER JOIN determination AS determination_1 ON determination_1.`IsCurrent`
        AND determination_1.`CollectionMemberID` = 4
        AND determination_1.`PreferredTaxonID` = taxon_1.`TaxonID`
    LEFT OUTER JOIN determination AS determination_2 ON determination_2.`IsCurrent`
        AND determination_2.`CollectionMemberID` = 4
        AND determination_2.`PreferredTaxonID` = taxon_2.`TaxonID`
    LEFT OUTER JOIN determination AS determination_3 ON determination_3.`IsCurrent`
        AND determination_3.`CollectionMemberID` = 4
        AND determination_3.`PreferredTaxonID` = taxon_3.`TaxonID`
    LEFT OUTER JOIN determination AS determination_4 ON determination_4.`IsCurrent`
        AND determination_4.`CollectionMemberID` = 4
        AND determination_4.`PreferredTaxonID` = taxon_4.`TaxonID`
    LEFT OUTER JOIN determination AS determination_5 ON determination_5.`IsCurrent`
        AND determination_5.`CollectionMemberID` = 4
        AND determination_5.`PreferredTaxonID` = taxon_5.`TaxonID`
WHERE
    taxon_1.`ParentID` = 33033
    AND taxon_1.`TaxonTreeDefID` = 1
GROUP BY
    taxon_1.`TaxonID`