开发者社区> 游客a3ep6ym4bwkkk> 正文

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

简介: 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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
转载:30多条mysql数据库优化方法,千万级数据库记录查询轻松解决
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描, Sql 代码 : select id from t where num is null; 可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询: Sql 代码 : select id from t where num=0; 3.应尽量避免在 where 子句中使用!=或操作符,否则将引擎放弃使用索引而进行全表扫描。
867 0
MySQL数据库优化技巧
MySQL优化三大方向① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。② 对MySQL配置参数进行优化(my.cnf)此优化需要进行压力测试来进行参数调整。③ 对SQL语句以及表优化。MySQL参数优化1:MySQL 默认的最大连接数为 100,可以在 mysql 客户端使用以下命令.
1588 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
18061 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
24834 0
记一次Mysql大数据分页优化问题
记一次Mysql大数据分页优化问题
30 0
优化MySQL数据库查询的三种方法简介
  在优化查询中,数据库应用(如MySQL)即意味着对工具的操作与使用。使用索引、使用EXPLAIN分析查询以及调整MySQL的内部配置可达到优化查询的目的。   任何一位数据库程序员都会有这样的体会:高通信量的数据库驱动程序中,一条糟糕的SQL查询语句可对整个应用程序的运行产生严重的影响,其不仅消耗掉更多的数据库时间,且它将对其他应用组件产生影响。
830 0
3
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载