INH mutex
User reported heavy contention on "INH list" mutex under high load. Initial investigation suggested it relates to inheritance/inheritance cache mechanisms. Support indicated it may involve table hierarchy or typed tables. User's schema had multiple levels of foreign key dependencies. A defect (idsdb00098913) was filed with support for high-load scenarios with complex table dependencies.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
Does anyone know what area of the engine the "INH list" mutex protects? I am
seeing a large number of waiters on this mutex under heavy load. I suspect it
may relate to access to the in-memory lock table but I am really only
guessing. I can't find any reference to this online.
A typical wait on this mutex looks like:
tid name rstcb flags curstk status
236362 sqlexec 4cc025288 S--P--- 7136 mutex wait INH list -
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
235372 UPDATE user_db CR Wait 15 0 0 9.28 Off
Current SQL statement (107133) :
update my_table set my_col = my_col where id = ? and my_col = 'P'
Host variables :
address type flags value
-----------------------------------------
0x00000003d978d6f0 INT8 0x000 20064788376
The session is doing some kind of dummy update to lock the row.
Ben.
Original post:
Hi,
Does anyone know what area of the engine the "INH list" mutex protects? I am
seeing a large number of waiters on this mutex under heavy load. I suspect it
may relate to access to the in-memory lock table but I am really only
guessing. I can't find any reference to this online.
A typical wait on this mutex looks like:
tid name rstcb flags curstk status
236362 sqlexec 4cc025288 S--P--- 7136 mutex wait INH list -
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
235372 UPDATE user_db CR Wait 15 0 0 9.28 Off
Current SQL statement (107133) :
update my_table set my_col = my_col where id = ? and my_col = 'P'
Host variables :
address type flags value
-----------------------------------------
0x00000003d978d6f0 INT8 0x000 20064788376
The session is doing some kind of dummy update to lock the row.
Ben.
Response:
That mutex name has something to do with inheritance and the inheritance
cache. Not sure that that helps you any, and I have not really seen that show
up myself. Not sure if that's something that will always be checked, or if you
have to create your tables/schema in specific way (like using named row types
or typed tables).
Jacques Renaut
HCL Informix Advanced Support
Hi Jacques, Thanks for replying. It doesn't give me an immediate solution but does gives me some pointers. We don't use special row types or typed tables but this particular table does have a large number of dependent tables (via foreign keys). Ben.
Wouldn't 'inheritance' rather be something regarding table hierarchy, tables defined under other tables (don't recall having ever seen this live.)
Hi Andreas, I'm not sure. The database in question doesn't have any non-standard column types such as multiset or hierarchical tables. Ben.
Just to update the group and anyone looking at this issue at a later date, I raised a case with support and a defect has been raised: idsdb00098913 To see the issue, you'll need a high load with a large number of threads plus a schema with several levels of table dependencies (via foreign keys). Ben.