Re: InnoDB - 16GB Data
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
InnoDB - 16GB Data
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
Re: InnoDB - 16GB Data
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
