Hi Guys i have a dedicated mysql-server and neeed tips and sugestion to optimize its to a better performance.
1-) Here i have results from mysqltunner >> MySQLTuner 1.0.1 - Major Hayden <ma...@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: toscaoSo Please enter your MySQL administrative password: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.4.3-beta-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 458M (Tables: 349) [--] Data in InnoDB tables: 15G (Tables: 73) [!!] Total fragmented tables: 47 -------- Performance Metrics ------------------------------------------------- [--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX: 77B) [--] Reads / Writes: 31% / 69% [--] Total buffers: 15.5G global + 16.2M per thread (50 max threads) [OK] Maximum possible memory usage: 16.3G (69% of installed RAM) [OK] Slow queries: 0% (386/334M) [OK] Highest usage of available connections: 46% (23/50) [OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M [OK] Key buffer hit rate: 100.0% (78M cached / 22K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts) [OK] Temporary tables created on disk: 0% (548 on disk / 1M total) [OK] Thread cache hit rate: 99% (23 created / 153K connections) [OK] Table cache hit rate: 44% (467 open / 1K opened) [OK] Open file limit used: 1% (684/65K) [OK] Table locks acquired immediately: 99% (320M immediate / 320M locks) [!!] InnoDB data size / buffer pool: 15.5G/15.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Variables to adjust: query_cache_size (>= 8M) innodb_buffer_pool_size (>= 15G) 2-) And here is my dedicate server i have (24GB ): 1 [ 0.0%] Tasks: 71 total, 2 running 2 [||||||| 7.8%] Load average: 0.11 0.18 0.19 3 [| 0.7%] Uptime: 62 days, 19:24:09 4 [| 0.7%] Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||16878/24165MB] Swp[| 0/5122MB] 3-) And my.cnf vim .my.cnf [client] #password = [your_password] port = 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] #large-pages # generic configuration options port = 3306 socket = /tmp/mysql.sock skip-locking skip-external-locking datadir = /disk3/Datareal/oficial/mysql net_buffer_length = 1024K join_buffer_size = 4M sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M table_cache = 1000 max_allowed_packet = 160M max_connections=50 max_user_connections=200 key_buffer = 300M key_buffer_size = 300M #thread_cache = 400 thread_stack = 192K thread_cache_size = 96 thread_concurrency = 8 #thread_stack = 128K default-character-set = utf8 innodb_flush_method=O_DSYNC innodb_buffer_pool_size= 15G innodb_additional_mem_pool_size=128M innodb_log_file_size= 256M innodb_log_buffer_size=72M innodb_flush_log_at_trx_commit=0 innodb_thread_concurrency=8 innodb_file_per_table=1 innodb_log_files_in_group=2 innodb_table_locks=0 innodb_lock_wait_timeout = 50 "~/.my.cnf" 72L, 1570C Thanks guys for any tips/suggestion ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
