Re: Best Configuration on Production Server
No Gavin server is hardly having few records because it is not live we are just testing .. after all set we will make live. *Please note that system is having 8GB of RAM and 8 CPU* My.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql lower-case-table-name =1 key_buffer = 384M query_cache_size = 128M sort_buffer_size = 32M myisam_sort_buffer_size = 32M query_cache_limit = 4M tmp_table_size = 128M # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid On 3/22/2010 2:48 PM, John Daisley wrote: Are you sure its not server load? We frequently have queries on our DEV system running faster than LIVE simply because the LIVE environment is getting hammered! Can you post your my.cnf file? On Mon, Mar 22, 2010 at 4:38 AM, Abdul Mohid Khan abdulmohid.k...@magnaquest.com mailto:abdulmohid.k...@magnaquest.com wrote: 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
Re: Best Configuration on Production Server
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| 1 | | 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
RE: Best Configuration on Production Server
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| 1 | | 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