MySQL Just Keeps Growing (Memory Use)

[james]

I have a MySQL instance running on an Athlon 4000+ with 1 GB of RAM. httpd is running on this server too. They have happily coexisted for quite some time.

My problem occurred recently when I deployed a new application that makes use of InnoDB and transactions. (That’s what I think is the problem; it’s extremely hard to trace).

My problem is basically this: MySQL runs quite happily for some time (it varies… 15 minutes or so all the way up to six hours), then all of the sudden, its memory use triples and the system starts swapping out and the load peaks up to 70 (because httpd gets starved by kswapd). It increases its size within the span of about three seconds, so I can’t really inspect what is going on in mysql when it happens. By the time I see it happening, I can’t connect to mysql or use any of the tools. No errors are reported in the log.

I honestly don’t know what the problem is. I’ve tried every possible combination of my.cnf settings that I could come up with. At the moment, I’m running with no custom settings except innodb_flush_log_at_trx_commit=2.

If someone could point me in the right direction, I’d really appreciate it.

[AlexN]

Isn’t it something like this :
http://forum.mysqlperformanceblog.com/s/t/463/ ?

It looks like in Apache-MySQL combination it is sometimes
enough to start ONE slow query to bring down the whole system.

[james]

That seems to be almost exactly my problem. I must have glanced right past that thread when I posted this.

However, there is a slight difference. In my scenario, it’s the MySQL process that expands in size. Here’s an example I witnessed last night:

Everything is running great with MySQL having a 750MB VSZ and about 500 RSS or so.

Then suddenly (over the course of about 3 top refresh cycles), that jumps to be about 1.2GB VSZ and 800 RSS.

I can’t figure out what’s wrong. I tried setting all of the MySQL memory settings (including what I think is the key–the innodb buffer size) to extremely low numbers, but I was still seeing the same memory usage.

I know the configuration file is getting read in because if I intentionally put an error into it, MySQL fails to start, and the filename is passed as the --defaults-file (or whatever that parameter is) when I start mysqld.

I just don’t know how to track it down because it happens so fast. Nothing shows up in the slow query log or the MySQL error log.