Die Ähnlichkeitssuche erzeugt teilweise extrem lange SQL-Anfragen

[DanielWeitenauer]

Unser Hoster wies darauf hin, dass eine mehrere tausend Zeilen lange SQL-Anfrage von search it den Datenbankserver lahmgelegt hat. Soweit ich das sehen kann, wurde die Anfrage durch die SOUNDEX-Ähnlichkeitssuche erzeugt.
Es scheint durch die Übergabe von Suchstrings mit mehr als 100-200 Zeichen provoziert worden zu sein.
Die Ähnlichkeitssuche übernimmt jedes Wort des Strings in die SQL-Abfrage.

[alxndr-w]

Handelt es sich um einen Wald-und-Wiesen-Hoster oder um ein ordentliches Hosting-Paket bei einem ordentlichen Hoster?

Ich habe probeweise eine Suche ausgeführt über 16 Begriffe mit insg. 130 Zeichen inkl. aktivierten Soundex über 175 Artikel.

Die Suche wird im Zehntelsekundenbereich durchgeführt:

   "searchterm" => "sommer sonne eis am stiel badehose strand bademeister sonnenbrand sonnencreme foto erinnerung wasser meer wassermelone klimaanlage"
    "sql" => """
        
                    SELECT SQL_CALC_FOUND_ROWS (SELECT SUM((( MATCH (`plaintext`) AGAINST ('sommer')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonne')) * 1) + (( MATCH (`plaintext`) AGAINST ('eis')) * 1) + (( MATCH (`plaintext`) AGAINST ('am')) * 1) + (( MATCH (`plaintext`) AGAINST ('stiel')) * 1) + (( MATCH (`plaintext`) AGAINST ('badehose')) * 1) + (( MATCH (`plaintext`) AGAINST ('strand')) * 1) + (( MATCH (`plaintext`) AGAINST ('bademeister')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnenbrand')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnencreme')) * 1) + (( MATCH (`plaintext`) AGAINST ('foto')) * 1) + (( MATCH (`plaintext`) AGAINST ('erinnerung')) * 1) + (( MATCH (`plaintext`) AGAINST ('wasser')) * 1) + (( MATCH (`plaintext`) AGAINST ('meer')) * 1) + (( MATCH (`plaintext`) AGAINST ('wassermelone')) * 1) + (( MATCH (`plaintext`) AGAINST ('klimaanlage')) * 1) + 1) FROM `rex_tmp_search_it_index` summe WHERE summe.fid = r1.fid AND summe.ftable = r1.ftable) AS RELEVANCE_SEARCH_IT, 
        (SELECT COUNT(*) FROM `rex_tmp_search_it_index` summe WHERE summe.fid = r1.fid AND (summe.ftable IS NULL OR summe.ftable = r1.ftable) AND (summe.fcolumn IS NULL OR summe.fcolumn = r1.fcolumn) AND summe.texttype = r1.texttype) AS COUNT_SEARCH_IT,
        `id`,
        `fid`,
        `catid`,
        `ftable`,
        `fcolumn`,
        `texttype`,
        `clang`,
        `unchangedtext`,
        `plaintext`,
        `teaser`,
        `values`,
        `filename`,
        `fileext`
                    FROM `rex_tmp_search_it_index` r1
                    WHERE (((((`plaintext` LIKE '%sommer%'))) AND (((`plaintext` LIKE '%sonne%'))) AND (((`plaintext` LIKE '%eis%'))) AND (((`plaintext` LIKE '%am%'))) AND (((`plaintext` LIKE '%stiel%'))) AND (((`plaintext` LIKE '%badehose%'))) AND (((`plaintext` LIKE '%strand%'))) AND (((`plaintext` LIKE '%bademeister%'))) AND (((`plaintext` LIKE '%sonnenbrand%'))) AND (((`plaintext` LIKE '%sonnencreme%'))) AND (((`plaintext` LIKE '%foto%'))) AND (((`plaintext` LIKE '%erinnerung%'))) AND (((`plaintext` LIKE '%wasser%'))) AND (((`plaintext` LIKE '%meer%'))) AND (((`plaintext` LIKE '%wassermelone%'))) AND (((`plaintext` LIKE '%klimaanlage%'))))) AND ( 
                      (
                        ((( MATCH (`plaintext`) AGAINST ('sommer')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonne')) * 1) + (( MATCH (`plaintext`) AGAINST ('eis')) * 1) + (( MATCH (`plaintext`) AGAINST ('am')) * 1) + (( MATCH (`plaintext`) AGAINST ('stiel')) * 1) + (( MATCH (`plaintext`) AGAINST ('badehose')) * 1) + (( MATCH (`plaintext`) AGAINST ('strand')) * 1) + (( MATCH (`plaintext`) AGAINST ('bademeister')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnenbrand')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnencreme')) * 1) + (( MATCH (`plaintext`) AGAINST ('foto')) * 1) + (( MATCH (`plaintext`) AGAINST ('erinnerung')) * 1) + (( MATCH (`plaintext`) AGAINST ('wasser')) * 1) + (( MATCH (`plaintext`) AGAINST ('meer')) * 1) + (( MATCH (`plaintext`) AGAINST ('wassermelone')) * 1) + (( MATCH (`plaintext`) AGAINST ('klimaanlage')) * 1) + 1) = (SELECT MAX((( MATCH (`plaintext`) AGAINST ('sommer')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonne')) * 1) + (( MATCH (`plaintext`) AGAINST ('eis')) * 1) + (( MATCH (`plaintext`) AGAINST ('am')) * 1) + (( MATCH (`plaintext`) AGAINST ('stiel')) * 1) + (( MATCH (`plaintext`) AGAINST ('badehose')) * 1) + (( MATCH (`plaintext`) AGAINST ('strand')) * 1) + (( MATCH (`plaintext`) AGAINST ('bademeister')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnenbrand')) * 1) + (( MATCH (`plaintext`) AGAINST ('sonnencreme')) * 1) + (( MATCH (`plaintext`) AGAINST ('foto')) * 1) + (( MATCH (`plaintext`) AGAINST ('erinnerung')) * 1) + (( MATCH (`plaintext`) AGAINST ('wasser')) * 1) + (( MATCH (`plaintext`) AGAINST ('meer')) * 1) + (( MATCH (`plaintext`) AGAINST ('wassermelone')) * 1) + (( MATCH (`plaintext`) AGAINST ('klimaanlage')) * 1) + 1) FROM `rex_tmp_search_it_index` r2 WHERE r1.ftable = r2.ftable AND r1.fid = r2.fid ) 
                        AND fid IS NOT NULL
                      ) OR
                      ftable IS NULL
                    )
                    GROUP BY ftable,fid,clang
                    ORDER BY RELEVANCE_SEARCH_IT DESC
                    LIMIT 0,20
        """

[staabm]

dass eine mehrere tausend Zeilen lange SQL-Anfrage

@DanielWeitenauer kannst du mal ein konkretes beispiel hier posten von so einem rießigen query?

[tyrant88]

@alxndr-w Das ist ja nur eine Suche, oder? Es geht ja wohl um die Erstellung der soundex- Ähnlichkeitswerte?
@ staabm Ich glaube Daniel würde uns mehr Fakten nennen, wenn er sie hätte.

[alxndr-w]

@tyrant88 wo, wenn nicht bei einer Suche, werden aus Suchbegriffen ein SQL-Befehl formuliert?

Die Indexierung lief bei mir ebenfalls problemlos durch.

[tyrant88]

https://github.com/FriendsOfREDAXO/search_it/blob/master/lib/search_it.php#L2006

[tyrant88]

Also natürlich Suche, aber eben mit den Soundex Ähnlichkeitswerten- das habe ich in deinem Beispiel nicht gesehen.
https://github.com/FriendsOfREDAXO/search_it/blob/master/lib/search_it.php#L2105

[alxndr-w]

Also natürlich Suche, aber eben mit den Soundex Ähnlichkeitswerten- das habe ich in deinem Beispiel nicht gesehen.

Die habe ich ausgeführt (zumindest aktiviert)

[DanielWeitenauer]

@staabm Ich glaube der ist zu lang. Der Hoster hat ihn an die E-Mail angehängt und es sind ca. 5000 Zeilen mit ca. 480000 Zeichen.

[alxndr-w]

@DanielWeitenauer hänge es doch hier an, Link zu einer Datei via Dropbox, OneDrive, Google Drive.

[DanielWeitenauer]

@staab @alxndr-w @tyrant88 Bei der Website handelt es sich um einen Custom Shop der inzwischen bei All-Inkl läuft, den ich allerdings nur übernommen habe. Artikel sind so gut wie keine drin, die Suche geht in der Hauptsache über eine DB-Tabelle mit ca 2000 Einträgen. Der Index hat ca. 10000 Einträge, Keywords ca. 12000.
Es sind unter anderem die Artikelnummern als Textfeld indiziert.
Die Anfrage scheint einen Stapel an Artikelnummern abgefragt zu haben. Schwer zu sagen, ob das eine manuelle oder automatisierte Abfrage war.
Die konkrete Abfrage scheint nicht mehr gespeichert worden zu sein, aber von der Struktur her sollte es etwas in diesem Stil gewesen sein (eine andere Abfrage aus der Statistik) : " & ' - . 04101 1 10 100 10008 10057 10058 10060 10065 10081 101 10103 10123 10143 10161 10176 102 10232 10269 10297 103 10350 10398 104 10406 10435 10484 10492 105 10502 10507 10539 10552 10559 10565 10579 10586 10588 10595 106 10605 10606 10609 10613 10

Der Query startet mit
SELECT* FROM ( (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platan' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'Platane' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platano' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platanoi' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platanoid' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platanoides' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'platanu' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, 'Platanus' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = 'P435')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '&' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '\'' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '-' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '.' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '04101' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '1' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '100' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10008' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10057' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10058' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10060' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10065' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10081' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '101' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10103' AS typedin, SUM(count) as count FROM `rex_search_it_keywords` WHERE 1 AND (soundex = '0000')) UNION (SELECT GROUP_CONCAT(DISTINCT keyword SEPARATOR ' ') as keyword, '10123'...
Danach folgt eine ewig lange Reihe an Artikelnummervariationen.

Der Link zu Query: https://drive.google.com/file/d/1WyPVHY9r7VLGTzHoS9abqoDN-eJZqVJj/view?usp=sharing

Das Problem war wohl auch nicht die Ausführungszeit, sondern ein Überlaufen des Speichers.

[alxndr-w]

Also halbieren könnte man das Szenario schon damit, dass Soundex dem Query nicht hinzugefügt wird, wenn es sowieso 0000 ist.

Dass die Eingabe sinnvoll beschränkt wird oder die maximale Anzahl von Suchbegriffen (es hängt ja eher mit der Menge als an der Begrifflänge), z.B. auf 20, sähe ich ebenfalls als kein Problem.

[tyrant88]

Also das mit dem "halbieren" habe ich nicht kapiert, weil "0000" ist der soundex-Wert zu all den Zahlen. Da kann man nichts weglassen, weil man jede der Zeilen braucht.

Aber mit dem anderen hast du Recht, das sind einfach tausende Suchwörter, die tausend SQL Zeilen in der Ähnlichkeitssuche erzeugen.

Ich denke die maximale Anzahl der Suchwörter lässt sich schon sinnvoll begrenzen. Bleibt nur die Frage wie. Noch ein Config-Value? Package.yml?

[alxndr-w]

@tyrant88 also grob gesagt braucht man doch nicht überall OR '0000' OR '0000' OR '0000' OR '0000' OR ..., einmal reicht. Der Soundex von Artikelnummern wird immer 0000 sein, wenn ich das richtig überblicke - die Artikelnummern erhalten ja keine Buchstaben.

Überdeutlich:

`10001` => '0000'
`10002` => '0000'
`10003` => '0000'
`12345` => '0000'

Nun können aber nicht nur Artikelnummern lange SQL-Queries auslösen.

Ich würde dafür keinen Config-Wert anlegen, weil es eine sinnvolle Entscheidung ist und mir kein Szenario einfällt, in der man riskieren will, dass der DB-Server abschmiert. Aus meiner Sicht wären das max. 10 Begriffe.

Andererseits könnte es auch ein Feature sein, dass die Eingabe sinnvoll begrenzt wird, aus Usability-Sicht. Dann sollte der Entwickler der Website entscheiden können, was für sinnvoll erachtet wird - mit einem guten Default-Wert. Das wäre aus meiner Sicht etwas zwischen 2 und 6, mit denen man noch sinnvolle Ergebnisse erwarten kann.

[tyrant88]

Problem ist, das die ganzen Zeilen ein Ergebnis bilden, die Liste der ähnlichen Wörter zu den Suchwörtern und ihre Häufigkeit und im Code so "weiterverarbeitet" werden :-)

Ich habe gerade nochmal ein bisschen rumgespielt und bei mir wirft schon die Testsuche einen Fehler bei 15 Suchwörtern...
Ein "SQLSTATE[HY000]: General error: 1436 Thread stack overrun: 164768 bytes used of a 196608 byte stack"

[TobiasKrais]

Könnte das mit diesem Bug in Zusammenhang stehen? Damals gab es Probleme wenn Zahlen im Suchstring vorhanden waren.

[alxndr-w]

Ich glaube nicht. @TobiasKrais – Hier geht es um tatsächliche Suchbegriffe, wenn zu viele eingegebenen/abgesendet wurden.

In dem anderen Bug ergaben halt Ziffern ein leeres Soundex Suchmuster ("0000")

[xong]

Ich seh das nicht als Problem von search_it.

Zwei Lösungsvorschläge:

1. Anpassung bei der Suche: Ähnlichkeitssuche abgestellen oder die Anzahl an Suchbegriffen eingeschränken
2. max_allowed_packet entsprechend erhöhen. Eventuell hilft es schon, auf MySQL 8 oder MariaDB >=10.2.4 zu aktualisieren. Dort sind die Limits mit 64MiB bzw. 16MiB höher.

[tyrant88]

Ja, es wird wohl auf eine Beschränkung der Suchbegriffe hinauslaufen.