A big part of your problem seems to be that 32bit OS's can not allocate
more than 2G memory to a single process, and with each client connection
needing so much buffer space, you quickly exceed that limit. Short of
moving to a 64bit OS, the only solution I have come across that allows
mysql to utilize more than 2G total memory is to run multiple concurrent
mysqld processes on the same machine. This can be done by configuring
them to replicate from eachother (A->B and B->A), and then write your
application so it connects to either A or B (or C, D, etc). It can also
be done with the mysql cluster (this was talked about in the
documentation and email list extensively some months ago when I was
testing the cluster), but that may be beyond what you are trying to
accomplish. Of course, either of these solutions may put too much stress
on another part of your systems (disk, cpu), it all depends on what you
are doing.
As per the InnoDB table extender, this is taken from the manual (section
15.8):
"InnoDB increases the size of that file automatically in 8MB increments
when it runs out of space. Starting with MySQL 4.0.24 and 4.1.5, the
increment size can be configured with the option
innodb_autoextend_increment, in megabytes. The default value is 8."
Regards,
Devananda
Kasthuri Ilankamban wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
