| 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: | |
| 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: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.
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.