The database loads the CPU at 100%. What could be the problem?

Question

MariaDB

The database loads the CPU at 100%. What can be wrong?
Salute to everyone, I've been suffering with the database (MariaDb) for a month now. I must say right away that I am poorly versed in this issue, but I honestly tried to solve it myself, the only time I have already asked such a question here for a long time and still did not find an answer. In general, there is an active project, already (reinforced the machine recently) is on VDS Intel Xeon E5 (cores 4 vCPU), 8 GB of RAM.
Server version: 5.5.56-MariaDB. And all the filling was put by the host, we didn’t climb there, we just put our project on the host.
At first, everything was fine, but now the project has grown and the user base has grown.
At the moment, the situation is such that if 30 users, for example, try to activate a promotional code, then the CPU flies up to 100%, the site simply does not open. If you look at the PMA monitor, then when 1 person enters the site (normal entry, no action), the CPU rises by 2-3%. And in the questions column, a "peak" appears and the number of questions becomes 400+, and if there is no activity on the site no, then there are still issues in the schedule, in the amount of ~ 100-150 and the CPU never drops below 10%.
Now I figured out the issue much deeper and got into the database state variables, after which I tried to configure the database config based on what "suffered" there, naturally the config can be crooked, but by increasing the size of the table cache, I noticed a strong increase, but the graph about that there are still a lot of open tables, this did not make it smaller, the more time passes, the more these tables open.
Screenshots: 1) Screenshot of the current state of the database variables (2 days have passed since the start of mysql on the screenshot): https://c.radikal.ru/c06/1809/95/ecfa64208b00.png
2) Screenshot of the monitor at the moment when the load on the site practically none (maximum 2 people entered during this period): https://c.radikal.ru/c35/1809/61/c9fe8d40c7bb.png
3)Screenshot of the machine resources, on the left - last night: https://b.radikal.ru/b04/1809/9d/f8b670efe8b3.png
4) And finally, a screenshot of my database config, which I somehow collected from American resources and tried something change it yourself, looking at the result: https://d.radikal.ru/d34/1809/0f/e6fb88b9e000.png and here https://b.radikal.ru/b11/1809/70/757a3244f2d5.png the rest is worth default.
I have already optimized the queries as far as I can, I re-read about indexes and so on, I see that there are indexes and I see that the database scans tables a lot, but here either I don’t understand where and how to set indexes correctly, or another reason, I optimized heavy tables using Optimize and still the same picture happens.
Another important point, if you go into PMA and try to roam there, then periodically (it used to be very often, but with the hosting extension it became only when the server is under load), you can catch an endless load, the page will not load until you refresh it. This happens when entering the table, or when exiting the main PMA screen, or when entering the search, it doesn’t matter, it just throws it into a dead load.
The machine also has proxying (DDoS protection), but it does not naturally affect local requests. Many thanks in advance to everyone ! I hope to be forgiven for my ignorance of how everything works, but I have encountered such a problem and it needs to be solved urgently, so I am forced to study and ask. Thank you!

Answer 1

It will probably not be possible to explain much, but at least I will start.
1) Enable logging of slow queries in the database
2) There is such a command - EXPLAIN - https://dev.mysql.com/doc/refman/8.0/en/using-expl...
Helps to understand how the SQL query should work
3) Indexes should not be abused either. Usually it makes sense to put an index on a field in two cases: either tables in JOIN are linked by this field, or a search is performed on it. More indexes - more memory consumption.
4) Usually database tuning in my experience ends up with an empirical selection of buffer sizes and so on, since it often depends on what data is stored in the database and how it is processed
5) All queries should be looked at for JOINs and analyzed using EXPLAIN. If two tables are related by some fields, it is not certain that the linking will be in the direction you think. It really helps a lot to build a SQL query schema with tables and relationships.
6) Sometimes, in order for the index to work, it may be necessary to slightly change the SQL query, it depends on the database and on the query planner. Sometimes it helps to rewrite the query in the opposite direction or simply by changing the order of the tables when linking.
Etc.
I'm currently solving problems similar to yours, but on PostgreSQL, the principles are still the same. Sometimes the query execution time can be reduced up to 15000 times (on large databases)

Answer 2

Well, first of all, look at how many unsuccessful connections to the database you have - as many as 66 pieces. On a normal project, this should not happen in principle. It suggests that there are suboptimal or incorrectly composed requests inside the project, which freeze at the time of loading and prevent others from connecting normally. This explains the PMA hang, most likely - it simply cannot connect to the DBMS. And apparently it's so sad that even your 2 thousand allowed connections are not enough. These queries can be tracked in MySQL itself, best in the console with the SHOW FULL PROCESSLIST command; - however, if there is a load, most likely you will not even be able to connect to the console. But if during the normal operation of the site you will have a dozen suspended requests there, it obviously doesn’t smell very good.
See also Low Query - the figure is also large. You need to review these requests and decide what exactly can be changed there. In some cases, it is enough to make an index on a field in a table in order to significantly speed up the processing of a request. And in some cases, you need to optimize the application code already in order to replace a long request with a shorter and faster one. At least temporarily include the logging of these requests in a separate log and parse each of them.
The question still remains - what loads the CPU more - exactly MySQL or still the application code? What it is written on, the framework, what CMS - all this must be taken into account.

Answer 3

Optimize does not help everywhere and not from everything, we read carefully . The slow query log is a very useful thing. If there are frequent requests, all the fields from the conditions in them are put into indexes (which ones are specific - a separate kung fu, a quick option - each field into a separate index). Well, it also helps a lot to enable logging of all requests on a test machine (NOT ON PRODUCTION). A large number of identical requests when running one case clearly indicates the developer's hand-to-hand.