menntarra_34 Posted September 23, 2011 Share Posted September 23, 2011 (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 Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/ Share on other sites More sharing options...
menntarra_34 Posted September 25, 2011 Author Share Posted September 25, 2011 i still haven't found any answers, fragmented table number is the same... Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1272669 Share on other sites More sharing options...
fenway Posted September 25, 2011 Share Posted September 25, 2011 Did you run OPTIMIZE TABLE as the tuner suggests? Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1272724 Share on other sites More sharing options...
menntarra_34 Posted September 26, 2011 Author Share Posted September 26, 2011 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... Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1272745 Share on other sites More sharing options...
fenway Posted September 26, 2011 Share Posted September 26, 2011 And where, exactly, are you seeing evidence of this fragmentation? Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1272871 Share on other sites More sharing options...
menntarra_34 Posted September 26, 2011 Author Share Posted September 26, 2011 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] Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1272883 Share on other sites More sharing options...
fenway Posted September 27, 2011 Share Posted September 27, 2011 Depending on the size of the actual tables, that might no be so much. Link to comment https://forums.phpfreaks.com/topic/247712-innodb-fragmentation-unable-to-defragment/#findComment-1273239 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.