•  

Announcement

Announcement Module
Collapse
No announcement yet.

Out of Memory

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

  • Out of Memory

    #1
    Hello,

    This is my first post here, after I have tried to search existing posts for an answer to my problem, and could not find it.

    I have recently moved my database from a single 2GB RAM machine (that ran the entire LAMP), to a cluster configuration, in which the database is on its own machine with 4GB RAM, and also being replicated.

    Since then, I started receiving a lot of "Out of Memory" errors in mysql error log. They come in bulks, and at random times. Here is a sample:
    Quote:


    080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
    080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
    080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1042860 bytes)
    080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
    080113 5:20:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
    080113 5:20:04 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:18:29 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:18:33 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:18:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:20:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 9:27:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 15:52:20 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 16:01:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 16:01:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
    080113 16:01:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
    080113 16:01:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)



    One time they also caused the server to crash and failover to the slave. Note, that it seems the process only needed 1M of allocation before it terminated...

    At first I thought it had to do with the OS being a 32-bit (rhel4), and the mysqld process larger than 2GB - I had innodb_buffer_pool_size of 2000M - so I reduced it to 1000M, and I see that mysqld is under 2GB (at the time of writing: VIRT - 1855m, RES - 1.4g), but I am still getting these errors.

    I also tried to isolate a specific query that does it, so I turned on query logging with error messages, and I see that several queries cause this.

    Here is my.cnf:

    Quote:


    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking

    max_connections = 530
    default-character-set = utf8
    max_allowed_packet = 16M

    query_cache_type = 1
    query_cache_size = 256M
    query_cache_limit = 2M

    key_buffer_size = 256M
    max_seeks_for_key = 500

    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 4
    thread_cache_size = 16

    table_cache = 2048

    tmp_table_size = 256M
    max_heap_table_size = 257M

    ft_min_word_len = 3

    sort_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 1M
    join_buffer_size = 2M

    innodb_buffer_pool_size = 1000M
    innodb_additional_mem_pool_size = 20M

    innodb_log_file_size = 512M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

    innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

    log-slow-queries=slow-log
    long_query_time=1

    log-bin=db-bin-log
    server-id = 1
    slave-skip-errors=126,1062



    Any help would be greatly appreciated.
    Or
    Tags: None

  • #2
    Is /proc/sys/vm/overcommit_memory set to 0?

    If not, I believe it's a setting in /etc/sysctl.conf on RedHat (I'm not a Redhat user). Make sure it's set to 0 if it's not. You can also change the value until next reboot by running "echo 0 > /proc/sys/vm/overcommit_memory" and see if it solves the problem.

    Comment


    • #3
      It is set to 0:

      # cat /proc/sys/vm/overcommit_memory0


      I forgot to mention, this is mysql 5.0.45.

      Also, now the server from time to time gets huge loads (over 100!), mysqld is burning cpu on close to 200%, and I get over 95% IO waits, and temporary tables start to pile up in /var/lib/mysql... I am not sure if this problem is connected, or an entirely new problem.

      Or

      Comment


      • #4
        Sounds like your system is starved for disk I/O. Are you seeing any disk errors showing in your log files?


        I bet the problems are connected. MySQL is could be waiting for IO on one action, so it starts another, which requires more RAM, which eventually leads it to being out of memory.

        Did you move from a SCSI system to an IDE system or something like that?

        Comment


        • #5
          The change in the machine was from OEM server with SATA drives to a HP with RAID10.

          The problems could be connected as you say, however the memory problems have been going on for a month (since the machine switching), but the IO problems are brand new (started today).

          More info that might help, is when this IO load occured, I restarted mysql, and upon restart I get this:

          Quote:


          080115 14:26:34 mysqld started
          080115 14:26:35 InnoDB: Started; log sequence number 8 1135678335
          080115 14:26:35 [Note] /usr/sbin/mysqld: ready for connections.
          Version: '5.0.45-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition (GPL)
          080115 14:27:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:43 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:44 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:46 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:27:53 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
          080115 14:28:02 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
          080115 14:28:05 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)



          Memory errors as soon as it starts up? Weird no?

          Or

          Comment


          • #6
            That is indeed.

            Can you paste in my.cnf and the output of "show status;"?

            I'm not an expert, but another set of eyes never hurts.

            Comment


            • #7
              orensol wrote on Tue, 15 January 2008 09:01


              Quote:


              080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
              080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1043352 bytes)
              080113 5:20:00 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1042860 bytes)

              [snip]

              080113 16:01:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
              080113 16:01:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)
              080113 16:01:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1044576 bytes)
              080113 16:01:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1045840 bytes)



              One time they also caused the server to crash and failover to the slave. Note, that it seems the process only needed 1M of allocation before it terminated...



              This looks like a new connection, but you have no memory left. As you are running an 32bit OS you AFAIK cant use all the 4GB. Anyway habe a look at your system also.
              Something like top or vmstat look into /var/log/messages.
              Type into your mysql-client "show global status like 'Threads_connected'" next time it happens. "Show status like 'Max_used_connections'" should give you a good clue.
              etc.

              Comment


              • #8
                Quote:


                USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
                mysql 15 0 196 404:07.09 34.7 1811m 1.4g 4688 S mysqld



                mysqld is burning the cpu (196%!!)... How can I reduce cpu usage? Maybe I should reduce number of threads?

                Or

                Comment


                • #9
                  How much free space do you have in /var/lib/mysql?

                  If a temporary table won't fit in RAM, MySQL will create a MyISAM table -- so even though your InnoDB tables are stored elsewhere, it doesn't apply to MyISAM tables.

                  You can also specify a tmpdir and point to a location with several gigs free.

                  Comment


                  • #10
                    MarkRose wrote on Wed, 16 January 2008 16:35

                    How much free space do you have in /var/lib/mysql?

                    If a temporary table won't fit in RAM, MySQL will create a MyISAM table -- so even though your InnoDB tables are stored elsewhere, it doesn't apply to MyISAM tables.

                    You can also specify a tmpdir and point to a location with several gigs free.


                    Help me Im missing the link to the topic:-(

                    Comment


                    • #11
                      orensol wrote on Wed, 16 January 2008 04:39

                      Quote:


                      USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
                      mysql 15 0 196 404:07.09 34.7 1811m 1.4g 4688 S mysqld



                      mysqld is burning the cpu (196%!!)... How can I reduce cpu usage? Maybe I should reduce number of threads?

                      Or


                      Off course.

                      But I would start with having al look at the queries. One of the first steps should always be to look at the SQL:-)

                      Comment


                      • #12
                        erkules wrote on Thu, 17 January 2008 02:32

                        MarkRose wrote on Wed, 16 January 2008 16:35

                        How much free space do you have in /var/lib/mysql?

                        If a temporary table won't fit in RAM, MySQL will create a MyISAM table -- so even though your InnoDB tables are stored elsewhere, it doesn't apply to MyISAM tables.

                        You can also specify a tmpdir and point to a location with several gigs free.


                        Help me Im missing the link to the topic:-(


                        Open a terminal and run "df -h" and paste the results here )

                        Comment


                        • #13
                          MarkRose wrote on Thu, 17 January 2008 20:52



                          Open a terminal and run "df -h" and paste the results here )


                          A "df -h" on my system wont help, because I dont have the problems. But I see your point.
                          Your you say, thant tmp-files are used and no room is left:-)

                          thx

                          Comment

                          Working...
                          X