Also, if you have read heavy workload, you might want to try using and tuning your query cache. Start off with something like 32M and incrementally tune it. You can monitor some query cache related server variables. Kyong
On Sat, Apr 10, 2010 at 4:28 PM, Rob Wultsch <wult...@gmail.com> wrote: > On Sat, Apr 10, 2010 at 12:10 AM, Junior Ortis <jror...@gmail.com> wrote: >> 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 ! >> > > First, most performance comes from optimized table structures, index, > and queries. Server tuning will not get you you all that much > additions performance, if you have a semi sane configuration. What is > your current bottleneck or performance problem? > > Anyways... here are some reactions: > > innodb_flush_log_at_trx_commit=0 ... THIS MEANS YOU CAN LOSE COMMITTED > TRANSACTIONS. Read up on this. > > "innodb_flush_method=O_DSYNC" > Any particular reason you aren't using O_DIRECT ? Read up on this. > > Why do you not have skip-name-resolve on? Read up on this. > > innodb_thread_concurrency... As you are running 5.4 you can probably > set this to 0. Assuming you have 4 cores or less I wouldn't worry too > much about this. > > I do not see log-bin... which would indicate that you don't have > binary logging on. What is your disaster recover plan? > > To create an optimal cnf would require more knowledge about your > workload and your hardware. > > -- > Rob Wultsch > wult...@gmail.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=kykim...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
