Announcement

Announcement Module
Collapse
No announcement yet.

mysql memory consumption / crashes

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

  • mysql memory consumption / crashes

    Hello,
    I have quite an annoying problem: after about one week running mysql-server (5.0.44) is terminated because of excessive memory usage (around 2.8GB, 32-bit system). I have allready reduced the memory settings several times, but the problem still persists.

    Mysql-tunig-primer calculates a max memory allocated of 447M and a configured limit of 1017M. top tells me the process uses 2239M at the moment, so time for a manual restart...

    Any ideas how to solve the issue??

    other applications involved
    - mainly InnoDB-Tables, 2 in memory tables
    - apache/php (running on same server)
    - mysqldump
    - replication to a standby-server

    CONFIG (memory-related parts)

    key_buffer = 8M
    max_allowed_packet = 32M
    table_cache = 1350
    sort_buffer_size = 2M
    net_buffer_length = 8K
    read_buffer_size = 256K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 1M
    max_connections = 150
    long_query_time = 4
    thread_cache_size = 20
    join_buffer_size = 2M
    max_heap_table_size = 16M
    tmp_table_size = 4M
    open_files_limit = 4000
    max_binlog_cache_size = 1073741824

    innodb_buffer_pool_size = 256M
    innodb_additional_mem_pool_size = 2M
    innodb_log_file_size = 50M
    innodb_log_buffer_size = 8M

    ERROR MESSAGE (Low Mem)

    090306 17:54:53 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1094604 bytes)
    090306 17:54:53 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

    ERROR MESSAGE (Crash)

    081221 19:10:15 InnoDB: Error: cannot allocate 1064960 bytes of
    InnoDB: memory with malloc! Total allocated memory
    InnoDB: by InnoDB 317687476 bytes. Operating system errno: 12
    InnoDB: Check if you should increase the swap file or
    InnoDB: ulimits of your operating system.
    InnoDB: On FreeBSD check you have compiled the OS with
    InnoDB: a big enough maximum process size.
    InnoDB: Note that in most 32-bit computers the process
    InnoDB: memory space is limited to 2 GB or 4 GB.
    InnoDB: We keep retrying the allocation for 60 seconds...
    081221 19:40:02 InnoDB: Error: cannot allocate 1064960 bytes of
    InnoDB: memory with malloc! Total allocated memory
    InnoDB: by InnoDB 317683032 bytes. Operating system errno: 12
    InnoDB: Check if you should increase the swap file or
    InnoDB: ulimits of your operating system.
    InnoDB: On FreeBSD check you have compiled the OS with
    InnoDB: a big enough maximum process size.
    InnoDB: Note that in most 32-bit computers the process
    InnoDB: memory space is limited to 2 GB or 4 GB.
    InnoDB: We keep retrying the allocation for 60 seconds...
    081221 19:41:02 InnoDB: We now intentionally generate a seg fault so that
    InnoDB: on Linux we get a stack trace.
    081221 19:41:02 - mysqld got signal 11;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    We will try our best to scrape up some info that will hopefully help diagnose
    the problem, but since we have already crashed, something is definitely wrong
    and this may fail.

    key_buffer_size=8388608
    read_buffer_size=258048
    max_used_connections=41
    max_connections=150
    threads_connected=4
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 353190 K
    bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.

  • #2
    Have you seen this post?

    http://www.mysqlperformanceblog.com/2006/05/17/mysql-server- memory-usage/


    Just a hunch, it might be your max_allowed_packet and your max_connections settings. 150 x 32MB = 4.6875 GB. As large packets get sent to each connection, it's possible they allocate more memory to accomodate the large packet and never release it.

    Try setting your max packet to something smaller.

    Also look at reducing your number of connections. If you don't need 150, reduce the number.

    For instance, on my busy forums (1M PHP hits daily), I have it limited to 48. I have 32 php-fastcgi processes, each which keeps a persistent connection to MySQL. I keep a few more around for things Debian maintenance scripts and running mytop or logging in myself.

    Comment


    • #3
      Thanks for your hint regarding max_allowed_packet, haven't considered this. I will check it out next week (don't want to change during the weekend).

      There were in fact changes to blobs during the last year when the crashes started. Well, the number of blobs - esp. with large size - highliy decreased, but sometimes changes have strange effects. Possible that the frequent use of larger blobs helped to free unused memory...

      Further lowering the number of connections would be quite hard. The crash reports usually showed 110-401 max connections (the setting was 400 before my first changes). The report above was quite extraordinary regarding this fact.

      BTW: The server has 8GB of physical memory. Would have been better to set it up with a 64-bit-install, but I worried additional troubles. atm there is no way to change this.

      Comment


      • #4
        No luck. max_allowed_packet_size does not seem to change anything.
        I lowered the value from 32 to 4 mb and restarted on 16.3., 19:00. The rise seems to quite the same as before.

        I had calculated a max of 1617MB (1017MB + 150*4MB) for the new setting, after 4 days i allready see 2000MB. The rise is usually slower during the weekend, but I expect the server to crash on monday or tuesday.

        Any other ideas/suggestions? I welcome any hint...
        Best regards, Stephan

        memory usage was as following:
        virt. res.
        17.3. 10:30 585m 497m
        17.3. 14:39 677m 588m
        18.3. 09:43 970m 878m
        18.3. 12:58 1044m 953m
        18.3. 23:55 1280m 1.2g
        19.3. 08:55 1343m 1.2g
        19.3. 17:07 1540m 1.4g
        19.3. 20:53 1621m 1.5g
        20.3. 11:02 1778m 1.6g
        20.3. 20:30 2085m 1.9g

        Max. connections used was 30 during the 4 days.

        Comment


        • #5
          Hmm... I'm not sure then.

          What is the output of
          mysql> show status;
          ?

          We might find a clue there.

          Comment


          • #6
            show status (cleaned a bit)


            +-----------------------------------+------------+| Variable_name | Value |+-----------------------------------+------------+| Aborted_clients | 130 || Aborted_connects | 0 || Binlog_cache_disk_use | 318 || Binlog_cache_use | 2937516 || Bytes_received | 117 || Bytes_sent | 170 || Com_admin_commands | 0 |... all zero ...| Com_savepoint | 0 || Com_select | 1 || Com_set_option | 0 |... all zero ...| Com_show_slave_status | 0 || Com_show_status | 1 || Com_show_storage_engines | 0 |... all zero ...| Com_xa_start | 0 || Compression | OFF || Connections | 2908869 || Created_tmp_disk_tables | 0 || Created_tmp_files | 919 || Created_tmp_tables | 1 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 0 |... all zero ...| Handler_update | 0 || Handler_write | 132 || Innodb_buffer_pool_pages_data | 15761 || Innodb_buffer_pool_pages_dirty | 22 || Innodb_buffer_pool_pages_flushed | 892741 || Innodb_buffer_pool_pages_free | 1 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 622 || Innodb_buffer_pool_pages_total | 16384 || Innodb_buffer_pool_read_ahead_rnd | 4547 || Innodb_buffer_pool_read_ahead_seq | 10603 || Innodb_buffer_pool_read_requests | 2259044321 || Innodb_buffer_pool_reads | 117628 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 59661331 || Innodb_data_fsyncs | 6025381 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 2730332160 || Innodb_data_reads | 174968 || Innodb_data_writes | 6651957 || Innodb_data_written | 222682112 || Innodb_dblwr_pages_written | 892741 || Innodb_dblwr_writes | 41706 || Innodb_log_waits | 0 || Innodb_log_write_requests | 4738969 || Innodb_log_writes | 5905524 || Innodb_os_log_fsyncs | 5941323 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 1015899648 || Innodb_page_size | 16384 || Innodb_pages_created | 25643 || Innodb_pages_read | 952945 || Innodb_pages_written | 892741 || Innodb_row_lock_current_waits | 0 || Innodb_row_lock_time | 817 || Innodb_row_lock_time_avg | 11 || Innodb_row_lock_time_max | 273 || Innodb_row_lock_waits | 72 || Innodb_rows_deleted | 3059761 || Innodb_rows_inserted | 3139589 || Innodb_rows_read | 23998375 || Innodb_rows_updated | 569052 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 7234 || Key_blocks_used | 982 || Key_read_requests | 21803056 || Key_reads | 111883 || Key_write_requests | 4245782 || Key_writes | 0 || Last_query_cost | 0.000000 || Max_used_connections | 30 || Not_flushed_delayed_rows | 0 || Open_files | 62 || Open_streams | 0 || Open_tables | 681 || Opened_tables | 0 || Prepared_stmt_count | 0 || Qcache_free_blocks | 0 || Qcache_free_memory | 0 || Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache | 0 || Qcache_total_blocks | 0 || Questions | 101516527 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 1 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | ON || Slow_launch_threads | 0 || Slow_queries | 0 || Sort_merge_passes | 0 || Sort_range | 0 || Sort_rows | 0 || Sort_scan | 0 || Table_locks_immediate | 111831235 || Table_locks_waited | 48845 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 0 || Threads_cached | 19 || Threads_connected | 2 || Threads_created | 35 || Threads_running | 2 || Uptime | 362018 |+-----------------------------------+------------+

            Comment


            • #7
              Nothing looks amiss there.

              How big are your memory tables?

              Comment


              • #8
                Many thanks for your efforts Mark, Checked the memory tables.
                In fact it is only one (plus those in information_schema).

                Size is 100-500 rows, 7 columns (about 100 bytes per row). But the table gets about 1/3 of the write requests.
                Values are only valid for 3-90 seconds and old ones are deleted about twice per minute.

                Comment


                • #9
                  What's your max_heap_table_size set to?

                  Comment


                  • #10
                    Max heap table size was 16M.
                    I just reduced it to 4M and restarted the server. Memory usage was up to 2390M.

                    Comment


                    • #11
                      When you are looking at top, what column are you looking at?

                      The RES column is the actual amount of RAM being used. Don't forget that a process can map files on disk to memory locations, without actually loading them (though MySQL eventually will as pages are referenced). That's what the VIRT column shows. The pages of the files will only be moved into memory if needed, and multiple processes can map the same files (the amount of shared files shows up in the SHR column).

                      A 32 bit operating system is limited to 4GB of addressable space. Some of that 4GB is used up for things like the PCI bus, memory mapped to a graphics card, etc. That means less than 4GB of files can be mapped into memory at once, not to mention the other uses for the RAM.

                      If this is the problem, the best thing would be to move to a 64 bit system. The other thing we can try is limiting the number of open tables. The rule of thumb for table_cache is concurrent connections x n, where is the max number of tables used in joins in any query you may execute, plus some extra for temporary tables. With a setting of 1350, and 30 max connections, that would be up to 45 tables in a join... which seems a bit high. I don't know your queries, but a setting around 200 might be more appropriate. Try that?

                      Comment


                      • #12
                        I look at both columns. Before the last restart the values were 2390m and 2.2g.
                        I think the problem is virtual memory (32bit and 3/1g memory split).

                        Swapping (physical memory) is not an issue at all. Even when mysql is close to crashing there are 2.5GB used for file cache. On normal days IO-wait is about 0.15% during the high load times.

                        You are absolutely right about the 64bit system, but this would cause quite a bit of work...

                        I will check the results of the change in max_heap_table_size and try table_cache as well.

                        The strange thing is that the server ran well for half a year with 450 max connections (the table_cache 1350 from that setting) and much higher memory settings. On the other hand there were more changes to tables/databases during that time and also some manual restarts. Looks like the only way is to reconstruct the history from backups.

                        As far as I remember the only major change during the time I noticed the problems was the move of some large blob columns to the file system. Unfortunately the changes were made over half a year and any other - small change - might be the real issue as well.
                        I'll check if I can somehow correlate the issues with these changes...

                        Comment


                        • #13
                          Yeah, I'd say it's definitely the 3GB limit. You can tweak that little in the kernel, but you'll still be limited to less than 4GB at an absolute max with 32 bit Linux.

                          It's work, yes, but moving to 64 bits is the only way around that memory limit.

                          The only other thing I can think of is that the combined size of the InnoDB log files can't exceed 4 GB. http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.h tml

                          Comment


                          • #14
                            Looks like I have a lead. After the last set of changes the server seems to be jailed within 1.1-1.2g (virtual memory). Thanks Mark.

                            Here is a short summary of the last changes:

                            The lowered max_heap_table_size does not look to have any relevance. I restarted after 4 days at 2.1g/1.9g (virt./res.) on 25.3. About the same rise as before and even before the changes in march (since I opened the topic).

                            Those were the changes for the last restart (25.3. 21:00) with much better mem usage:
                            - max_connections 150 > 100
                            - table_cache 1350 > 400
                            - open_files_limit 4000 > 1200
                            - thread_cache_size 20 > 10

                            I will try to isolate the responsible setting and report back.
                            Will try to check performance impact as well, but this has only minor relevance.

                            I also managed to nail down the set of changes that caused the issues. Well hidden in times of low usage and config changes so the problems appeared quite a bit later.
                            Anyway, there is no way to revert.

                            Comment


                            • #15
                              if you have many tables with blobs and those blobs are large, then table_cache can consume more memory than you think...

                              see http://bugs.mysql.com/bug.php?id=38002 for an example of this.

                              Comment

                              Working...
                              X