Limiting MySQL RAM Usage

[FLaRe85]

I highly doubt it, but is anybody on this forum well-versed in the art of MySQL server optimization? I know how to make MySQL scream, but I think I may have become a bit carried away with making it too performance-centric. I think I might need to scale back a bit in favor of saving RAM. I'm finding that the MySQL service is starting to eek past 800 MB of RAM usage on a regular basis. I've found it consuming as much as an entire gig once. That's not good considering the server only has 4 GB of RAM and is also running VMWare server with a virtual-dedicated SpamAssassin server set @ 512 MB allocation. This server also does email (running around 150 MB usage), Web (250 MB+ depending on how many PHP processes are running in the FCGI pool), primary DNS (90 MB), Antivirus (70 MB), etc.. Simply put, I'm running out of RAM and would like to stave off adding more if possible.

I've scaled the default engine back to MyISAM from InnoDB and converted as many tables as I can back to MyISAM. I dropped the QCache down to 128 MB as well. Nothing seems to help. MySQL RAM usage just keeps creeping higher and higher. I'm running a recent version, although I'm not positive if it's the latest (will check after posting this) so I'm not sure if there could be a memory leak.

I'm thinking I could probably afford to bump several buffers back a bit, but I haven't had a chance to do any profiling. I guess I'm just wondering if anybody has some suggestions right off the bat to limit usage for the time being until I have time to really sit and profile hits and queries against the server.

Oh right...this is MySQL version 5.0. x64 too.

Edit: Just confirmed...5.0.51a - so it is the latest version.

__________________

.: Flaretech.Net :: Flaretech.Biz Web Hosting :: H3 Stats :: My Blog :.



.: Mac Pro :: Dual Quad-Core Intel Xeon 5400s :: 6 GB 800MHz DDR2 ECC FB-DIMMs :: NVIDIA GeForce 8800 GT 512 MB GDDR3 :.
.: Macbook Pro 17" :: 2.33 GHz Intel Core 2 Duo :: 2 GB 667 MHz DDR2 :: ATI Radeon X1600 :.

[Phil]

im not the most knowledgable person on this, but the latest versions of MySQL should tend too eat up more ram. And like you said, you have all the spyware/ adware fighters thrown into the mix too...

And 4gigs?? buffered memory cant cost that much more (ive found 4gb server ram for $430 on ebay) but oh well..



Edit: What OS are you running? I just did some research and it seems that if youre running Vista, MySQL 5.0 and later doesnt run too well on the x64 bit version....

__________________

Vision is the art of seeing what is invisible to others

[KillerShots]

Is this cached or reserved memory? If it's just cached, it will claim to be eating gobs and gobs of memory, but will allow any other process/service to use that memory as well. Cached simply means it remembers a previous transaction, and if it loses it it's no big deal - it'll simply repeat the transaction. If it's reserved, no other process/service can use that memory for any other purpose.

Many people fail to realize this difference and buy much more RAM than they need, then scratch their head when they realize that all that RAM got sucked up as well.

__________________
Primary
CPU: Athlon 64 X2 4400+ Mobo: Biostar N4SLI-A9 RAM: 2G Crucial (DDR400) Video: eVGA GeForce 7900 GTX (512M) Audio: HDA X-Mystique HD(s): Maxtor 300G SATA2, Samsung 400G SATA2 OS(s): WinXP x64 Pro, Vista x32 Ultimate, Gentoo x64 Monitor(s): Primary - 19" Flat Panel (1280x1024) Secondary - 19" Flat Panel (1280x1024) Tertiary - Zenith 42" Plasma TV (1024x768 res)

Many other machines... sig too short

[FLaRe85]

Originally Posted by KillerShots

Is this cached or reserved memory? If it's just cached, it will claim to be eating gobs and gobs of memory, but will allow any other process/service to use that memory as well. Cached simply means it remembers a previous transaction, and if it loses it it's no big deal - it'll simply repeat the transaction. If it's reserved, no other process/service can use that memory for any other purpose.

Many people fail to realize this difference and buy much more RAM than they need, then scratch their head when they realize that all that RAM got sucked up as well.

I'm fairly certain qcache allocates the entire amount as a reserve, only because, when I limited it to 128 MB, I saw it immediately jump to a level above that once the service was restarted. No tables should have been accessed immediately except maybe those in 'mysql.' The documentation suggests this as well, but isn't conclusive.

I imagine the other data is simply being cached in memory and likely going to fluctuate depending upon variables such as open_tables. I haven't observed any paging at this point, but it's just absurd to log into your server and find it's dipped below 400 MB of free physical RAM. I would think it would start pruning at that point. 1 GB+ is a lot of RAM usage for MySQL imo. I mean, it's a busy MySQL server, but it's not THAT busy.

One interesting thing to note...apparently I'm an idiot and didn't realize that my mail server was scanning the emails for virii and then passing it off to the spamd inside of VMWare Server, which was also launching clamd and eating up the VM's memory. spamd kept running out of memory and killing itself and I couldn't figure out why. I realized ClamAV was installed, but I had no idea it was scanning the crap going through SpamAssassin. It was eating 30% of the RAM with each hit.

Phil: No offense, but you seem to have missed the boat completely, so to speak. This is a dedicated server. A quad-core Xeon Dell PowerEdge box with 4 GB of unbuffered high-performance RAM. I have an enterprise-level A/V filter on it to benefit my customers. There is no spyware filtering because that's not necessary.

__________________

.: Flaretech.Net :: Flaretech.Biz Web Hosting :: H3 Stats :: My Blog :.



.: Mac Pro :: Dual Quad-Core Intel Xeon 5400s :: 6 GB 800MHz DDR2 ECC FB-DIMMs :: NVIDIA GeForce 8800 GT 512 MB GDDR3 :.
.: Macbook Pro 17" :: 2.33 GHz Intel Core 2 Duo :: 2 GB 667 MHz DDR2 :: ATI Radeon X1600 :.

[Phil]

Now I remember, you said you ran a company hosting websites (300 if i remember) in the show your ****ing pics thread. I thought you were running this from a home computer (it was early man XD) Send me a virus if i dont remember again

__________________

Vision is the art of seeing what is invisible to others