SemanticGlossary inefficient and causing many temporary tables to be created

[nicjansma]

Hello,

I've been tracking down a performance issue we've been seeing on the sarna.net BattleTech Wiki.

The issue is described here:

sarna-net/sarna-issues#43

Essentially, I'm seeing our MariaDB binary log balloon in size after a recent MediaWiki upgrade from 1.34 to 1.43 (along with an upgrade of corresponding plugins, including SMW and SemanticGlossary).

I've narrowed the binlog increase to 90% of the statements being CREATE TEMPORARY TABLE, COMMIT and DROP TEMPORARY TABLE.

These are all coming from this call stack:

SMW\SQLStore\TableBuilder\TemporaryTableBuilder->drop (\www\wiki\extensions\SemanticMediaWiki\src\SQLStore\TableBuilder\TemporaryTableBuilder.php:73)
SMW\SQLStore\QueryEngine\QuerySegmentListProcessor->cleanUp (\www\wiki\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QuerySegmentListProcessor.php:382)
SMW\SQLStore\QueryEngine\QueryEngine->getQueryResult (\www\wiki\extensions\SemanticMediaWiki\src\SQLStore\QueryEngine\QueryEngine.php:229)
SMW\SQLStore\SQLStore->fetchQueryResult (\www\wiki\extensions\SemanticMediaWiki\src\SQLStore\SQLStore.php:379)
SMW\SQLStore\SQLStore->getQueryResult (\www\wiki\extensions\SemanticMediaWiki\src\SQLStore\SQLStore.php:367)
SG\Cache\ElementsCacheBuilder->getElements (\www\wiki\extensions\SemanticGlossary\src\Cache\ElementsCacheBuilder.php:114)
SG\LingoBackendAdapter->next (\www\wiki\extensions\SemanticGlossary\src\LingoBackendAdapter.php:66)
Lingo\LingoParser->buildLingo (\www\wiki\extensions\Lingo\src\LingoParser.php:164)
Lingo\LingoParser->getLingoTree (\www\wiki\extensions\Lingo\src\LingoParser.php:148)
Lingo\LingoParser->realParse (\www\wiki\extensions\Lingo\src\LingoParser.php:233)
Lingo\LingoParser->parse (\www\wiki\extensions\Lingo\src\LingoParser.php:76)
Lingo\Lingo::Lingo\{closure:/var/www/html/wiki/extensions/Lingo/src/Lingo.php:60-74} (\www\wiki\extensions\Lingo\src\Lingo.php:72)
MediaWiki\HookContainer\HookContainer->run (\www\wiki\includes\HookContainer\HookContainer.php:159)
MediaWiki\HookContainer\HookRunner->onContentAlterParserOutput (\www\wiki\includes\HookContainer\HookRunner.php:1225)
MediaWiki\Content\ContentHandler->getParserOutput (\www\wiki\includes\content\ContentHandler.php:1708)
MediaWiki\Content\Renderer\ContentRenderer->getParserOutput (\www\wiki\includes\content\Renderer\ContentRenderer.php:79)
MediaWiki\Revision\RenderedRevision->getSlotParserOutputUncached (\www\wiki\includes\Revision\RenderedRevision.php:264)
MediaWiki\Revision\RenderedRevision->getSlotParserOutput (\www\wiki\includes\Revision\RenderedRevision.php:236)
MediaWiki\Revision\RevisionRenderer->combineSlotOutput (\www\wiki\includes\Revision\RevisionRenderer.php:239)
MediaWiki\Revision\RevisionRenderer->MediaWiki\Revision\{closure:/var/www/html/wiki/includes/Revision/RevisionRenderer.php:170-173} (\www\wiki\includes\Revision\RevisionRenderer.php:172)

On some pages, e.g. https://www.sarna.net/wiki/Clans with many words, we're seeing hundreds of temporary tables being created. That page has 3,283 unique words, and it appears SemanticGlossary is issuing new queries for each word in batches of 5, so it creates 657 temporary tables and then drops those temp tables. This happens on every page load, and I don't see it caching results anywhere.

Whatever it's doing here, it seems inefficient :)

I found that LingoBackendAdapter explicitly disables caching:

https://github.com/SemanticMediaWiki/SemanticGlossary/blob/master/src/LingoBackendAdapter.php#L91

I wasn't sure why, and it seems like this was a recent change from @YOUR1 in #78

This sub-commit describes just that caching is disabled: f79c065 with Bumped Lingo requirement and disable backend cache

@YOUR1 can you confirm disabling of caching intentional? Was it causing other issues?

I've changed useCache() to return true and it appears everything is working much smoother again.