MySQL内存及虚拟内存优化设置参数

作者:计算机专家

从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx 开头。

1.1.5 Binlog_cache_size(线程独有)

二进制日志缓存支持事务存储引擎并且服务器启用了二进制日志的前提下为每个客户端分配内存。通过binlog_cache_use和binlog_cache_disk_use来判断当前binlog_cache_size是否合适,默认值1M

与binlog_cache_size对应,max_binlog_cache_size,代表binlog能够使用的最大的cache的大小。注意,当执行多语句事务的时候,max_binlog_cache_size不够大的话,系统会保出”multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”的错误。

(8)、thread_cache_size:
可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。

  由于SHOW VARIABLES下的参数都是静态值。当mysql重启时,上面的缓存设置将失效。

1.3.1 innodb_buffer_pool

innodb_buffer_pool不同于key_buffer的地方是它不仅缓存索引还会缓存实际的数据。所以完全相同的数据库,使用innodb存储引擎可以使用更多的内存来缓存数据库相关的信息。

关于innodb_buffer_pool_size的设置,建议设置为系统物理内存的50%-80%之间,最好是比innodb的tablespace多10%的空间。

通过以下两个比率来查看innodb_buffer_pool_size的值是否设置过大:

使用率:(innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total)*100% >90%

命中率:(innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/

innodb_buffer_pool_read_requests * 100%  >90%

您可能感兴趣的文章:

  • MySQL性能参数详解之Skip-External-Locking参数介绍
  • MySQL性能参数详解之Max_connect_errors 使用介绍
  • MySQL修改tmpdir参数
  • my.ini优化mysql数据库性能的十个参数(推荐)
  • mysql数据存储过程参数实例详解

  上面内存约等于2031912/1024.0=1984M。
2. buffer_pool 运行参数

mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参数的配置在一定程度内对mysql性能的影响是显著的。同时各种个性化参数的设置,可以使mysql表现出不同的性状。

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m),我把它改为 16777208 (16m)。

查看内存大小:
[root@xuegod64 ~]# cat /proc/meminfo

1.1.4 sort buffer(线程独有)

当sql需要进行排序操作时会用到sort buffer。通过增大sort buffer的大小可以提高order by或group by的处理性能。系统默认为2M,最大限制和join buffer一样,一般设置在2mb~4mb之间可以满足大多数应用的需求。

(9)mysql的搜索功能
用mysql进行搜索,目的是能不分大小写,又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312

Innodb_buffer_pool_pages_total 

缓存池页总数目。
共占用了16382 页 。单位page

Innodb_buffer_pool_pages_free

缓存池剩余的页数目。
在16382 页中有2000页没有使用。 单位page

Innodb_buffer_pool_pages_data

缓存池中包含数据的页的数目,包括脏页。
14273个页含有数据。单位page

Innodb_buffer_pool_read_requests

innodb进行逻辑读的数量。
529670886次请求读。单位次数

Innodb_buffer_pool_reads

进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数。
941147次是物理I/0读取。单位次数

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。
48606702次请求写入。单位次数

Innodb_buffer_pool_read_ahead_rnd

记录进行随机读的时候产生的预读次数。
0次

Innodb_buffer_pool_read_ahead

预读到innodb buffer pool里次数。
1465370次。  单位page

Innodb_buffer_pool_read_ahead_evicted

预读的页数,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率。 0次

Innodb_buffer_pool_wait_free

数据要写入buffer pool的时候,需要等待空闲页的次数。是指缓存池里没有干净页的时候读取或创建页,要先等待页被刷新。
2927次。 单位次数。

Innodb_buffer_pool_pages_dirty

buffer pool缓存池中脏页的数目。
0次。单位是page

Innodb_buffer_pool_pages_flushed

buffer pool缓存池中刷新页请求的数目。
15437744次。单位page

Innodb_buffer_pool_pages_misc

buffer pool缓存池中当前页已经被用作管理用途或hash index而不能用作为普通数据页的数目。
109次。单位page

Innodb_buffer_pool_pages_old

在旧区域存放着多少个页。
5249次。单位page

Innodb_buffer_pool_pages_made_young

移动到新区域的有多少个页。
353059次。单位page

Innodb_buffer_pool_pages_made_not_young

没有移动到新区域的有多少个页。
31725809次。单位page

1.3.2 Innodb_additional_mem_pool_size

Innodb_additional_mem_pool_size用于存放innodb的字典信息和其他一些内部结构所需要的内存空间。Innodb表越多,需要的空间就越大,系统默认为1M。

一个常规的几百个innodb表的mysql,如果不是每个表都是有上百个字段的话,20M内存就足够,设置超过实际所需要的内存并没有太大的意义,只是浪费内存而已。

 

MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
(db用户在dbname的数据库上控制用户每小时修改更新数据库的次数为5次)
MAX_USER_CONNECTIONS 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql单个用户的最大连接数
(db用户在dbname的数据库上控制用户每小时的连接数为20个)
 
调优举例
针对my.cnf文件进行优化:
[mysqld]
skip-locking(取消文件系统的外部锁)
skip-name-resolve(不进行域名反解析,注意由此带来的权限/授权问题)
key_buffer_size = 256M(分配给MyISAM索引缓存的内存总数)对于内存在4GB左右的服务器该参数可设置为256M或384M。
  注意:该参数值设置的过大反而会是服务器整体效率降低!
  max_allowed_packet = 4M(允许最大的包大小)
  thread_stack = 256K(每个线程的大小)
  table_cache = 128K(缓存可重用的线程数)
  back_log = 384(临时停止响应新请求前在短时间内可以堆起多少请求,如果你需要在短时间内允许大量连接,可以增加该数值)
  sort_buffer_size = 2M(分配给每个线程中处理排序)
  read_buffer_size = 2M(读取的索引缓冲区大小)
  join_buffer_size = 2M(分配给每个线程中处理扫描表连接及索引的内存)
  myisam_sort_buffer_size = 64M(myisam引擎排序缓冲区的大小)
  table_cache = 512(缓存数据表的数量,避免重复打开表的开销)
  thread_cache_size = 64(缓存可重用线程数,见笑创建新线程的开销)
  query_cache_size = 64M(控制分配给查询缓存的内存总量)
  tmp_table_size = 256M(指定mysql缓存的内存大小)
  max_connections = 768(最大连接数)指mysql整个的最大连接数
max_connect_errors = 10000(最大连接错误数据)
  wait_timeout = 10(超时时间,可以避免攻击)
  thread_concurrency = 8(根据cpu数量来设置)
  skip-bdb 禁用不必要的引擎
  skip-networking(关闭mysql tcp/ip连接方式)
  Log-slow-queries = /var/log/mysqlslowqueries.log
  long_query_time = 4(设定慢查询的时间)
  skip-host-cache(提高mysql速度的)
  open_files_limit = 4096(打开文件数)
interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
max_user_connections = 500(最大用户连接数)
 
key_buffer_size                 默认为218       调到128最佳
query_cache_size 
tmp_table_size                  默认为16M        调到64-256最挂

  图片 1

2.3 innodb存储引擎级别的参数

Innodb_max_dirty_pages_pct=75

当脏数据达到多少时,刷新innodb_buffer_pool,此参数的设置一方面可以加速数据的处理,另 一方面也可能导致数据的不一致。

Innodb_flush_log_at_trx_commit

控制innodb事务日志刷新方式的参数

0 每个一秒刷新

1 每次事务结束同步

2 每次事务结束同步,由于调用了文件系统的写入操作,而文件系统是有缓存的,所以并没有真正同 步,但性能最好

Innodb_log_files_in_group=2

指定几个日志组,默认是2

Innodb_log_file_size

指定innodb日志文件大小

Innodb_file_io_threads=4

文件读写io数,这个参数只在window下起作用,linux下只会等于4    ---废弃了吗?

Innodb_file_per_table   

使用单独的表空间

innodb_doublewrite

使用的是一种较为独特的flush实现技术,主要作用是为了通过减少文件同步次数提高io性能的情况 下,提高系统crash或者断电情况下数据的安全性,避免写入的数据不完整。

innodb_adaptive_hash_index

不是为了改善磁盘io的性能,而是为了提高buffer pool中的数据的访问效率,就是给buffer pool中的 数据做的索引。

Innodb_thread_concurrency=0

默认设置为0,让系统自己控制

Innodb_lock_wait_timeout = 50

该参数主要用于出现死锁的时候等待指定时间后回滚

(3)、key_buffer_size:
为了最小化磁盘的 I/O , MyISAM 存储引擎的表使用键高速缓存来缓存索引,这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主,则应该适当增加该参数的值,以便尽可能的缓存索引,提高访问的速度。

    图片 2

1.1 跨引擎缓存参数优化

这类缓存参数是针对查询的优化,优化方向是sql、表、日志、线程对象的缓存优化。具体说明如下:

(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上,可以考虑把该参数设置为物理内存大小的 60%-80%
 
InnoDB占用的内存,除innodb_buffer_pool_size用于存储页面缓存数据外,另外正常情况下还有大约8%的开销,主要用在每个缓存页帧的描述、adaptive hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开大约12%的内存用于恢复,两者相加就有差不多21%的开销。

本文由杏彩发布,转载请注明来源

关键词: