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. List-Archive: http://lists.mysql.com/benchmarks/259 Message-Id: <69069d7d0902102300o37a1a199m68688ace041ec799@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=000e0cd2116214721f04629f2a37 --000e0cd2116214721f04629f2a37 Content-Type: text/plain; charset=ISO-2022-JP Content-Transfer-Encoding: 7bit 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 wrote: > > On Tue, Feb 10, 2009 at 6:55 PM, Moon's Father 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 --000e0cd2116214721f04629f2a37--