Mysql using lots of Virtual Memory

Posted on 2006-04-12
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
    LVL 7

    Expert Comment

    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.
    LVL 3

    Accepted Solution

    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.
    LVL 6

    Expert Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A lot of new and distinct gadgets are making their appearance every other day. The latest gadget that has wooed the attention of all gadget lovers and non gadget lovers alike is the Smartwatch. This tiny gadget is capable of offering live access to …
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now