List:Database Benchmarks« Previous MessageNext Message »
From:Moon's Father Date:February 11 2009 7:00am
Subject:Re: The result of MySQL's high concurrency and very large data
performance benchmark.
View as plain text  
Thanks for your reply.
But I think that it's nothing to do with MySQL itself.


On Wed, Feb 11, 2009 at 11:57 AM, Mark Callaghan <mcallaghan@stripped>wrote:

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



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