MySQL Forums
Forum List  »  Performance

Performance tuning - comparing two computers
Posted by: Francis Mariani
Date: January 23, 2013 07:23PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance tuning - comparing two computers
3521
January 23, 2013 07:23PM


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.