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.