Potential drawbacks with InnoDB clustered indexes and MODx session structure

This problem/topic is not directly related to MODx but it might cause issues for some people when MyISAM is switched to InnoDB. So I thought it would be interesting to discuss.

We are having serious issues with one of our productions servers. The setup in question is CentOS 7 64-bit server with 16 GB of ram and 250GB SSD (so the setup should be very fast). The LEMP stack is configured with MariaDB 10.1.29. We are having about 20 applications running on this server, each using its own database.

Couple of weeks ago we saw in our status monitor (pinging each application in 5 minute intervals and measuring response time) that 3 applications are performing very slowly from time to time. (Response times from 2s-10s while the average is around ~0.1s). The slow log of MariaDB is full of slow queries (most of them easy insert and update queries for session storage) which sometimes take up to 15 seconds, but only from these three applications. (not MODx)

What these three applications have in common compared to MODx applications, they are all using InnoDB instead of MyISAM. So for further debugging we converted 3 MODx applications from MyISAM to InnoDB and these also started to suffer from the same delays.

This is a typical slowlog entry:



As you can see, this insert took more than 10 seconds. I can run this query 50 times and it will perform under 0.003 ms, but occasionally it will be very slow. (only happens with InnoDB).

After some research I came across the following SE thread https://dba.stackexchange.com/questions/18663/random-write-freezes which I'd like to sum up here:

From MySQL docs (https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html):

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

From SO Answer (https://stackoverflow.com/questions/2267326/how-to-choose-the-clustered-index-in-sql-server):

According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:
- Unique

- Narrow

- Static

And if you can also guarantee:
- Ever-increasing pattern
then you're pretty close to having your ideal clustering key!

So in the case of MODx session table from the query above:

- Unique => yes
- Narrow => yes
- Static => yes
- Ever-increasing pattern => no

Here's what can happen when a non-ever-increasing clustered index is used (http://www.sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx):

If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity).

So I assume in my case the problem is, when a new row is inserted (and this happens a lot under heavy load) at a random location of the index (because of the id/PK being a random hash) InnoDB sometimes won't find any space available and therefore has to rearrange the index and that takes time. Additionally this will cause fragmentation of the index and then slows down other queries not directly related to session.

Maybe this is just a wrong guess or I am completely off track or my server setup is completely messed up, but I'd like to hear your opinions on this. What would be a solution? Keep the session table in MyISAM or switch to different session_handler?

I just switched from MyISAM over to InnoDB and we're noticing a significant slowdown. Prior to making the switch to InnoDB we upgraded from MySQL 5.5.53 to MySQL 5.6.37. So with the combination of upgrade plus conversion to a different format, it's hard to say which is impacting the performance more, or if there's some other factor.

It's interesting that authenticated sessions (the user logs in to access our content) are fast. Anonymous sessions (MODX user ID = 0) are slow. I can't figure out why that would be the case.

Did you come to any conclusions from your situation?

These issues seems to be getting hotter and hotter, especially since MODx 2.6 is using InnoDB as it's default storage engine and more and more people are complaining about the same issues:

- Change new installs to create tables with InnoDB engine on mysql [#13462]

The team didn't make any changes in the table/session design which should be considered when switching from MyISAM to InnoDB. (https://mariadb.com/kb/en/library/converting-tables-from-myisam-to-innodb/)

I am now going back and forth for more than 4 weeks with these issues, debugging, switching my.cnf configuration files (playing with InnoDB buffer size), analyzing queries, explain queries, checking my InnoDB Engine status but in the end it all breaks down to session related Inserts and Updates of my MODx session tables. The queries get stuck in query_end state as you can see in this example:



Digging in deeper this happens when the db server stalls/flushes/re-organizes the index. When we take a look at the MODx sessions table with SHOW CREATE TABLE modx_sessions:



We can see that id is not using `auto_increment` and `DEFAULT ''` which brings me back to the InnoDB and clustered indexex issue from my opening post. This sessions PK structure seems to be keeping InnoDB busy, especially when a lot of INSERTS and UPDATES are going on, either with a high-traffic installation or multiple low to mid-sized MODx installations. Obviously MyISAM was more solid/faster for this workflow...

From my dba stack thread:

For every row added to modx_session table, you can expect it to take more time for any INSERT, UPDATE or DELETE as long as the ID is not UNIQUE (which it isn't because auto-increment is not used). Give this a try on just one table that is listed in slow log and then use EXPLAIN to confirm you went from RANGE type to EQ_REF type and you will get away from 100% filtering [..]

Of course not all MODx default tables should be re-structured, but when MODx now is using InnoDB as their default engine this should be taken care of. Maybe set session driver to file by default or re-structure sessions table...

Also people that are just hosting one MODx installation on their server/webspace won't see any significant slowdowns, but in my case I am running about 10 MODx installations an my slowlogs exploded with slow queries related to sessions after switching from MyISAM to InnoDB. Here's what I did to bypass this issue (starting from easiest to most complicated solutions):

- disabled session for the whole installation (if not needed)
- disabled anonymous_session (if not needed)
- converted session related tables back to MyISAM
- switched session from database to file driver (definitely faster than InnoDB)
- switched session from database to redis driver (best and fastest solution, but complicated to setup)

Regarding your case: anonymous slow, authenticated fast => Maybe MODx recovered already existing sessions for authenticated users which processed faster then INSERTING new sessions...I am not really sure.

PS: If you see slowlogs for other tables than session, check the PK/Index and the create statements. InnoDB is also slow with text type fields and try to analyze the table if the next auto_increment (e.g. 560) and the cardinality (e.g. 230) have a huge difference.

It is not an ideal solution but my slowlog is empty again and Redis Session Storage has the best performance so far.

Great information in this post. If you have time, can you enter an issue at GitHub for this? I'll look into it in more depth tomorrow afternoon.

Done: https://github.com/modxcms/revolution/issues/13726