What process(es) is(are) using up the memory?

MySQL & MariaDB Other MySQL® Questions
1

Hello,

We are using MySQL 5.044 and have the following replication topology: 1 Master and 3 identical slaves.

Master machine has 7GB of memory and Innodb buffer is set to 2 GB.

Each slave has 35 GB of memory and innodb buffer is set to 25 GB.

Recently we began loading a lot of data on a daily basis (~ 300,000 articles) and I noticed that throughout the day the memory on the machine which starts out with ~ 31GB after mysqld service starts ends up with just 16MB of memory when I run “free -m” command.

The data load involves a lot if inserts, but also deletes and updates, so my initial assumption was that a lot of data goes into the innodb buffer pool (due to selects during insert/delete/update process?) and the buffer is not cleared up after data is commited to the database. However, doing more investigation seems to indicate that this is not the case (maybe only partially the case). Namely, I just looked at how much memory is used up now using “free -m” command, and it says 15GB. I then look at how much innodb buffer pool is used up using ‘mysqlreport tool’ and it shows that only 2.79GB are used.

So my questions are:

  1. Where is the other 12GB going?
  2. How does innodb buffer flush/clear up its contents? What is the internal algorithm?
  3. Any other suggestions I can use to investigate the memory usage? [i.e. once i totally run out of memory again on the machine, what can I look at to get an indication of what the memory is being used for?]

Below is the .cnf file for the machine in questions:

datadir=/mnt/ebs/lib/mysql
socket=/var/lib/mysql/mysql.sock

set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

query_cache_size = 10M
thread_cache_size = 40
table_cache = 300
key_buffer_size = 132M

InnoDB settings

innodb_data_home_dir = /mnt/ebs/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
set-variable = innodb_buffer_pool_size=25G
set-variable = innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=1
max_heap_table_size=256M
tmp_table_size=256M

#replication
server-id=17

slave-skip_errors = 1062

#max-binlog_size = 1000M
#log-bin=/mnt/ebs/log/mysql/mysql-bin.log
#binlog-ignore-db=mysql
#binlog-ignore-db=test

#information for becoming slave.
master-host =
master-user = replication
master-password = ***
master-port = 3306

user=mysql

Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).

old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Thanks a lot for the help!

2

What does the figure “cached” say (furthest to the right on the free output)?
That is essentially free memory, because that is the OS file cache size. The OS uses basically all surplus memory as file cache if needed to speed up reads/writes against disk.
So a normal system will usually have a very small figure “free” memory since the rest of it is used by the OS file cache and that is the “cached” figure.

Otherwise if you want to see the biggest memory hogs on your system, you run “top” and type SHIFT-M and it will sort the process with the most memory consumption at the top.

And BTW the buffer will basically not give back memory already allocated since that would make no sense since the buffer is there to cache data so that you don’t have to read data from disk.
So it will only remove a page if there is a need for the space due to a client requesting data that is currently on disk or a client is writing data to the database and we need to store this new data.
And at that point it will remove the page(s) that hasn’t been accessed for the longest time from the InnoDB buffer.

And that thinking about buffering technique is basically the same for the OS cache.

BTW 2 if you are running MyISAM tables on the same server then the data tables themselves will be placed in the OS cache due to that MyISAM does not have it’s own cache buffer.

3

what is max_connections value?
each mysql connection takes certain amount of memory depending on various buffer/thread values, the more connection you have the more memory will be used.

4

It is set to 100