List:Database Benchmarks« Previous MessageNext Message »
From:Mark Callaghan Date:February 11 2009 3:57am
Subject:Re: The result of MySQL's high concurrency and very large data
performance benchmark.
View as plain text  
On Tue, Feb 10, 2009 at 6:55 PM, Moon's Father <yueliangdao0608@stripped>wrote:

> 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.
>

I think you went from an 8 core to a 16 core server. Workloads that are
CPU-bound and highly-concurrent degrade more than expected on servers with
more than 4 cores when using InnoDB and MyISAM. Workarounds include:
* using the Percona build (or OurDelta or Google patches)
* innodb_thread_concurrency=4 -- but this can cause other problems


>
>
>
>
>
> [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
>



-- 
Mark Callaghan
mcallaghan@stripped

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