- Newest
- Most votes
- Most comments
After getting in touch with AWS support and long investigations we discovered that the max_tmp_table was set to 32 and since we had a large number of concurrent requests on a view that created multiple temporary tables, that 32 was too small and tmp tables started to spill to disk hence the aria cache wait time increased until the point of hanging.
Couldn't find any information on google searches about this, but I was wondering if it was some time of memory pressure issue.
I did a made up sort
select * from persons a, persons b , persons c , persons d , persons e order by a.firstname;
using data from
http://www.moinne.com/blog/downloads/countries_persons_tables_2012-11-28.zip
and ran it by a few sessions and got the same kind of load
CPU
synch/mutex/aria/PAGECACHE::cache_lock
What is the explain plan of the query like? Are the indexes it needs in place ? Is it doing sorting?
Kyle
All the graphs I could get on the RDS:
https://imgur.com/a/8XO04gZ
The analyze of the select:
https://imgur.com/S9uQJpW
I'm not a database expert but I guess some of this is not done properly. The question though still remains.. why does this happen all of a sudden? We do have automatic minor update for the engine but I checked and there were not updates in the past week.
I've also checked the process list for the db and could see the select from v_links query stuck in "Sending data"
I've also run the query that was stuck in "sending data" and that finished in 15ms.
After doing some research I've found these 2 database statuses:
Aria_pagecache_read_requests | 11323551304
Aria_pagecache_reads | 834127724
The ratio being somewhere ~1:13 which might be too low if aria is the same as myisam in this regard: https://mariadb.com/kb/en/library/optimizing-key_buffer_size/
However when trying to test this and increase the aria_pagecache_buffer_size to something more than the standard 128MB I realized that the parameter is not present in the parameter group and it's read only when trying to change it directly on the db. Is the small size of the aria pagecache buffer what impacted my performance in such a way?
Relevant content
- asked a year ago
- asked a year ago
- AWS OFFICIALUpdated 6 months ago
- How do I resolve the storage full issue on my RDS for MySQL instance or my RDS for MariaDB instance?AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 5 months ago
- AWS OFFICIALUpdated 2 years ago
How did you solve your problem because max_tmp_table id not a configurable parameter of AWS RDS for MariaDB.