Hi Gavin,
Same setup i am using in both production server and test environment.
But the Problem is in test environment one operation is taking 5ms ...
but in production(better h/w configuration) 50 ms....
The Operation which i am talking about is consist of one select, 2
insertion (MyISM) and one update (InnoDB).
Thanks in Advance
On 3/19/2010 11:14 PM, Gavin Towey wrote:
What changed between your test that took 5ms and the current production system?
-----Original Message-----
From: Abdul Mohid Khan [mailto:abdulmohid.k...@magnaquest.com]
Sent: Friday, March 19, 2010 12:27 AM
To: mysql@lists.mysql.com
Cc: Abdul Mohid Khan
Subject: Best Configuration on Production Server
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@ffn.com
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org