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:
Under the hodd, it will do the following:
If you would like mass defrag all InnoDB Tables, just run this:
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.