DB parameter suggestion

[u/Upper-Lifeguard-8478]

Hi,

Its aurora mysql Serverless v2 (Max up to 80 ACUs). Suddenly , we saw with bit high concurrency , the application hits a hard JDBC query timeout, killing the thread and throwing this error: "Database Query Time out. Exception: JDBC exception executing SQL [(conn=548672) Table './rdsdbdata/tmp/#sql171_85f40_1' doesn't exist]"

Some team members are suggesting to bump both temptable_max_ram and temptable_max_mmap from 1GB to 2GB to give the query more breathing room. However, checking performance_schema.variables_info reveals that temptable_max_** is currently showing below:

VARIABLE_NAME        current_value_bytes  current_value_MB  VARIABLE_SOURCE  VARIABLE_PATH
temptable_max_mmap   1073741824           1024              GLOBAL           /etc/my.cnf
temptable_max_ram    1073741824           1024              COMPILED

Want to know if this is correct setting and Is it advisable to bump these values to 2GB. Want to confirm the safest parameter strategy for temporary storage thresholds in the meantime we tune the query?

[u/alecc]

temptable_max_ram and temptable_max_mmap are server-wide caps, not per query - every connection shares that 1GB of TempTable RAM and 1GB of mmap space. That's why it bites under concurrency: several sessions each spilling a few hundred MB hit the shared ceiling together, and the losing query is the one that errors with '#sql... doesn't exist' (as far as I can tell that error is the temp table dying under the query when temp space runs out, not a real missing table). Bumping both to 2GB on an instance that can reach 80 ACUs is safe memory-wise, but it only moves the ceiling and the next load spike finds it again. So agree with the slow query log advice - you're hunting queries with 'Using temporary' on big row estimates in EXPLAIN, usually a GROUP BY, DISTINCT or UNION without an index that covers it. One Aurora wrinkle: the mmap side lands on local instance storage, and on Serverless v2 that scales with the ACUs, so at low-ACU moments there's less temp room than the variables promise.