InnoDB Fragmentation ???????
Posted: Sun Oct 10, 2010 3:48 am
Hello i did some settings on my mysql server and follow the following steps:
1. Add innodb_file_per_table to my.cnf
2. Startup mysql and optimize all tables
3. mysqldump all DBs
4. Stop mysql
5. delete ibdata1 and logfiles
6. Startup mysql and import from mysqldump
Configuration used:
Hardware(running LS + CB + GS + Mysql):
1. Add innodb_file_per_table to my.cnf
2. Startup mysql and optimize all tables
3. mysqldump all DBs
4. Stop mysql
5. delete ibdata1 and logfiles
6. Startup mysql and import from mysqldump
Configuration used:
Code: Select all
default-storage-engine=INNODBmax_connections=600query_cache_size=0table_cache=6000tmp_table_size=64Mthread_cache_size=8log_slow_queries #*** MyISAM Specific options myisam_max_sort_file_size=100Gmyisam_max_extra_sort_file_size=100Gmyisam_sort_buffer_size=30Mkey_buffer_size=350Mread_buffer_size=64Kread_rnd_buffer_size=256Ksort_buffer_size=256k #*** INNODB Specific options *** innodb_additional_mem_pool_size = 8Minnodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECTinnodb_log_buffer_size = 16Minnodb_buffer_pool_size = 1Ginnodb_data_file_path = ibdata1:10M:autoextendinnodb_autoextend_increment = 8innodb_max_dirty_pages_pct = 90innodb_file_io_threads = 4innodb_log_file_size = 256Minnodb_thread_concurrency = 16innodb_file_per_table = 1memlock And i still have fragmentation in InnoDB filesDual Intel Xeon 5520 2.26 GHz Quad Core Processor
6 GB RAM DDR3
2x 146 GB 15K RPM SCSI/SAS HDD
100 Mbps Network Uplink
Ubuntu GNU/Linux 9.04 x64
Someone can help me ?-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 452M (Tables: 150)
[!!] Total fragmented tables: 23
-------- Performance Metrics -------------------------------------------------
[--] Up for: 33m 31s (94K q [47.152 qps], 133 conn, TX: 64M, RX: 11M)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 1.4G global + 960.0K per thread (600 max threads)
[OK] Maximum possible memory usage: 1.9G (33% of installed RAM)
[OK] Slow queries: 0% (1/94K)
[OK] Highest usage of available connections: 6% (41/600)
[OK] Key buffer size / total MyISAM indexes: 350.0M/224.9M
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 15% (48 on disk / 311 total)
[OK] Thread cache hit rate: 69% (41 created / 133 connections)
[OK] Table cache hit rate: 26% (189 open / 715 opened)
[OK] Open file limit used: 0% (47/12K)
[OK] Table locks acquired immediately: 100% (94K immediate / 94K locks)
[OK] InnoDB data size / buffer pool: 452.4M/1.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_size (>= 8M)
