Geoffrey,
----- Original Message -----
From: "Geoffrey" <g.dufour@stripped>
To: "Heikki Tuuri" <Heikki.Tuuri@stripped>; <mysql@stripped>
Sent: Saturday, February 07, 2004 10:11 PM
Subject: Re: Memory Leak using InnoDB ?
> 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 ?
correct!
> Regards.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
Order MySQL technical support from https://order.mysql.com/
