How to reduce server load?

Question

MySQL

Good time everyone!
There is a virtual server.
Memory: 4 GB
Processor: 1 core
Disk: 40 GB
Worth ispmanager5 deployed a couple of sites on YII2. And then I decided to transfer the site to Joomla + VM (the hosting always swore that it was killing mysql due to lack of memory, there were 2 gaga.)
Now my server is bent over, it works, but Htop shows that every time you go through the site, the percentage eats 100 percent. And the loading of the site is greatly increased.
And the operative eats at 94-97 percent.

Actually a question help, though a little to dump loading. I just ask you to answer more detailed, which team, where the files are, and so on))) otherwise I'm a noob)

Answer 1

profiling and more profiling. Look for missing indexes in queries, then redo the queries, then aggregate the data.

Answer 2

It is impossible to give a single piece of advice here.
There is something in common for the relief of suffering.
1. (Here I advise everyone right and left in general) Set nginx to return static files (do not forget to enable compression), unloading Apache. This will slightly reduce the load when returning numerous JS and CSS files. Those. general well-being will improve slightly.
2. Enable slow-log in MySQL to see slow queries and queries that don't use indexes. People often forget about composite indexes, and as a result of a SELECT on a couple of fields, which should return two values ​​as a result, the entire table is shoveled into several hundred thousand records.
3. PHP profiling. Install xdebug or XHProf for PHP. Using xdebug as an example - enabled the module, restarted / reloaded Apache, followed the most retarded links (or waited a few seconds for users to follow them), commented out the module, reloaded Apache again to return it to normal. Well, then QCacheGrind in hand, open the fattest dump files and see where the time is spent during execution ...

Answer 3

slow_query_log, then EXPLAIN for slow queries, then indexes.
the next step is PROCEDURE ANALYSE. Often the data is not stored optimally at all - and you can win well by optimizing data types.