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! List-Archive: http://lists.mysql.com/mysql/224445 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=20cf3054a529935ee8049c757111 --20cf3054a529935ee8049c757111 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 rai= d 10 with 15k. Metrics show Old raid Secuecial writting 1G: 533 mb/s (using dd if=3D/dev/zero of=3D1G bs=3D1024 count=3D102400) 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 wrot= e: > 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=E9s Tello wrot= e: > >> 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 =3D 6699.90 MB/sec >> Timing buffered disk reads: 174 MB in 3.02 seconds =3D 57.64 MB/sec >> >> free >> total used free shared buffers cached >> Mem: 16631296 16065356 565940 0 83148 1341552= 0 >> -/+ 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 =3D 3306 >> socket =3D /tmp/mysql.sock >> [mysqld] >> innodb_file_per_table >> port =3D 3306 >> socket =3D /tmp/mysql.sock >> back_log =3D 50 >> max_connections =3D 100 >> max_connect_errors =3D 10 >> table_cache =3D 2048 >> max_allowed_packet =3D 256M >> binlog_cache_size =3D 16M >> max_heap_table_size =3D 64M >> sort_buffer_size =3D 16M >> join_buffer_size =3D 32M >> thread_cache =3D 8 >> thread_concurrency =3D 8 >> query_cache_size =3D 256M >> query_cache_limit =3D 32M >> ft_min_word_len =3D 4 >> memlock >> default_table_type =3D INNODB >> thread_stack =3D 192K >> transaction_isolation =3D REPEATABLE-READ >> tmp_table_size =3D 256M >> log_slow_queries >> long_query_time =3D 2 >> log_long_format >> tmpdir =3D /tmp >> key_buffer_size =3D 128M >> read_buffer_size =3D 64M >> read_rnd_buffer_size =3D 128M >> bulk_insert_buffer_size =3D 64M >> myisam_sort_buffer_size =3D 128M >> myisam_max_sort_file_size =3D 10G >> myisam_max_extra_sort_file_size =3D 10G >> myisam_repair_threads =3D 1 >> myisam_recover >> skip-bdb >> innodb_additional_mem_pool_size =3D 16M >> innodb_buffer_pool_size =3D 2G >> innodb_data_file_path =3D ibdata1:10M:autoextend >> innodb_file_io_threads =3D 4 >> innodb_thread_concurrency =3D 32 >> innodb_flush_log_at_trx_commit =3D 1 >> innodb_log_buffer_size =3D 8M >> innodb_log_file_size =3D 256M >> innodb_log_files_in_group =3D 3 >> innodb_max_dirty_pages_pct =3D 90 >> innodb_lock_wait_timeout =3D 120 >> [mysqldump] >> quick >> max_allowed_packet =3D 16M >> [mysql] >> no-auto-rehash >> [isamchk] >> key_buffer =3D 512M >> sort_buffer_size =3D 512M >> read_buffer =3D 8M >> write_buffer =3D 8M >> [myisamchk] >> key_buffer =3D 512M >> sort_buffer_size =3D 512M >> read_buffer =3D 8M >> write_buffer =3D 8M >> [mysqlhotcopy] >> interactive-timeout >> [mysqld_safe] >> open-files-limit =3D 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 =3D 2932.17 MB/sec >> Timing buffered disk reads: 1304 MB in 3.00 seconds =3D 434.06 MB/sec >> >> total used free shared buffers cached >> Mem: 33008624 2097924 30910700 0 21308 7602= 4 >> -/+ 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 =3D 3306 >> socket =3D /tmp/mysqld.sock >> [safe_mysqld] >> err_log =3D /mysql/logs/mysql.err >> >> [mysqld] >> skip-external-locking >> server_id =3D 9000 >> user =3D mysql >> port =3D 3306 >> socket =3D /tmp/mysqld.sock >> max_connections =3D 2048 >> back_log =3D 128 >> max_connect_errors =3D 1000 >> connect_timeout =3D 2 >> wait_timeout =3D 60 >> max_allowed_packet =3D 16M >> net_buffer_length =3D 8K >> datadir =3D /mysql/data >> tmpdir =3D /mysql/tmp >> log-error =3D /mysql/logs/mysqld.err >> pid-file =3D /mysql/tmp/mysqld.pid >> slow_query_log_file =3D /mysql/logs/slow-queries.log >> log_output =3D FILE >> # 5.1 only >> long_query_time =3D 5 >> log-short-format >> log_bin =3D /mysql/binary-logs/produccion-bin >> relay_log =3D >> /mysql/binary-logs/produccion-relay-bin >> binlog_format =3D row = # >> 5.1 only >> binlog_cache_size =3D 10M >> skip_slave_start >> table_cache =3D 4096 >> join_buffer_size =3D 256M >> tmp_table_size =3D 2G >> max_heap_table_size =3D 2G >> sort_buffer_size =3D 1G >> thread_cache_size =3D 2048 >> thread_concurrency =3D 8 >> thread_stack =3D 192K >> query_cache_size =3D 2G >> query_cache_type =3D 1 >> query_cache_limit =3D 256M >> default_storage_engine =3D InnoDB >> transaction_isolation =3D REPEATABLE-READ >> key_buffer_size =3D 512M >> read_buffer_size =3D 16M >> read_rnd_buffer_size =3D 8M >> myisam_sort_buffer_size =3D 246M >> bulk_insert_buffer_size =3D 64M >> myisam_max_sort_file_size =3D 6G >> myisam_repair_threads =3D 2 >> innodb_data_home_dir =3D /mysql/innodb >> innodb_data_file_path =3D ibdata1:10M:autoextend >> innodb_file_per_table >> innodb_buffer_pool_size =3D 12G # 16GB RAM >> innodb_additional_mem_pool_size =3D 128M >> innodb_log_group_home_dir =3D /mysql/innodb-logs >> innodb_log_files_in_group =3D 4 >> innodb_log_file_size =3D 128M # 8GB RAM >> innodb_log_buffer_size =3D 16M >> innodb_max_dirty_pages_pct =3D 80 >> innodb_flush_log_at_trx_commit =3D 1 >> innodb_lock_wait_timeout =3D 50 >> innodb_flush_method =3D O_DIRECT >> innodb_thread_concurrency =3D 16 >> innodb_autoinc_lock_mode =3D 1 >> innodb_locks_unsafe_for_binlog # 5.1 on= ly >> innodb_fast_shutdown =3D 1 >> innodb_max_purge_lag =3D 0 >> [mysqldump] >> quick >> max_allowed_packet =3D 16M >> [mysql] >> [myisamchk] >> key_buffer =3D 256M >> sort_buffer =3D 256M >> read_buffer =3D 64M >> write_buffer =3D 64M >> [mysqlhotcopy] >> interactive_timeout >> > > -- > The best compliment you could give Pythian for our service is a referral. > > > --20cf3054a529935ee8049c757111--