Mysql using lots of Virtual Memory

Posted on 2006-04-12
Medium Priority
Last Modified: 2008-04-02
Our MySQL has been running slow as of late.  We're running the default version that comes with OS X Server 10.3.9.  We have about 30 clients submitting a query about every second (total).

When I look at the Activity Monitor I see that mysqld is using 8 Megs of Real Memory and 500 to 600 Megs of Vritual memory.  Does this mean that most of MySQL's memory is on the hard disk and performing slowly?  Should it be listed in Real memory?  I see that the activity monitor says I only have 14megs of system memory free.

The server has 1 Gb of memory, and is running as a busy file server, domain manager and MySQL server.  Would adding memory make a difference to Mysql?

Thanks in advance,

Question by:Kayjon

Expert Comment

ID: 16441926
A program accesses virtual memory when there is not enough "Real" memory.  I would add another GB.  i think you will see a great improvement.

Accepted Solution

hfern earned 750 total points
ID: 16454874
Database tuning is a complex matter. Adding memory may help, but there could also be other reasons why MySql uses so much memory. To start off, let me ask some questions:
1. The 30 queries by 30 users, are they all reads (ie select queries) or are there also writes (updates or inserts)? MySql does table locking for writes. This means that the write will only commence once the table can be locked and that during the write, all reads and other writes are queued until the lock is released. This can cause delays and more memory consumption. Adding memory will not help if this is the cause of your problem.
2. What type of tables are you using? Are thay all disk based tables or do you also use memory mapped tables? Compressed ISAM tables can be expanded in memory. Obviously memory mapped tables will use memory. If the system does not have enough real memory then indeed it will use vritual memory and you will see memory swapping occurring.
3. Are your queries using a lot of Joins? They are also mostly performed in memory.

While researching this, I also found that memory usage of mySql is not always reported correctly. See the folloing bit from the MySql manual:
"`ps' and other system status programs may report that `mysqld' uses a lot of memory. This may be caused by thread stacks on different memory addresses.  For example, the Solaris version of `ps' counts the unused memory between stacks as used memory."

You can read more on MySql memory usage by opening a terminal window and type "info mysqld". Memory usage for the mySql server is described in chapter 7.5: Optimizing the MySql Server.

Expert Comment

by:James Looney
ID: 16493642
I have 2Gb of RAM in my XServe, when I see the Free RAM showing up as real low, I run the periodic scripts - and that always helps - as it frees up memory being used to keep big log files open. So try that.

Open Terminal and type this in:

periodic daily weekly monthly

then hit Return.

It'll take a little while to run, but it should help.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Even though Mac OS X has been around for many years now, certain well-liked and eminently useful software apps never made the jump from Mac OS 9 to Mac OS X. Ever used Word Perfect for Mac? Version 3.5 was the last version, built for Mac OS 9. Who c…
A professional opinion on which Apple product to buy, and a tidbit about the WWDC.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question