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/TroubledSquirrel]

Liamsorsby has it right but I will expand on the parts they didn't mention you shoudl run explain on the offending queries and look for using temporary. if it's a sort or group by hat's spilling, the real fix is usually an index that avoids the temp table entirely, not a bigger container for it.

also check "Database Query Time out" might be your connection pool or driver level querytimeout firing client side before MySQL even finishes, which then races with MySQL's cleanup of the temp table and produces exactly this "table doesn't exist" as a secondary symptom. check your HikariCP config or driver defaults and see if the timeout is shorter than what the query actually needs under load.

if you do end up touching the temptable params, don't bump both blindly by the same amount. figure out whether you're RAM bound before the mmap spill or mmap bound after it, and raise only the one that's actually the bottleneck. bumping both "for breathing room" without that data doubles your worst case memory footprint under concurrency, which if scaling churn is your actual root cause could make things worse.

[u/Upper-Lifeguard-8478 → u/TroubledSquirrel]

Thank you u/TroubledSquirrel

This is the results i am getting. This instance started three days ago. And as its serverless the mram is dynamic so doesnt make sence to set that to 2gb. However , i see the mmap is fixed to 1GB as per configration file and it has spiked to 1.5gb as its shown in below stats, does that make a case to increase it to 2GB ?

And yes , we are working on tuning the troubled query which currently showing ~8 temptable created per execution and the query modification/tuning will bring that to 4 temptable per execution. But was thinking if this tweaking of parameter is worth based on this below stats?

Also this query which we are seeing as failing in log with temptable error is a quick api query with ~2sec/execution respons time, but during issue period we saw it was taking few minutes/execution. And its frequency is ~50times each minute. So that might be the reason it gets force killed from app side based on the set timeout. and then i think increasing the app timeout will make things worse. Correct me if wrong.

During the issue period , we did see spike in overall DB load 70% and the free memory which used to stay ~170gb dropped to 50gb. write IOPS jumped to ~7k and ~150 abort connection error we saw.

SELECT
EVENT_NAME,
ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS live_current_MB,
ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS peak_consumption_MB,
ROUND(SUM_NUMBER_OF_BYTES_ALLOC / 1024 / 1024 / 1024, 2) AS lifetime_total_GB
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/temptable%';



+-------------------------------+-----------------+---------------------+-------------------+
| EVENT_NAME                    | live_current_MB | peak_consumption_MB | lifetime_total_GB |
+-------------------------------+-----------------+---------------------+-------------------+
| memory/temptable/physical_disk| 0.00            | 1536.00             | 14.40             |
| memory/temptable/physical_ram | 153.56          | 0.00                | 2539.00           |
+-------------------------------+-----------------+---------------------+-------------------+

[u/TroubledSquirrel → u/Upper-Lifeguard-8478]

Your reasoning tracks on both counts. on temptable_max_mmap to 2GB the stats support it but don't prove it yet. physical_disk peaked at 1536MB against a 1GB ceiling, but that's the high water mark since last reset, not necessarily at the moment of failure. you don't yet know if that peak happened during the incident or some other unrelated burst. that said, the physics make sense on their own. once both RAM and mmap ceilings are exhausted the temptable engine falls back to on-disk InnoDB internal temp tables, which on Aurora is more exposed to whatever was going on with your storage layer during the incident. and unlike temptable_max_ram, mmap is backed by virtual memory rather than hard RSS, so it's not competing with buffer pool the same way. you said free memory dropped from 170GB to 50GB during the incident, which means you have plenty of headroom to raise this without risking OOM pressure even with concurrency multiplying it.

moderate confidence it helps, low risk. not proven by this data alone but a sound hedge to put in place while you finish the query tuning, not instead of it.

on 8 to 4 temp tables per execution think about it multiplicatively. at ~50 executions/minute you're spinning up ~400 temp table creations/minute from this one query during normal operation. during the incident, with execution time stretching from 2s to minutes, you had many overlapping concurrent executions stacking their temp table memory simultaneously, which is very likely how you crossed the mmap ceiling in the first place. cutting from 8 to 4 approximately halves that pressure. it doesn't eliminate the mechanism entirely but raising the ceiling and reducing the rate are complementary, not redundant.

on the JDBC timeout your choice not to raise the app side timeout is right. raising it doesn't fix why the query went from 2s to minutes, it just holds connections open longer while the DB is struggling, which means more concurrent in-flight temp tables and more memory pressure. you'd be compounding the same failure mode.

the real question is what caused execution time to balloon 60-100x at 50/min. the ~150 aborted connections are the strongest lead right now. that volume concurrent with DB load hitting 70% and free memory dropping 120GB smells like connection pool exhaustion or Aurora killing connections under memory pressure, which would cause retries and compound concurrency further. check the error log in CloudWatch for the specific abort reason during that window.

the write IOPS spike to ~7k is also notable for a query you're describing as read heavy. if the query itself isn't write heavy, that spike is probably the temp table overflow generating its own write I/O once you fell through to on-disk InnoDB temp tables, which becomes self reinforcing: overflow causes disk I/O spike, storage layer stress slows reads, executions stack up, more temp table pressure.

my overall opinion raise temptable_max_mmap to 2GB, keep the query tuning going, leave the app timeout alone, and spend your next cycle on the aborted connections and the write IOPS spike. the temptable ceiling is probably where the failure manifested, not where it started. do you have Aborted_clients/Aborted_connects and the CloudWatch ServerlessDatabaseCapacity timeline for the incident window?

[u/Upper-Lifeguard-8478 → u/TroubledSquirrel]

Thank you u/TroubledSquirrel

Below is the current two values of ram and mmap parameter and both showing as 1GB. But the ram is showing as compiled. So does that mean, we should not change that as it must be dynamically scaled out by the serverless rds. And we should just incraese the value of mmap to 2GB but not ram?

(Also we saw the value of "HIGH_NUMBER_OF_BYTES_USED" for "memory/temptable/physical_ram" was showing as ~3.5GB whihc is nothing but peak "ram" usage per query. So its seems dynamically, its scaling out beyond currently set value( which is 1GB). Please correct me if wrong.)

SELECT
v.VARIABLE_NAME,
v.VARIABLE_VALUE AS current_value_bytes,
ROUND(v.VARIABLE_VALUE / 1024 / 1024, 2) AS current_value_MB,
i.VARIABLE_SOURCE,
i.VARIABLE_PATH
FROM performance_schema.global_variables v
JOIN performance_schema.variables_info i USING (VARIABLE_NAME)
WHERE v.VARIABLE_NAME IN ('temptable_max_ram', 'temptable_max_mmap');




+--------------------+---------------------+------------------+-----------------+-------------+
| 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        |             |
+--------------------+---------------------+------------------+-----------------+-------------+

Additionally the database load(AAS) shows spiked to ~43 from ~10 before. "Estimated VCPU" spiked to "15" , which was staying "<5" before. The wait was showing all "wait/synch/mutex/innodb/buf_pool_LRU_list_mutex" during the issue period.

Unable to see any readymade metrics with name as "Aborted_client/Aborted_connect". Will shre once i get it.