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 +
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
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 +
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/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
