The table in MariaDB weighs 321MB, how to make it work quickly?

Question

MySQL

CMS Simple

table weight

musqltuner 1

musqltuner 2

It's more interesting to hear personal experience. Thank you all for your time.

Answer 1

1. You started mysqltuner without even letting it run for 24 hours. It's better to look at it again later.
2. Follow all his recommendations.
3. The weight of the table is not particularly large. How many lines are there?
4. What exactly is bothering you? Track slow queries, keep slow log and error log mysql, see what will fall there. Optimize heavy queries.
5. You can put the mytop utility and monitor in real time for bad requests. then optimize them.
6. Use indexes. If used, review whether the table is indexed correctly.
7. For slow queries, you can use explain.
8. If the table is innodb, then you can speed up its work by disabling flushing data to disk innodb_flush_log_at_trx_commit = 2. (but there are some pitfalls here, google it).
9. You can set up moving mysql temporary files not to disk, but to tmpfs (directive tmpdir ) this will speed up the work of the entire mysql server a little.
10. query cache - configure. no matter what anyone says, he helps. (but you also need to test it, maybe in your case it will be better or maybe not)
11. set the key buffer size in accordance with the RAM
12. If there are a lot of rps (and in any case) spin the kernel, expand the /etc/sysctl.conf limits /etc/security/limits.conf
13. And of course it's understandable to use a good multi-core server with ssd in raid-e.
14. And of course, make sure that you use the correct types of fields in the table; perhaps you can still optimize the architecture of the table itself. Thus, when using the correct types, the weight of the tables can also be reduced.
15. Perhaps there are a bunch of unnecessary entries in the table that will never be useful and they need to be cleaned by the crown once a day, for example?
16. And of course, to speed up the entire mysql server, do not write bin log and general log.
17. If the myisam tablet can be heavily defragmented? sometimes it is possible and optimize table to execute then.
18. If everything is absolutely powerful and there are a lot of requests to it (highload project), then you can already look towards the cluster / replication.