MySQL参数详解及优化

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

 MySQL安装完成之后,需要对MySQL进行配置以及优化,从而使MySQL可以更高效的运行。


  vi /etc/my.cnf    #打开配置文件


    [mysqld]

      datadir=/data/mysqldata             #在此位置读取数据

      socket=/tmp/mysql.sock        #sock 通信设置

      user=mysql                    #使用mysql启动

      

      symbolic-links=0                #是否支持快捷方式

      log-bin=mysql-bin                 #开启 bin-log 日志

      server-id=1                   #mysql 服务ID

      auto_increment_offset=1            #自增长字段从那个数开始,取值

                                范围:1-65535

      auto_increment_increment=2           #自增长每次递增的量,默认1,取

                                值范围:1-65535

##########在做 主-主同步时需要设置此两项

########## master:auto_increment_offset=1

##########     auto_increment_increment=2

########## master: auto_increment_offset=2

##########     auto_increment_increment=2

##########   将 auto_increment_offset 分别设置为1和2,可以避免两台服务器同时做更新时自增

########## 长字段的值之间发生冲突


      

      port = 3306

      key_buffer = 384M            #用于索引块的缓冲区大小,增加它可以

                          更好的处理索引(对所有读和多重写)。

                    索引块是缓冲的并且被所有线程共享,大小视内存而定


      table_cache = 512        #为所有线程打开表的数量。增加该值能增加mysql

                       描述符的数量。可以避免频繁打开数据表产生开销


      sort_buffer_size = 2M      #每个线程排序时的一个缓冲区,可加速ORDER BY 或

                    GROUP BY  操作。注意:该分配大小是每个连接独占

                    如有100个连接,将占用 2 * 100 = 600M


      read_buffer_size = 2M      #查询操作使用的缓冲区,每连接独占


      query_cache_size = 32M      #指定查询结果缓冲区大小


      read_rnd_buffer_size = 8M     #在使用指针排序后,随机读取用


      myisam_sort_buffer_size = 64M   #MyISAM表发生变化时重新排序所需要的缓冲


      thread_concurrency = 8     #最大线程并发线程数,取值为:CPU数量 * 2,如果

                     CPU 支持 H.T 超线程,再 * 2


      thread_cache = 8        #缓存可重用的线程数


      skip-locking            #避免MYSQL的外部锁定,减少出错几率增强稳定性




    [mysqld_safe]            #mysql安全启动配置


      log-error=/var/log/mysqld.log        #日志


      pid-file=/var/run/mysqld/mysqld.pid     #PID文件


      replicate-do-db=all             #允许 slave 同步哪个库


      key_buffer_size               #指定索引缓冲区的大小,它决定索引

                    处理的速度,尤其是索引读的速度。一般为内存的 50%

      

      show variables lik 'key_buffer_size';

      

      max_connection = 3000    #每个客户端连接最大的错误允许数量,达到此限制,

                    阻止连接,直到执行“FLUSH HOSTS” 或重启服务


      innodb_buffer_pool_size    #相当于key_buffer_size对于MyISAM 表的作用,使用

                        该参数指定的内存大小来缓冲数据和索引

    ###对于单独的MySQL 服务器来说,可以把该值设置成物理内存的 80%

      

      basedir = path      #  使用此目录作为根目录(安装目录)

      datadir = path      #  从此目录读取数据文件

      pid-file= path      #  为mysqld程序指定一个存放进程 ID 的文件,

                    适用于UNIX/Linux 系统


   


    [mysqldump]

      max_allowed_packet = 16M



此处附一个真实环境 mysql 配置 my.cnf 内容,可以根据实际情况修改----


#########################################################################################


[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

server_id = 10

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mysql/data1

old_passwords = 1

lower_case_table_names = 1

character-set-server = utf8

default-storage-engine = MYISAM

log-bin = bin.log

log-error = error.log

pid-file = mysql.pid

long_query_time = 2

slow_query_log

slow_query_log_file = slow.log

binlog_cache_size = 4M

binlog_format = mixed

max_binlog_cache_size = 16M

max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

back_log = 512

max_allowed_packet = 64M

max_connections = 4096

max_connect_errors = 100

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

sort_buffer_size = 2M

query_cache_size = 64M

table_open_cache = 10000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack = 192K

thread_concurrency = 24

local-infile = 0

skip-show-database

skip-name-resolve

skip-external-locking

connect_timeout = 600

interactive_timeout = 600

wait_timeout = 600

#*** MyISAM

key_buffer_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 1G

myisam_repair_threads = 1

concurrent_insert = 2

myisam_recover

#*** INNODB

innodb_buffer_pool_size = 16G

innodb_additional_mem_pool_size = 32M

innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 120

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_thread_concurrency = 16

innodb_open_files = 10000

#innodb_force_recovery = 4

#*** Replication Slave

read-only

#skip-slave-start

relay-log = relay.log

log-slave-updates



本文转自   tianshuai369   51CTO博客,原文链接:http://blog.51cto.com/songqinglong/1683033



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
95
分享
相关文章
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
574 9
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
115 15
MySQL底层概述—7.优化原则及慢查询
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
35 9
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
80 9
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
66 23
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
38 3
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等