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 + > 1000000 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: 6198896 > Top-most, releasable space: 16552 > Estimated memory (with thread stack): 1319610352 > > > Number of processes running now: 0 > 050704 18:38:23 mysqld restarted > 050704 18:38:23 [ERROR] Can't start server: Bind on TCP/IP port: > Address already in use > 050704 18:38:23 [ERROR] Do you already have another mysqld server > running on port: 3306 ? > 050704 18:38:23 [ERROR] Aborting > > 050704 18:38:23 [Note] /usr/libexec/mysqld: Shutdown complete > > 050704 18:38:23 mysqld ended > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
