| Bug #15378 | With many .ibd files, high CPU usage when opening a new .ibd file | ||
|---|---|---|---|
| Submitted: | 1 Dec 2005 9:12 | Modified: | 8 Dec 2005 17:38 |
| Reporter: | Heikki Tuuri | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.1, 5.0 | OS: | Any (All) |
| Assigned to: | Marko Mäkelä | CPU Architecture: | Any |
[1 Dec 2005 9:14]
Heikki Tuuri
Workaround: increase innodb_open_files in my.cnf.
[2 Dec 2005 8:14]
James Day
Good catch. Interesting. Can this happen if there are 120,000 tables in 600 databases but the only SQL since server startup has been inserting one row at a start to one table using a script which loops through: myql -D test -e "insert into foo values (1)" where foo is CREATE TABLE foo (i int) engine=innodb. innodb_open_files is default when I've experimented with this.
[2 Dec 2005 8:19]
James Day
I suppose that the symptom of this on the stack would be lots of futex(whatever, FUTEX_WAIT, n) calls as the other threads wait for the mutex?
[2 Dec 2005 9:25]
Heikki Tuuri
James, this should not happen if you have only used 1 table after the mysqld startup. What does SHOW INNODB STATUS print during that peak of CPU usage? Does 'top' show the CPU being used in 'system' or 'user'? Regards, Heikki
[8 Dec 2005 17:38]
Heikki Tuuri
Oops, I misread the code. If there is success in closing files, it calls again fil_try_to_close_file_in_LRU()! If no success, it will go to sleep for 20 milliseconds. No bug here. I am closing this bug report.
Description: If there are many .ibd files, and modifications to many tables, CPU usage can shoot high for a few seconds. The high CPU usage is probably explained by: /************************************************************************ Tries to close a file in the LRU list. The caller must hold the fil_sys mutex. */ static ibool fil_try_to_close_file_in_LRU( /*=========================*/ /* out: TRUE if success, FALSE if should retry later; since i/o's generally complete in < 100 ms, and as InnoDB writes at most 128 pages from the buffer pool in a batch, and then immediately flushes the files, there is a good chance that the next time we find a suitable node from the LRU list */ ibool print_info) /* in: if TRUE, prints information why it cannot close a file */ { ... /*********************************************************************** Reserves the fil_system mutex and tries to make sure we can open at least one file while holding it. This should be called before calling fil_node_prepare_for_io(), because that function may need to open a file. */ static void fil_mutex_enter_and_prepare_for_io( /*===============================*/ ulint space_id) /* in: space id */ { ... close_more: success = fil_try_to_close_file_in_LRU(print_info); if (success && system->n_open >= system->max_n_open) { goto close_more; } ... -------------------------------------------------------------- Note that there is a busy loop, though it should release &(system->mutex) and sleep for a while! How to repeat: Do not know. Suggested fix: Release the mutex and sleep 10 milliseconds.