I’m using Couchbase Enterprise server 7.1.1 and I would like to empty the prepared statement cache.
According to the documentation, it is emptied when you restart the couchbase server.
In my cluster setup, I have 2 query+index nodes. Does it mean that I should restart both of them or just one is enough? If I restart one node, would its cache be refilled with the prepared queries statements from the other node? If so, does it mean that both nodes should be removed from the cluster and restarted at same time? Is that possible letting the cluster without any query+index node during such timing?
Otherwise, what is the effect of deleting every prepared statement from system:prepareds? Is that list just a history or is it really the content of prepared statement cache?
system:prepareds is the prepared statement cache, presented as a keyspace.
To clear the cache, just delete from system:prepareds or use the Rest API (/admin/prepareds) to do the same.
If you restart a single node, on restart it will populate the cache from the other node. If both nodes were stopped and restarted together (don’t have to be removed from the cluster, the services on the hosts could just be stopped & started) the cache would be cleared. (Usually much simpler to just delete from the system keyspace.)
Obviously the effect of deleting any statement from the cache is that any attempt to execute a prepared statement will fail and probably will cause the application to re-prepare (assuming it is coded to do so); this could cause a spike in CPU load if there are many statements that are simultaneously prepared again.