Re: innodb keeps crashing due to out-of-memory errors.
Hi Heikki. "Heikki Tuuri" <[EMAIL PROTECTED]> writes: > Why does InnoDB allocate so much memory in your case? If you follow > the memory allocation with innodb_monitor, does the allocation grow > steadily over days? Then it could be a memory leak in InnoDB. This might be the case. I started MySQL with a very big additional_mem_pool (400M) as you suggested. The server ran very stable for 12 hours. At first, memory allocation was like this: Total memory allocated 1499024287; in additional pool allocated 1205632 Total memory allocated 1499024287; in additional pool allocated 1651072 Total memory allocated 1499024287; in additional pool allocated 1815808 Total memory allocated 1499024287; in additional pool allocated 1887488 Total memory allocated 1499024287; in additional pool allocated 1962880 Total memory allocated 1499024287; in additional pool allocated 2005504 Total memory allocated 1499024287; in additional pool allocated 2029056 Total memory allocated 1499024287; in additional pool allocated 2060288 Total memory allocated 1499024287; in additional pool allocated 2068352 Total memory allocated 1499024287; in additional pool allocated 2106496 12 hours later: Total memory allocated 1499024287; in additional pool allocated 4044928 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4046208 Total memory allocated 1499024287; in additional pool allocated 4038912 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4048000 Total memory allocated 1499024287; in additional pool allocated 4046464 Total memory allocated 1499024287; in additional pool allocated 4046080 Total memory allocated 1499024287; in additional pool allocated 4043264 Total memory allocated 1499024287; in additional pool allocated 4046080 Total memory allocated 1499024287; in additional pool allocated 4049024 Total memory allocated 1499024287; in additional pool allocated 4059008 Total memory allocated 1499024287; in additional pool allocated 4067200 Total memory allocated 1499024287; in additional pool allocated 4063744 Total memory allocated 1499024287; in additional pool allocated 4049792 Total memory allocated 1499024287; in additional pool allocated 4061952 (The reason the server crashed was due to many threads - we had approx 350 threads and we hit the 2GB barrier with a bang). > 1) Monitor the memory consumption with innodb_monitor. Is there a symptom of > a memory leak? I will get back to you on this one ~ friday when I have more data. > 2) Try a smaller buffer pool or bigger. Does the crash always happen when > InnoDB has allocated about 1400 MB of memory? > 3) Try setting innodb_additional_mem_pool much bigger, say, 400 MB. Does > memory allocation still spill over from it? No. It runs very stable with a much bigger innodb_additional_mem_pool. Looks the problem is linked with increasing the size of the innodb_additional_mem_pool. Greetings, -- Per Andreas Buer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb keeps crashing due to out-of-memory errors.
Per, > set-variable= key_buffer=16M > set-variable= sort_buffer=1M > set-variable= record_buffer=1M > set-variable= max_allowed_packet=16M > set-variable= thread_stack=256K > set-variable = innodb_log_file_size=32M > set-variable = innodb_log_buffer_size=8M > > set-variable = innodb_buffer_pool_size=950M > set-variable = innodb_additional_mem_pool_size=128M these look ok, you should be able to make safely at least 300 connections without memory consumption approaching 2 GB. But I tested your my.cnf on our computer, and InnoDB Monitor reported the following when I had the standard test set of some 15 threads running: ... Total memory allocated 1214054564; in additional pool allocated 743552 ... Looks like in your case InnoDB has allocated a lot of memory also outside the additional mem pool, and it crashes when it has allocated about 1400 000 000 bytes. Actually, because of memory fragmentation, the 200 MB which InnoDB in your case has allocated from the operating system may in reality take a lot more space. As long as InnoDB allocates from its own additional_mem_pool, we know exactly how much memory is used. But when InnoDB has to resort to allocation with normal malloc(), we do not know any more what is the real memory usage inside glibc. Why does InnoDB allocate so much memory in your case? If you follow the memory allocation with innodb_monitor, does the allocation grow steadily over days? Then it could be a memory leak in InnoDB. > > Could it be that the mysqld memory usage may approach 2 GB? In that case > > glibc will start allocating memory over thread stacks, which will cause all > > kinds of crashes and errors. > > Hmm. I thought the heap could grow up to 3GB on Linux 2.4 (with one > gigabyte for stack). Is the memory space split 2/2? This is bad news for > me - I need as much memory as I can get. Well, I think it is 2 GB user memory. Better Linux experts please correct me if I am wrong. Here I found some patch you can use to get even 3.5 GB user memory: http://marc.theaimsgroup.com/?l=linux-kernel&m=101081428112601&w=2. > > The trace above is sensible. Do you have a BLOB or TEXT column in the table > > where it is doing an insert? > > No. Its mostly varchars. > > > If not, then the index entry tuple which InnoDB has just constructed > > has probably garbage in its fields, and that is why InnoDB believes it > > is a 'big record'. > > Hmm. So, if the heap has grown to much - glibc is writing garbage all > over the stack. Is there any way to tell how much memory MySQL has > allocated? You can calculate it approximately from the formula in InnoDB manual, section 2. Hmm.. what to do? 1) Monitor the memory consumption with innodb_monitor. Is there a symptom of a memory leak? 2) Try a smaller buffer pool or bigger. Does the crash always happen when InnoDB has allocated about 1400 MB of memory? 3) Try setting innodb_additional_mem_pool much bigger, say, 400 MB. Does memory allocation still spill over from it? 4) Run inside gdb so that we get a bt full of the crash. 5) I will add some assertions to 3.23.52 which should catch the garbage in the index entry earlier, and will also print diagnostics about the garbage. 6) I will run tests with InnoDB memory debugging switched on, and look if I can find some memory leak or memory overwrite. > -- > Per Andreas Buer Best regards, Heikki Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb keeps crashing due to out-of-memory errors.
Hi Heikki. "Heikki Tuuri" <[EMAIL PROTECTED]> writes: > > Oh, by the way. The error does not occur if I use less memory. > > please show us your complete my.cnf. # This will be passed to all mysql clients [client] #password = my_password port= 3306 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 set-variable= table_cache=500 set-variable= max_connections=800 # the highest number of threads recorded is approx. 270 set-variable= max_connect_errors=10 # # Replication log-bin server-id=1 #log= /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-locking # # The skip-networkin option will no longer be set via debconf menu. # You have to manually change it if you want networking i.e. the server # listening on port 3306. The default is "disable" - for security reasons. # skip-networking set-variable= key_buffer=16M set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= max_allowed_packet=16M set-variable= thread_stack=256K # # Here you can see queries with especially long duration log-slow-queries= /var/log/mysql/mysql-slow.log # The following can be used as easy to replay backup logs or for replication #server-id = 1 #log-bin= /var/log/mysql/mysql-bin.log #binlog-do-db = include_database_name #binlog-ignore-db = include_database_name # skip-innodb # InnoDB innodb_data_home_dir=/var/lib/mysql/innodb/ innodb_data_file_path=ibdata00:1G;ibdata01:1G;ibdata02:1G;ibdata03:1G;ibdata04:1 G;ibdata05:1G;ibdata06:1G;ibdata07:1G;ibdata08:1G;ibdata09:1G;ibdata10:1G;ibdata 11:1G;ibdata12:1G;ibdata13:1G;ibdata14:1G;ibdata15:1G;ibdata16:1G;ibdata17:1G;ib data18:1G;ibdata19:1G innodb_log_group_home_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_files_in_group=3 innodb_log_arch_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M set-variable = innodb_buffer_pool_size=950M set-variable = innodb_additional_mem_pool_size=128M # innodb_flush_method=O_DSYNC innodb_flush_log_at_trx_commit=0 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] set-variable= key_buffer=16M > Could it be that the mysqld memory usage may approach 2 GB? In that case > glibc will start allocating memory over thread stacks, which will cause all > kinds of crashes and errors. Hmm. I thought the heap could grow up to 3GB on Linux 2.4 (with one gigabyte for stack). Is the memory space split 2/2? This is bad news for me - I need as much memory as I can get. > Note that each thread using sorting will use sort_buffer much memory, and > each thread using a MyISAM table scan will use record_buffer much memory. Do > you have many queries doing sorting simultaneously? Hmm. So Innobase tables don't use the record_buffer? But it does use the sort buffer, rigth? This is new to me - the documentation is a bit thin here - maybe you could add a section on how to configure a "pure" InnoDB MySQL-server? > > InnoDB: You may get better performance if you configure a bigger > > InnoDB: value in the MySQL my.cnf file for > > InnoDB: innodb_additional_mem_pool_size. > > InnoDB: Fatal error: cannot allocate 2851686576 bytes of > > InnoDB: memory with malloc! Total allocated memory > > InnoDB: by InnoDB 1413577223 bytes. Operating system errno: 12 > > InnoDB: Cannot continue operation! > > 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. > > Obtained 10 stack frames. > > /usr/sbin/mysqld(ut_malloc_low+0xb8) [0x823ba28] > > /usr/sbin/mysqld(ut_malloc+0x13) [0x823c37b] > > /usr/sbin/mysqld(mem_area_alloc+0xf9) [0x823a601] > > /usr/sbin/mysqld(mem_heap_create_block+0x63) [0x82397d3] > > /usr/sbin/mysqld(dtuple_convert_big_rec+0x1b6) [0x8235fde] > > /usr/sbin/mysqld(btr_cur_optimistic_insert+0x3e1) [0x8198bdd] > > /usr/sbin/mysqld(row_ins_index_entry_low+0x674) [0x81614b4] > > /usr/sbin/mysqld [0x816175e] > > /usr/sbin/mysqld(row_ins+0x1cc) [0x8161950] > > /usr/sbin/mysqld(row_ins_step+0xf7) [0x8161a83] > > The trace above is sensible. Do you have a BLOB or TEXT column in the table > where it is doing an insert? No. Its mostly varchars. > If not, then the index entry tuple which InnoDB has just constructed > has probably garbage in its fields, and that is why InnoDB believes it > is a 'big record'. Hmm. So, if the heap has grown to much - glibc is writing garbage all over the stack. Is there any way to tell how
Re: innodb keeps crashing due to out-of-memory errors.
Per, - Original Message - From: "Per Andreas Buer" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Sunday, June 16, 2002 2:10 PM Subject: Re: innodb keeps crashing due to out-of-memory errors. > Oh, by the way. The error does not occur if I use less memory. please show us your complete my.cnf. Could it be that the mysqld memory usage may approach 2 GB? In that case glibc will start allocating memory over thread stacks, which will cause all kinds of crashes and errors. Note that each thread using sorting will use sort_buffer much memory, and each thread using a MyISAM table scan will use record_buffer much memory. Do you have many queries doing sorting simultaneously? > 020615 21:45:28 InnoDB: Out of memory in additional memory pool. > InnoDB: InnoDB will start allocating memory from the OS. > InnoDB: You may get better performance if you configure a bigger > InnoDB: value in the MySQL my.cnf file for > InnoDB: innodb_additional_mem_pool_size. > InnoDB: Fatal error: cannot allocate 2851686576 bytes of > InnoDB: memory with malloc! Total allocated memory > InnoDB: by InnoDB 1413577223 bytes. Operating system errno: 12 > InnoDB: Cannot continue operation! > 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. > Obtained 10 stack frames. > /usr/sbin/mysqld(ut_malloc_low+0xb8) [0x823ba28] > /usr/sbin/mysqld(ut_malloc+0x13) [0x823c37b] > /usr/sbin/mysqld(mem_area_alloc+0xf9) [0x823a601] > /usr/sbin/mysqld(mem_heap_create_block+0x63) [0x82397d3] > /usr/sbin/mysqld(dtuple_convert_big_rec+0x1b6) [0x8235fde] > /usr/sbin/mysqld(btr_cur_optimistic_insert+0x3e1) [0x8198bdd] > /usr/sbin/mysqld(row_ins_index_entry_low+0x674) [0x81614b4] > /usr/sbin/mysqld [0x816175e] > /usr/sbin/mysqld(row_ins+0x1cc) [0x8161950] > /usr/sbin/mysqld(row_ins_step+0xf7) [0x8161a83] The trace above is sensible. Do you have a BLOB or TEXT column in the table where it is doing an insert? If not, then the index entry tuple which InnoDB has just constructed has probably garbage in its fields, and that is why InnoDB believes it is a 'big record'. > > What do you mean by syncing logs? > > Syncing the log-files, or journal. This only makes sense if one runs > with innodb_flush_log_at_trx_commit=0. This is really not an important > issue. If one requires durability one should buy adequate hardware. :/ InnoDB tries to flush the log once per second even if you set ...=0. > -- > Per Andreas Buer > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb keeps crashing due to out-of-memory errors.
"Heikki Tuuri" <[EMAIL PROTECTED]> writes: > good. I have also modified 3.23.52 so that it will generate a seg fault when > it runs out of memory. That way we will get a stack trace on Linux. The error occered again. Now I se that I only pick 10 frames of the stack (curse me for cut'n pasteing). If you need a complete trace let me know. Oh, by the way. The error does not occur if I use less memory. 020615 21:45:28 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. InnoDB: Fatal error: cannot allocate 2851686576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1413577223 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! 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. Obtained 10 stack frames. /usr/sbin/mysqld(ut_malloc_low+0xb8) [0x823ba28] /usr/sbin/mysqld(ut_malloc+0x13) [0x823c37b] /usr/sbin/mysqld(mem_area_alloc+0xf9) [0x823a601] /usr/sbin/mysqld(mem_heap_create_block+0x63) [0x82397d3] /usr/sbin/mysqld(dtuple_convert_big_rec+0x1b6) [0x8235fde] /usr/sbin/mysqld(btr_cur_optimistic_insert+0x3e1) [0x8198bdd] /usr/sbin/mysqld(row_ins_index_entry_low+0x674) [0x81614b4] /usr/sbin/mysqld [0x816175e] /usr/sbin/mysqld(row_ins+0x1cc) [0x8161950] /usr/sbin/mysqld(row_ins_step+0xf7) [0x8161a83] > > Another question: As the server has some limitation (io kapasity) I am > > running with innodb_flush_log_at_trx_commit set to "0". Would it be > > unwise to try to sync the logs when we hit this kind of error? Does > > syncing of logs require mallocing of memory? > > What do you mean by syncing logs? Syncing the log-files, or journal. This only makes sense if one runs with innodb_flush_log_at_trx_commit=0. This is really not an important issue. If one requires durability one should buy adequate hardware. :/ -- Per Andreas Buer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb keeps crashing due to out-of-memory errors.
Hi Heikki, thanks for replying so swiftly. "Heikki Tuuri" <[EMAIL PROTECTED]> writes: > this is probably a real bug. It is trying to allocate 1.5 GB of memory in > one shot, which does not make sense. More probably the argument to the > allocator is garbage. > > What MySQL version you are running? mysqld Ver 3.23.49 for pc-linux-gnu on i686 MySQL is installed from a Debian Package (I am running Debian GNU/Linux 3.0) - package version 3.23.49-8. > Can you compile MySQL yourself with > > CFLAGS="-g -O3" ./configure --with-innodb > > and run it inside gdb? Put a breakpoint to the code where it prints the > error message, so that we get the stack trace with > > gdb> bt full Hmm. This is a production machine and I have a very limited window to tinker around with things. The crash occures when the server is really busy - I have so far failed to provoke this to happen. I've modified innobase/ut/ut0mem.c to give a nice stack-trace when the error occures, and recompiled with symbols. Hopefully (uhh ;) it will crash again monday and I will have a nice stack-trace to show you. Another question: As the server has some limitation (io kapasity) I am running with innodb_flush_log_at_trx_commit set to "0". Would it be unwise to try to sync the logs when we hit this kind of error? Does syncing of logs require mallocing of memory? For completeness; here is my innodb configuration: # InnoDB innodb_data_home_dir=/var/lib/mysql/innodb/ innodb_data_file_path=ibdata00:1G;ibdata01:1G;ibdata02:1G;ibdata03:1G;ibdata04:1G;ibdata05:1G;ibdata06:1G;ibdata07:1G;ibdata08:1G;ibdata09:1G;ibdata10:1G;ibdata11:1G;ibdata12:1G;ibdata13:1G;ibdata14:1G;ibdata15:1G;ibdata16:1G;ibdata17:1G;ibdata18:1G;ibdata19:1G innodb_log_group_home_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_files_in_group=3 innodb_log_arch_dir=/var/lib/mysql/innodb/ set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=96M set-variable = innodb_buffer_pool_size=950M set-variable = innodb_additional_mem_pool_size=64M innodb_flush_method=O_DSYNC innodb_flush_log_at_trx_commit=0 -- Per Andreas Buer -- Per Andreas Buer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb keeps crashing due to out-of-memory errors.
Per, - Original Message - From: "Per Andreas Buer" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, June 15, 2002 11:40 PM Subject: Re: innodb keeps crashing due to out-of-memory errors. > > Hi Heikki, thanks for replying so swiftly. > > "Heikki Tuuri" <[EMAIL PROTECTED]> writes: > > > this is probably a real bug. It is trying to allocate 1.5 GB of memory in > > one shot, which does not make sense. More probably the argument to the > > allocator is garbage. > > > > What MySQL version you are running? > > mysqld Ver 3.23.49 for pc-linux-gnu on i686 > > MySQL is installed from a Debian Package (I am running Debian GNU/Linux > 3.0) - package version 3.23.49-8. > > > Can you compile MySQL yourself with > > > > CFLAGS="-g -O3" ./configure --with-innodb > > > > and run it inside gdb? Put a breakpoint to the code where it prints the > > error message, so that we get the stack trace with > > > > gdb> bt full > > Hmm. This is a production machine and I have a very limited window to > tinker around with things. The crash occures when the server is really > busy - I have so far failed to provoke this to happen. > > I've modified innobase/ut/ut0mem.c to give a nice stack-trace when the > error occures, and recompiled with symbols. Hopefully (uhh ;) it will > crash again monday and I will have a nice stack-trace to show you. good. I have also modified 3.23.52 so that it will generate a seg fault when it runs out of memory. That way we will get a stack trace on Linux. > Another question: As the server has some limitation (io kapasity) I am > running with innodb_flush_log_at_trx_commit set to "0". Would it be > unwise to try to sync the logs when we hit this kind of error? Does > syncing of logs require mallocing of memory? What do you mean by syncing logs? > For completeness; here is my innodb configuration: > > # InnoDB > > innodb_data_home_dir=/var/lib/mysql/innodb/ > innodb_data_file_path=ibdata00:1G;ibdata01:1G;ibdata02:1G;ibdata03:1G;ibdata 04:1G;ibdata05:1G;ibdata06:1G;ibdata07:1G;ibdata08:1G;ibdata09:1G;ibdata10:1 G;ibdata11:1G;ibdata12:1G;ibdata13:1G;ibdata14:1G;ibdata15:1G;ibdata16:1G;ib data17:1G;ibdata18:1G;ibdata19:1G > > innodb_log_group_home_dir=/var/lib/mysql/innodb/ > set-variable = innodb_log_files_in_group=3 > > innodb_log_arch_dir=/var/lib/mysql/innodb/ > > set-variable = innodb_log_file_size=32M > set-variable = innodb_log_buffer_size=96M The log buffer is unnecessarily big. Set it to 8M. > set-variable = innodb_buffer_pool_size=950M > set-variable = innodb_additional_mem_pool_size=64M > > innodb_flush_method=O_DSYNC You could try removing the flush_method line above. O_(D)SYNC is not much used on Linux and there is a risk it could be buggy. > innodb_flush_log_at_trx_commit=0 > > > > -- > Per Andreas Buer Regards, Heikki Innobase Oy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
