-
Notifications
You must be signed in to change notification settings - Fork 380
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Database CPU spikes and cripples database after upgrade from .NET Core 2.2 to .NET Core 3.0 with Pomelo 3.0.0-rc1.final #874
Comments
EF Core changed its query pipeline for 3.0. In contrast to EF Core 2.2, every LINQ query is now converted into a single SQL statement (if possible). That means, that LINQ queries with many This can lead to a cartesian product, that takes longer to execute, than the currently set timeout for the query. See dotnet/efcore#18022 for a discussion about that. Also see the
Troubleshooting: You can just search your LINQ queries for those containing many |
@lauxjpn Thanks for your help with this one. I was able to use the slow query log in the MySQL from the time that it brought my site down. I thought it could be some serious issue with Pomelo/EF or something, but it was indeed a single Include() that brought my database to it's knees. Just in case anyone is interested, this is the query that did it:
An The Include created an INNER JOIN, but in the join there was a query that scanned every row in the table in order to get the data to join on. Pretty hideos. Hopefully this helps someone. |
Thank you for sharing your solution! |
Steps to reproduce
I have recently converted my website that uses Identity Framework from .NET Framework MVC to .NET Core 2.2 MVC. I went live with it last week using Pomelo 2.2.0 and it's worked a treat. No problems at all with regards to Entity Framework or my MySQL database.
Yesterday I saw on this thread that 3.0.0-rc1.final for Pomelo was released so I thought it would be wise as I had just upgraded to .NET Core to at least be on the latest version.
I followed this migration guide and I converted my site to .NET Core 3, using the 3.0.0-rc1.final version of Pomelo. I tested the site locally and in my test environment and all looked good.
Unfortunately when it came to going live in my production environment, where there are around 100-150 people on the site at any one time my database spiked to 80-100% CPU as soon as a single container span up. Also the log was outputting database timeout errors immediately.
I tried it on two occasions to bring a container in using the new version. You can see these two occasions clearly on the metrics for my AWS RDS MySQL database below:
The issue
The log is being flooded with command timeout expired errors at the same time as the spike and it only takes one container to come into the load balancer to bring my database to it's knees. Sadly at this point I'm not sure what to do to troubleshoot it. It looks like all of the exceptions stem from the Authorization/Session middleware:
(snippet from full stack trace below).
The migration guide says that the Authorization middleware is required now, so I don't even think that I can remove the new calls that I added to the startup.cs.
Here's my startup class:
I'm really not sure how I can go about troubleshooting this without all of the traffic that is going through my site. I'd appreciate any help that I can get.
If you are seeing an exception, include the full exceptions details (message and stack trace).
Further technical details
MySQL version: 5.6.41
Operating system: Amazon ECS, Docker, .NET Core 3.0 Runtime bionic image
Pomelo.EntityFrameworkCore.MySql version: 3.0.0-rc1.final
Microsoft.AspNetCore.App version: 3.0.0
Other details about my project setup:
The text was updated successfully, but these errors were encountered: