Home » Performance » MySQL » MySQL Optimal Settings
| |
| Re: MySQL Optimal Settings [message #1215 is a reply to message #1192 ] |
Sun, 06 May 2007 02:56   |
 mimayls
Messages: 11
Registered: May 2007 |
Junior Member |
|
|
Thanks linuxrunner for the replay 
1.Yes i do checked slow queries,
2.I have few queries of 3-5 seconds execution time.
3.I have optimized some of them
4.The are 2 tables , one OrderMaster, and other OrderDetail
the SQL running on this tables are slower.
5.They have index for the search fields in this tables
6.The master tbl have 116,524 Recs,
and detail tbl have 453,465 recs
7.And also we have applications accessing these tables in
regular time interval
8. So these tables are busy.
9.Since these tables have all data , and we only use data not
older than 3 months ,
10.Is it a best idea to remove those unused data to archive DB.
11.Thus making these tables less weight
12.Will produce better response time
13 Or is there any other setting or consideration we have to change to get better response time and reduce CPU usage.
expecting the reply soon , pls
thank you
mimayls
|
|
|
| Re: MySQL Optimal Settings [message #1225 is a reply to message #1215 ] |
Tue, 08 May 2007 14:24 |
|
|
Are you sure your indexes are on the correct columns? indexes on columns with high null counts or low cardinality can often worsen your performance. also, if you're creating an order by on a large body of results, you'll see slow downs as sometimes the sorts will result in disk based creations of MyISAM tmp tables to order your data before sending it to your app. Make sure your query only includes the columns you need (not * if you dont need everything) and eliminate large bodies of info when possible by showing only the most recent results. If you're getting a lot of traffic you might try farming out all your select statements to a clustered server with MyISAM tables instead of INNODB table types.
|
|
| | |
| Re: MySQL Optimal Settings [message #1232 is a reply to message #1230 ] |
Thu, 10 May 2007 04:14 |
mimayls
Messages: 11
Registered: May 2007 |
Junior Member |
|
|
Hai tanj
The database size is about 120 MB
The MySQL version is 4.0.17
expecting your reply
Thank You
mimayls
This are the variables
Show Variables Listing
----------------------
'Variable_name','Value'
'back_log','50'
'basedir','C:\mysql\'
'binlog_cache_size','32768'
'bulk_insert_buffer_size','8388608'
'character_set','latin1'
'character_sets','latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5'
'concurrent_insert','ON'
'connect_timeout','5'
'convert_character_set',''
'datadir','C:\mysql\data\'
'default_week_format','0'
'delay_key_write','ON'
'delayed_insert_limit','100'
'delayed_insert_timeout','300'
'delayed_queue_size','1000'
'flush','OFF'
'flush_time','1800'
'ft_boolean_syntax','+ -><()~*:""&|'
'ft_min_word_len','4'
'ft_max_word_len','254'
'ft_max_word_len_for_sort','20'
'ft_stopword_file','(built-in)'
'have_bdb','NO'
'have_crypt','NO'
'have_innodb','YES'
'have_isam','YES'
'have_raid','NO'
'have_symlink','YES'
'have_openssl','NO'
'have_query_cache','YES'
'init_file',''
'innodb_additional_mem_pool_size','1048576'
'innodb_buffer_pool_size','8388608'
'innodb_data_file_path','ibdata1:10M:autoextend'
'innodb_data_home_dir',''
'innodb_file_io_threads','4'
'innodb_force_recovery','0'
'innodb_thread_concurrency','8'
'innodb_flush_log_at_trx_commit','1'
'innodb_fast_shutdown','ON'
'innodb_flush_method',''
'innodb_lock_wait_timeout','50'
'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_mirrored_log_groups','1'
'innodb_max_dirty_pages_pct','90'
'interactive_timeout','28800'
'join_buffer_size','131072'
'key_buffer_size','262144000'
'language','C:\mysql\share\english\'
'large_files_support','ON'
'local_infile','ON'
'log','OFF'
'log_update','OFF'
'log_bin','OFF'
'log_slave_updates','OFF'
'log_slow_queries','OFF'
'log_warnings','OFF'
'long_query_time','10'
'low_priority_updates','OFF'
'lower_case_table_names','ON'
'max_allowed_packet','1047552'
'max_binlog_cache_size','4294967295'
'max_binlog_size','1073741824'
'max_connections','100'
'max_connect_errors','10'
'max_delayed_threads','20'
'max_heap_table_size','16777216'
'max_join_size','4294967295'
'max_relay_log_size','0'
'max_seeks_for_key','4294967295'
'max_sort_length','1024'
'max_user_connections','0'
'max_tmp_tables','32'
'max_write_lock_count','4294967295'
'myisam_max_extra_sort_file_size','268435456'
'myisam_max_sort_file_size','2147483647'
'myisam_repair_threads','1'
'myisam_recover_options','OFF'
'myisam_sort_buffer_size','67108864'
'named_pipe','OFF'
'net_buffer_length','16384'
'net_read_timeout','30'
'net_retry_count','10'
'net_write_timeout','60'
'new','OFF'
'open_files_limit','0'
'port','3306'
'protocol_version','10'
'query_alloc_block_size','8192'
'query_cache_limit','1048576'
'query_cache_size','262144000'
'query_cache_type','ON'
'query_prealloc_size','8192'
'range_alloc_block_size','2048'
'read_buffer_size','2093056'
'read_only','OFF'
'read_rnd_buffer_size','262144'
'rpl_recovery_rank','0'
'server_id','0'
'slave_net_timeout','3600'
'skip_external_locking','ON'
'skip_networking','OFF'
'skip_show_database','OFF'
'slow_launch_time','2'
'sort_buffer_size','2097144'
'sql_mode','0'
'table_cache','512'
'table_type','MYISAM'
'thread_cache_size','8'
'thread_stack','196608'
'tx_isolation','REPEATABLE-READ'
'timezone','Arab Standard Time'
'tmp_table_size','33554432'
'tmpdir','C:\WINNT\TEMP\'
'transaction_alloc_block_size','8192'
'transaction_prealloc_size','4096'
'version','4.0.17-nt'
'version_comment','Source distribution'
'wait_timeout','28800'
|
|
|
| Re: MySQL Optimal Settings [message #1234 is a reply to message #1229 ] |
Thu, 10 May 2007 09:16 |
|
|
What are your stats for table locks? are you seeing long table lock times in your slow query log?
|
|
| |
| Re: MySQL Optimal Settings [message #1238 is a reply to message #1235 ] |
Sat, 12 May 2007 02:25 |
mimayls
Messages: 11
Registered: May 2007 |
Junior Member |
|
|
Hi tanj
Thank tanj
Below are the Status values , these values not at peak , took them at morning .
# Query:
# show status
#
'Variable_name','Value'
'Aborted_clients','101'
'Aborted_connects','5'
'Bytes_received','29409510'
'Bytes_sent','219532571'
'Com_admin_commands','2'
'Com_alter_table','0'
'Com_analyze','0'
'Com_backup_table','0'
'Com_begin','1'
'Com_change_db','113494'
'Com_change_master','0'
'Com_check','0'
'Com_commit','814'
'Com_create_db','0'
'Com_create_function','0'
'Com_create_index','0'
'Com_create_table','0'
'Com_delete','1'
'Com_delete_multi','0'
'Com_drop_db','0'
'Com_drop_function','0'
'Com_drop_index','0'
'Com_drop_table','0'
'Com_flush','0'
'Com_grant','0'
'Com_ha_close','0'
'Com_ha_open','0'
'Com_ha_read','0'
'Com_insert','1738'
'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_purge','0'
'Com_rename_table','0'
'Com_repair','0'
'Com_replace','0'
'Com_replace_select','0'
'Com_reset','0'
'Com_restore_table','0'
'Com_revoke','0'
'Com_rollback','95'
'Com_savepoint','0'
'Com_select','37405'
'Com_set_option','1743'
'Com_show_binlog_events','0'
'Com_show_binlogs','0'
'Com_show_create','36'
'Com_show_databases','2'
'Com_show_fields','72'
'Com_show_grants','0'
'Com_show_keys','36'
'Com_show_logs','0'
'Com_show_master_status','0'
'Com_show_new_master','0'
'Com_show_open_tables','0'
'Com_show_processlist','0'
'Com_show_slave_hosts','0'
'Com_show_slave_status','0'
'Com_show_status','10'
'Com_show_innodb_status','0'
'Com_show_tables','40'
'Com_show_variables','0'
'Com_slave_start','0'
'Com_slave_stop','0'
'Com_truncate','0'
'Com_unlock_tables','0'
'Com_update','2708'
'Connections','3600'
'Created_tmp_disk_tables','1222'
'Created_tmp_tables','3843'
'Created_tmp_files','0'
'Delayed_insert_threads','0'
'Delayed_writes','0'
'Delayed_errors','0'
'Flush_commands','1'
'Handler_commit','0'
'Handler_delete','1'
'Handler_read_first','1602'
'Handler_read_key','481833'
'Handler_read_next','1953645'
'Handler_read_prev','0'
'Handler_read_rnd','584567'
'Handler_read_rnd_next','3563059479'
'Handler_rollback','0'
'Handler_update','418271'
'Handler_write','540143'
'Key_blocks_used','5365'
'Key_read_requests','1158956'
'Key_reads','5337'
'Key_write_requests','222'
'Key_writes','143'
'Max_used_connections','51'
'Not_flushed_key_blocks','0'
'Not_flushed_delayed_rows','0'
'Open_tables','58'
'Open_files','94'
'Open_streams','0'
'Opened_tables','1377'
'Questions','238613'
'Qcache_queries_in_cache','299'
'Qcache_inserts','18108'
'Qcache_hits','76908'
'Qcache_lowmem_prunes','0'
'Qcache_not_cached','19298'
'Qcache_free_memory','261074480'
'Qcache_free_blocks','129'
'Qcache_total_blocks','771'
'Rpl_status','NULL'
'Select_full_join','606'
'Select_full_range_join','0'
'Select_range','0'
'Select_range_check','0'
'Select_scan','26134'
'Slave_open_temp_tables','0'
'Slave_running','OFF'
'Slow_launch_threads','44'
'Slow_queries','9'
'Sort_merge_passes','0'
'Sort_range','0'
'Sort_rows','199459'
'Sort_scan','4407'
'Table_locks_immediate','89172'
'Table_locks_waited','2177'
'Threads_cached','1'
'Threads_created','241'
'Threads_connected','35'
'Threads_running','1'
'Uptime','97491'
|
|
| | | | | | | | | | |
| Re: MySQL Optimal Settings [message #1292 is a reply to message #1288 ] |
Wed, 23 May 2007 09:25 |
mimayls
Messages: 11
Registered: May 2007 |
Junior Member |
|
|
Thank you bambarbia
Sure we have to review our Schema and the Queries.
No other application running on the server
The Server is same as mentioned earlier
We have multilpe client applications accessing the server regularly for every 10 second.This applications checks ordermaster table.
Can u please check the system variables , if they are correct.
Thank you
mimayls
|
|
|
Goto Forum:
Current Time: Tue Jan 6 15:53:09 EST 2009
Total time taken to generate the page: 0.14372 seconds |
Members
Search
Help
Register
Login
Home
MySQL Optimal Settings
