Dan, Heikki,
----- Original Message -----
From: "Heikki Tuuri" <Heikki.Tuuri@stripped>
To: <mysql@stripped>
Sent: Saturday, February 07, 2004 11:39 AM
Subject: Re: Memory Leak using InnoDB ?
> Geoffrey, Dan,
>
> ----- Original Message -----
> From: "Dan Nelson" <dnelson@stripped>
> Newsgroups: mailing.database.myodbc
> Sent: Saturday, February 07, 2004 5:27 AM
> Subject: Re: Memory Leak using InnoDB ?
>
>
> > In the last episode (Feb 07), Geoffrey said:
> > > I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM.
> > >
> > > One application has to access the database (1 connection to the DB is
> > > open on startup and left open). However this application performs a
> > > lot of queries on the DB.
> > >
> > > Thanks to "top", I can see that the "used memory" is constantly
> > > increasing and never freed (up to 1 GB) when performing a lot of
> > > queries. In fact, I can see the total memory usage increasing but the
> > > mysqld process memory usage remains the same.
> > >
> > > When the DB is not accessed, the memory usage is stable. Stopping
MySQL
> > > server doesn't free the abnormaly allocated memory.
> >
> > Ideally, you should have very little "free" memory according to top
> > (most systems will see under 20MB free). Free memory is wasted memory.
> > Unix uses memory not allocated to processes for a disk cache. To
> > determine whether you are truly low on memory, run iostat and watch the
> > swap columns. Constant swap activity means you're low on memory.
>
> Dan is right. A memory leak in the mysqld process should show up in the
> 'top' line for that process.
>
> By the way, monitoring if InnoDB has memory leaks is very easy. Just look
> with SHOW INNODB STATUS at the 'total allocated memory'. If that keeps
> climbing without bounds, that is an indication of a memory leak.
>
Thanks for your help.
For testing, I tried the following my.cnf :
server-id=1
socket=/tmp/mysql.sock
innodb_data_file_path = ibdata1:750M:autoextend
set-variable = innodb_buffer_pool_size=50M
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_file_size=300M
set-variable = innodb_log_buffer_size=2M
innodb_flush_log_at_trx_commit=0
skip-locking
set-variable = max_connections=5
set-variable = read_buffer_size=1M
set-variable = sort_buffer=1M
set-variable = key_buffer=10M
Here is the result of "top" after 20 hours :
8:26pm up 18:35, 1 user, load average: 0.42, 0.26, 0.20
46 processes: 44 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 3.7% user, 0.5% system, 0.0% nice, 95.6% idle
Mem: 1031048K av, 1021460K used, 9588K free, 0K shrd, 55288K
buff
Swap: 2044072K av, 0K used, 2044072K free 845544K
cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
960 mysql 15 0 67872 66M 2656 S 0.1 6.5 6:29 mysqld
Here is the result of "top" after a reboot :
8:54pm up 9 min, 1 user, load average: 0.16, 0.17, 0.10
46 processes: 44 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 4.5% user, 0.3% system, 0.0% nice, 95.0% idle
Mem: 1031048K av, 136512K used, 894536K free, 0K shrd, 13276K
buff
Swap: 2044072K av, 0K used, 2044072K free 61620K
cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
774 mysql 15 0 36276 35M 2496 S 0.7 3.5 0:03 mysqld
Here is the SHOW INNODB STATUS after 20 hours :
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 75115912; in additional pool allocated 1611008
Buffer pool size 3200
Free buffers 1
Database pages 3091
Modified db pages 160
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 7212, created 2274, written 801221
0.00 reads/s, 0.02 creates/s, 12.45 writes/s
Buffer pool hit rate 1000 / 1000
As far as I understand, the "Total Mem" increasing constantly when MySQL is
heavily accessed is not really memory allocated by MySQL but allocated by
Linux to take advantage of the unused memory ?
So I should not pay attention to it (as long as used swap is 0).
Correct ?
Regards.
