MySQL Bugs: #16786: Possible memory leak.

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:	None
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

Description:
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. 
innodb_buffer_pool_size=1024M
max_connections=1000
max_user_connections=780
key_buffer_size=64M
read_buffer_size=125K
sort_buffer_size=50K

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.

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

Hi,

it is statical realease from mysql download page.

Dimi

Hi,

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

Dimi

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 ...

Memory usage rdb88 wich mysql 4/5

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

You had forgotten to add:

innodb_buffer_pool_size=1024M

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?

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.

Hil,

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

Regards,

Dimi

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?

Hi,

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.

Dimi

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.

Hi,

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

Dimi

> 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?

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.

Hi,

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

Dimi

Anders:

So, are you or Dimitrij able to provide a query log or describe the exact way to repeat this problem?

Hi Valeriy,

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

Dimi

Please, do, and reopen this bug report.

See rdb88.log

Dimi

Hi Dimitrij,

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

http://bugs.mysql.com/bug.php?id=18300

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

Regards,

Jeremy

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?

Hi,

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.

Dimi

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.

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.

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.

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 :)

Please see posting from Anders.

Dimi

still exists in mysql-5.0.21 too.

Dimi

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.

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?

Dimi

Sidenote to Dimi's last entry:
the dump job is basically something like this:

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

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). 

Anders

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.

Dimi

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.

Please, try to repeat with a newer version, 5.0.24, just released. Many bugs are fixed in it, so everything possible...

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.

See answer from Anders. If you needs more information, please ask for it.

Dimi

Please, try to repeat with a newer version, 5.0.26. Many optimizer problems are
fixed in it, so, once again, everything possible...

Hi,

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

Dimi

What exact ibinary of 5.0.26 did you use? If not "static", please, send the results of

getconf GNU_LIBPTHREAD_VERSION

I want to check if NPTL has some relation to this report.

Hi,

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

Dimi

Please, try to repeat with a newer version, 5.0.37, and inform about the results.

Please, try to repeat with a newer version, 5.0.37, and inform about the results.

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".