I am having some performance tests with different MemSQL data warehouse architectures and I faced 2 problems:
After executing a query, the server stores the result in cache memory and the query times fall considerably to later runs of the same query. Is there any way to clear the cache memory in MemSQL? (DROP ALL FROM PLANCACHE)?
For query manipulation, do you use MemSQL Studio or another tool? it is somewhat unproductive to have only one space for queries. I was able to connect to the MySQL Workbench and it improved a bit, however, it does not recognize reserved words from MemSQL itself.
I’m testing rowstore, columnstore, different fields for shard keys, normalization. How big is the amount of techniques in MemSQL, in your opinion, what other possibilities can be tested for modeling?
Regarding your first question, MemSQL has an in-memory plan cache and a disk-based plan cache. Moreover, each node has its own plan cache. So leaf-only parts of the plan are stored on leaves, and aggregator (agg)-only parts on aggs.
DROP ALL FROM PLANCACHE drops all plans that are in memory from both memory and disk, only on the current node. If you run it from an agg, then the leaf plan components will still be there on the leaves. As of version 6.7, there is no easy way to completely wipe out the entire memory and disk-based plan cache across the whole cluster. If you really want to do that, you can shut down the whole cluster, then delete the plan cache directory contents on every node (https://docs.memsql.com/operational-manual/v6.7/managing-plancache/#deleting-plancache-files), then start the system back up.
Regarding your third question, there are lots of options for physical database design for MemSQL of course. This is a good discussion of how to approach it: SingleStoreDB Cloud · SingleStore Documentation
DROP ALL FROM PLANCACHE in 7.1 invalidates and purges all previously compiled query plans, both in-memory and on-disk. You get a fresh compiled plan for any new query.