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:
- Where is the other 12GB going?
- How does innodb buffer flush/clear up its contents? What is the internal algorithm?
- 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:
query_cache_size = 10M
thread_cache_size = 40
table_cache = 300
key_buffer_size = 132M
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
slave-skip_errors = 1062
#max-binlog_size = 1000M
#information for becoming slave.
master-user = replication
master-password = ***
master-port = 3306
Default to using old password format for compatibility with mysql 3.x
clients (those using the mysqlclient10 compatibility package).
Thanks a lot for the help!