MySQL Bugs: #30409: Value of query_cache_size is limited to 4294966272 on Windows x64

Bug #30409	Value of query_cache_size is limited to 4294966272 on Windows x64
Submitted:	14 Aug 2007 15:50	Modified:	19 Jun 2008 22:04
Reporter:	Iggy Galarza	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1 bk	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The value of the server variable query_cache_size is limited to 4294966272 on Windows x64.  This seems to be UNINT32_MAX minus the overhead. 

How to repeat:
Using CygWin in the mysql-test directory of a freshly built x64 clone here are the steps I used:

$ export MTR_BUILD_THREAD=51
$ export MTR_VS_CONFIG=release
$ ./mysql-test-run.pl --mysqld=--query_cache_size=5368709120 --start-and-exit
...

Servers started, exiting
Administrator@win2003-64b /cygdrive/r/mysql-5.1-maint_29553/mysql-test
$ ../client/release/mysqladmin.exe -uroot -P 10510 variables | grep -i query_cache_size
| query_cache_size                | 4294966272       |

Suggested fix:
The limit on x64 should be UINT64_MAX (~18446744073713551616) minus overhead.

Iggy, are you aware of any practical use cases or sensible customer requests for a query cache larger than 4GB? Even 100-200M is a large value that's commonly suggested as a reasonable upper limit by the Support team. This looks far more like an S4 feature request that has rearchitecting the query cache as a prerequisite than an S1 bug.

James,

  I can't think of a practical reason for configuring such a high value for query_cache_size.  I wrote this as a placeholder bug when testing bug#29552.  I set the severity because of the difference in behavior on Windows and Linux x64.  

Here's the output on Linux x64:

iggy@amd64:/src/mysql-5.1-maint_nix/mysql-test$ ./mysql-test-run.pl --mysqld=--query_cache_size=5368709120 --start-and-exit

Logging: ./mysql-test-run.pl --mysqld=--query_cache_size=5368709120 --start-and-exit

MySQL Version 5.1.23

...
master> show variables like 'query_cache_size';

+------------------+------------+

| Variable_name    | Value      |

+------------------+------------+

| query_cache_size | 5368709120 | 

+------------------+------------+

1 row in set (0.00 sec)

If there is no reason to set query_cache_size to these values we should add a sane upper limit on both Windows and Linux.

Iggy, best ask the Consulting team what size they think is appropriate as a limit. They may have encountered someone with say unchanging MyISAM tables who wants something like 64GB of query cache for a large product catalogue. That's way outside normal and very bad if a row gets changed with the current design but isn't impossible.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".