Re: innodb crashes during heavy usage with exceeded memory error
Kasthuri, Maybe it's time to re-think your application architecture? A 200-meg BLOB is quite large for a highly-concurrent system, considering that MySQL will have to read/save it in its entirety _and_ allocate network buffers for it, so essentially you're allocating _400_ megs or so _per_ client. Thanks a lot for all who responded. Yes, I'm fighting that battle with developers right now. Until I can convince developers to redesign their application, I'm working on ways to keep mysql from not crashing. I think our option is to move to 64 bit machine or store session data on local disk instead of in the database. Thanks again. Kasthuri (not to mention that many of your web sessions are sending 200 megs of data around your network between your appserver(s) and your database, which is a performance issue as well) -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD4DBQFCzBgMtvXNTca6JD8RAhPYAKDDqEMlqXKM1q+cEkj2DTUcR795EQCY4h8J xIIf3/Uyktd0PO5M6573qw== =gWXC -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb crashes during heavy usage with exceeded memory error
Thank a lot for your quick reply. SInce we are not using myisam tables ( except for the system tables), I deallocated memory from myisam and allocated to innodb. When I allocated close to 1.7G to innodb buffer size, mysql used to crash more often. So I decreased innodb_buffer_size to 1G. What memory parameter do you suggest to increase and how do you alter innodb table extender? This is the error that causes mysql to crash. 050704 18:19:21 InnoDB: Warning: could not allocate 100892621 + 100 bytes to retrieve InnoDB: a big column. Table name `sessions/horde_sessionhandler` 050704 18:37:16 InnoDB: ERROR: the age of the last checkpoint is 483189148, InnoDB: which exceeds the log group capacity 483180135. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 050704 18:38:23 InnoDB: Fatal error: cannot allocate 100892688 bytes of InnoDB: memory with malloc! Total allocated memory The table that's it's complaining about is a session table which stores web session information. Basically the data in that table is a throw away data and gets deleted when the user logs out. The session data column in that table is defined as long blob. The average length of the row is not that big but once in a while application inserts a row for a user with session data that could exceed 200MG . Since we have lots of concurrent users and if application inserts few rows with > 200MG data simultaneously innodb runs out of memory and crashes. Eventhough we have 8 gig memory, I'm not able to start mysql if I allocate more than 2Gig to innodb_buffer_size. Do you know how I can allocate more than 2 Gig memory to innodb on 32 bit machine. That might solve our problem. Thanks for your suggestions. Kasthuri On Jul 5, 2005, at 2:36 PM, Mir Islam wrote: You definitely should increase memory sizes in your my.cnf file. The settings that you have are for a very smal setup. You also need to allocate more space for innodb table extender. So instead of 10m have something like 50m or try and see which settings is better. The reason is under heavy load if innodb has to constantly extend the table space it will not have opportunity to anything else. So having 100m for each extend will reduce the number of times it has to increase table space. Are you doing any deletes/updates at the same time? You said 50m inserts to a table. Is that figure per day ? How large (in bytes) is an average row? Lastly look into the my.cnf for a large setup that ships with mysql. I think it is called my-large.cnf that will give you some help on settings. On 7/5/05, Kasthuri Ilankamban <[EMAIL PROTECTED]> wrote: Hi, We are running mysql version 4.1.7 with innodb on i686 running 2.4.26 linux kernal with 8G memory. Mysql crashes consistently during heavy usage with fatal innodb error. We are running a high volume front end application which inserts > 50M data to a row in innodb table often. I don't know whether these inserts causing the memory overflow. Anyway I have included our my.cnf file and error logs from last crash below. Any help would be greatly appreciated. Thanks in advance. Kasthuri -- /etc/my.cnf key_buffer = 8M max_allowed_packet = 128M read_buffer_size = 512K sort_buffer_size = 512K myisam_sort_buffer_size = 5M thread_cache = 1024 table_cache = 1024 query_cache_size = 32M max_connections=1000 wait_timeout=300 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/mysql/data innodb_data_file_path = ibdata1:10M;ibdata2:1G;ibdata3:1G:autoextend # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DSYNC innodb_lock_wait_timeout = 10 - Mysql Error from error logs: 050704 18:19:21 InnoDB: Error: cannot allocate 101892621 bytes of memory for InnoDB: a BLOB with malloc! Total allocated memory InnoDB: by InnoDB 1989384815 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. 050704 18:19:21 InnoDB: Warning: could not allocate 100892621 + 100 bytes to retrieve InnoDB: a big column. Table name `sessions/horde_sessionhandler` 050704 18:37:16 InnoDB: ERROR: the age of the last checkpoint is 483189148, InnoDB: which exceeds the log group capacity 483180135. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 050704 18:38:23 InnoDB: Fatal error: cannot alloc
innodb crashes during heavy usage with exceeded memory error
Hi, We are running mysql version 4.1.7 with innodb on i686 running 2.4.26 linux kernal with 8G memory. Mysql crashes consistently during heavy usage with fatal innodb error. We are running a high volume front end application which inserts > 50M data to a row in innodb table often. I don't know whether these inserts causing the memory overflow. Anyway I have included our my.cnf file and error logs from last crash below. Any help would be greatly appreciated. Thanks in advance. Kasthuri -- /etc/my.cnf key_buffer = 8M max_allowed_packet = 128M read_buffer_size = 512K sort_buffer_size = 512K myisam_sort_buffer_size = 5M thread_cache = 1024 table_cache = 1024 query_cache_size = 32M max_connections=1000 wait_timeout=300 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/mysql/data innodb_data_file_path = ibdata1:10M;ibdata2:1G;ibdata3:1G:autoextend # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DSYNC innodb_lock_wait_timeout = 10 - Mysql Error from error logs: 050704 18:19:21 InnoDB: Error: cannot allocate 101892621 bytes of memory for InnoDB: a BLOB with malloc! Total allocated memory InnoDB: by InnoDB 1989384815 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. 050704 18:19:21 InnoDB: Warning: could not allocate 100892621 + 100 bytes to retrieve InnoDB: a big column. Table name `sessions/horde_sessionhandler` 050704 18:37:16 InnoDB: ERROR: the age of the last checkpoint is 483189148, InnoDB: which exceeds the log group capacity 483180135. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 050704 18:38:23 InnoDB: Fatal error: cannot allocate 100892688 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 1787599407 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. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=524288 max_used_connections=608 max_connections=700 threads_connected=33 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size) *max_connections = 724986 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x893b6018 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfb3e0b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x81767d9 0x401698e5 0x83c1657 0x83c1690 0x83bef0c 0x83bef9f 0x832d1c1 0x832d33c 0x82ff5f3 0x83026c5 0x8202b0b 0x8202d63 0x81f021a 0x81bb4fc 0x81bb01d 0x81b1432 0x81b1b20 0x81aea76 0x8189f10 0x818ef68 0x8188a2e 0x81885ee 0x8187e34 0x40163d03 0x402f0267 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x8ca47e80 is invalid pointer thd->thread_id=25038128 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Memory status: Non-mmapped space allocated from system: 31660016 Number of free chunks: 236 Number of fastbin blocks:5 Number of mmapped regions: 22 Space in mmapped regions:1275957248 Maximum total allocated space: 0 Space available in freed fastbin blocks: 168 Total allocated space: 25461120 Total free space: