CUBRID和MySQL使用SSD前后性能测试对比-技术开发专区

CUBRID和MySQL使用SSD前后性能测试对比

作者：IT168 黄永兵编译编辑：覃里 2010-09-23 00:39 IT168网站原创

　　MySQL配置(my.cnf)

[client]
socket = /home1/mysql/mysql/tmp/mysql.sock

[mysqld]
user = mysql
port = 3306
basedir = /home1/mysql/mysql
datadir = /home1/mysql/mysql/data
tmpdir = /home1/mysql/mysql/tmp
socket = /home1/mysql/mysql/tmp/mysql.sock

default-character-set = utf8
default_table_type = InnoDB
skip_name_resolve

back_log = 100
max_connections = 500
max_connect_errors = 999999
max_allowed_packet = 16M
max_heap_table_size = 64M
tmp_table_size = 64M
binlog_cache_size = 1M
thread_cache_size = 128

table_cache = 1024
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
query_cache_size = 64M
query_cache_limit = 2M

# MyISAM options
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
ft_min_word_len = 4

# INNODB options
innodb_buffer_pool_size = 4G # 50 ~ 70% of main memory
innodb_log_buffer_size = 8M
innodb_additional_mem_pool_size = 16M
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_per_table
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_support_xa=0
innodb_thread_concurrency = 16
innodb_lock_wait_timeout = 60
innodb_flush_log_at_trx_commit = 0 # 0 for slave, 1 for master

# Loging Configuration
log-bin=mysql-bin
expire_logs_days=5
log_warnings
log_slow_queries
log_slow_admin_statements
long_query_time = 2
log_long_format

# Replication setting
server-id = 1

　　测试场景(测试使用的表方案)

　　测试使用下面的SQL命令创建40个表名从tbl_200到tbl_239的数据表。

CREATE TABLE tbl_200;

ALTER CLASS tbl_200 ADD ATTRIBUTE
    id character varying(20) NOT NULL,
    seq integer NOT NULL,
    col3 character varying(16) NOT NULL,
    col4 character varying(5) NOT NULL,
    col5 character varying(50) NOT NULL,
    col6 character varying(1000),
    col7 character varying(300) NOT NULL,
    col8 character varying(150),
    col9 timestamp NOT NULL,
    col10 smallint DEFAULT 0 NOT NULL,
    col11 timestamp NOT NULL,
    col12 character varying(15) NOT NULL,
    col13 character(1) NOT NULL,
    col14 character(1) NOT NULL,
    col15 timestamp DEFAULT timestamp '04:25:44 PM 07/30/2009' NOT NULL;

ALTER CLASS tbl_200 ADD ATTRIBUTE
    CONSTRAINT "iuk_tbl" UNIQUE(id, col3, col4, col5),
    CONSTRAINT "ipk_tbl" PRIMARY KEY(id, seq);

CREATE INDEX ink1_tbl ON tbl_200 (id, col9 DESC, col14);

第1页：测试机环境第2页：MySQL配置(my.cnf)第3页：I/O Bound Insert负载测试第4页：CPU Bound Select负载测试第5页：合并SELECT测试结果

关注我们