List:Database Benchmarks« Previous MessageNext Message »
From:Moon's Father Date:February 11 2009 2:55am
Subject:The result of MySQL's high concurrency and very large data
performance benchmark.
View as plain text  
Yesterday, we used *DELL 2650, 16G Memory **,**2 CPU *4 Cores,SAN* storage
to test MySQL performance. and we passed the test of around 2000 TPS in
database simulation load test.

At that time the CPU load is very high and the customer accepted our
database performance in this level hardware(The configuration file is as in
following.).

So today in order to improve the performance, we changed hardware to *DELL
6850, 4CPU * 4cores (3.0 G),32G memory,* same SAN storage.* *First time, we
used the same mysql.cnf

as successful 30W in DELL 2650, and test failed quickly (low CPU
usage) because of long database response time. In the later, we also tried
to

modified innodb_thread_concurrency = 16 etc, but actually the failure point
of the test is not stable and some times even 4W online users, some times
10W online users with the same

configuration;

So our question is do we have any benchmark in 4CPU *4 Cores DELL hardware?
We could understand if the configuration file is not the best one for this
DELL 6850, but we

couldn't explain why the performance is so poor(lower 1/3 performance of
DELL 2650) and the database is not stable(some times response time is low).



It will be great appreciated if you could give some comments.





[client]

port            = 3306

socket          = /tmp/mysql.sock

default-character-set=utf8

[mysqld]

connect_timeout = 20

port            = 3306

socket          = /tmp/mysql.sock

init_connect='SET AUTOCOMMIT=0'

default-character-set=utf8

back_log = 200

skip-name-resolve

max_connections = 1200

max_connect_errors = 10

table_cache = 8192

max_allowed_packet = 16M

binlog_cache_size = 1M

max_heap_table_size = 2G

sort_buffer_size = 1M

join_buffer_size = 2M

thread_cache_size = 8

thread_concurrency = 32

query_cache_size = 0

default_table_type = MYISAM

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 2G

log-bin=/mysql/logs/mysql-bin

binlog_format=mixed

log_slow_queries

log_queries_not_using_indexes = 0

long_query_time = 3

log_long_format

tmpdir = /dev/shm

server-id = 1

key_buffer_size = 64M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 32M

myisam_max_sort_file_size = 10G

myisam_max_extra_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_checksums=0

innodb_doublewrite=0

innodb_flush_method=O_DIRECT

table_definition_cache = 1024

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 10G

innodb_data_file_path =
ibdata1:2000M;ibdata2:122852212736;ibdata3:5G:autoextend

innodb_data_home_dir = /mnt/xfs/mysql/data

innodb_file_io_threads = 4

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 0

innodb_support_xa = 0

innodb_log_buffer_size = 48M

innodb_log_file_size = 1G

innodb_log_files_in_group = 3

innodb_log_group_home_dir = /mysql/logs

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 28192


-- 
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
The result of MySQL's high concurrency and very large data performance benchmark.Moon's Father11 Feb
  • Re: The result of MySQL's high concurrency and very large data performance benchmark.Mark Callaghan11 Feb
    • Re: The result of MySQL's high concurrency and very large data performance benchmark.Moon's Father11 Feb