Query cache seems to be limited to ~2.1GB

[mknapphrt]

We've set the query cache size to be 4095MB (just shy of 4096 because we're currently running version ProxySQL version v1.4.10-1-g5eb0f3e, codename Truls in production which doesn't yet have the fix #2040). But it seems that we're currently bound to ~2.1GB, and once that's hit a bunch of cache entries are being purged all at the same time. Easier to visualize w/ some prometheus graphs:

We've got machines w/ way moee than 2Gb of ram and we'd like to utilize more of it for caching. Possibly there's other configurations that we're missing that need to be tweaked too?

We tried using the newest release 2.0.12 just now to see if this was addressed and it doesn't seem to be, we experienced the same results.

Here are the cache related settings we're running w/:

mysql> SHOW VARIABLES LIKE 'mysql-query%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| mysql-query_retries_on_failure        | 1     |
| mysql-query_digests_max_digest_length | 2048  |
| mysql-query_digests_max_query_length  | 65000 |
| mysql-query_processor_iterations      | 0     |
| mysql-query_processor_regex           | 1     |
| mysql-query_digests                   | true  |
| mysql-query_digests_lowercase         | false |
| mysql-query_cache_size_MB             | 4095  |
+---------------------------------------+-------+

mysql> SELECT cache_ttl FROM mysql_query_rules;
+-----------+
| cache_ttl |
+-----------+
| NULL      |
| NULL      |
| 600000    |
| NULL      |
+-----------+

OS Version: debian 9.11, 4.19.92 kernel version

I don't see any log files in our data dir or any output in the journal, happy to provide logs if someone can advise where to find them.

[mknapphrt]

Another thing of note, I've also noticed that the cache reports several Extabytes of cached memory sometimes, it seems that it does this whenever it passes 2^31. Presumably then triggering the purge to start. In my graph above I filtered out items less than 1e12 to see the graph better but gaps there are times when the reported cache jumps so high.

[mknapphrt]

Looking at the code a bit, I think this may be the issue here:

proxysql/lib/Query_Cache.cpp

Lines 296 to 304 in f02efa2

	uint64_t Query_Cache::get_data_size_total() {
	int r=0;
	int i;
	for (i=0; i<SHARED_QUERY_CACHE_HASH_TABLES; i++) {
	r+=KVs[i]->get_data_size();
	}
	r += __sync_fetch_and_add(&Glo_size_values,0);
	return r;
	};

I would think r would also need to be a uint64_t

[renecannao]

hi @mknapphrt .
Thank you for the PR.
I think this issue can be closed, right?

[mknapphrt]

Yes! Thanks for the review!