INNODB fragmentation, unable to defragment...

[menntarra_34]

(NOTE: i recently converted my old myisam database tables to innodb, and this is the result)

i found a strange problem: nearly all new innodb tables are reported to be fragmented, i tried to

1.) optimize them

2.) repair them

3.) ALTER TABLE table_name Engine= INNODB;

 

The third should have solved it ( i read that in innodb the alter table method works...)

But still none of them got repaired...

 

I tried to find some kind of order of the fragmented tables, but found none: i mean there are tables fragmented with only id field in them. There are tables with id and varchar fields etc...

I also tried with one of them to convert back to myisam and repair, than convert back to innodb without success...

 

I also saw in mysql doc, that dumping table, droping it and importing back does the same so, i gave that a try with one table but didn't help. (http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html)

 

mysqltuner:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 373)
[--] Data in InnoDB tables: 9G (Tables: 186)
[!!] Total fragmented tables: 161

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3m 26s (30K q [149.500 qps], 1K conn, TX: 389M, RX: 24M)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 19.4G global + 12.2M per thread (512 max threads)
[OK] Maximum possible memory usage: 25.5G (81% of installed RAM)
[OK] Slow queries: 0% (42/30K)
[OK] Highest usage of available connections: 34% (176/512)
[OK] Key buffer size / total MyISAM indexes: 3.0G/2.9G
[!!] Key buffer hit rate: 91.0% (202K cached / 18K reads)
[OK] Query cache efficiency: 57.4% (14K cached / 24K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 917 sorts)
[OK] Temporary tables created on disk: 10% (73 on disk / 688 total)
[OK] Thread cache hit rate: 88% (176 created / 1K connections)
[OK] Table cache hit rate: 98% (586 open / 593 opened)
[OK] Open file limit used: 3% (304/7K)
[OK] Table locks acquired immediately: 99% (22K immediate / 22K locks)
[OK] InnoDB data size / buffer pool: 9.4G/14.7G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate

[menntarra_34]

i still haven't found any answers, fragmented table number is the same...

[fenway]

Did you run OPTIMIZE TABLE as the tuner suggests?

[menntarra_34]

Of course! But with innodb instead of optimize, you should defragment them by ALTER TABLE tablename ENGINE=InnoDB;

Anyway i tried bith optimize, and alter table, none of them works...

[fenway]

And where, exactly, are you seeing evidence of this fragmentation?

[menntarra_34]

well i already showed mysqltuner results, and if i  run this command:

select TABLE_NAME,Data_free
from information_schema.TABLES
where TABLE_SCHEMA NOT IN ('information_schema','mysql')
and Data_free > 0;

 

/I attached picture of output of the command/

 

It shows the same number, about 160-168 tables fragmented...

 

Whatsmore,  i checked indexes on these tables and the cardinality is constantly changing on all indexes on these fragmented tables.

 

I also checked a table, and tried to find fragmented part of it, by trying to delete first part of the table(fragmentation dissapeard), and later i imported the original back again and deleted the other part, and strangely fragmentation dissapeared again. So the only thing what was important is to delete a spesific amount of data of the table. maybe it has something to do with indexes?

 

[attachment deleted by admin]

[fenway]

Depending on the size of the actual tables, that might no be so much.