Am thinking to upgrade my DB and add more memory to it, till that time am thinking to tune it in that way after reading mysql optimization, can you please check it..
skip-locking skip-innodb query_cache_type=1 query_cache_limit=1M query_cache_size=32M max_connections=200 interactive_timeout=100 # Reduced wait_timeout to prevent idle clients holding connections. wait_timeout=15 connect_timeout=10 # Checked opened tables and adjusted accordingly after running for a while. table_cache=512 tmp_table_size=32M # Reduced it to 32 to prevent memory hogging. Also, see notes below. thread_cache=32 # Reduced it by checking current size of *.MYI files, see notes below. key_buffer=128M # Commented out the buffer sizes and keeping the default. sort_buffer_size=2M #read_buffer # 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below. read_rnd_buffer_size=1M read_buffer_size=1M # myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands. myisam_sort_buffer_size=32M # thread_concurrency = 2 * (no. of CPU) thread_concurrency=2 #logs log_slow_queries=/var/log/mysqld.slow.log long_query_time=2 Thanks On Fri, Jan 15, 2010 at 1:04 AM, Brandon Ooi <[email protected]> wrote: > I highly recommend using mysql 5 for anything new. we use 5.0.84 in > production. > for both 4 and 5, if you're only using myisam, set your key_buffer as large > as possible (maybe 50% of total available memory). Leave the rest for OS > file caches. If this is going to be a high traffic database, having enough > memory so all your indexes fit is best. The other sizes don't have to be > large. Make sure you run this on a 64bit machine/OS. > Brandon > > On Thu, Jan 14, 2010 at 2:55 PM, madunix <[email protected]> wrote: >> >> I am seeing if anyone can help me to optimize our mysql server any >> more than it is. >> I Have to do some fine tuning of MySQL 4 and here is what my.cnf file >> looks like for a intel based machine with 2GBRAM of memory 2GHz CPU, i >> would exactly know which values should i have for the following and >> other values >> key_buffer = .. >> myisam_sort_buffer_size=.. >> read_buffer_size=.. >> read_rnd_buffer_size=.. >> sort_buffer_size=.. >> Here is our my.cnf and show variables >> >> >> >> >> >> >> >> mysql> show variables; >> >> >> +---------------------------------+------------------------------------------+ >> >> | Variable_name | Value >> | >> >> >> +---------------------------------+------------------------------------------+ >> >> | concurrent_insert | ON >> | >> >> | connect_timeout | 5 >> | >> >> | datadir | /var/lib/mysql/ >> | >> >> | date_format | %Y-%m-%d >> | >> >> | datetime_format | %Y-%m-%d %H:%i:%s >> | >> >> | default_week_format | 0 >> | >> >> | delay_key_write | ON >> | >> >> | delayed_insert_limit | 100 >> | >> >> | interactive_timeout | 28800 >> | >> >> | join_buffer_size | 131072 >> | >> >> | key_buffer_size | 16777216 >> | >> >> | key_cache_age_threshold | 300 >> | >> >> | key_cache_block_size | 1024 >> | >> >> | key_cache_division_limit | 100 >> | >> >> | language | /usr/share/mysql/english/ >> | >> >> | large_files_support | ON >> | >> >> | license | GPL >> | >> >> | local_infile | ON >> | >> >> | lower_case_table_names | 0 >> | >> >> | max_allowed_packet | 1047552 >> | >> | max_binlog_cache_size | 4294967295 >> | >> | max_binlog_size | 1073741824 >> | >> | max_connect_errors | 10 >> | >> | max_connections | 100 >> | >> | max_delayed_threads | 20 >> | >> | max_error_count | 64 >> | >> | max_heap_table_size | 16777216 >> | >> | max_insert_delayed_threads | 20 >> | >> | max_join_size | 4294967295 >> | >> | max_length_for_sort_data | 1024 >> | >> | max_relay_log_size | 0 >> | >> | max_seeks_for_key | 4294967295 >> | >> | max_sort_length | 1024 >> | >> | max_tmp_tables | 32 >> | >> | max_user_connections | 0 >> | >> | max_write_lock_count | 4294967295 >> | >> | myisam_data_pointer_size | 4 >> | >> | myisam_max_extra_sort_file_size | 2147483648 >> | >> | myisam_max_sort_file_size | 2147483647 >> | >> | myisam_recover_options | OFF >> | >> | myisam_repair_threads | 1 >> | >> | myisam_sort_buffer_size | 8388608 >> | >> | query_alloc_block_size | 8192 >> | >> | query_cache_limit | 1048576 >> | >> | query_cache_min_res_unit | 4096 >> | >> | query_cache_size | 0 >> | >> | query_cache_type | ON >> | >> | query_cache_wlock_invalidate | OFF >> | >> | query_prealloc_size | 8192 >> | >> | range_alloc_block_size | 2048 >> | >> | read_buffer_size | 258048 >> | >> | read_only | OFF >> | >> read_rnd_buffer_size | 520192 >> | >> | relay_log_purge | ON >> | >> | relay_log_space_limit | 0 >> | >> | rpl_recovery_rank | 0 >> | >> | secure_auth | OFF >> | >> | server_id | 1 >> | >> | skip_external_locking | ON >> | >> | skip_networking | OFF >> | >> skip_show_database | OFF >> | >> >> slave_net_timeout | 3600 >> | >> >> | slave_transaction_retries | 0 >> | >> >> | slow_launch_time | 2 >> | >> >> | sync_frm | ON >> | >> >> | system_time_zone | EET >> | >> >> | table_cache | 512 >> | >> >> | table_type | MyISAM >> | >> >> | version | 4.1.14-standard-log >> | >> >> | version_comment | MySQL Community Edition - Standard >> (GPL) | >> >> | version_compile_machine | i686 >> | >> >> | version_compile_os | pc-linux-gnu >> | >> >> | wait_timeout | 28800 >> | >> >> >> +---------------------------------+------------------------------------------+ >> >> >> >> [mysqld] >> skip-locking >> key_buffer = 16M >> max_allowed_packet = 1M >> table_cache = 64 >> sort_buffer_size = 512K >> net_buffer_length = 8K >> read_buffer_size = 256K >> read_rnd_buffer_size = 512K >> myisam_sort_buffer_size = 8M >> >> >> [mysqldump] >> quick >> max_allowed_packet = 16M >> [mysql] >> no-auto-rehash >> [isamchk] >> key_buffer = 20M >> sort_buffer_size = 20M >> read_buffer = 2M >> write_buffer = 2M >> [myisamchk] >> key_buffer = 20M >> sort_buffer_size = 20M >> read_buffer = 2M >> write_buffer = 2M >> [mysqlhotcopy] >> interactive-timeout >> >> _______________________________________________ >> Linux-PowerEdge mailing list >> [email protected] >> https://lists.us.dell.com/mailman/listinfo/linux-poweredge >> Please read the FAQ at http://lists.us.dell.com/faq > > _______________________________________________ Linux-PowerEdge mailing list [email protected] https://lists.us.dell.com/mailman/listinfo/linux-poweredge Please read the FAQ at http://lists.us.dell.com/faq
