Reposted from the #public-memsql-chat created by Aaron Pfeifer:
Hey folks – I’ve been observing an issue with the memsql cli (on 6.5) where queries will have vastly different performance (40x difference in one case) based on a difference in the spacing within the query. The query is executed multiple times to verify consistent performance.
When I paste the query (split up onto multiple lines), my query will take 4s. When I paste the query as a single line, the query takes 0.1s. The results are identical and can be replicated. Additionally, I’ve verified that the queries are the exact same syntax.
Has anyone seen this before?
The version of memsql being used is:
/var/lib/memsql-ops/lib/memsql-client Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper```
Just following up on this. I’m actually experiencing this through every connector (MemSQL, MySQL, jdbc adapter, etc.). Sending our query with on multiple lines and often prefixed with spaces results in degraded query performance.
Once I switch it to a single line, the queries run faster.
To be clear, the query time is the same every time. I can run the one with multiple lines several times and it’s always 4s. When I run it on a single line it’s always 0.1s.
It looks like when I profile the command, it runs faster (0.1s). When I don’t profile it, it takes 4s to run. Based on that, it seems like just the act of profiling affects the performance.
Profiling will force the query to recompile with the query counters built in to the plan. It sounds like the stored plan in the plancache is less efficient than if the query were to be compiled now.
The easiest way to fix this would be to run ANALYZE TABLE on any tables involved in the query. If the statistics have significantly changed, this will automatically invalidate your older plans. If this is not sufficient to invalidate the plans, you can manually drop your plancache by running DROP ALL FROM PLANCACHESingleStoreDB Cloud · SingleStore Documentation.