List:General Discussion« Previous MessageNext Message »
From:Abdul Mohid Khan Date:March 19 2010 7:27am
Subject:Best Configuration on Production Server
View as plain text  
Hi 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
Thread
Best Configuration on Production ServerAbdul Mohid Khan19 Mar
  • RE: Best Configuration on Production ServerGavin Towey19 Mar
    • Re: Best Configuration on Production ServerAbdul Mohid Khan22 Mar
Re: Best Configuration on Production ServerAbdul Mohid Khan22 Mar