一.概述

         mysql
提供了很多参数来进行服务器的设置,当服务第一次启动的时候,所有启动参数值都是系统默认的。这些参数在很多生产环境下并不能满足实际的应用需求。在这个系列中涉及到了liunx
服务器,我这里是centos7.4, mysql 5.7,Xshell6。

  1. 查看mysql server参数

         通过show variables和show
status命令查看mysql的服务器静态参数值和动态运行状态信息。前者是在数据库启动后不会动态更改的值。比如缓冲区大小,字符集,数据文件名称等;
后者是数据库运行期间的动态变化的信息,比如锁等待,当前连接数等。下面来简单查看下两个命令

--  mysql服务静态参数值
SHOW VARIABLES;

威尼斯城真人赌钱网站 1

--  mysql服务运行状态值
SHOW STATUS;

威尼斯城真人赌钱网站 2

修改一个索引缓存

和创建一个索引缓存一样一样的,都是set global
缓存索引名.key_buffer_size=100*1024;

max_connections = 1000

4)、back_log:要求 mysql
能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间检查连接并且启动一个新线程。back_log
值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。当你观察你的主机进程列表,发现大量
264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null
| login | null 的待连接进程时,就要加大 back_log
的值了。默认数值是50,我把它改为500。

默认索引缓存

  • load index into cache tbl; 将tbl的索引装载到默认的索引缓存中
  • 默认的索引缓存不能删除

二.  影响mysql性能的重要参数

  在上面介绍了mysql server 端的参数查看方法 ,对于这么多参数,
实际大多数参数是不需要用户调整的,下面介绍一些重要参数。先介绍下MyISAM存储引擎的key_buffer_size和table_cache。

  1. key_buffer_size设置

                   key_buffer_size是用来设置索引块(index
Blocks)缓存的大小,它被所有线程共享,此参数只应于MYISAM存储引擎。在mysql
5.1后,系统除了默认的索引块缓存,还可以配置多个key_buffer,可以将指定的表索引,缓存入指定的key_buffer,这样可以更小地降低线程之间的竞争。

-- 查看默认设置
 SHOW VARIABLES LIKE 'key_buffer_size';  

    8388608/1024.0/1024.0=M  默认8M 如下图所示:

     威尼斯城真人赌钱网站 3

-- 建立一个新的索引块缓存
SET  GLOBAL hot_cache2.key_buffer_size=128*1024

  对于GLOBAL
表示对每一个新的连接,此参数都将生效,hot_cache2是新的key_buffer名称,可以随时进行重建,例如:

SET  GLOBAL hot_cache2.key_buffer_size=200*1024

  把相关表的索引,放到指定的索引块缓存中如下:

-- 将表(userbymyisam )索引放入指定的索引块中 
CACHE INDEX userbymyisam IN hot_cache2

威尼斯城真人赌钱网站 4

  想将索引预装到默认的key_buffer中,可以使用load index into
cache语句,例如预装表userbymyisam的所有索引 预装到默认的key_buffer。

LOAD INDEX INTO CACHE userbymyisam

威尼斯城真人赌钱网站 5

-- 删除索引缓存,如下命令
SET  GLOBAL hot_cache2.key_buffer_size=0

  注意:默认的key_buffer是不能删除的如:  SET  GLOBAL
key_buffer_size=0 下次重启时还会有。

 总结: cache
index命令在一个表和key_buffer之间建立一种联系,但每次服务器重启时key_buffer中的数据将清空,如果想每次服务器重启时相应表的索引能自动放到key_buffer中,可以在配置文件中设置init-file选项来指定包含cache
index语句文件路径,然后在对应的文件中写入cache index语句。

   下面创建二个缓存索引块:

威尼斯城真人赌钱网站 6

  每次服务器启动时,执行mysqld_init.sql中的语句,
文件中几个表,分别对应hot_cache和cold_cache:
威尼斯城真人赌钱网站 7

  2. 通过操作系统来设置key_buffer

    如果要设置mysql服务系统参数可以在liunx里设置,先要找到my.cnf
文件位置,一般会放在/etc/my.cnf,/etc/mysql/my.cnf。
    如下图所示,尝试修改默认key_buffer_size改为12M:
    威尼斯城真人赌钱网站 8
    威尼斯城真人赌钱网站 9
    停止服务再重启sql服务
    威尼斯城真人赌钱网站 10
    威尼斯城真人赌钱网站 11
    再次查询如下

    威尼斯城真人赌钱网站 12

将索引放到默认的kef_buffer中

可以使用load index into cache +表名

 不过为了安全期间,建议大家直接到my.ini里修改,么有可以加上。

innodb_thread_concurrency=8
你的服务器CPU有几个就设置为几,默认为8table_cache=1024
物理内存越大,设置就越大.默认为2402,调到512-1024最佳innodb_additional_mem_pool_size=8M
默认为2Minnodb_flush_log_at_trx_commit=0
等到innodb_log_buffer_size列队满后再统一储存,默认为1innodb_log_buffer_size=4M
默认为1M

参数意义

MyISAM 设置索引块的大小,它被所有的线程共享

8.innodb_lock_wait-timeout

Mysql可以自动地监控行锁导致的死锁并经行相应的处理,但是对于表锁导致的死锁,不能自动检测,该参数主要是用于在出现行死锁时候等待指定的时间后回滚。

3、实时(临时)修改此参数的值

  • @@sort_buffer_size+ @@join_buffer_size + @@binlog_cache_size +
    @thread_stack) ) / @giga_bytes AS
    MAX_MEMORY_GB;mysql关键参数设置Mysqld
    数据库的参数设置有两种类型,一种是全局参数,影响服务器的全局操作;另一种是会话级参数,只影响当前的客户端连接的相关操作。服务器启动时,所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行
    SET GLOBAL var_name
    语句可以更改动态全局参数。要想更改全局参数,必须具有 SUPER
    权限。全局参数的修改只对新的连接生效,已有的客户端连接并不会生效。服务器还可以为每个客户端连接维护会话级参数,客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过
    SET SESSION var_name
    语句来更改动态会话参数。设置会话级参数不需要特殊权限,但每个客户端可以只更改自己的会话级参数,不能更改其它客户的会话级参数。不指定设置的参数类型时,默认设置的是会话级参数。

key_buffer与表之间的关联

服务器每次重启都会将key_buffer中的数据清空,每次重启要将相应表的索引缓存到key_buffer中,通过配置文件中的init-file关联,如下例子:

key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/mysqld-index-init.sql

mysqld-index-init.sql文件内容如下:

cache index a.tbl1, a.tbl2, b.tbl3 in hot_cache;
cache index a.t3, a.t4, b.t1 in cold_cache

将相关表的索引放到自己创建的索引缓存中

格式:cache index 表名1,表名2 in
索引缓存将t1、t2、t3表中的索引放到my_cache索引缓存中
威尼斯城真人赌钱网站 13

因为t1表式InnoDB表,t2,t3表为MyISAM表,故只有t2、t3表中的索引可以放到my_cache缓存中。

如果你正用 HANDLER tbl_name
OPEN语句打开一个表,将为该线程专门分配一个表。该表不被其它线程共享,只有线程调用HANDLER
tbl_name
CLOSE或线程终止后才被关闭。表关闭后,被拉回表缓存中(如果缓存不满)。

、interactive_timeout:服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对
mysql_real_connect()使用 client_interactive 选项的客户。
默认数值是28800,我把它改为7200。

创建新的索引缓存

  1. set global hot_cache2.key_buffer_size = 100 * 1024 * 1024
    建立一个100M索引缓存
  2. cache index tbl1,tbl2 in hot_cache2
    把先关表的索引放到指定的索引缓存中
  3. set global hot_cache2.key_buffer_size = 0; 删除索引缓存

配置mysql服务器启动时自动加载索引缓存

在MySQL配置文件中添加如下内容(在Windows下叫my.ini,在Linux下叫my.cnf)
my_cache.key_buffer_size=1G #指定索引缓存区大小
init_file=/usr/local/mysql/init_index.sql#在该文件中指定要加载到缓存区德索引
init_index.sql内容如下:
cache index t2 into my_cache;
cache index t3 into my_cache;

个人感觉有点像php里面的fopen打开一个连接,操作完数据之后,并不立即
关闭,而是缓存起来,等待下一个连接这个文件的请求就不必去重新打开文件了,不知样理解对不对,哈.

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-lockingskip-name-resolve(不进行域名反解析,注意由此带来的权限/授权问题)key_buffer_size
= 256M对于内存在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 table_cache = 512 thread_cache_size
= 64 query_cache_size = 64M tmp_table_size = 256M max_connections =
768指mysql整个的最大连接数max_connect_errors = 10000 wait_timeout =
10 thread_concurrency = 8 skip-bdb 禁用不必要的引擎 skip-networking
Log-slow-queries = /var/log/mysqlslowqueries.log long_query_time = 4
skip-host-cache open_files_limit = 4096interactive_timeout =
10(服务器在关闭它前在一个交互连接上等待行动的秒数)max_user_connections
= 500key_buffer_size 默认为218 调到128最佳query_cache_size
tmp_table_size 默认为16M 调到64-256最挂

计算索引未命中缓存的概率

  • show global status like ‘key_read%’ 查看key_buffer_size的使用情况

+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+
  • Key_read_requests:从缓存读取索引的请求次数。
  • Key_reads:从磁盘读取索引的请求次数。

概述

这个参数表示数据库用户打开表的缓冲数量,table_cache与max_connections有关。当某一连接访问一个表时,MySQL会检查当前已缓存表的数量,如果该表已经在缓冲中打开,则直接访问缓存中的表,如果为被缓存,则会将当前表添加进缓存并进行查询。在执行缓存操作之前,table_cache用于限制缓存表的最大数目,如果当前已经缓存的表未达到table_cache,则会将新表添加进来;若已经达到此值,MySQL将根据缓存表的最后查询时间、查询率等规则释放之前缓存的表,添加新表。

MySQL的max_connections参数用来设置最大连接(用户)数。每个连接MySQL的用户均算作一个连接,max_connections的默认值为100。本文将讲解此参数的详细作用与性能影响。

这样,12G的innodb_buffer_pool_size,最多的时候InnoDB就可能占用到14.5G的内存,再加上操作系统用的几百M,近千个线程堆栈,就差不多16G了。MAX_QUERIES_PER_HOUR
用来限制用户每小时运行的查询数量:mysql> grant all on dbname。* to
db@localhost identified by “123456” with max_connections_per_hour
5;(db用户在dbname的数据库上控制用户每小时打开新连接的数量为5个)MAX_USER_CONNECTIONS
限制有多少用户连接MYSQL服务器:mysql> grant all on dbname。* to
db@localhost identified by “123456” with max_user_connections
2;(db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个)

查看未使用的缓存簇(blocks)数

show global status like ‘key_blocks_u%’;

+------------------------+-------------+
| Variable_name | Value |
+------------------------+-------------+
| Key_blocks_unused | 0 |
| Key_blocks_used | 413543 |
+------------------------+-------------+
  • Key_blocks_unused表示未使用的缓存簇(blocks)数
  • Key_blocks_used表示曾经用到的最大的blocks数
  • key_clocks_unused为0,所有的缓存都用到了,可以增加key_buffer_size的值,或者过度索引

3.Innodb_buffer_pool_size

这个参数定义了InnoDB存储引擎的表数据和索引数据的最大内存缓存区大小。和MyISAM存储引擎不同,MyISAM的key_buffer_size只缓存索引键,而Innodb_buffer_pool_size同时为数据块和索引块做了缓存,这个只设的越高,访问表中的数据需要的磁盘I/O就越少。但是设置的过大,会导致物理内存竞争过大。

打开MySQL配置文件my.cnf

、wait_timeout:服务器在关闭它之前在一个连接上等待行动的秒数。
默认数值是28800,我把它改为7200。、innodb_thread_concurrency:你的服务器CPU有几个就设置为几,默认为8。、query_cache_size
与 query_cache_limitQueryCache 之后所带来的负面影响:a) Query
语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache
之后,每条SELECT类型的Query 在到达MySQL 之后,都需要进行一个hash
运算然后查找是否存在该Query 的Cache,虽然这个hash
运算的算法可能已经非常高效了,hash
查找的过程也已经足够的优化了,对于一条Query
来说消耗的资源确实是非常非常的少,但是当我们每秒都有上千甚至几千条Query
的时候,我们就不能对产生的CPU 的消耗完全忽视了。b) Query Cache
的失效问题。如果我们的表变更比较频繁,则会造成Query Cache
的失效率非常高。这里的表变更不仅仅指表中数据的变更,还包括结构或者索引等的任何变更。也就是说我们每次缓存到Query
Cache 中的Cache
数据可能在刚存入后很快就会因为表中的数据被改变而被清除,然后新的相同Query
进来之后无法使用到之前的Cache。c) Query Cache 中缓存的是Result Set
,而不是数据页,也就是说,存在同一条记录被Cache
多次的可能性存在。从而造成内存资源的过渡消耗。当然,可能有人会说我们可以限定QueryCache
的大小啊。是的,我们确实可以限定Query Cache 的大小,但是这样,Query
Cache 就很容易造成因为内存不足而被换出,造成命中率的下降。QueryCache
的正确使用:虽然Query Cache
的使用会存在一些负面影响,但是我们也应该相信其存在是必定有一定价值。我们完全不用因为Query
Cache 的上面三个负面影响就完全失去对Query Cache
的信心。只要我们理解了Query Cache
的实现原理,那么我们就完全可以通过一定的手段在使用Query Cache
的时候扬长避短,重发发挥其优势,并有效的避开其劣势。首先,我们需要根据Query
Cache 失效机制来判断哪些表适合使用Query 哪些表不适合。由于QueryCache
的失效主要是因为Query 所依赖的Table 的数据发生了变化,造成Query 的Result
Set 可能已经有所改变而造成相关的Query Cache
全部失效,那么我们就应该避免在查询变化频繁的Table 的Query
上使用,而应该在那些查询变化频率较小的Table 的Query 上面使用。MySQL
中针对Query Cache 有两个专用的SQL Hint:SQL_NO_CACHE
和SQL_CACHE,分别代表强制不使用Query Cache 和强制使用Query
Cache。我们完全可以利用这两个SQL Hint,让MySQL 知道我们希望哪些SQL
使用Query Cache 而哪些SQL 就不要使用了。这样不仅可以让变化频繁Table
的Query 浪费Query Cache 的内存,同时还可以减少Query Cache
的检测量。其次,对于那些变化非常小,大部分时候都是静态的数据,我们可以添加SQL_CACHE
的SQL Hint,强制MySQL 使用Query
Cache,从而提高该表的查询性能。最后,有些SQL 的Result Set
很大,如果使用Query Cache 很容易造成Cache
内存的不足,或者将之前一些老的Cache 冲刷出去。对于这一类Query
我们有两种方法可以解决,一是使用SQL_NO_CACHE 参数来强制他不使用Query
Cache 而每次都直接从实际数据中去查找,
另一种方法是通过设定“query_cache_limit”参数值来控制Query Cache
中所Cache 的最大Result Set ,系统默认为1M。当某个Query 的Result Set
大于“query_cache_limit”所设定的值的时候,QueryCache 是不会Cache
这个Query 的。

手工添加索引缓存块降低线程间的竞争

影响MySQL性能的重要参数

主要介绍的是使用MyISAM存储引擎的key_buffer_size和table_cache,以及使用使用InnoDB存储引擎的一些以innodb_开头的参数。

 

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引,到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600,我的mysql主机有2gb内存,所以我把它改为
402649088。默认情况下,所有的索引都使用相同的键高速缓存,当访问的索引不在缓存中时,使用
LRU ( Least Recently Used
最近最少使用)算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用,从
MySQL5.1
开始,可以设置多个键高速缓存,并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值,如何设置多个键高速缓存,以及如何为不同的索引指定不同的缓存:显示当前的参数大小,为16M:mysql>
show variables like ‘key_buffer_size’;+—————–+——-+|
Variable_name | Value |+—————–+——-+| key_buffer_size |
16384 |+—————–+——-+1 row in set
修改参数值到200M:mysql> set global key_buffer_size=204800;Query
OK, 0 rows affected mysql> show variables like
‘key_buffer_size’;+—————–+——–+| Variable_name | Value
|+—————–+——–+| key_buffer_size | 204800
|+—————–+——–+1 row in set
上面介绍的是默认的键缓存,下面介绍如何设置多个键缓存:设置 hot_cache
的键缓存 100M , cold_cache 的键缓存 100M ,另外还有 200M
的默认的键缓存。如果索引不指定键缓存,则会放在默认的键缓存中。mysql>
set global hot_cache.key_buffer_size=102400;Query OK, 0 rows affected
mysql> set global cold_cache.key_buffer_size= 1024 00;Query OK, 0
rows affected mysql> show variables like
‘key_buffer_size’;+—————–+——–+| Variable_name | Value
|+—————–+——–+| key_buffer_size | 204800
|+—————–+——–+1 row in set
如果要显示设置的多键缓存的值,可以使用:mysql> SELECT
@@global.hot_cache.key_buffer_size;+————————————+|
@@global.hot_cache.key_buffer_size
|+————————————+| 102400
|+————————————+1 row in set mysql> SELECT
@@global.cold_cache.key_buffer_size;+————————————-+|
@@global.cold_cache.key_buffer_size
|+————————————-+| 102400
|+————————————-+1 row in set
指定不同的索引使用不同的键缓存:mysql> CACHE INDEX test1 in
hot_cache;+————+——————–+———-+———-+|
Table | Op | Msg_type | Msg_text
|+————+——————–+———-+———-+| test .test1
| assign_to_keycache | status | OK
|+————+——————–+———-+———-+1 row in set
mysql> CACHE INDEX test2 in
hot_cache;+————+——————–+———-+———-+|
Table | Op | Msg_type | Msg_text
|+————+——————–+———-+———-+| test .test2
| assign_to_keycache | status | OK
|+————+——————–+———-+———-+1 row in set
通常在数据库刚刚启动的时候,需要等待数据库热起来,也就是等待数据被缓存到缓存区中,这段时间数据库会因为
buffer
的命中率低而导致应用的访问效率不高。使用键高速缓存的时候,可以通过命令将索引预加载到缓存区中,大大缩短了数据库预热的时间。具体的操作方式是:mysql>
LOAD INDEX INTO CACHE test1,test2 IGNORE
LEAVES;+————+————–+———-+———-+| Table | Op |
Msg_type | Msg_text
|+————+————–+———-+———-+| test .test1 |
preload_keys | status | OK || test .test2 | preload_keys | status | OK
|+————+————–+———-+———-+2 rows in set
如果已经使用 CACHE INDEX
语句为索引分配了一个键高速缓冲,预加载可以将索引块放入该缓存,否则,索引块将被加载到默认的键高速缓冲。

比较合理的设置

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈
80%

5.Innodb_additional_mem_pool_size

这个参数用来存在数据库结构和其他内部数据结果的内存池的大小。

 

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

缓存命中概率

key_cache_miss_rate = (key_reads / key_read_requests) * 100%,
如果key_cache_miss_rate
在0.01%以下的话,key_buffer_size分配的过多,可以适当减少。

查看MySQL中参数

Mysql服务启动之后,我们可以使用show variables和show status
命令可以查看mysql服务的静态参数值和动态运行状态信息。其中show
variables是查看数据库启动后不会动弹更改的值,比如缓冲区大小、字符集、数据文件名等。show
status是查看数据库运行期间的动态变化信息,比如锁等待、当前连接数等。查看服务器含有buffer的参数值
威尼斯城真人赌钱网站 14
查看服务器含有buffer的运行状态值
威尼斯城真人赌钱网站 15

当前服务器的Thread_cache命中率约为95.6%这个结果我还是比较满意的.但是可以看出
thread_cache_size有点多余改成16或8更合理一些.

read_buffer_size=4M 默认为64Kread_rnd_buffer_size 随机读 缓存区
默认为256Ksort_buffer_size=32M 默认为256Kmax_connections=1024
默认为1210thread_cache_size=120 默认为60性能测试1、mysql
自带测试工具shell> perl -MCPAN -e shellcpan> install DBIcpan>
install DBD::mysqlshell> cd sql-benchshell> perl run-all-tests
–server=server_nameserver_name是一个支持的服务器。要获得所有选项和支持的服务器,调用命令:shell>
perl run-all-tests
–help2、mysqlreport

2.table_cache

下面先说我的服务器的硬件以及论坛情况,
CPU: 2颗四核Intel Xeon 2.00GHz
内存: 4GB DDR
硬盘: SCSI 146GB
论坛:在线会员 一般在 5000 人左右 – 最高记录是 13264.
下面,我们根据以上硬件配置结合一份已经做过一次优化的my.cnf进行分析说明:有些参数可能还得根据论坛的变化情况以及程序员的程序进行再调整。
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking # 避免MySQL的外部锁定,减少出错几率增强稳定性。

、innodb_buffer_pool_sizeinnodb_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%的开销。

参数调优

通过检查mysqld的状态变量open_tables和opend_tables确定table_cache这个参数的大小。open_tables代表当前打开的表缓冲数量,如果执行flush
tables,则系统会关闭一些当前没有使用的表缓存,使得open_tables值减少。opend_tables表示曾经打开的表缓存数,会一直进行累加,不会因为执行flush
tables操作,有所减少。如下图,变化还是很明显的。
威尼斯城真人赌钱网站 16
当执行一个查询之后,发现opend_table和open_table都增加了1
威尼斯城真人赌钱网站 17
当再次执行同一个查询之后,发现opend_table和open_table都不再变化
威尼斯城真人赌钱网站 18
由此可见open_table对于设置table_cache更有价值

这样修改之后,即便重启mysql也会默认载入这个配置了

、sort_buffer:每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order
by或group by操作。默认数值是2097144,我把它改为 16777208
、table_cache:为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

7.innodb_log_file_size

日志组中每个日志文件的大小

复制代码代码如下:

、record_buffer:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072,我把它改为16773120
、key_buffer_size:为了最小化磁盘的 I/O , MyISAM
存储引擎的表使用键高速缓存来缓存索引,这个键高速缓存的大小则通过
key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM
存储引擎为主,则应该适当增加该参数的值,以便尽可能的缓存索引,提高访问的速度。

1.key_buffer_size

该参数是用来设置索引块(Index
Blocks)缓存的大小,它被索引线程共享,此参数只使用MyISAM存储引擎。MySQL5.1之后的版本,可以将指定的表索引缓存入指定的key_buffer,这样可以降低线程之间的竞争。

 

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

4.Innodb_flush_log_at_trx_commit

这个参数是控制缓存区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。默认值为1。可以有以下值:
0:日志缓冲每秒一次地写到日志文件,并对日志文件作向磁盘刷新操作,但事务提交不做任何操作。
1:每个事务提交时,日志缓冲被写到日志文件,并且对日志文件做向磁盘刷新操作。
2:每个事务提交时候,日志缓冲被写到日志文件,但是不对日志文件作向磁盘刷新操作,对日志文件每秒向磁盘做一次刷新操作。

==========================================================================================================

mysql 优化调试命令1、mysqld –verbose
–help这个命令生成所有mysqld选项和可配置变量的列表2、通过连接它并执行这个命令,可以看到实际上使用的变量的值:mysql>
SHOW
VARIABLES;还可以通过下面的语句看到运行服务器的统计和状态指标:mysql>SHOW
STATUS;使用mysqladmin还可以获得系统变量和状态信息:shell> mysqladmin
variablesshell> mysqladmin extended-statusshell> mysqladmin
flush-table
命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭,这样可以有效释放大多数使用中的内存。FLUSH
TABLE在关闭所有表之前不返回结果。swap
-s检查可用交换区mysql内存计算公式mysql used mem = key_buffer_size +
query_cache_size + tmp_table_size+ innodb_buffer_pool_size +
innodb_additional_mem_pool_size+ innodb_log_buffer_size+
max_connections * (read_buffer_size + read_rnd_buffer_size+
sort_buffer_size+ join_buffer_size+ binlog_cache_size +
thread_stack)在mysql
中输入如下命令,可自动计算自己的当前配置最大的内存消耗SHOW VARIABLES
LIKE ‘innodb_buffer_pool_size’;SHOW VARIABLES LIKE
‘innodb_additional_mem_pool_size’;SHOW VARIABLES LIKE
‘innodb_log_buffer_size’;SHOW VARIABLES LIKE ‘thread_stack’;SET
@kilo_bytes = 1024;SET @mega_bytes = @kilo_bytes * 1024;SET
@giga_bytes = @mega_bytes * 1024;SET @innodb_buffer_pool_size = 2
* @giga_bytes;SET @innodb_additional_mem_pool_size = 16 *
@mega_bytes;SET @innodb_log_buffer_size = 8 * @mega_bytes;SET
@thread_stack = 192 * @kilo_bytes;SELECT( @@key_buffer_size +
@@query_cache_size + @@tmp_table_size+ @innodb_buffer_pool_size +
@innodb_additional_mem_pool_size+ @innodb_log_buffer_size+
@@max_connections * (@@read_buffer_size + @@read_rnd_buffer_size

6.Innodb_log_buffer_size

日志缓存大小

我们先来看下如何查看当前mysql的max_connections的值:

、max_connections:允许的同时客户的数量。增加该值增加 mysqld
要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 too many
connections 错误。 默认数值是100,我把它改为1024 。

创建一个索引缓存

set global 缓存索引名.key_buffer_size=100*1024;
威尼斯城真人赌钱网站 19
global是全局限制,表示对每一个新的会话(连接)都有效。

 

删除索引缓存

将其索引缓冲大小设置为了0,就可以删除了,注意不能删除默认的key_buffer。

当前未使用的表被释放,以最近最少使用顺序。

索引缓存概述

MyISAM存储引擎和其他很多数据库系统一样,采用了一种将最经常访问的表保存在内存中的策略。对应索引区块来说,它维护者一个叫做索引缓存(索引缓冲)的结构体,这个结构体中存放着许多哪些经常使用的索引区块的缓冲区块。对应数据区块来说,Mysql主要依靠系统的本地文件系统缓存。有了索引缓冲后,线程之间不再是串行地访问索引缓存。多个线程可以并行地访问索引缓存。可以设置多个索引缓存,同时也能指定数据表索引到特定的缓存中。

 

影响Mysql性能的重要参数详解

这个设置会马上生效,但是当mysql重启时这个设置会失效,更好的办法是修改mysql的ini配置文件my.ini

9.Innodb_support_xa

设置是否支持分布式事务,默认为ON或者1,表示支持。

查看MySQL中参数
Mysql服务启动之后,我们可以使用show variables和show status
命令可以查看mysql服务的静态参数值和…

调整此参数的方法有几种,既可以在编译的时候设置,也可以在MySQL配置文件
my.cnf 中设置,也可以直接使用命令调整并立即生效。

 

如果短连接多的话可以适当加大.

在myisam表引擎中,数据文件的描述符
(descriptor)是不共享的,但是索引文件的描述符却是所有线程共享的.Innodb中和使用表空间类型有关,假如是共享表空间那么实际就一个数
据文件,当然占用的数据文件描述符就会比独立表空间少.

 

复制代码代码如下:

相关文章