Cpu % maxing out

[Alex]

Any folks that know how to find offending processes that are eating up CPU in a digital ocean droplet?

 

[adam]

I think if you run the top command it should give you a list of all processes and their usage.

 

[Alex]

Thanks, I get MySQL %86..
This means it has to be the darn queries. I am running a Laravel app.
Any way the particularly slow query can be found?

 

[adam]

Paging @Gummibeer Laravel guru. I know we have a few other Laravel pros here but can’t remember names. 😇

If nobody gets back to you on this thread may be worth making a new one specifically about slow laravel queries.

 

[Alex]

I have a hint about one initial page load. It's an initial list that requires paging. But I rather use a tool that tells me exactly where the issue lies.

 

[Gummibeer]

Search for MySQL slow query log. It will slow your DB more down but logs you EVERY slow query. There's also a tool, forgot the name, that analyses the log and gives recommendation to improve - add/remove indices, split queries and so on.

Some general things:
Laravel queries (MySQL at all) gets slow with joins, n+1 queries and really large result sets.

Ideas to fix: check if you can use subqueries instead of joins. Use eager loading instead of n+1 loading, paginate in DB not PHP.
You should also check if you Aggregate any values that you can store in cache/redis.

Deep relation loading via too complex accessors/mutators is a very common issue.

If you have any code or query I can try to help with more detailed recommendations.

MySQL :: MySQL 5.7 Reference Manual :: 5.4.5 The Slow Query Log

dev.mysql.com

Analyzing a MySQL slow query log with pt-query-digest | Jeff Geerling

www.jeffgeerling.com

 

[Alex]

A top command in shell showed me the pain the processor gets when I hit a specific route and DB query.
Can you tell me more about pagination in DB?
Maybe if I could share a screen with you and we could see it together?

 

[Gummibeer]

Database: Pagination - Laravel - The PHP Framework For Web Artisans

laravel.com

Sharing a screen is everytime a tough thing because I know that I have time for it like 5min before. 😅
You can send me a repo, gist or only the single query - code and SQL in best case.