MySQL 单表插入 10w+ TPS达成(2)

发表于:2013-12-27来源:IT博客大学习作者:淘宝文通点击数: 标签:MySQL
MySQL 使用Percona 5.5.18 my.cnf的配置如下 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 default-storage-engine = INNODB innodb_flush_method = O_DIRECT innodb_file_per_table = 1 in

  MySQL 使用Percona 5.5.18

  my.cnf的配置如下

1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    

default-storage-engine = INNODB

     innodb_flush_method = O_DIRECT

     innodb_file_per_table = 1

     innodb_flush_log_at_trx_commit = 1

     innodb_lock_wait_timeout = 100

     innodb_additional_mem_pool_size = 20M

     innodb_buffer_pool_size = 5G

     innodb_log_buffer_size= 800M

     innodb_log_file_size = 200M

     innodb_log_files_in_group = 4

     innodb_file_io_threads = 4

     innodb_thread_concurrency = 32

     innodb_max_dirty_pages_pct = 90

     innodb_data_file_path = ibdata1:1G:autoextend

     innodb_sync_spin_loops = 0

     innodb_spin_wait_delay = 0

    

     tdh_socket_thread_num = 8

     tdh_socket_slow_read_thread_num = 64

     tdh_socket_io_thread_num = 4

     tdh_socket_write_thread_num = 16

     tdh_socket_optimize_on = 1

     tdh_socket_cache_table_num_for_thd = 40

  插入的表结构

1

    2

    3

    4

    5

    6

    7

    8

    

CREATE TABLE `test` (

     `id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

     `k` INT(20) DEFAULT NULL,

     `i` INT(20) NOT NULL,

     `c` CHAR(120) DEFAULT NULL,

     `kc` INT(20) DEFAULT \'1\',

     PRIMARY KEY (`id`)

     ) ENGINE=InnoDB DEFAULT CHARSET=gbk;

  压测脚本:

1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

    27

    28

    29

    30

    31

    32

    33

    34

    35

    36

    37

    38

    39

    40

    

package benchmark.insert

    

     import benchmark.StressTest

     import com.taobao.tdhs.client.TDHSClient

     import com.taobao.tdhs.client.TDHSClientImpl

     import com.taobao.tdhs.client.response.TDHSResponse

     import com.taobao.tdhs.client.response.TDHSResponseEnum

     import java.util.concurrent.atomic.AtomicLong

    

     /**

     * @author 文通

原文转自:http://blogread.cn/it/article/5070