Bug #16786 Possible memory leak.
Submitted: 25 Jan 2006 16:33 Modified: 16 Apr 2007 11:44
Reporter: Dimitrij HIlt Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.26, 5.0.24a, 5.0.18-standard-log/official tarball/static OS:Linux (Linux/Debian ia32/2.4.29)
Assigned to: CPU Architecture:Any

[25 Jan 2006 16:33] Dimitrij HIlt
After update from MySQL 4.0.25 to 5.0.18 we get lot of problems with system ressources. Database mean:

Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

But our Server has 2 GB RAM and only InnoDB tables. 

So is MAX possible use of RAM ( Key_buffer_size + (read_buffer_size +sort_buffer_size)*max_connections ) 688MB in our case.

Old MySQL 4.0.25 runs with same conditions without any problems.

How to repeat:
Setup big databese with lot of clients on 4.0 and update to 5.0. Keep configuration parameter and see you Load/RAM monitor.
[25 Jan 2006 21:33] Hartmut Holzgraefe
not a bug, see http://dev.mysql.com/doc/refman/5.0/en/source-notes-linux.html

you want to check the download pages for the 'static' linux version
[25 Jan 2006 22:48] Dimitrij HIlt

it is statical realease from mysql download page.

[26 Jan 2006 11:11] Dimitrij HIlt

in privat file ( grafic ) you can see mysql memory usage with mysql 4.0 ( up to wee 03 ) and than with mysql 5.0.

[26 Jan 2006 11:20] Anders Henke
I second this ... but I'm sitting right across from Dimi's desk :-)

Dimi, could you please attach the graph for rdb88?
The memory leak is more obvious on that image, as rdb88 is 
configured not to to max out its RAM that much as rdb89 is ...

Public display of that graph is fine for me, those graphs aren't that sensitive ...
[26 Jan 2006 11:23] Dimitrij HIlt
Memory usage rdb88 wich mysql 4/5

Attachment: rdb88_memory.gif (image/gif, text), 14.21 KiB.

[28 Jan 2006 10:51] Valeriy Kravchuk
You had forgotten to add:


to you calculations, so the MAX possible use of RAM is more than 1700MB in your case...

Please, describe your upgrade procedure. Did you perform mysqldump and restore of data? SHOW CREATE TABLE results for a typical table used may also give us some hints.

Do you have any other explicitely defined parameters in your my.cnf for 5.0.18?
[28 Jan 2006 22:48] Anders Henke
Ok, but that's still less than the 2 GB or 4 GB of RAM those servers do have
and still doesn't explain why 4.0 did behave in such a different way :-)

Due to http://bugs.mysql.com/bug.php?id=16298, the upgrade procedure has
been to create a dump via mysqldump and load this into a MySQL server with
freshly created Innodb-space. This procedure revealed a bug filed under http://bugs.mysql.com/bug.php?id=16668 :-)

We've also overwritten the "mysql" database with a dump, so there shouldn't
remain any 4.0-created files on our 5.0-servers.

I'll upload the full table design of the actively used database 
of rdb88 as well as all parsed settings (mysqld --verbose --help) in a private file.

I just saw that Dimitrij filed the wrong version - we're using 5.0.18
from the "Linux (x86, glibc-2.2, static (Standard only), gcc)" tarball download,
not the RPM package. However, that's still an "official" binary.
[29 Jan 2006 13:12] Dimitrij HIlt

in report_16786.txt ( new privat file ) you will see settings from mysqld and table definitions from rdb88.


[8 Feb 2006 15:57] Valeriy Kravchuk
Thank you for the additional information. Can you, please, send a typical queries for that tables? Are there [NOT] IN clauses with a lot of values in them?
[9 Feb 2006 14:34] Dimitrij HIlt

we does not have any query logging, but all queries are mostly simple. Select by Key ( mostly ) primary. We does not have a lot of queries with NOT or IN. And we have queries with UNION SELECT and LEFT JOIN/RIGHT JOIN in one query.

If you need queries, so a will enable query logging and upload this logfile as privat attachment.

[10 Feb 2006 12:54] Valeriy Kravchuk
Yes, real queries will be useful to check for already known memory-leak related issues. Please, upload the (compressed) query log or, at least, SHOW PROCESSLIST results when you have this problem manifested itself clearly.
[10 Feb 2006 22:55] Dimitrij HIlt

i'v attach query log from rdb88. we have inserts too, but i can't put this information in a bugreport.

[19 Feb 2006 13:53] Valeriy Kravchuk
> i'v attach query log from rdb88. we have inserts too, but i can't put this
> information in a bugreport.

Why? Is it larger than 200K or it contains sensitive data?
[19 Feb 2006 15:03] Anders Henke
We're running quite a lot of mysql replication clients for many different uses,
so Dimitrij's first thought might attend to "his" setups (e.g. radius servers,
who grant dialin acess according to mysql databases or warehouse-type 
systems with quite excessive SQL statements, who rely on temporary tables and
"INSERT INTO ..SELECT"-statements).

For analysis of this issue, rdb88 is much easier: there are no local 
insert/update/delete statemens on rdb88 (who is also switched to read_only=on).
It does contain usernames/passwords, but those aren't used in SELECT statements,
so it's easy to share the query log.

The problem does seem to show only on hosts with a high frequency of selects; 
the replication master is running at about 83 queries/second (mainly insert/update/delete)
for 24 days without a largely noticable memory leak (it "lost" about 50M over three weeks),
but rdb88 as a replication client is now running with an uptime of about two days,
runs at 100 queries/second (only selects) and "lost" about 250M of memory till today.

Maybe this issue is also an InnoDB issue; we do require transactions on
our updates, so we're using InnoDB databases only.
[27 Feb 2006 8:51] Dimitrij HIlt

our log contains sensitive data, so i can't post it.

[3 Mar 2006 12:32] Valeriy Kravchuk

So, are you or Dimitrij able to provide a query log or describe the exact way to repeat this problem?
[3 Mar 2006 13:02] Dimitrij HIlt
Hi Valeriy,

i will remove all sensitive data from query log from rdb88 and upload a file with sample queries.

[3 Mar 2006 13:50] Valeriy Kravchuk
Please, do, and reopen this bug report.
[3 Mar 2006 14:10] Dimitrij HIlt
See rdb88.log

[17 Mar 2006 18:46] Jeremy Cole
Hi Dimitrij,

I'm tracking down the same issue, it seems.  Here's another bug:


Please feel free to contact me at jeremy@jcole.us


[23 Mar 2006 10:44] Valeriy Kravchuk
In your last log uploaded I see only one type of SELECTs (with mostly INSERTs and DELETEs):

select UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(then) from somewhere where id=1

Are this selects leads to memory leak, after a long run of many queries per second?

Have you tried to work with a newer version, 5.0.19? Have you ever seen similar problem on other Linux, not Debian? With our static binaries?
[23 Mar 2006 11:01] Dimitrij HIlt

we have lot of another selects to:
select pw_name, pw_passwd, pw_uid, pw_gid, p
w_gecos, pw_dir,pw_shell from passwd where pw_uid='INTEGER' and hostname='STRING
' and primary_user=1

This query is a mostly queried.

A can't see which query leads to memory leak. Sorry.

I'v tested 5.0.19 and this version leaks too.

We does't have any another linux distribution, so i can test it only on Debian.

We use allways your static binaries.

[23 Mar 2006 11:02] Anders Henke
We're running multiple replication clusters, where one of them does indeed run such a time difference query, but others don't do so, while all clusters show the memory leak.

Please don't mistake "Debian" as "we're running some debian-supplied binaries",
Debian only refers to the base system (glibc, sshd, ...). We're currently running 
MySQL 5.0.19, always your static binaries - and the bug is still present.
[5 Apr 2006 10:19] Valeriy Kravchuk
Thank you for the additional information.

Do you use prepared statements and/or connection pool in your applications? I am checking similarities with bug #18300.
[5 Apr 2006 12:00] Anders Henke
Hmmmm. Some applications are using pooled connections and some other applications might also use prepared statements. The majority of connections or queries don't use connection pooling or prepared statements.
[15 Apr 2006 16:22] Anders Henke
I just verified on rdb85 that the same issue is still present in 5.0.20 
(Release from mysql-standard-5.0.20-linux-i686-glibc23.tar.gz).
Quite interesting is that the issue doesn't occur on the master.

@Dimitrij: please add some dummy comment to change the status from "need feedback" to "open" in order to draw back some attention to this issue :)
[15 Apr 2006 16:34] Dimitrij HIlt
Please see posting from Anders.

[7 May 2006 6:27] Dimitrij HIlt
still exists in mysql-5.0.21 too.

[8 Jun 2006 14:44] Valeriy Kravchuk
I do not know, how to repeat this behaviour. So, I'll leave this report Open, at least until the exact reason for bug #18300 will be found.
[4 Jul 2006 8:50] Dimitrij HIlt
New one Issue. During all databases on another DBMS (5.0.19-standard-log with 1620 databese) are dumped, mysqld incrase memoryusage and takes all RAM in system. See attached image.

Very strange. Can i see why mysqld needs a lot of RAM?

[4 Jul 2006 9:20] Anders Henke
Sidenote to Dimi's last entry:
the dump job is basically something like this:

for db in `mysql -NBe 'show databases'`
    mysqldump \
    --add-drop-table --add-locks --all --extended-insert \
    --quick $db | gzip >  $db.dump.gz

I think it's noteworthy that this doesn't strike on regular MySQL-5 servers and only shows up that significant on a certain set of mysql servers, who work as backend for an "advanced" shop application. Such a typical database contains 160 InnoDB and 8 MyISAM tables, so maybe this memory leak is related to SELECT on InnoDB-tables. "Usual" MySQL servers used by customers make much less use of InnoDB.

This would als explain why this issue mainly rises on our replication slaves (serving InnoDB tables) and those shop database servers (who heavily do rely on InnoDB, too). 

[4 Jul 2006 12:58] Dimitrij HIlt
New Information. In our shop systems we got this problem after all tables and columns ( *text, char*, varchar* ) was updatet from latin1 to utf8. I hope it will  help find out the problem.

[5 Jul 2006 11:33] Anders Henke
Yesterday, I've converted all tables and columns on one database server from UTF8 to latin1 (collation latin1_german2_ci), restarted the SQL server (as most of those tables were within the MYSQL database and I don't know how mysqld handles such a change) - but the memory usage still did increase.

So, after all, the UTF8-idea is a dead end street.
[3 Aug 2006 14:42] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.24, just released. Many bugs are fixed in it, so everything possible...
[16 Aug 2006 1:39] Anders Henke
Unluckily, Dimitrij is on vacation right now ...

I'd like to add a rrdgraph which basically would show that the problem
has changed a little bit, but as I'm neither a developer nor the original
reporter, I have to state it in a few words.

The problem hasn't been solved. After restart, it takes a few days for
memory usage to become more or less stable, then after about two days, 
the memory usage increases in quite a steady way.
[21 Aug 2006 7:45] Dimitrij HIlt
See answer from Anders. If you needs more information, please ask for it.

[22 Oct 2006 8:23] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.26. Many optimizer problems are
fixed in it, so, once again, everything possible...
[23 Oct 2006 9:05] Dimitrij HIlt

i'v tested newest mysql last week. Th problem still exists...

[1 Nov 2006 11:13] Valeriy Kravchuk
What exact ibinary of 5.0.26 did you use? If not "static", please, send the results of


I want to check if NPTL has some relation to this report.
[2 Nov 2006 6:34] Dimitrij HIlt

it is mysql-standard-5.0.26-linux-i686 and staticaly compiled by mysql.com.

[16 Mar 2007 11:44] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[16 Mar 2007 12:08] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.37, and inform about the results.
[16 Apr 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".