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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 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
相关文章
|
12天前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
79 0
|
1天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
7 1
|
8天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
35 9
|
9天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
8天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
28 5
|
5天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
17 1
|
6天前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
|
13天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
18 1
|
13天前
|
缓存 关系型数据库 MySQL
一文彻底弄懂MySQL优化之深度分页
【10月更文挑战第24天】本文深入探讨了 MySQL 深度分页的原理、常见问题及优化策略。首先解释了深度分页的概念及其带来的性能和资源问题。接着介绍了基于偏移量(OFFSET)和限制(LIMIT)以及基于游标的分页方法,并分析了它们的优缺点。最后,提出了多种优化策略,包括合理创建索引、优化查询语句和使用数据缓存,帮助提升分页查询的性能和系统稳定性。
|
3天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
13 0