GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

How is MySQL using RAM

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How is MySQL using RAM

    My MySQL memory usage isn't high enough ). I've a 10GB database on a 8GB-RAM DB Server. MySQL is only consuming 2GB of RAM.

    How do I figure out what buffers MySQL is using the 2GB of RAM for?

    That'll give me some insight as to what buffers are being maxed out and need more memory.

    Is there any other solution to this problem? Shouldn't MySQL cache more in RAM so it has to hit the DB less often?

  • #2
    To answer your questions I'd like to ask mine

    1) What storage engine do you use for your tables: innodb/myisam?

    2) What CPU do you use on your server: 32-bit/64 bit?

    3) What OS do you use: name, version, 32/64 bit?

    Comment


    • #3
      1. Currently Myisam but I'm working on converting to innodb
      2. OS & CPU are 64 bit
      3. OS is centOS..not sure where the OS version info is ).

      Is there some tool that dumps the size of/info about various mysql buffers/caches?

      Comment


      • #4
        untenable wrote on Tue, 25 September 2007 16:01

        1. Currently Myisam but I'm working on converting to innodb
        2. OS & CPU are 64 bit
        3. OS is centOS..not sure where the OS version info is ).

        Is there some tool that dumps the size of/info about various mysql buffers/caches?


        1. Great - innodb could be much better here because it could cache data pages along with indexes in memory.
        2. Ok. 64 bit would allow you to use more than 4gb in mysql.
        3. uname -a, please

        Comment


        • #5
          Ah thanks. Unfortunately I spent too much time in Windows.
          Here's the full version info:

          Linux 2.6.9-55.0.6.ELsmp #1 SMP Tue Sep 4 21:36:00 EDT 2007 i686 i686 i386 GNU/Linux

          So you're saying there is no utility that tells you how MySQL is using memory?

          Comment


          • #6
            untenable wrote on Tue, 25 September 2007 16:10


            Linux 2.6.9-55.0.6.ELsmp #1 SMP Tue Sep 4 21:36:00 EDT 2007 i686 i686 i386 GNU/Linux



            Unfortunately this means your mysql can't use more than 4Gb ram - you have 32bit version of Linux kernel running on your server (

            Comment


            • #7
              Oh. Urgh.

              Well MySQL is only using 2GB of that 4GB address space. Why )?

              Comment


              • #8
                Usually you reserve half of the process addressspace for OS usage.
                That's why you can only use 2GB.

                Comment


                • #9
                  There are too many things which could be tuned to have mysql using more memory. Major ones are:

                  * key_buffer_size
                  Used by MyISAM tables to cache Index only, not data
                  30% of memory for MyISAM only system
                  4GB limit per key buffer, can have multiple of these
                  MyISAM tables used for temporary tables anyway

                  * innodb_buffer_pool_size
                  Used by Innodb tables
                  70% of memory for Innodb only system
                  Innodb performance is very critical to this setting

                  * query_cache_size
                  Set if using query cache

                  * read_buffer_size, read_rnd_buffer_size
                  sequential read buffers used by MyISAM and some others
                  Allocated when needed.
                  1M/4M typically good to start

                  * sort_buffer_size
                  buffer used for sorting. Increase if a lot of large sorts are done

                  * tmp_table_size
                  Maximum size of in-memory table allowed (used by complex
                  queries)
                  May need more than one temporary table per query
                  max_heap_table_size limit is also used


                  So, if you use myisam on your server, I'd like you to increase your key_buffer_size which could improve your work with indexes. In case of innodb - innodb_buffer_pool_size is what you'd like to increase. But anyways, when you'll increase any values you have too keep in mind, that you have 4Gb of RAM only (if mysql would try to allocate more, it could crash).

                  Comment


                  • #10
                    Yeah, I got those covered but it's still not consuming more than 2GB RAM.

                    I'd love to have you spend a couple hours looking at it and pay you for that time instead of asking lots of questions on the forums.

                    But from what I hear you guys charge $200/hour?

                    I could do like $400 for the job. It might take you less than two ours but could take up to 4 or 6...

                    Comment


                    • #11
                      sterin wrote on Tue, 25 September 2007 22:35

                      Usually you reserve half of the process addressspace for OS usage.
                      That's why you can only use 2GB.



                      From my post two steps up.

                      If you want to use your servers potential you should run a 64bit OS and 64bit MySQL.

                      Comment


                      • #12
                        untenable wrote on Tue, 25 September 2007 16:43

                        Yeah, I got those covered but it's still not consuming more than 2GB RAM.

                        I'd love to have you spend a couple hours looking at it and pay you for that time instead of asking lots of questions on the forums.

                        But from what I hear you guys charge $200/hour?

                        I could do like $400 for the job. It might take you less than two ours but could take up to 4 or 6...


                        Of course we could try to help. Please, fill out our consulting request form and we'll contact you ASAP.

                        Comment

                        Working...
                        X