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
