mysql常用重要参数

最新推荐文章于 2024-06-01 11:28:53 发布

達爾蓋的旗幟

最新推荐文章于 2024-06-01 11:28:53 发布

阅读量557

点赞数

分类专栏： Mysql

Mysql 专栏收录该内容

35 篇文章 1 订阅

订阅专栏

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

server-id = 1

back_log = 500
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。

thread_concurrency = 64
thread_concurrency应设为CPU核数的2倍
show variables like 'thread_concurrency';

skip-name-resolve
禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

default-storage-engine= InnoDB
(设置InnoDB类型，另外还可以设置MyISAM类型)

#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_log_file_size = 500M
#innodb_log_buffer_size = 20M
这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。

#innodb_flush_log_trx_commit = 1
注：innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0，1，2，解释如下：
0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;
1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;
2：事务提交会触发log buffer到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

key_buffer_size = 400M
key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的一种类型，可以百度等查看详情)表性能影响最大的一个参数
怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads ，比例key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下命令查得：show status like 'key_read%';

innodb_buffer_pool_size = 1G
MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O;
可以通过 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests * 100% 计算缓存未命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得：show status like 'Innodb_buffer_pool_read%';

innodb_additional_mem_pool_size = 20M
设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数大小了。

query_cache_size = 40M
主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。
可以通过命令：show status like 'Qcache_%';查看目前系统Query catch使用大小
| Qcache_hits | 1892463 |
| Qcache_inserts | 35627
命中率98.17%=1892463/(1892463 +35627 )*100

read_buffer_size = 4M
是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一
缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能.

sort_buffer_size = 4M
是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小

read_rnd_buffer_size = 8M
是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开
销过大。

table_open_cache = 128
指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了.SHOW STATUS LIKE 'Open%tables';
mysql手册上给的建议大小是:table_open_cache=max_connections*n
n表示查询语句中最大表数, 还需要为临时表和文件保留一些额外的文件描述符。

thread_cache_size = 64
默认的thread_cache_size=8，但是看到好多配置的样例里的值一般是32，64，甚至是128
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(–>表示要调整的值) 根据物理内存设置规则如下：
1G —> 8
2G —> 16
3G —> 32
>3G —> 64
mysql> show status like 'thread%';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 0 | <—当前被缓存的空闲线程的数量
| Threads_connected | 1 | <—正在使用（处于连接状态）的线程
| Threads_created | 1498 | <—服务启动以来，创建了多少个线程
| Threads_running | 1 | <—正在忙的线程（正在查询数据，传输数据等等操作）
+——————-+——-+
查看开机起来数据库被连接了多少次？
mysql> show status like '%connection%';
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Connections | 1504 | –>服务启动以来，历史连接数
| Max_used_connections | 2 |
+———————-+——-+
通过连接线程池的命中率来判断设置值是否合适？命中率超过90%以上,设定合理。
(Connections - Threads_created) / Connections * 100 %