在过去的几个小时里我已经测试了很多,我就是不能让 mysql 5.7 表现得很好
已使用带有单个插入语句的 sql 转储进行测试
在 30 秒内 mysql 5.7.18 只能导入290 rows
,而 mysql 5.6.36 可以导入470 rows
.. 它已经在两台相同的服务器上进行了测试.. mysql 5.6 的服务器甚至是负载的生产服务器
- 英特尔 i7 2600
- 16GB 内存
- 表:InnoDB
mysql 5.6 的 my.cnf
[client]
# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
symbolic-links = 0
bind_address = 127.0.0.1
# DATA STORAGE #
datadir = /var/lib/mysql
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 10
max_binlog_size = 100M
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 100
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G
# MARIADB OPTIONS #
# innodb-defragment = 1
# thread-handling = pool-of-threads
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
innodb_print_all_deadlocks = 1
mysql 5.7 的 my.cnf
[mysqld]
# GENERAL #
user = mysql
port = 3306
default_storage_engine = InnoDB
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_ALL_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
sysdate_is_now = 1
symbolic_links = 0
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
server_id = 1
log_bin = /var/lib/mysql/mysql-bin
log_slave_updates
expire_logs_days = 14
sync_binlog = 1
log_bin_trust_function_creators= 1
binlog_format = ROW
# REPLICATION #
gtid_mode = ON
enforce_gtid_consistency
# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 4096
table_open_cache = 4096
wait_timeout = 60
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_autoinc_lock_mode = 2
innodb_adaptive_hash_index = 0
innodb_doublewrite = 0
innodb_change_buffering = none
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
basedir = /usr
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
explicit_defaults_for_timestamp
桌子
CREATE TABLE `account` (
`id` mediumint(7) unsigned NOT NULL AUTO_INCREMENT,
`block_id` smallint(5) unsigned NOT NULL,
`account_id_` mediumint(8) unsigned NOT NULL,
`type` tinyint(1) unsigned NOT NULL,
`system` tinyint(1) unsigned NOT NULL DEFAULT '0',
`module` tinyint(1) unsigned NOT NULL DEFAULT '0',
`stock` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_bank` tinyint(1) unsigned NOT NULL DEFAULT '0',
`is_monitored` tinyint(1) unsigned NOT NULL,
`vatcode_id` smallint(5) unsigned DEFAULT NULL,
`name` varchar(40) COLLATE utf8_danish_ci NOT NULL,
`ref_currency_id` tinyint(3) unsigned DEFAULT NULL,
`sum_account_id_` mediumint(8) unsigned NOT NULL,
`is_dimension` tinyint(1) unsigned NOT NULL,
`accountoff_id` mediumint(7) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `block_id` (`block_id`,`account_id_`),
KEY `account_id_` (`account_id_`),
KEY `type` (`type`),
KEY `system` (`system`),
KEY `module` (`module`),
KEY `stock` (`stock`),
KEY `is_bank` (`is_bank`),
KEY `is_monitored` (`is_monitored`),
KEY `vatcode_id` (`vatcode_id`),
KEY `ref_currency_id` (`ref_currency_id`),
KEY `sum_account_id_` (`sum_account_id_`),
KEY `accountoff_id` (`accountoff_id`),
CONSTRAINT `account_ibfk_1` FOREIGN KEY (`block_id`) REFERENCES `block` (`id`) ON DELETE CASCADE,
CONSTRAINT `account_ibfk_3` FOREIGN KEY (`ref_currency_id`) REFERENCES `ref_currency` (`id`),
CONSTRAINT `account_ibfk_8` FOREIGN KEY (`vatcode_id`) REFERENCES `vatcode` (`id`) ON DELETE SET NULL,
CONSTRAINT `account_ibfk_9` FOREIGN KEY (`accountoff_id`) REFERENCES `account` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=487803 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
innodb_flush_log_at_trx_commit = 1
速度较慢但更安全。这可能解释了差异。令我困惑的是,它怎么会如此缓慢,以至于每秒只有 10 或 15 行。即使使用 HDD,我也希望 100/秒;更多与 SSD。
您愿意提供
SHOW CREATE TABLE
样品行吗?另外,
ROW_FORMAT
使用的是什么?都是 InnoDB 吗?