3

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

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
Tom G
  • 43
  • 1
  • 5

2 Answers2

2

Are there lots of clients accessing that database or just couple of simultaneous connections? Is there enough RAM or does the server swap?

The thing you describe absolutely can happen, but that would mean either a very busy workload or a very slow/unreliable I/O subsystem.

Have you tried what kind of performance bonnie++, iozone or some other benchmarking tools will give your server?

Janne Pikkarainen
  • 31,852
  • 4
  • 58
  • 81
  • RAM and Swap is fine. innodb_pool_size is taking up around 50% of the RAM - 32gigs. It is not swapping. Bonnie++ and other stress tests cannot replicate the issue. however this can't really test read/writes to the fragmented database. – Tom G Aug 21 '12 at 06:23
  • Check with iostat, maybe you don't have any IO at all while you've got ram. – GioMac Aug 21 '12 at 08:21
  • what does that mean? hardware or driver issue? could this issue be due to a misconfigured My.cnf? – Tom G Aug 22 '12 at 02:57
1

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.

Community
  • 1
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • is running 'mysqlcheck -u root -p --auto-repair --check --optimize --all-databases' the same thing? with Innodb an optimize tables would essentially re-create the entire tables as if it was dumped/restored? – Tom G Aug 22 '12 at 18:47
  • Using `mysqlcheck -u root -p --auto-repair --check --optimize --all-databases` is a little overkill because 1) mysqlcheck cannot repair InnoDB, 2) ANALYZE TABLE is executed on the fly for InnoDB ( See my post http://dba.stackexchange.com/a/6856/877 ). OPTIMIZE TABLE performs what I said in my answer + ANALYZE. That's why `ALTER TABLE mydb.mytable ENGINE=InnoDB;` is all you will ever need in terms of defragmentation of InnoDB tables. – RolandoMySQLDBA Aug 22 '12 at 18:54
  • how about mysqlcheck --analyze --optimize --all-database ? i'd rather do all tables in one shot - all are innodb. – Tom G Aug 22 '12 at 19:38
  • I supplied a InnoDB Defrag script in my answer – RolandoMySQLDBA Aug 22 '12 at 19:57
  • what is the difference between that script and running ' mysqlcheck --analyze --optimize --all-database ' ? wouldn't --optimize just map to alter table with innodb? – Tom G Aug 22 '12 at 20:07
  • `--optimize` runs `--analyze` and `--analyze` does not execute on InnoDB. I tend not to use mysqlcheck against InnoDB. My script does work on all databases. – RolandoMySQLDBA Aug 22 '12 at 20:11