List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:February 13 2011 9:40pm
Subject:Performance issue old server witn mysql 4 vs new server with mysql 5
and old server WINS!
View as plain text  
I have a test process, which runs in the "old server" in 35 seconds, the new
server runs the same process in 110.

There is a change of version from mysql 4.1.22 to  5.1.22.
We were stuck at 5.1.22 because higher version give us another issules like
encoding, case sensitivity...

I really belive that the issue is regarding the mysql server....
there is extensive information about my setup...

I have more processors, more memory, more disk speed, but lower results...
T_T, because the wtf is long forgotten.



hdparm  -tT /dev/sda
/dev/sda:
 Timing cached reads:   13392 MB in  2.00 seconds = 6699.90 MB/sec
 Timing buffered disk reads:  174 MB in  3.02 seconds =  57.64 MB/sec

 free
             total       used       free     shared    buffers     cached
Mem:      16631296   16065356     565940          0      83148   13415520
-/+ buffers/cache:    2566688   14064608
Swap:     16779852        128   16779724

uname -r
2.6.16.21-0.8-bigsmp


Your MySQL connection id is 21 to server version: 4.1.22-standard-log

more /proc/cpuinfo  | grep -e "processor\|name"
processor       : 0
model name      : Intel(R) Xeon(R) CPU            5160  @ 3.00GHz
processor       : 1
model name      : Intel(R) Xeon(R) CPU            5160  @ 3.00GHz
processor       : 2
model name      : Intel(R) Xeon(R) CPU            5160  @ 3.00GHz
processor       : 3
model name      : Intel(R) Xeon(R) CPU            5160  @ 3.00GHz

grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
innodb_file_per_table
port            = 3306
socket          = /tmp/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 256M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 16M
join_buffer_size = 32M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 256M
query_cache_limit = 32M
ft_min_word_len = 4
memlock
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp
key_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240

New Server, which happnes to be 2x SLOWER!

hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e
"processor\|name"

/dev/sda:
 Timing cached reads:   5858 MB in  2.00 seconds = 2932.17 MB/sec
 Timing buffered disk reads:  1304 MB in  3.00 seconds = 434.06 MB/sec

             total       used       free     shared    buffers     cached
Mem:      33008624    2097924   30910700          0      21308      76024
-/+ buffers/cache:    2000592   31008032
Swap:      8388604          0    8388604

uname -r
2.6.34.7-0.7-desktop

processor       : 0
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 1
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 2
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 3
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 4
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 5
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 6
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 7
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 8
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 9
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 10
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz
processor       : 11
model name      : Intel(R) Xeon(R) CPU           E7450  @ 2.40GHz

Server version: 5.1.52-log Source distribution



grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port                              = 3306
socket                            = /tmp/mysqld.sock
[safe_mysqld]
err_log                           = /mysql/logs/mysql.err

[mysqld]
skip-external-locking
server_id                         = 9000
user                              = mysql
port                              = 3306
socket                            = /tmp/mysqld.sock
max_connections                   = 2048
back_log = 128
max_connect_errors                = 1000
connect_timeout                   = 2
wait_timeout                      = 60
max_allowed_packet                = 16M
net_buffer_length                 = 8K
datadir                           = /mysql/data
tmpdir                            = /mysql/tmp
log-error                         = /mysql/logs/mysqld.err
pid-file                          = /mysql/tmp/mysqld.pid
slow_query_log_file                  = /mysql/logs/slow-queries.log
log_output                        = FILE
   # 5.1 only
long_query_time                   = 5
log-short-format
log_bin                           = /mysql/binary-logs/produccion-bin
relay_log                         = /mysql/binary-logs/produccion-relay-bin
binlog_format                     = row                                 #
5.1 only
binlog_cache_size                 = 10M
skip_slave_start
table_cache                       = 4096
join_buffer_size                  = 256M
tmp_table_size                    = 2G
max_heap_table_size               = 2G
sort_buffer_size                  = 1G
thread_cache_size                 = 2048
thread_concurrency                = 8
thread_stack                      = 192K
query_cache_size                  = 2G
query_cache_type                  = 1
query_cache_limit                 = 256M
default_storage_engine            = InnoDB
transaction_isolation             = REPEATABLE-READ
key_buffer_size                   = 512M
read_buffer_size                  = 16M
read_rnd_buffer_size              = 8M
myisam_sort_buffer_size           = 246M
bulk_insert_buffer_size           = 64M
myisam_max_sort_file_size         = 6G
myisam_repair_threads             = 2
innodb_data_home_dir              = /mysql/innodb
innodb_data_file_path             = ibdata1:10M:autoextend
innodb_file_per_table
innodb_buffer_pool_size           = 12G          # 16GB RAM
innodb_additional_mem_pool_size   = 128M
innodb_log_group_home_dir         = /mysql/innodb-logs
innodb_log_files_in_group         = 4
innodb_log_file_size             = 128M                 # 8GB RAM
innodb_log_buffer_size            = 16M
innodb_max_dirty_pages_pct        = 80
innodb_flush_log_at_trx_commit    = 1
innodb_lock_wait_timeout          = 50
innodb_flush_method               = O_DIRECT
innodb_thread_concurrency         = 16
innodb_autoinc_lock_mode = 1
innodb_locks_unsafe_for_binlog                                  # 5.1 only
innodb_fast_shutdown              = 1
innodb_max_purge_lag              = 0
[mysqldump]
quick
max_allowed_packet                = 16M
[mysql]
[myisamchk]
key_buffer                        = 256M
sort_buffer                       = 256M
read_buffer                       = 64M
write_buffer                      = 64M
[mysqlhotcopy]
interactive_timeout

Thread
Performance issue old server witn mysql 4 vs new server with mysql 5and old server WINS!Andrés Tello13 Feb
  • Re: Performance issue old server witn mysql 4 vs new server withmysql 5 and old server WINS!Henrik Ingo16 Feb
Re: Performance issue old server witn mysql 4 vs new server withmysql 5 and old server WINS!Andrés Tello17 Feb