Clear cache memory and alternative tool


#1

I am having some performance tests with different MemSQL data warehouse architectures and I faced 2 problems:

  1. 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)?

  2. 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.

  3. 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?

Thanks.


#2

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 second question, we’re considering adding the ability to edit multiple SQL files at once in Studio; no timeline for it yet. See Tool recommendations for connecting to MemSQL for tool recommendations.

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: https://docs.memsql.com/tutorials/v6.7/optimizing-table-data-structures/