List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:February 17 2011 7:35am
Subject:Re: Performance issue old server witn mysql 4 vs new server with
mysql 5 and old server WINS!
View as plain text  
Yup, I'm doing clean tests,lshutdown, and reload mysql each test.
The raid setup is similar, Faster is raid1 with 10k harddisk, slower is raid
10 with 15k.

Metrics show
Old raid
Secuecial writting 1G: 533 mb/s  (using dd if=/dev/zero of=1G bs=1024
count=102400)
Secuencial reading 1G: 500 mb/s

New raid
Writting: 500 mb/s
Reading 800 mb/s

Average...


On Wed, Feb 16, 2011 at 4:33 PM, Singer X.J. Wang <wang@stripped>wrote:

> Dumb questions
>
> 1) Are you doing clean tests? Shutdown and restart MySQL after each test?
> 2) Is the RAID setup similar on the servers? Are you doing something like
> RAID10 on the old and RAID6 on the new?
>
> Singer
>
>
>
> On Sun, Feb 13, 2011 at 16:40, Andrés Tello <mr.criptos@stripped>
> wrote:
>
>> 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
>>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>
>
>

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