New post
Question
· Oct 30, 2023

IRISTEMP database size increases leads the production instance down

#Databases #SQL #System Administration #InterSystems IRIS for Health

Hello Community

IRISTEMP database consumes more disk space and make the production instance down when running Bulk SQL queries (maybe other processes aswell)

Is setting up the "Maximum" size it resolves the problem?. Is this setting impacts the performance? 

What are the approaches to resolve it

sample screenshot

 

Product version: IRIS 2021.2
Discussion (2)2
Log in or sign up to continue
Chad Severtson · Oct 30, 2023

A few thoughts: 

  1. I consider <FILEFULL> or <DSKFUL> errors to be risks for data integrity -- the system is unable to write everything attempted. This is less of a risk with IRISTEMP, especially if the entire instance fails. However, if other databases are affected, you may have some physical or logical data integrity issues to resolve. 
  2. Check your SQL query plans. I suspect you're generating some large temp tables if your IRISTEMP is the database that is filling up your disk. 
  3. IRISTEMP is special -- it tries to keep the blocks in memory as much as possible before writing them out to the disk. If you're exhausting your disk space because IRISTEMP grew too large, then Alexander is correct: some additional global buffers could help. However, if your load exceeds your capacity, the bag will eventually burst. 
  4. Setting a Maximum Size for IRISTEMP will not completely resolve the issue. However, it may make the situation more readily recoverable. If your IRISTEMP is on the same filesystem as your OS, it can be more difficult to recover without a restart. 
  5. Consider setting MaxIRISTempSizeAtStart to be be able to recover more readily by automatically reducing the size of IRISTEMP
0 0