MySql5.7数据优化,瞬间飞起

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySql5.7数据优化,瞬间飞起

Other default tuning values

MySQL Server Instance Configuration File

----------------------------------------------------------------------

Generated by the MySQL Server Instance Configuration Wizard

Installation Instructions

----------------------------------------------------------------------

On Linux you can copy this file to /etc/my.cnf to set global options,

mysql-data-dir/my.cnf to set server-specific options

(@localstatedir@ for this installation) or to

~/.my.cnf to set user-specific options.

On Windows you should keep this file in the installation directory

of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To

make sure the server reads the config file use the startup option

"--defaults-file".

To run the server from the command line, execute this in a

command line shell, e.g.

mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

To install the server as a Windows service manually, execute this in a

command line shell, e.g.

mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

And then execute this in a command line shell to start the server, e.g.

net start MySQLXY

Guidelines for editing this file

----------------------------------------------------------------------

In this file, you can use all long options that the program supports.

If you want to know the options a program supports, start the program

with the "--help" option.

More detailed information about the individual options can also be

found in the manual.

For advice on how to change settings please see

http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

CLIENT SECTION

----------------------------------------------------------------------

The following options will be read by MySQL client applications.

Note that only client applications shipped by MySQL are guaranteed

to read this section. If you want your own MySQL client program to

honor these values, you need to specify it as an option during the

MySQL client library initialization.

[client]

pipe=

socket=MYSQL

port=3306

[mysql]
no-beep

default-character-set=

SERVER SECTION

----------------------------------------------------------------------

The following options will be read by the MySQL Server. Make sure that

you have installed the server correctly (see above) so it reads this

file.

server_type=2

[mysqld]

The next three options are mutually exclusive to SERVER_PORT below.

skip-networking

enable-named-pipe

shared-memory

shared-memory-base-name=MYSQL

The Pipe the MySQL Server will use

socket=MYSQL

The TCP/IP Port the MySQL Server will listen on

port=3306

Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

Path to the database root

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

The default character set that will be used when a new schema or table is

created and no character set is defined

character-set-server=

automatic_sp_privileges = 1
auto_increment_increment = 1
auto_increment_offset = 1
avoid_temporal_upgrade = 0
back_log = 3000
binlog_cache_size = 128KB
binlog_checksum = CRC32
binlog_order_commits = 1
binlog_rows_query_log_events = 0
binlog_row_image = full
binlog_stmt_cache_size = 32768
block_encryption_mode = "aes-128-ecb"
bulk_insert_buffer_size = 4194304
character_set_filesystem = binary
character_set_server = utf8
concurrent_insert = 1
connect_timeout = 10
default_storage_engine = InnoDB
default_time_zone = SYSTEM
default_week_format = 0
delayed_insert_limit = 100
delayed_insert_timeout = 300
delayed_queue_size = 1000
delay_key_write = 1
disconnect_on_expired_password = 1
div_precision_increment = 4
end_markers_in_json = 0
eq_range_index_dive_limit = 10
event_scheduler = 0
explicit_defaults_for_timestamp = 0
flush_time = 0
ft_max_word_len = 84
ft_min_word_len = 4
ft_query_expansion_limit = 20
group_concat_max_len = 1024
host_cache_size = 644
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_hash_index = 1
innodb_adaptive_max_sleep_delay = 150000
innodb_autoextend_increment = 64
innodb_autoinc_lock_mode = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_change_buffering = all
innodb_change_buffer_max_size = 25
innodb_checksum_algorithm = crc32
innodb_cmp_per_index_enabled = 0
innodb_commit_concurrency = 0
innodb_compression_failure_threshold_pct = 5
innodb_compression_level = 6
innodb_compression_pad_pct_max = 50
innodb_concurrency_tickets = 5000
innodb_deadlock_detect = 1
innodb_disable_sort_file_cache = 1
innodb_flush_method = normal
innodb_flush_neighbors = 1
innodb_flush_sync = 1
innodb_ft_cache_size = 8000000
innodb_ft_enable_diag_print = 0
innodb_ft_enable_stopword = 1
innodb_ft_max_token_size = 84
innodb_ft_min_token_size = 3
innodb_ft_num_word_optimize = 2000
innodb_ft_result_cache_limit = 2000000000
innodb_ft_sort_pll_degree = 2
innodb_ft_total_cache_size = 640000000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_large_prefix = 0
innodb_lock_wait_timeout = 50
innodb_log_checksums = 1
innodb_log_compressed_pages = 0
innodb_lru_scan_depth = 1024
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 0
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 0
innodb_max_undo_log_size = 1073741824
innodb_monitor_enable =
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 3000
innodb_optimize_fulltext_only = 0
innodb_page_cleaners = 1
innodb_print_all_deadlocks = 0
innodb_purge_batch_size = 300
innodb_purge_rseg_truncate_frequency = 128
innodb_purge_threads = 1
innodb_random_read_ahead = 0
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 4
innodb_rollback_on_timeout = 0
innodb_rollback_segments = 128
innodb_sort_buffer_size = 1048576
innodb_spin_wait_delay = 30
innodb_stats_auto_recalc = 1
innodb_stats_method = nulls_equal
innodb_stats_on_metadata = 0
innodb_stats_persistent = 1
innodb_stats_persistent_sample_pages = 20
innodb_stats_sample_pages = 8
innodb_stats_transient_sample_pages = 8
innodb_status_output = 0
innodb_status_output_locks = 0
innodb_strict_mode = 0
innodb_sync_array_size = 1
innodb_sync_spin_loops = 100
innodb_table_locks = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 10000
innodb_write_io_threads = 4
interactive_timeout = 7200
join_buffer_size = 352KB
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
lc_time_names = en_US
lock_wait_timeout = 31536000
log_queries_not_using_indexes = 0
log_throttle_queries_not_using_indexes = 0
long_query_time = 1
loose_optimizer_trace = enabled=off,one_line=off
loose_optimizer_trace_features = greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
loose_opt_rds_enable_show_slave_lag = 1
loose_opt_rds_last_error_gtid = 1
loose_rds_check_core_file_enabled = 1
loose_rds_kill_connections = 20
loose_thread_handling = "one-thread-per-connection"
loose_thread_pool_oversubscribe = 10
loose_thread_pool_stall_limit = 30
loose_validate_password_length = 8
low_priority_updates = 0
master_verify_checksum = 0
max_allowed_packet = 1024M
max_binlog_stmt_cache_size = 18446744073709547520
max_connect_errors = 100
max_error_count = 64
max_heap_table_size = 67108864
max_join_size = 18446744073709551615
max_length_for_sort_data = 1024
max_points_in_geometry = 65536
max_prepared_stmt_count = 16382
max_seeks_for_key = 18446744073709500000
max_sort_length = 1024
max_sp_recursion_depth = 0
max_write_lock_count = 102400
metadata_locks_cache_size = 1024
min_examined_row_limit = 0
myisam_sort_buffer_size = 262144
mysql_native_password_proxy_users = 0
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 60
ngram_token_size = 2
old_passwords = 0
open_files_limit = 65535
optimizer_prune_level = 1
optimizer_search_depth = 62
optimizer_trace_limit = 1
optimizer_trace_max_mem_size = 16384
optimizer_trace_offset = -1
performance_schema = 0
preload_buffer_size = 32768
query_alloc_block_size = 8192
query_cache_limit = 1048576
query_cache_min_res_unit = 1024
query_cache_size = 3145728
query_cache_type = 0
query_cache_wlock_invalidate = 0
query_prealloc_size = 8192
range_alloc_block_size = 4096
range_optimizer_max_mem_size = 8388608

rds_reserved_connections = 512

session_track_gtids = 0
session_track_schema = 1
session_track_state_change = 0
sha256_password_proxy_users = 0
show_compatibility_56 = 0
show_old_temporals = 0
slave_net_timeout = 60
slave_parallel_type = LOGICAL_CLOCK
slow_launch_time = 2
slow_query_log = 1
sort_buffer_size = 704KB
sql_mode =
stored_program_cache = 256
table_definition_cache = 512
table_open_cache = 2000
table_open_cache_instances = 1
thread_cache_size = 100
thread_stack = 262144
tls_version = TLSv1,TLSv1.1,TLSv1.2
transaction_isolation = READ-COMMITTED
updatable_views_with_limit = YES
wait_timeout = 86400
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
tmp_table_size = 2097152

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
119 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
13天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
13天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
50 14
|
16天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
46 9
|
17天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
44 3
|
20天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
43 1
|
27天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
71 9
|
21天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
102 1
|
27天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
27天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
56 5