Best Configuration on Production Server
am 19.03.2010 08:27:24 von Abdul Mohid KhanHi list,
I am having a problem with one of our production server. The performance
of the server get decrease considerably in production server. We are
using mysql data base of a Authentication Server. When we have tested
same server on test environment (2GB RAM, 2 CPU) we are getting 5 ms but
in production same request is taking 50 ms.
The Production sever configuration
No of CPU : 8
RAM : 8 GB
OS : Cent OS
Here i am giving the show variable out put on the system. Please help to
do the best configuration for my mysql server on production. We are
using both MyISM and InnoDB engine.
| auto_increment_offset |
1 |
| automatic_sp_privileges |
ON |
| back_log |
50 |
| basedir |
/usr/ |
| bdb_cache_size |
8384512 |
| bdb_home |
/var/lib/mysql/ |
| bdb_log_buffer_size |
262144 |
| bdb_logdir
| |
| bdb_max_lock |
10000 |
| bdb_shared_data |
OFF |
| bdb_tmpdir |
/tmp/ |
| binlog_cache_size |
32768 |
| bulk_insert_buffer_size |
8388608 |
| character_set_client |
latin1 |
| character_set_connection |
latin1 |
| character_set_database |
latin1 |
| character_set_filesystem |
binary |
| character_set_results |
latin1 |
| character_set_server |
latin1 |
| character_set_system |
utf8 |
| character_sets_dir |
/usr/share/mysql/charsets/ |
| collation_connection |
latin1_swedish_ci |
| collation_database |
latin1_swedish_ci |
| collation_server |
latin1_swedish_ci |
| completion_type |
0 |
| concurrent_insert |
1 |
| connect_timeout |
10 |
| 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 |
| delayed_insert_timeout |
300 |
| delayed_queue_size |
1000 |
| div_precision_increment |
4 |
| keep_files_on_create |
OFF |
| engine_condition_pushdown |
OFF |
| expire_logs_days |
0 |
| flush |
OFF |
| flush_time |
0 |
| ft_boolean_syntax | +
-><()~*:""&| |
| ft_max_word_len |
84 |
| ft_min_word_len |
4 |
| ft_query_expansion_limit |
20 |
| ft_stopword_file |
(built-in) |
| group_concat_max_len |
1024 |
| have_archive |
NO |
| have_bdb |
YES |
| have_blackhole_engine |
NO |
| have_compress |
YES |
| have_crypt |
YES |
| have_csv |
NO |
| have_dynamic_loading |
YES |
| have_example_engine |
NO |
| have_federated_engine |
NO |
| have_geometry |
YES |
| have_innodb |
YES |
| have_isam |
NO |
| have_merge_engine |
YES |
| have_ndbcluster |
NO |
| have_openssl |
DISABLED |
| have_ssl |
DISABLED |
| have_query_cache |
YES |
| have_raid |
NO |
| have_rtree_keys |
YES |
| have_symlink |
YES |
| hostname | server
|
| init_connect
| |
| init_file
| |
| init_slave
| |
| innodb_additional_mem_pool_size |
1048576 |
| innodb_autoextend_increment |
8 |
| innodb_buffer_pool_awe_mem_mb |
0 |
| innodb_buffer_pool_size |
8388608 |
| innodb_checksums |
ON |
| innodb_commit_concurrency |
0 |
| innodb_concurrency_tickets |
500 |
| innodb_data_file_path |
ibdata1:10M:autoextend |
| innodb_data_home_dir
| |
| innodb_adaptive_hash_index |
ON |
| innodb_doublewrite |
ON |
| innodb_fast_shutdown |
1 |
| innodb_file_io_threads |
4 |
| innodb_file_per_table |
OFF |
| innodb_flush_log_at_trx_commit |
1 |
| innodb_flush_method
| |
| innodb_force_recovery |
0 |
| innodb_lock_wait_timeout |
50 |
| innodb_locks_unsafe_for_binlog |
OFF |
| innodb_log_arch_dir
| |
| innodb_log_archive |
OFF |
| innodb_log_buffer_size |
1048576 |
| innodb_log_file_size |
5242880 |
| innodb_log_files_in_group |
2 |
| innodb_log_group_home_dir |
../ |
| innodb_max_dirty_pages_pct |
90 |
| innodb_max_purge_lag |
0 |
| innodb_mirrored_log_groups |
1 |
| innodb_open_files |
300 |
| innodb_rollback_on_timeout |
OFF |
| innodb_support_xa |
ON |
| innodb_sync_spin_loops |
20 |
| innodb_table_locks |
ON |
| innodb_thread_concurrency |
8 |
| innodb_thread_sleep_delay |
10000 |
| interactive_timeout |
28800 |
| join_buffer_size |
131072 |
| key_buffer_size |
268435456 |
| key_cache_age_threshold |
300 |
| key_cache_block_size |
1024 |
| key_cache_division_limit |
100 |
| language |
/usr/share/mysql/english/ |
| large_files_support |
ON |
| large_page_size |
0 |
| large_pages |
OFF |
| lc_time_names |
en_US |
| license |
GPL |
| local_infile |
ON |
| locked_in_memory |
OFF |
| log |
OFF |
| log_bin |
OFF |
| log_bin_trust_function_creators |
OFF |
| log_error
| |
| log_queries_not_using_indexes |
OFF |
| log_slave_updates |
OFF |
| log_slow_queries |
OFF |
| log_warnings |
1 |
| long_query_time |
10 |
| low_priority_updates |
OFF |
| lower_case_file_system |
OFF |
| lower_case_table_names |
1 |
| max_allowed_packet |
1048576 |
| max_binlog_cache_size |
18446744073709547520 |
| 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 |
18446744073709551615 |
| max_length_for_sort_data |
1024 |
| max_prepared_stmt_count |
16382 |
| max_relay_log_size |
0 |
| max_seeks_for_key |
18446744073709551615 |
| max_sort_length |
1024 |
| max_sp_recursion_depth |
0 |
| max_tmp_tables |
32 |
| max_user_connections |
0 |
| max_write_lock_count |
18446744073709551615 |
| multi_range_count |
256 |
| myisam_data_pointer_size |
6 |
| myisam_max_sort_file_size |
9223372036853727232 |
| myisam_recover_options |
OFF |
| myisam_repair_threads |
1 |
| myisam_sort_buffer_size |
8388608 |
| myisam_stats_method |
nulls_unequal |
| net_buffer_length |
16384 |
| net_read_timeout |
30 |
| net_retry_count |
10 |
| net_write_timeout |
60 |
| new |
OFF |
| old_passwords |
ON |
| open_files_limit |
1024 |
| optimizer_prune_level |
1 |
| optimizer_search_depth |
62 |
| pid_file |
/var/run/mysqld/mysqld.pid |
| plugin_dir
| |
| port |
3306 |
| preload_buffer_size |
32768 |
| profiling |
OFF |
| profiling_history_size |
15 |
| protocol_version |
10 |
| query_alloc_block_size |
8192 |
| query_cache_limit |
1048576 |
| query_cache_min_res_unit |
4096 |
| query_cache_size |
16777216 |
| query_cache_type |
ON |
| query_cache_wlock_invalidate |
OFF |
| query_prealloc_size |
8192 |
| range_alloc_block_size |
4096 |
| read_buffer_size |
131072 |
| read_only |
OFF |
| read_rnd_buffer_size |
262144 |
| relay_log
| |
| relay_log_index
| |
| relay_log_info_file |
relay-log.info |
| relay_log_purge |
ON |
| relay_log_space_limit |
0 |
| rpl_recovery_rank |
0 |
| secure_auth |
OFF |
| secure_file_priv
| |
| server_id |
0 |
| skip_external_locking |
ON |
| skip_networking |
OFF |
| skip_show_database |
OFF |
| slave_compressed_protocol |
OFF |
| slave_load_tmpdir |
/tmp/ |
| slave_net_timeout |
3600 |
| slave_skip_errors |
OFF |
| slave_transaction_retries |
10 |
| slow_launch_time |
2 |
| socket |
/var/lib/mysql/mysql.sock |
| sort_buffer_size |
2097144 |
| sql_big_selects |
ON |
| sql_mode
| |
| sql_notes |
ON |
| sql_warnings |
OFF |
| ssl_ca
| |
| ssl_capath
| |
| ssl_cert
| |
| ssl_cipher
| |
| ssl_key
| |
| storage_engine |
MyISAM |
| sync_binlog |
0 |
| sync_frm |
ON |
| system_time_zone |
IST |
| table_cache |
64 |
| table_lock_wait_timeout |
50 |
| table_type |
MyISAM |
| thread_cache_size |
0 |
| thread_stack |
262144 |
| time_format |
%H:%i:%s |
| time_zone |
SYSTEM |
| timed_mutexes |
OFF |
| tmp_table_size |
33554432 |
| tmpdir |
/tmp/ |
| transaction_alloc_block_size |
8192 |
| transaction_prealloc_size |
4096 |
| tx_isolation |
REPEATABLE-READ |
| updatable_views_with_limit |
YES |
| version |
5.0.77 |
| version_bdb | Sleepycat Software: Berkeley DB
4.1.24: (January 29, 2009) |
| version_comment | Source
distribution |
| version_compile_machine |
x86_64 |
| version_compile_os |
redhat-linux-gnu |
| wait_timeout | 28800
Thanks in Advance
Abdul Mohid Khan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org