From: Todd Lyons Date: August 18 2010 2:58pm Subject: Re: Moving from one MySQL server to three MySQL servers? List-Archive: http://lists.mysql.com/mysql/222566 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Thu, Aug 5, 2010 at 8:35 AM, Nunzio Daveri wrot= e: > > The server in this case is a stand alone with nothing more then CentOS an= d MySQL > 5.1.44 on it. =A0The drives are sas 10K rpm drives. =A0The problem I see = is that > when you stress test the server (typically by running loads of reports - > selects, joins) the machine hits 98% cpu and leaves only 800mb of free RA= M out > of the 16 GB of which I told it to allocate 12GB for Innodb in my.cnf. = =A0Once the > server sucks up all the memory when we are stress testing it, it holds th= e 12 gb > as hostage and refuses to release it back into the pool, regardless of we= ather We had a similar system where the innodb data dictionary (poor-man's description is an in-memory map of all the tables it has opened up to that point) consumed all free memory and the kernel started killing processes to get some back. At the time, stock mysql did not have the ability to limit the size of the data dictionary memory usage (and I don't see it as an available option for 5.1.x currently), so I tested XtraDB and it prevented the memory usage from growing without bound. XtraDB also gave us lots of knobs to twiddle and extra insights into what innodb was doing. I've not experimented with current mysql 5.1.x to see how much of that extra control has made it into the mainline. > only 800mb of ram free??? =A0If I start to stress it again then it starts= to go > into swap. =A0Really weird, thus wanting to split the load onto 3 machine= s. When you restarted the stress test, did it access new tables or the same ones that the previous stress test did? What happens if you tell innodb to use less memory and do your tests? Have you found any way to make mysql not run out of memory? --=20 Regards...=A0 =A0 =A0 Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm.=A0 -- Marcus Aurealius