my.cnf option file , which one to use ?
Posted by: Ilan Goldman
Date: March 07, 2006 02:48PM
Date: March 07, 2006 02:48PM
Hello ,
I running dedicated server for 2 web sites which those 2 web sites are in high load.
I use linux centos version 4 and mysql version 4.1.18 std
currently I don't use any option file which need to be located under :
/etc
I would like to use option file but I don't know which one to use ?
The specification of the server is :
Intel Pentium 4 3000 MHZ with 2 MB cache memory (model 630)
2 GB of ram memory (DDR SDRAM 400 MHZ non ECC non REG)
160 GB under raid 1 (mirror) SATA 7200 RPM with 8 MB cache memory
The problem is that I host only 2 web site under this server and there is very high load. There are alot's of visitors every day.
also there is 1 database which the capacity is 1.2 GB
so , about the option file , There are the follwing files , which one do recommend me to use as basis configuration option file ?
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
The storage engine of the database is : MyIsam
I can't move to InnoDB because I use IPB forum board which use MyISAM only to the best of my knowledge .
also I show the status of the mysql server under load minute :
+----------------------------+--------------+
| Variable_name | Value |
+----------------------------+--------------+
| Aborted_clients | 206 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 499611847 |
| Bytes_sent | 149084110497 |
| Com_admin_commands | 517 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 414934 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 10548 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 12451 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1594 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 728473 |
| Com_set_option | 202903 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 19 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 220694 |
| Com_update_multi | 0 |
| Connections | 271644 |
| Created_tmp_disk_tables | 247 |
| Created_tmp_files | 4274 |
| Created_tmp_tables | 2021 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 9175 |
| Handler_discover | 0 |
| Handler_read_first | 1639 |
| Handler_read_key | 9491478 |
| Handler_read_next | 89227295 |
| Handler_read_prev | 882808 |
| Handler_read_rnd | 509459 |
| Handler_read_rnd_next | 418512817 |
| Handler_rollback | 0 |
| Handler_update | 3197796 |
| Handler_write | 483454 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 6698 |
| Key_read_requests | 31885825 |
| Key_reads | 173717 |
| Key_write_requests | 751920 |
| Key_writes | 268480 |
| Max_used_connections | 59 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 104 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 4072 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 1863156 |
| Rpl_status | NULL |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 224823 |
| Select_range_check | 0 |
| Select_scan | 35336 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 42 |
| Sort_merge_passes | 2135 |
| Sort_range | 18543 |
| Sort_rows | 1437124 |
| Sort_scan | 9876 |
| Table_locks_immediate | 1059076 |
| Table_locks_waited | 6017 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 271643 |
| Threads_running | 1 |
| Uptime | 40265 |
+----------------------------+--------------+
163 rows in set (0.06 sec)
I running dedicated server for 2 web sites which those 2 web sites are in high load.
I use linux centos version 4 and mysql version 4.1.18 std
currently I don't use any option file which need to be located under :
/etc
I would like to use option file but I don't know which one to use ?
The specification of the server is :
Intel Pentium 4 3000 MHZ with 2 MB cache memory (model 630)
2 GB of ram memory (DDR SDRAM 400 MHZ non ECC non REG)
160 GB under raid 1 (mirror) SATA 7200 RPM with 8 MB cache memory
The problem is that I host only 2 web site under this server and there is very high load. There are alot's of visitors every day.
also there is 1 database which the capacity is 1.2 GB
so , about the option file , There are the follwing files , which one do recommend me to use as basis configuration option file ?
my-huge.cnf
my-innodb-heavy-4G.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
The storage engine of the database is : MyIsam
I can't move to InnoDB because I use IPB forum board which use MyISAM only to the best of my knowledge .
also I show the status of the mysql server under load minute :
+----------------------------+--------------+
| Variable_name | Value |
+----------------------------+--------------+
| Aborted_clients | 206 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 499611847 |
| Bytes_sent | 149084110497 |
| Com_admin_commands | 517 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 414934 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 10548 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 12451 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1594 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 728473 |
| Com_set_option | 202903 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 19 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 220694 |
| Com_update_multi | 0 |
| Connections | 271644 |
| Created_tmp_disk_tables | 247 |
| Created_tmp_files | 4274 |
| Created_tmp_tables | 2021 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 9175 |
| Handler_discover | 0 |
| Handler_read_first | 1639 |
| Handler_read_key | 9491478 |
| Handler_read_next | 89227295 |
| Handler_read_prev | 882808 |
| Handler_read_rnd | 509459 |
| Handler_read_rnd_next | 418512817 |
| Handler_rollback | 0 |
| Handler_update | 3197796 |
| Handler_write | 483454 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 6698 |
| Key_read_requests | 31885825 |
| Key_reads | 173717 |
| Key_write_requests | 751920 |
| Key_writes | 268480 |
| Max_used_connections | 59 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 104 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 4072 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 1863156 |
| Rpl_status | NULL |
| Select_full_join | 1 |
| Select_full_range_join | 0 |
| Select_range | 224823 |
| Select_range_check | 0 |
| Select_scan | 35336 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 42 |
| Sort_merge_passes | 2135 |
| Sort_range | 18543 |
| Sort_rows | 1437124 |
| Sort_scan | 9876 |
| Table_locks_immediate | 1059076 |
| Table_locks_waited | 6017 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 271643 |
| Threads_running | 1 |
| Uptime | 40265 |
+----------------------------+--------------+
163 rows in set (0.06 sec)
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.