Temp table contention

Help Procedural SQL
1

Folks,
Has anybody seen the following while creating session level temp tables heavily?

  1. Contention on catalog tables while creating the tamp tables.
  2. Orphan temp tables.

Q:

  1. How do I see the list of temp tables and associated session id.
  2. How do I see the space usages for existing temp tables.
2

  1. ‘show temporary tables’ will list all temporary tables in a database along with their session_id.

  2. Unfortunately I don’t think it is possible to view it in one place, you can view memory usage of temp tables from current session by quering ‘information_schema.tables’

1 Like
3

If your application is rapidly creating/dropping temp tables I would make sure your on Singlestore 7.3. It has improvements to the connection pooling for table DDL which improves the performance of cases that do a lot of temp table DDL.

4

Hi Adam,
Thank you for the response. Yes we are using 7.3. Here are our results with 100 simultaneous connections.

Option A:

  1. We create a temp table in main SP and call a child SP that inserts data into it.
  2. Main SP joins with other tables and sends results to the application. There is no transaction in main SP. Just select.
  3. It takes an average of 1164ms.

Option B:
We create a permanent table with an additional column session_id.

  1. Follow exactly the same process in option a to add data into it thru sub-SP & use it in main SP.
  2. At the end we delete it from this table based on the session ID.
    This takes an average of 290ms.

Option 3:

  1. Same process as in option 2, but instead of delete we rollback in main SP.
    It takes an average of 182ms.

Looks like we are seeing contention on catalog tables during ‘create table’.

Another question. Do you have any option so I can disable temp table logging?

Thanks,
Sid.