Ubuntu – Can fragmented MySQL innodb tables cause I/O issues? “task hung for 120 seconds” crashes

innodbioMySQLUbuntuxen

Large innodb databases with load spikes seem to be causing random crashes with "task hung for 120 seconds" in the console. There are no logs being written to the system during these crashes.
The innodb tables are fairly large, 20+ gigs in storage.

Could table fragmentation with heavy I/O loads on Ubuntu 10.04 with Kernel 2.6.36 and 2.6.38-15 64 bit cause random system crashes?

We are looking into issues with random system crashes running off large innodb tables hosted on a "dedicated baremetal" vps hosted servers.

MySQL version is 5.1.

Here is the results of: "SELECT data_length,index_length,(data_length+index_length)/power(1024,3) GB FROM information_schema.tables WHERE ENGINE='InnoDB' ORDER BY data_length+index_length DESC LIMIT 10;":

+-------------+--------------+-------------------+
| data_length | index_length | GB                |
+-------------+--------------+-------------------+
| 14758707200 |  17220501504 |   29.782958984375 |
|  9456762880 |  16465543168 |  24.1420288085938 |
| 16983785472 |   6954041344 |  22.2938385009766 |
|  5625610240 |   2997813248 |  8.03118896484375 |
|  3694133248 |   1730150400 |      5.0517578125 |
|  2031091712 |     35209216 |  1.92439270019531 |
|  1357905920 |    706740224 |      1.9228515625 |
|  1107312640 |    320356352 |  1.32962036132812 |
|   637534208 |    760889344 |  1.30238342285156 |
|   488636416 |    260620288 | 0.697799682617188 |
+-------------+--------------+-------------------+

Open files = 300.

tia

Best Answer

It looks like you have huge tables

If you would like to defragment an InnoDB table mydb.mytable just run the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Under the hodd, it will do the following:

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytableold;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytableold;

If you would like mass defrag all InnoDB Tables, just run this:

echo "SET SQL_LOG_BIN = 0;" > /root/DefragInnoDB.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
mysql ${MYSQL_CONN} -ANe"${SQL}" >> /root/DefragInnoDB.sql
mysql ${MYSQL_CONN} -A < /root/DefragInnoDB.sql

You may not need to defrag InnoDB that often. Check out my post on the DBA StackExchange to determine if any one InnoDB table needs to be defragmented.

On a sidenote, some of the tables look like there is more space consumed by the index than by the data. After run the defrag on those tables, go back and look over the indexes in each table. Try to determine if there are any unused indexes and remove them.

You have 300 as the innodb_open_files. You can raise it higher but don't go crazy setting it too high

See the following posts in innodb_open_files

I would also like to recommend that you upgrade ro MySQL 5.5 where you can raise innodb_read_io_threads and innodb_write_io_threads for better CPU utilization by the InnoDB Storage Engine.

Related Topic