Performance tuning - comparing two computers
Hello,
This is my first post so I apologize in advance if I am not following established conventions. I am a "business intellgence" developer and care more about read performance than write performance.
I recently switched from one development laptop to another and I have performance issues when reading MySQL tables (innodb) on the new PC. The same select request takes about 40% longer on the new PC. I think the differences are minor - even though the CPU speed is lower on the new PC, it is three years newer, with (I'm guessing) a newer Intel processor.
Old PC - Apple MacBook Pro:
Intel i7 M620 @ 2.67GHz / 8GB RAM / 7200rpm drive / Windows 7 Pro SP1 / 64-bit
New PC - Samsung Series 7:
Intel i7 3635QM @ 2.4GHz / 8GB RAM / 5400rpm drive / Windows 8 / 64-bit
I started with the same mysql.ini file on both, but have made changes on the new one - trying to improve performance.
Comparing the two SHOW VARIABLES LIKE 'innodb%' commands yields only two diffrences - innodb_file_per_table and innodb_log_file_size (please see below).
The SQL I run on both computers - takes about 7 seconds on old PC and about 12 seconds on new PC:
RESET QUERY CACHE;
FLUSH QUERY CACHE;
SELECT FUND, SUM(PURCHASES) FROM f_transaction_level1 WHERE CYCLE_DATE_I >= 20100101 AND CYCLE_DATE_I < 20120101 GROUP BY FUND ORDER BY FUND ;
Is it possible that the hard drive speed (5400rpm vs 7200rpm) makes such a big difference?
I hope I haven't drowned you with too much information. I would really like to improve the read response time.
Thanks very much,
Francis.
Old PC SHOW VARIABLES LIKE 'innodb%':
Variable_name,Value
innodb_adaptive_flushing,ON
innodb_adaptive_hash_index,ON
innodb_additional_mem_pool_size,15728640
innodb_autoextend_increment,8
innodb_autoinc_lock_mode,1
innodb_buffer_pool_instances,1
innodb_buffer_pool_size,4294967296
innodb_change_buffering,all
innodb_checksums,ON
innodb_commit_concurrency,0
innodb_concurrency_tickets,500
innodb_data_file_path,ibdata1:10M:autoextend
innodb_data_home_dir,"F:\MySQL Datafiles\"
innodb_doublewrite,ON
innodb_fast_shutdown,1
innodb_file_format,Antelope
innodb_file_format_check,ON
innodb_file_format_max,Antelope
innodb_file_per_table,OFF
innodb_flush_log_at_trx_commit,1
innodb_flush_method,
innodb_force_load_corrupted,OFF
innodb_force_recovery,0
innodb_io_capacity,200
innodb_large_prefix,OFF
innodb_lock_wait_timeout,50
innodb_locks_unsafe_for_binlog,OFF
innodb_log_buffer_size,8388608
innodb_log_file_size,20971520
innodb_log_files_in_group,2
innodb_log_group_home_dir,.\
innodb_max_dirty_pages_pct,75
innodb_max_purge_lag,0
innodb_mirrored_log_groups,1
innodb_old_blocks_pct,37
innodb_old_blocks_time,0
innodb_open_files,300
innodb_purge_batch_size,20
innodb_purge_threads,0
innodb_random_read_ahead,OFF
innodb_read_ahead_threshold,56
innodb_read_io_threads,4
innodb_replication_delay,0
innodb_rollback_on_timeout,OFF
innodb_rollback_segments,128
innodb_spin_wait_delay,6
innodb_stats_method,nulls_equal
innodb_stats_on_metadata,ON
innodb_stats_sample_pages,8
innodb_strict_mode,OFF
innodb_support_xa,ON
innodb_sync_spin_loops,30
innodb_table_locks,ON
innodb_thread_concurrency,10
innodb_thread_sleep_delay,10000
innodb_use_native_aio,ON
innodb_use_sys_malloc,ON
innodb_version,1.1.8
innodb_write_io_threads,4
New PC SHOW VARIABLES LIKE 'innodb%':
Variable_name,Value
innodb_adaptive_flushing,ON
innodb_adaptive_hash_index,ON
innodb_additional_mem_pool_size,15728640
innodb_autoextend_increment,8
innodb_autoinc_lock_mode,1
innodb_buffer_pool_instances,1
innodb_buffer_pool_size,4294967296
innodb_change_buffering,all
innodb_checksums,ON
innodb_commit_concurrency,0
innodb_concurrency_tickets,500
innodb_data_file_path,ibdata1:10M:autoextend
innodb_data_home_dir,"F:\MySQL Datafiles\"
innodb_doublewrite,ON
innodb_fast_shutdown,1
innodb_file_format,Antelope
innodb_file_format_check,ON
innodb_file_format_max,Antelope
innodb_file_per_table,ON
innodb_flush_log_at_trx_commit,2
innodb_flush_method,
innodb_force_load_corrupted,OFF
innodb_force_recovery,0
innodb_io_capacity,200
innodb_large_prefix,OFF
innodb_lock_wait_timeout,50
innodb_locks_unsafe_for_binlog,OFF
innodb_log_buffer_size,8388608
innodb_log_file_size,1073741824
innodb_log_files_in_group,2
innodb_log_group_home_dir,.\
innodb_max_dirty_pages_pct,75
innodb_max_purge_lag,0
innodb_mirrored_log_groups,1
innodb_old_blocks_pct,37
innodb_old_blocks_time,0
innodb_open_files,300
innodb_purge_batch_size,20
innodb_purge_threads,0
innodb_random_read_ahead,OFF
innodb_read_ahead_threshold,56
innodb_read_io_threads,4
innodb_replication_delay,0
innodb_rollback_on_timeout,OFF
innodb_rollback_segments,128
innodb_spin_wait_delay,6
innodb_stats_method,nulls_equal
innodb_stats_on_metadata,ON
innodb_stats_sample_pages,8
innodb_strict_mode,OFF
innodb_support_xa,ON
innodb_sync_spin_loops,30
innodb_table_locks,ON
innodb_thread_concurrency,10
innodb_thread_sleep_delay,10000
innodb_use_native_aio,ON
innodb_use_sys_malloc,ON
innodb_version,1.1.8
innodb_write_io_threads,4
Old PC my.ini file:
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir = "F:/ProgramData/MySQL/MySQL Server 5.5/data"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size = 256M
table_cache=256
tmp_table_size=35M
thread_cache_size = 32
myisam_max_sort_file_size=100G
myisam_sort_buffer_size = 150M
key_buffer_size=50M
read_buffer_size = 200K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_data_home_dir="F:/MySQL Datafiles/"
innodb_additional_mem_pool_size = 15M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 4096M
innodb_log_file_size=20M
innodb_thread_concurrency=10
New PC my.ini file:
[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir = "F:/ProgramData/MySQL/MySQL Server 5.5/data"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=40
query_cache_size = 0
query_cache_type = 0
table_cache=256
tmp_table_size=32M
thread_cache_size = 40
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
myisam_max_sort_file_size=100G
myisam_sort_buffer_size = 150M
key_buffer_size=32M
read_buffer_size = 200K
read_rnd_buffer_size=256K
sort_buffer_size=256K
max_allowed_packet = 16M
max_connect_errors = 1000000
innodb_data_home_dir="F:/MySQL Datafiles/"
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_additional_mem_pool_size = 15M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 4G
innodb_log_file_size=1G
innodb_thread_concurrency=10