mysql性能最大化

灰太狼 2022-08-26 08:43 80阅读 0赞

\[client\]  
  
\#passwd = your\_passwd  
  
port = 3306  
  
socket = /data/mysql/mysql.sock  
  
\[mysqld\]  
  
\#global settings  
  
port = 3306  
  
socket = /data/mysql/mysql.sock  
  
basedir=/usr/local/mysql  
  
tmpdir=/data/mysql  
  
datadir=/data/mysql  
  
pid-file=/data/mysql/rongzhong.pid  
  
\#跳过外部锁定 只对MyISAM  
  
skip-external-locking  
  
\#禁用tcp socket连接，只能使用unix/linux socket连接  
  
skip\_networking  
  
\#禁用DNS解析，提高连接速度  
  
skip\_name\_resolve  
  
\#跳过授权表，忘记root密码时使用  
  
\#skip-grant-tables  
  
\#默认存储引擎  
  
\#default-storage-engine=MyISAM/INNODB  
  
\#默认字符集  
  
character\_set\_server=utf8  
  
\#最大连接数  
  
max\_connections=20480  
  
\#最大单用户连接数  
  
max\_user\_connections=1024  
  
\#服务器关闭一个交互连接前等待的秒数，默认28800  
  
interactive\_timeout=7200  
  
\#线程缓存数，直接利用空闲连接来创建新连接，1G  —> 8 2G  —> 16 3G  —> 32 >3G  —> 64  
  
\#短连接较多的情况下可以适当增加该值  
  
thread\_cache\_size=64  
  
\#单个线程（连接）进行数据排序（eg: order/group by）时的buffer，连接创建时一次性分配，默认2M，当 Sort\_merge\_passes较大时可增加该值  
  
sort\_buffer\_size=2M  
  
\#对没有索引的表进行join操作时的buffer，默认256K，连接创建时一次性分配  
  
join\_buffer\_size=262144  
  
\#指定连接侦听队列的大小，该值超过OS的tcp\_max\_syn\_backlog/somaxconn时无效，推荐设置为小于512的整数  
  
back\_log=512  
  
\#table\_open\_cache中岁多能打开的表数  
  
table\_open\_cache = 256  
  
\#一次消息传输量的最大值，是net\_buffer\_length的最大值  
  
max\_allowed\_packet=8M  
  
  
\#bin-log  
  
\#打开二进制日志  
  
log-bin=mysql-bin  
  
log-bin-index=mysql-bin.index  
  
\#日志格式，可选row/statement/mixed，默认statement，推荐mixed  
  
binlog\_format=mixed  
  
\#一个binlog的最大值  
  
max\_binlog\_size=1G  
  
\#对单个连接的binlog缓存大小，默认1M  
  
binlog\_cache\_size=2M  
  
\#binlog cache的总大小  
  
max\_binlog\_cache\_size=2G  
  
\#sync\_binlog may be 0 or 1  
  
\#binlog是否进行磁盘同步，为0时交给OS，为1：事物结束立即同步，IO损耗大  
  
sync\_binlog=0  
  
\#slow-log  
  
\#开启slow log  
  
slow-query-log=1  
  
\#指定慢查询的时长  
  
long-query-time=2  
  
\#路径  
  
slow-query-log-file=/data/mysql/slowlog/slow.log  
  
\#未使用索引的查询也记录到slow log中  
  
log\_queries\_not\_using\_indexes=1  
  
\#query cache  
  
\#启用query cache  
  
query\_cache\_type=1  
  
\#存放单条query cache的最大值，默认1M  
  
query\_cache\_limit=1M  
  
\#存放单条query cache的最小值，默认4K  
  
query\_cache\_min\_res\_unit=4096  
  
\#query的最大值  
  
query\_cache\_size=256M  
  
  
\#MyISAM  
  
\#索引缓存大小，公式：Key\_Size = key\_number \* (key\_length+4)/0.67  
  
key\_buffer\_size=128M  
  
\#单个线程进行顺序读取时的缓存，默认128K  
  
read\_buffer\_size=2M  
  
\#单个线程进行随机读取时的缓存，默认256K  
  
read\_rnd\_buffer\_size = 4M  
  
\#重建索引时允许的最大缓存大小，默认8M  
  
myisam\_sort\_buffer\_size = 64M  
  
\#指定索引缓存中block大小，默认1K  
  
key\_cache\_block\_size=1024  
  
\#禁止索引的单条刷新  
  
delay\_key\_write=1  
  
\#innodb  
  
\#启用独立的表空间  
  
\#innodb\_file\_per\_table=1  
  
\#设置innodb内存缓冲区大小，在纯mysql环境下，推荐设置为服务器内存的60~80%  
  
innodb\_buffer\_pool\_size =8G  
  
\#设置OS能进入innodb内核的线程数，推荐2\*（CPU核心数+磁盘数）  
  
innodb\_thread\_concurrency=16  
  
\#设置innodb的io行为，可选值fsync/O\_DSYNC/O\_DIRECT 推荐O\_DIRECT提高随机写效率  
  
innodb\_flush\_method=O\_DIRECT  
  
\#设置mysql主进程每秒钟向磁盘刷入的脏页数，默认值200  
  
\#For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100  
  
innodb\_io\_capacity=100  
  
\#设置写脏页的线程数  
  
innodb\_write\_io\_threads = 8  
  
\#设置从磁盘读文件块的线程数  
  
innodb\_read\_io\_threads = 8  
  
\#设置事物日志缓冲区大小  
  
\#设置事物日志缓冲区大小  
  
innodb\_log\_buffer\_size=8M  
  
\#log thread向磁盘同步日志的方式，可选值为0/1/2  
  
\#0: 每秒钟刷新  1：每次事物提交后都刷新 2：交给OS来同步  
  
innodb\_flush\_log\_at\_trx\_commit =0  
  
\#数据库字典信息和表结构空间  
  
innodb\_additional\_mem\_pool\_size =16M  
  
\#启用Double write Buffer区域，保证数据完整性，但消耗IO  
  
innodb\_doublewrite=1  
  
  
\[mysqldump\]  
  
\# Do not buffer the whole result set in memory before writing it to file. Required for dumping very large tables  
  
quick  
  
max\_allowed\_packet = 16M  
  
\[mysql\]  
  
no-auto-rehash  
  
\[myisamchk\]  
  
key\_buffer\_size = 512M  
  
sort\_buffer\_size = 512M  
  
read\_buffer = 8M  
  
write\_buffer = 8M  
  
\[mysqlhotcopy\]  
  
interactive-timeout  
  
\[mysqld\_safe\]  
  
\# Increase the amount of open files allowed per process. Warning: Make  
  
\# sure you have set the global system limit high enough! The high value  
  
\# is required for a large number of opened tables  
  
open-files-limit = 8192