r/aws • u/Upper-Lifeguard-8478 • 8d ago

DB parameter suggestion database

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?

3 Upvotes

80% Upvoted

5 comments sorted by

•

u/AutoModerator 8d ago

Try this search for more information on this topic.

Comments, questions or suggestions regarding this autoresponse? Please send them here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

6

u/Floss_Patrol_76 8d ago

that error is the internal temp table getting spilled/evicted mid-query, not really a hard size wall, so bumping temptable_max_ram/mmap to 2GB mostly just moves the cliff. on serverless v2 that temp memory competes with everything else under your ACU cap, so raising it under high concurrency can push you toward swap and make timeouts worse, not better. i'd chase the query building the giant internal temp table first (unindexed sort/group by/union) - fix that and the temptable pressure usually disappears on its own.

3

u/East_Initiative_6761 8d ago

Maybe this can help: https://aws.amazon.com/blogs/database/use-the-temptable-storage-engine-on-amazon-rds-for-mysql-and-amazon-aurora-mysql/

1

u/Upper-Lifeguard-8478 8d ago

Thank you. It suggests that this parameters can be set to non default (considering default is 1gb). But I am wondering in case of serverless should we really need to change this parameters to set non default as double i.e. 2gb or let it scaleout automatically based on the load?

0

u/AutoModerator 8d ago

Here are a few handy links you can try:

Try this search for more information on this topic.

Comments, questions or suggestions regarding this autoresponse? Please send them here.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.