Bug #15242 mysqld's memory is increased by problem.
Submitted: 25 Nov 2005 6:24 Modified: 25 Nov 2005 8:53
Reporter: EUN KEYONG YUN Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:mysql-standard-4.1.14-pc-linux-gnu-i686 OS:Linux (Red Hat Enterprise Linux AS rele)
Assigned to: CPU Architecture:Any

[25 Nov 2005 6:24] EUN KEYONG YUN
Description:
kernel vesion : 2.4.21-4.ELsmp 
Until September, It had no problem.
But after October, It have the problem.
In fact, at first server version is 4.1.10
but, now 4.1.14 

 20:38:04  up 36 days, 17:54,  2 users,  load average: 2.49, 2.79, 2.97
146 processes: 144 sleeping, 2 running, 0 zombie, 0 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total    7.9%    0.0%    1.1%   0.0%     0.2%   18.2%   72.4%
           cpu00    1.9%    0.0%    0.1%   0.5%     0.5%   10.5%   86.0%
....
Mem:  5932076k av, 5913336k used,   18740k free,       0k shrd,   28708k buff
                   4594036k actv,  570428k in_d,   95984k in_c
Swap: 2044072k av,  375108k used, 1668964k free                 3477328k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
25416 XXXX      15   0 2198M 2.1G  3008 S     1.5 37.5   0:10   2 mysqld
24940 XXXX      15   0 2198M 2.1G  3008 S     1.0 37.5   6:33   4 mysqld
25309 XXXX      15   0 2198M 2.1G  3008 R     1.0 37.5   1:09   3 mysqld
25279 XXXX      15   0 2198M 2.1G  3008 S     0.8 37.5   1:05   7 mysqld
25317 XXXX      15   0 2198M 2.1G  3008 S     0.8 37.5   1:25   5 mysqld
25379 XXXX      15   0 2198M 2.1G  3008 S     0.7 37.5   0:34   3 mysqld
25368 XXXX      16   0 2198M 2.1G  3008 S     0.6 37.5   0:48   1 mysqld
25336 XXXX      16   0 2198M 2.1G  3008 S     0.3 37.5   0:15   5 mysqld

this is a top command's data. as you can see, mysqld's memory is over 2G.
memory size is increasing until 2.5G
After mysqld's memory increase 2.5G, mysqld accur the fatal error.

this is error message.

051004 14:06:16  InnoDB: Fatal error: cannot allocate 1064960 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 2022148345 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.
A mysqld process already exists at  Tue Oct 4 14:47:31 JST 2005
051004 14:48:15  mysqld started
051004 14:48:15 [Warning] Changed limits: max_open_files: 1024  max_connections: 384  table_cache: 315
051004 14:48:15 [Warning] Asked for 196608 thread stack, but got 126976
051004 14:48:17  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051004 14:48:17  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 55 2064594704.
InnoDB: Doing recovery: scanned up to log sequence number 55 2069725122
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 0 3963294720
051004 14:48:18  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 3963294423, 1 rows to undo
InnoDB: Rolling back of trx id 0 3963294423 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file position 0 111833022, file name ./hpdb-bin.000013
051004 14:49:54  InnoDB: Flushing modified pages from the buffer pool...
051004 14:50:01  InnoDB: Started; log sequence number 55 2069725122

When the error is accured, mysqld dosen't work and mysqladmin dosen't work.
So, I use kill -9 ....
the DB have 2 slave db server.
But, replication DB Servers have no problem.
Only master DB have the problem.
So, I checked the the DB's status.....
Open_tables in DB's status is increasing the count until about 2000 over.

How to repeat:
this is a top command's data. as you can see, mysqld's memory is over 2G.
memory size is increasing until 2.5G
After mysqld's memory increase 2.5G, mysqld accur the fatal error and 
finally, mysqld doesn't work.
Now, I restart in a once a 3~4days.
[25 Nov 2005 6:28] EUN KEYONG YUN
DB's status and variable

Attachment: status&variables.txt (text/plain), 48.16 KiB.

[25 Nov 2005 8:05] Heikki Tuuri
Hi!

Please post a few outputs of:

SHOW INNODB STATUS\G

during the problem.

This looks like a memory leak in InnoDB.

Do you have many InnoDB tables? If you have > 100 000 InnoDB tables, memory usage in the InnoDB data dictionary cache could get as big as this.

Regards,

Heikki
[25 Nov 2005 8:08] Heikki Tuuri
Hi!

Please post your my.cnf.

Regards,

Heikki
[25 Nov 2005 8:12] Valeriy Kravchuk
Thank you for a problem report. 

Please, describe the hardware you use (EM64T, AMD64, IA64)? You may need to use the other binary to be able to overcome that 2.5G limit (http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.15-unknown-linux-gnu-x86_6..., for example).

You my.cnf content may be useful too. Some setting may need adjustments.
[25 Nov 2005 8:14] Hartmut Holzgraefe
You are overcommiting memory, see 

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

  Warning: On 32-bit GNU/Linux x86, you must be careful not to set memory usage too 
  high. glibc may allow the process heap to grow over thread stacks, which crashes your 
  server. It is a risk if the value of the following expression is close to or exceeds 2GB:

  innodb_buffer_pool_size
  + key_buffer_size 
  + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
  + max_connections*2MB

  Each thread uses a stack (often 2MB, but only 256KB in MySQL AB binaries) 
  and in the   worst case also uses sort_buffer_size + read_buffer_size additional  
  memory. 

Your innodb_pool_buffer_size is 1.8GB alone, together with the other buffers
this easily sums up to the operating systems per-process memory limit

Additional memory is even required whenever a temporary table is created for 
sorting which happens quite a lot on your server.
[25 Nov 2005 8:45] EUN KEYONG YUN
my.cnf

Attachment: my.cnf (application/octet-stream, text), 14.05 KiB.

[25 Nov 2005 8:46] EUN KEYONG YUN
data of innodb's status

Attachment: INNODBMONITOROUTPUT.txt (text/plain), 49.68 KiB.

[25 Nov 2005 8:53] EUN KEYONG YUN
First of all,  Thank you for your comments and your informations.

I uploaded my.cnf & data of innodb status.

There are 100 under innodb tables in db.
and
cpu is  Intel(R) Xeon(TM) MP CPU 2.20GHz.