业务上为了测试DB写入性能,需测试大量数据写入速度,数据库部署在aws上,4核8G内存100G硬盘,MySQL5.7.27
mysql.png

--创建测试数据库
create database a1 default charset utf8mb4;
--创建测试表
CREATE TABLE `test110` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comp` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--创建插入存储过程
DELIMITER $$
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i=0;
while i<1000000 do
INSERT INTO `test110` VALUES (null,'TMP01','user11','会员中台','2019-08-15 00:00:02','11','22');
set i=i+1;
end while;
end $$
DELIMITER ;

测试如下,速度很快:

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.74 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> select count(1) from test110;
+----------+
| count(1) |
+----------+
|  3000000 |
+----------+
1 row in set (0.44 sec)

MySQL配置如下my.cnf

[client]
default-character-set = utf8mb4
port        = 3306
socket        = /data/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4
prompt="\u@oatestdb \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]

secure_file_priv = ''
#validate-password = off
slow_query_log = On
log_output = FILE
slow-query-log-file=/data/mysql/slow.log
long_query_time = 30 
log_queries_not_using_indexes = ON

lower_case_table_names=1
auto-increment-increment = 1
auto-increment-offset = 1

log_warnings=1
log_error_verbosity=1

default-storage-engine=INNODB
group_concat_max_len = 18446744073709551615
# generic configuration options
port        = 3306
socket        = /data/mysql/mysql.sock 
pid-file        = /data/mysql/mysqld.pid
basedir        = /usr/local/mysql
datadir         = /data/mysql/db
language    = /usr/local/mysql/share/english 
user        = mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-external-locking
skip-name-resolve
skip-character-set-client-handshake
character-set-server = utf8mb4
explicit_defaults_for_timestamp = true
init_connect='SET NAMES utf8mb4'
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
explicit_defaults_for_timestamp

#replication
expire_logs_days = 30
sync-binlog = 1000
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/mysql/binlog/binlog
binlog_format=ROW


relay_log_recovery = 1
skip_slave_start = 0
slave-skip-errors = all
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#group replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1:24901"
loose-group_replication_group_seeds= "db1:24901,db2:24902,db3:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 5000
max_connect_errors = 100000
max_prepared_stmt_count=1048576
table_open_cache = 10240
table_definition_cache = 10240
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 7680
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 900
wait_timeout = 900
tmp_table_size = 256M
max_heap_table_size = 256M

# innodb
innodb_buffer_pool_size = 12G 
innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC

innodb_io_capacity = 50000
innodb_io_capacity_max = 80000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0

[mysqld_safe]
open-files-limit = 8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 32M
Tags标签: 存储过程,插入 Tags相关文章如下:

上一篇:Oracle11gR2将A用户下的所有table,packe的权限赋予给B用户的方法

下一篇:CentOS7启用ena网络增强模块