MySQL Forums
Forum List  »  Performance

Re: Time performance in query Inner Join
Posted by: angel rivero
Date: July 08, 2011 07:03AM

Peter Brawley Wrote:
-------------------------------------------------------
> How much RAM does the machine have?
3,25 GB RAM

> What is the innodb_buffer_pool_size setting? In
> fact show the results of
> show variables like 'inno%';

Variable_name	Value
innodb_adaptive_hash_index	ON
innodb_additional_mem_pool_size	3145728
innodb_autoextend_increment	8
innodb_autoinc_lock_mode	1
innodb_buffer_pool_size	112197632
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	
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_buffer_size	2097152
innodb_log_file_size	56623104
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_stats_on_metadata	ON
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	10
innodb_thread_sleep_delay	10000
innodb_use_legacy_cardinality_algorithm	ON
Innodb_buffer_pool_pages_data	6249
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	2872863
Innodb_buffer_pool_pages_free	1
Innodb_buffer_pool_pages_misc	598
Innodb_buffer_pool_pages_total	6848
Innodb_buffer_pool_read_ahead_rnd	57220
Innodb_buffer_pool_read_ahead_seq	161813
Innodb_buffer_pool_read_requests	647328807
Innodb_buffer_pool_reads	1292274
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	858124166
Innodb_data_fsyncs	115666
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	219910144
Innodb_data_reads	11547635
Innodb_data_writes	3007781
Innodb_data_written	2868088832
Innodb_dblwr_pages_written	2872863
Innodb_dblwr_writes	34004
Innodb_log_waits	396
Innodb_log_write_requests	94022908
Innodb_log_writes	70974
Innodb_os_log_fsyncs	43316
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	3215001600
Innodb_page_size	16384
Innodb_pages_created	2218674
Innodb_pages_read	11547625
Innodb_pages_written	2872863
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	50844
Innodb_row_lock_time_avg	50844
Innodb_row_lock_time_max	50844
Innodb_row_lock_waits	1
Innodb_rows_deleted	13072
Innodb_rows_inserted	139273063
Innodb_rows_read	998792033
Innodb_rows_updated	3589043

> show status like 'inno%';

Variable_name	Value
innodb_adaptive_hash_index	ON
innodb_additional_mem_pool_size	3145728
innodb_autoextend_increment	8
innodb_autoinc_lock_mode	1
innodb_buffer_pool_size	112197632
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	
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_buffer_size	2097152
innodb_log_file_size	56623104
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_stats_on_metadata	ON
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	10
innodb_thread_sleep_delay	10000
innodb_use_legacy_cardinality_algorithm	ON
Innodb_buffer_pool_pages_data	6249
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	2872863
Innodb_buffer_pool_pages_free	1
Innodb_buffer_pool_pages_misc	598
Innodb_buffer_pool_pages_total	6848
Innodb_buffer_pool_read_ahead_rnd	57220
Innodb_buffer_pool_read_ahead_seq	161813
Innodb_buffer_pool_read_requests	647328807
Innodb_buffer_pool_reads	1292274
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	858124166
Innodb_data_fsyncs	115666
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	219910144
Innodb_data_reads	11547635
Innodb_data_writes	3007781
Innodb_data_written	2868088832
Innodb_dblwr_pages_written	2872863
Innodb_dblwr_writes	34004
Innodb_log_waits	396
Innodb_log_write_requests	94022908
Innodb_log_writes	70974
Innodb_os_log_fsyncs	43316
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	3215001600
Innodb_page_size	16384
Innodb_pages_created	2218674
Innodb_pages_read	11547625
Innodb_pages_written	2872863
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	50844
Innodb_row_lock_time_avg	50844
Innodb_row_lock_time_max	50844
Innodb_row_lock_waits	1
Innodb_rows_deleted	13072
Innodb_rows_inserted	139273063
Innodb_rows_read	998792033
Innodb_rows_updated	3589043

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Time performance in query Inner Join
1842
July 08, 2011 07:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.