Announcement

Announcement Module
Collapse
No announcement yet.

What process(es) is(are) using up the memory?

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

  • What process(es) is(are) using up the memory?

    Hello,

    We are using MySQL 5.044 and have the following replication topology: 1 Master and 3 identical slaves.

    Master machine has 7GB of memory and Innodb buffer is set to 2 GB.

    Each slave has 35 GB of memory and innodb buffer is set to 25 GB.

    Recently we began loading a lot of data on a daily basis (~ 300,000 articles) and I noticed that throughout the day the memory on the machine which starts out with ~ 31GB after mysqld service starts ends up with just 16MB of memory when I run "free -m" command.

    The data load involves a lot if inserts, but also deletes and updates, so my initial assumption was that a lot of data goes into the innodb buffer pool (due to selects during insert/delete/update process?) and the buffer is not cleared up after data is commited to the database. However, doing more investigation seems to indicate that this is not the case (maybe only partially the case). Namely, I just looked at how much memory is used up now using "free -m" command, and it says 15GB. I then look at how much innodb buffer pool is used up using 'mysqlreport tool' and it shows that only 2.79GB are used.

    So my questions are:

    1. Where is the other 12GB going?
    2. How does innodb buffer flush/clear up its contents? What is the internal algorithm?
    3. Any other suggestions I can use to investigate the memory usage? [i.e. once i totally run out of memory again on the machine, what can I look at to get an indication of what the memory is being used for?]

    Below is the .cnf file for the machine in questions:

    datadir=/mnt/ebs/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    set-variable=long_query_time=1
    log-slow-queries=/var/log/mysql/log-slow-queries.log

    query_cache_size = 10M
    thread_cache_size = 40
    table_cache = 300
    key_buffer_size = 132M

    # InnoDB settings
    innodb_data_home_dir = /mnt/ebs/lib/mysql/
    innodb_data_file_path = ibdata1:100M:autoextend
    set-variable = innodb_buffer_pool_size=25G
    set-variable = innodb_additional_mem_pool_size=10M
    innodb_flush_log_at_trx_commit=1
    max_heap_table_size=256M
    tmp_table_size=256M

    #replication
    server-id=17

    slave-skip_errors = 1062

    #max-binlog_size = 1000M
    #log-bin=/mnt/ebs/log/mysql/mysql-bin.log
    #binlog-ignore-db=mysql
    #binlog-ignore-db=test

    #information for becoming slave.
    master-host =
    master-user = replication
    master-password = ***
    master-port = 3306

    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid


    Thanks a lot for the help!

  • #2
    What does the figure "cached" say (furthest to the right on the free output)?
    That is essentially free memory, because that is the OS file cache size. The OS uses basically all surplus memory as file cache if needed to speed up reads/writes against disk.
    So a normal system will usually have a very small figure "free" memory since the rest of it is used by the OS file cache and that is the "cached" figure.

    Otherwise if you want to see the biggest memory hogs on your system, you run "top" and type SHIFT-M and it will sort the process with the most memory consumption at the top.

    And BTW the buffer will basically not give back memory already allocated since that would make no sense since the buffer is there to cache data so that you don't have to read data from disk.
    So it will only remove a page if there is a need for the space due to a client requesting data that is currently on disk or a client is writing data to the database and we need to store this new data.
    And at that point it will remove the page(s) that hasn't been accessed for the longest time from the InnoDB buffer.

    And that thinking about buffering technique is basically the same for the OS cache.

    BTW 2 if you are running MyISAM tables on the same server then the data tables themselves will be placed in the OS cache due to that MyISAM does not have it's own cache buffer.

    Comment


    • #3
      what is max_connections value?
      each mysql connection takes certain amount of memory depending on various buffer/thread values, the more connection you have the more memory will be used.

      Comment


      • #4
        It is set to 100

        Comment


        • #5
          sql_er wrote on Tue, 02 November 2010 16:20

          It is set to 100

          And what did the "cached" figure look like that I mentioned in my post above?

          Comment


          • #6
            Hi Mr. Sterin, Thanks for making what is the "Cached" figure for very clear.. We are experiencing the same problem as sql_er and we actually have the same assumption.. our current system specifications are as follows:

            RAM: 4G
            CPU: Intel Xeon, 2 Quad Core
            OS: Fedora Core 7, 64bit
            MySQL: 5.0.24, 32bit
            Storage Engine: InnoDB

            query_cache_size = 33554432
            thread_cache_size = 8
            table_cache = 512
            key_buffer_size = 402653184
            sort_buffer_size = 2097144
            read_buffer = 2M
            write_buffer = 2M

            max_connections = 500

            # InnoDB settings
            innodb_buffer_pool_size = 16777216
            innodb_additional_mem_pool_size = 2097152
            innodb_flush_log_at_trx_commit = 1
            innodb_log_file_size = 5242880
            innodb_log_buffer_size = 8388608
            innodb_thread_concurrency = 8
            max_heap_table_size = 16776192
            tmp_table_size = 33554432

            You may find our current settings troublesome as we are just starting to optimize our settings.. I'm about to change the above settings into the following

            query_cache_size = 64M
            thread_cache_size = 16
            table_cache = 1024
            key_buffer_size = 512M
            sort_buffer_size = 2M
            read_buffer = 2M
            write_buffer = 2M

            max_connections = 200

            # InnoDB settings
            innodb_buffer_pool_size = 2G
            innodb_additional_mem_pool_size = 16M
            innodb_flush_log_at_trx_commit = 2
            innodb_log_file_size = 64M
            innodb_log_buffer_size = 10M
            innodb_thread_concurrency = 16
            max_heap_table_size = 256MB
            tmp_table_size = 256MB

            It would really be great if you could provide me with feedback/s for the settings I changed.

            I would like to ask as to what are the guidelines, if there is any, or things to consider in knowing the right value for mysql's max_connection.. ours is currently set to 500.. we are using innodb and have 876tables and that is 680.3MB in size (total size). we are averaging of only having 50-51 connections on a daily basis.. does that mean that we can safely reduce the max_connection to 200?

            You mentioned that each mysql connection takes certain amount of memory depending on various buffer/thread values.. can you provide me what are those?

            Hope to hear from you.

            Thank you so much.

            Comment


            • #7
              tpalermo6 wrote on Wed, 08 December 2010 08:55
              I'm about to change the above settings into the following
              ...
              key_buffer_size = 512M
              ...
              max_heap_table_size = 256MB
              tmp_table_size = 256MB

              It would really be great if you could provide me with feedback/s for the settings I changed.
              key_buffer_size:
              If you are only using InnoDB then you don't need this at all and should comment it out and let it be default 8MB IIRC.
              And anyway you should generally never set it as big as this when you have a large InnoDB buffer that allocates RAM also.
              Rule for this is about 25% of _available_ RAM, meaning that if you configure InnoDB to 2GB and OS takes 100MB and possibly some other process on the machine takes 400MB then this setting should be at most 1.5GB*0.25 = 375M.


              max_heap_table_size
              tmp_table_size
              Why are you allowing these to be as large as 256MB?
              If you had a lot of memory on the server it's understandable, but you seem to have only 4GB and you are using it for buffers etc.
              Does your application really have extensive use of large heap/temporary tables?

              Yes with that few connections you should without a problem be able to reduce your max_connections setting, since that settings max concurrent connections.

              Comment


              • #8
                Hi Mr. Sterin, Thank you so much for your immediate and detailed response. It would really be a big help and it's really appreciated.

                The following are the new settings of our my.cnf

                ------------------------------------------------------------
                # The MySQL server
                [mysqld]
                port = 3306
                socket = /tmp/mysql.sock
                skip-locking
                max_allowed_packet = 16M
                table_cache = 1024
                sort_buffer_size = 8M
                net_buffer_length = 8M
                read_buffer_size = 2M
                write_buffer_size = 2M
                join_buffer_size = 8M
                read_rnd_buffer_size = 16M
                thread_cache_size = 8
                query_cache_size = 64M
                query_cache_limit = 2M
                max_heap_table_size = 64MB
                tmp_table_size = 64MB
                max_connections = 200

                # key_buffer = 384M
                # myisam_sort_buffer_size = 64M
                # thread_concurrency=8

                innodb_buffer_pool_size = 2G
                innodb_additional_mem_pool_size = 16M
                innodb_log_file_size = 64M
                innodb_log_buffer_size = 10M
                innodb_flush_log_at_trx_commit = 2
                innodb_lock_wait_timeout = 120
                innodb_thread_concurrency = 16

                log-bin=mysql-bin
                binlog_cache_size = 1M

                [mysqldump]
                quick
                max_allowed_packet = 16M

                [isamchk]
                key_buffer = 128M
                sort_buffer_size = 128M
                read_buffer = 2M
                write_buffer = 2M

                [myisamchk]
                key_buffer = 128M
                sort_buffer_size = 128M
                read_buffer = 2M
                write_buffer = 2M

                [mysqlhotcopy]
                interactive-timeout

                [mysqld]
                skip-character-set-client-handshake
                collation_server=utf8_unicode_ci
                character_set_server=utf8
                ------------------------------------------------------------

                Hope you could provide me with your comments again.

                The settings for [isamchk] and [myisamchk] are already on our old my.cnf, and I'm wondering what those are for? I'm not really sure as to their use so I just left them there. Question: Is it safe to remove them since we are set to use only InnoDB?

                Lastly, I'm kinda confused on how to compute the total memory allocated. From my settings above, is my computation below correct?

                max_allowed_packet = 16M
                sort_buffer_size = 8M
                net_buffer_length = 8M
                read_buffer_size = 2M
                write_buffer_size = 2M
                join_buffer_size = 8M
                read_rnd_buffer_size = 16M
                query_cache_size = 64M
                query_cache_limit = 2M
                max_heap_table_size = 64MB
                tmp_table_size = 64MB
                innodb_buffer_pool_size = 2G
                innodb_additional_mem_pool_size = 16M
                innodb_log_file_size = 64M
                innodb_log_buffer_size = 10M
                binlog_cache_size = 1M
                max_allowed_packet = 16M

                [isamchk]
                key_buffer = 128M
                sort_buffer_size = 128M
                read_buffer = 2M
                write_buffer = 2M

                [myisamchk]
                key_buffer = 128M
                sort_buffer_size = 128M
                read_buffer = 2M
                write_buffer = 2M
                -----------------------------Total Allocated Memory = 2881MB

                Hope to hear from you again. Again, Thank you so much!!!

                Comment

                Working...
                X