I would start with the slow query log to see whats happening.

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

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.

Thanks JGilbert for the information and replay
1. I have only indexed the fields which is used mostly for search and these fields are NOT NULL, the orderno and customerno fields.

2. As i mentioned in earlier post,
There are other sub applications which checks the 'order master' and 'order detail' tables for changes, regularly for every 5 secs.
These regular queries in this applications are slower and takes larger CPU time.

Iam using MyISAM type Tables ,
Is there any other setting , that i can work on.

thanks and reply
mimayls

Hello,

can you post output of SHOW VARIABLES and SHOW GLOBAL STATUS?

Also what is the size of your database?

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'

What are your stats for table locks? are you seeing long table lock times in your slow query log?

Hello,

thanks for posting Status variables. Can you also post SHOW STATUS output so I would see where the performance bottlenecks could be?

120MB is quite small size for database and I'm used to play with tables having 4 million records or more. So it should be no problem for your config )

I suspect that your database usage may have bad joins or table scans. But I need output of SHOW STATUS or slow query log for this.

Thanks.

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'

Hi JGilbert
Thank You

I checked the slowquery log , The Lock time is 0 , even for slow queries.

thank you
mimayls

The Lock Time field in the slow query log represents how long it had to wait to obtain a lock, not how long it held a lock. What's important is how long the query execution took and how many rows it had to look at to return the result.

Thank You
for the information linuxrunner,
Hoping more help

Thank You
mimayls

Hello mimayls

Thanks for the status values, it proves instructive.


'Handler_read_key','481833'
'Handler_read_rnd_next','3563059479'

Is showing that your queries are doing, most of the time, table scans instead of index reads. Looks like you'll have to review most of your queries' execution plans (i.e. take the queries and EXPLAIN on them). Maybe you will want to post some examples of query and EXPLAIN query so I could give you more advice.

About the configuration, you might want to increase the tmp_table_size to 64M (it shows that you have a lot of on-disk temporary tables created)

Also I see that your query cache size is 256M, but only 1M is used. It's way too large. In regards to your database size I recommend that you set query_cache_size to 8M, so you will free unnecessary reserved memory.

Regards.

Hai tanj
Thank you for the suggections.

Can you please tell me
1.why query cache is used only 1MB,
Because query are small size Or There is another problem

2.Why there are large no. of tmp tables
This is the problem of the unoptimized queries?
Lack of indexes in the table


please guid me

Thank You
mimayls