MySQL and Memory

Question

MySQL

How to find out what exactly eats up memory in MySQL?

more precisely, what is the reason for the large memory consumption of MySQL by the server?

many tables?
large table sizes?
many indexes?

As far as I understand, MySQL stores indexes in memory, and the database itself lies in files.

Answer 1

There are a couple of questions, who set up MySQL for you and what type of tables do you use?
Still, offhand, I can say that the memory is leaving most likely due to sorting by non-key fields. MySQL allocates memory for sorting per connection, by default 8 megabytes, memory is allocated as soon as a request with sorting arrives, and the entire portion is allocated at once, regardless of whether you need 100kb for this operation or all 8 megabytes.
See SHOW FULL PROCESSLIST while loading.
For complex queries, see EXPLAIN.
A few strategies:
1. Add keys for selections and sorts if applicable and not yet available.
2. Increase the chances of queries hitting the MySQL cache.
3. Cache data at the application level.
4. Transfer some operations to the application level.

Answer 2

Check the number of max. connections and threads (processes on Linux) MySQL. You understand that each of them requires memory.
Check the set values ​​of key_buffer_size (Spanish MyISAM, one buffer for all threads), thread_stack (each thread needs a stack), net_buffer_length (each thread has its own), read_buffer_size (each thread has its own), read_rnd_buffer_size, sort_buffer, pool_buffer_size, as you wrote above .
In general, MySQL is a fairly predictable system and eats as much memory as it is told to. The link dev.mysql.com/doc/refman/5.0/en/memory-use.html will help you.
Stop using bad things like JOIN or fulltable row scan on large tables. They all also eat memory, and how.

Answer 3

In general, if you use only innodb, the memory is consumed there by the data buffer file - it is customary to set it to 80% of the available operational - innodb_buffer_pool_size. It stores pages with data and indexes, so as not to climb once again on a slow disk, but always refer to the RAM.
And all the other buffers are crumbs compared to this (unless, of course, we are talking about a server with gigabytes of RAM :) )

Answer 4

Lots of requests?