| Bug #6904 | Innodb data file grows uncontrollably | ||
|---|---|---|---|
| Submitted: | 1 Dec 2004 4:51 | Modified: | 14 Oct 2007 15:49 |
| Reporter: | Michael Yudin | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1, 5.0.27 | OS: | Linux (Red Hat ES 3) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[1 Dec 2004 4:51]
Michael Yudin
[1 Dec 2004 9:50]
Heikki Tuuri
Michael, are you sure that NO other queries were running on the server at the time it processed the TRUNCATE? How long did it take to do the TRUNCATE? Did the ibdata file grow during the TRUNCATE, or AFTER it? How fast did it grow? If you afterwards do SHOW TABLE STATUS, how much free space in ibdata files does it report? Please after such huge inflation, do CREATE TABLE innodb_tablespace_monitor(a INT) TYPE=InnoDB; <wait for about 60 secs here> DROP TABLE innodb_tablespace_monitor; Repeat the same with innodb_table_monitor. Those print a lot of info to the .err log of mysqld. Send the COMPLETE .err log to me heikki dot tuuri at innodb dot com. Do NOT cut anything off the .err log. Do you have innodb_file_per_table set in my.cnf? Upcoming 4.1.8 contains some debug code that tries to track a problem where the history list starts to grow uncontrollably. When 4.1.8 is out, please upgrade. Regards, Heikki
[1 Dec 2004 20:40]
Michael Yudin
are you sure that NO other queries were running on the server at the time it processed the TRUNCATE? How long did it take to do the TRUNCATE? ** YES, I am sure. TRUNCATE never finished, I had to kill the server. Did the ibdata file grow during the TRUNCATE, or AFTER it? How fast did it grow? ** DURING TRUNCATE. It was growing extremely fast. The server was consuming most of the system resources, I saw that in "top" output. If you afterwards do SHOW TABLE STATUS, how much free space in ibdata files does it report? ** InnoDB free: 68536320 kb. This looks about right. The size of the data file is 72,842,477,568 bytes Please after such huge inflation, do CREATE TABLE innodb_tablespace_monitor(a INT) TYPE=InnoDB; <wait for about 60 secs here> DROP TABLE innodb_tablespace_monitor; Repeat the same with innodb_table_monitor. Those print a lot of info to the .err log of mysqld. Send the COMPLETE .err log to me heikki dot tuuri at innodb dot com. Do NOT cut anything off the .err log. ** DONE. Email will be on it's way. Do you have innodb_file_per_table set in my.cnf? ** NO -- Thanks, Michael
[1 Dec 2004 22:34]
Heikki Tuuri
Hi!
Putting this back to 'open' state. I got lots of feedback. Looks like the _try_extend function gets wild. I am classifying this as 'serious', because the bug does not seem to bite that often.
"
================================================
041201 15:32:06 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 4445952, free limit 101056, free extents 3
"
The output above is strange. InnoDB has only touched the first 1 579 MB from
the ibdata file start. But it has extended the file to 69 468 MB!
This could be caused by a bug that makes InnoDB to extend the file even
though free space is available. Or a bug that causes InnoDB to request
billions of 1 MB 'extents' in some operation.
If the function below gets a nonsensical argument value in n_ext, that could
explain the runaway extension. I may add diagnostic prints to it in 4.1.8.
ibool
fsp_reserve_free_extents(
/*=====================*/
/* out: TRUE if we were able to make the reservation
*/
ulint* n_reserved,/* out: number of extents actually reserved; if
we
return TRUE and the tablespace size is < 64 pages,
then this can be 0, otherwise it is n_ext */
ulint space, /* in: space id */
ulint n_ext, /* in: number of extents to reserve */
ulint alloc_type,/* in: FSP_NORMAL, FSP_UNDO, or FSP_CLEANING */
mtr_t* mtr) /* in: mtr */
Regards,
Heikki
[1 Dec 2004 23:30]
Heikki Tuuri
Hi! The problem found. The value of the auto-extend increment in my.cnf is given in units of 1 MB. If you specify the value as 100M, it will try to add 100 million 1 MB increments at a time! Regards, Heikki
[2 Dec 2004 11:13]
Marko Mäkelä
To prevent this from happening in the future, I introduced a maximum limit of innodb_autoextend_increment=1000 as requested by Heikki. The fix should be included in 4.1.8 and 5.0.3.
[14 Sep 2007 6:35]
JinRong Ye
innodb status and monitor output
Attachment: innodb.txt (text/plain), 10.81 KiB.
[14 Sep 2007 6:37]
JinRong Ye
Hi, Heikki，i also meet this problem. The innodb data file ibdata1 grew from 10MB to 100GB，there are parameters about innodb: +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | innodb_additional_mem_pool_size | 16777216 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 2147483648 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:1024M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 120 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 16777216 | | innodb_log_file_size | 419430400 | | innodb_log_files_in_group | 3 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 16 | | innodb_thread_sleep_delay | 10000 | +---------------------------------+--------------------------+ and the output of "SHOW ENGINE INNODB STATUS" and innodb_tablespace_monitor were attched above. the output in error log were up to 900MB+,so i can't paste them all,sorry thanks much
[14 Sep 2007 6:39]
JinRong Ye
the old parameter innodb_data_file_path were: | innodb_data_file_path | ibdata1:10M:autoextend | sorry
[14 Sep 2007 6:40]
JinRong Ye
the version of mysql is: 5.0.27-standard-log thanks
[14 Sep 2007 14:59]
Heikki Tuuri
JinRong, how did it grow from 10 MB to 100 GB? How quickly? Did you insert/update many rows? The .err log printout shows a hang in InnoDB. Please post one complete printout of the hang. Regards, Heikki
[14 Sep 2007 15:49]
MySQL Verification Team
Changing to need feedback according Heikki's last comment question.
[17 Sep 2007 3:01]
JinRong Ye
the innodb hung up error log
Attachment: innodb_error_070912.txt (text/plain), 10.91 KiB.
[17 Sep 2007 3:02]
JinRong Ye
Hi, Heikki, i am sorry for so late to attach the error log. Thanks much
[5 Oct 2007 12:57]
Marko Mäkelä
JinRong, The first three lines of your attached error log are as follows. InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. The rest of the log only shows the intentional assertion failure and the crash recovery. We are interested in all diagnostic printout preceding this crash.
[9 Oct 2007 5:54]
JinRong Ye
Marko Mäkelä there was no more error message before server crashed,sorry
[14 Oct 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
