mysql配置优化 mysql优化命令

转载

archangle 2024-03-10 11:14:22

文章标签 mysql配置优化 mysql 性能优化 query cache 文章分类 MySQL 数据库

1、mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表

2、通过连接它并执行这个命令，可以看到实际上使用的变量的值：mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标：mysql>SHOW STATUS；
使用mysqladmin还可以获得系统变量和状态信息：

shell> mysqladmin variables 
 shell> mysqladmin extended-status

shell> mysqladmin flush-table 命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭，这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。

swap -s检查可用交换区

在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗：
-- 先记下后面查看到的一些参数设置：

HOW 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';

-- 再把相关值替换掉下面后4个SET中的数字

SET @kilo_bytes = 1024;
 SET @mega_bytes = @kilo_bytes * 1024;
 SET @giga_bytes = @mega_bytes * 1024;
 SET @innodb_buffer_pool_size = 2 * @mega_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 + @@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 语句来更改动态会话参数。设置会话级参数不需要特殊权限，但每个客户端可以只更改自己的会话级参数，不能更改其它客户的会话级参数。
不指定设置的参数类型时，默认设置的是会话级参数。
(1)max_connections：允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。默认数值是100，我把它改为1024。

(2)record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120(16m)。

(3)key_buffer_size：为了最小化磁盘的 I/O ， MyISAM 存储引擎的表使用键高速缓存来缓存索引，这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主，则应该适当增加该参数的值，以便尽可能的缓存索引，提高访问的速度。索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为 402649088(400mb)。
默认情况下，所有的索引都使用相同的键高速缓存，当访问的索引不在缓存中时，使用 LRU （ Least Recently Used 最近最少使用）算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用，从 MySQL5.1 开始，可以设置多个键高速缓存，并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值，如何设置多个键高速缓存，以及如何为不同的索引指定不同的缓存：
将全局的默认键缓存的参数大小设置为200M：set global key_buffer_size=204800;
设置多个键缓存：名为hot_cache的键缓存100M，cold_cache的键缓存100M，另外还有200M的默认的键缓存。如果索引不指定键缓存，则会放在默认的键缓存中。
set global hot_cache.key_buffer_size=102400;
set global cold_cache.key_buffer_size=102400;
如果要显示设置的多键缓存的值，可以使用：SELECT @@global.hot_cache.key_buffer_size;
指定不同的索引使用不同的键缓存：CACHE INDEX test1 in hot_cache;
通常在数据库刚刚启动的时候，需要等待数据库热起来，也就是等待数据被缓存到缓存区中，这段时间数据库会因为 buffer 的命中率低而导致应用的访问效率不高。使用键高速缓存的时候，可以通过命令将索引预加载到缓存区中，大大缩短了数据库预热的时间。具体的操作方式是：LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;

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

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

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

(7)table_cache：为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。

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

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

(10)wait_timeout：等待超时的秒数。默认数值是28800，我把它改为7200。

(11)innodb_thread_concurrency：你的服务器CPU有几个就设置为几,默认为8。

(12)query_cache_size 与 query_cache_limit
QueryCache之后所带来的负面影响：
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多次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定Query Cache的大小啊。是的，我们确实可以限定Query Cache的大小，但是这样，Query Cache就很容易造成因为内存不足而被换出，造成命中率的下降。
QueryCache的正确使用：
虽然Query Cache的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我们完全不用因为Query Cache的上面三个负面影响就完全失去对Query Cache的信心。只要我们理解了Query Cache的实现原理，那么我们就完全可以通过一定的手段在使用Query Cache的时候扬长避短，重点发挥其优势，并有效的避开其劣势。
首先，我们需要根据Query Cache失效机制来判断哪些表适合使用Query哪些表不适合。由于Query Cache的失效主要是因为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（1048576）。当某个Query的Result Set 大于“query_cache_limit”所设定的值的时候，Query Cache是不会Cache这个Query的。

(13)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%的开销。这样，12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占用到14.5G(12G X 21%)的内存，再加上操作系统用的几百M，近千个线程堆栈，就差不多16G了。

MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量：grant all on dbname.* to db@localhost identified by "123456" with max_connections_per_hour 5;
MAX_USER_CONNECTIONS 限制有多少用户连接MYSQL服务器：grant all on dbname.* to db@localhost identified by "123456" with max_user_connections 2;
MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量：grant all on dbname.* to db@localhost identified by "123456" with max_updates_per_hour 5;

调优举例
------------------------------------------
针对my.cnf文件进行优化：

[mysqld]
 skip-locking（取消文件系统的外部锁）
 skip-name-resolve（不进行域名反解析,注意由此带来的权限/授权问题）
 key_buffer_size = 256M（分配给MyISAM索引缓存的内存总数）对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会使服务器整体效率降低！
 max_allowed_packet = 4M（允许最大的包大小）
 thread_stack = 256K（每个线程的堆栈大小）
 thread_cache=32 (大量并发连接)
 thread_cache_size = 64（缓存可重用线程数，减少创建新线程的开销。在某些服务器上，此参数加入后MYSQL运行不了）
 table_cache = 512（缓存数据表的数量，避免重复打开表的开销。物理内存越大,设置就越大.默认为2402,调到512-1024最佳）
 table_cache_size = 128K（缓存数据表用的内存大小）
 back_log = 384（临时停止响应新请求前在短时间内可以堆起多少请求，如果你需要在短时间内允许大量连接，可以增加该数值）
 sort_buffer_size = 2M(分配给每个线程中处理排序)
 read_buffer_size = 2M（读取的索引缓冲区大小）
 join_buffer_size = 2M（分配给每个线程中处理扫描表连接及索引的内存）
 myisam_sort_buffer_size = 64M（myisam引擎排序缓冲区的大小）
 query_cache_size = 64M（控制分配给查询缓存的内存总量）
 tmp_table_size = 256M(指定mysql临时表用的内存大小,调到64-256M最挂)
 max_connections = 768（最大连接数）指mysql整个的最大连接数
 max_connect_errors = 10000(最大连接错误数据)
 max_user_connections = 500（最大用户连接数）
 open_files_limit = 4096(打开文件数）
 thread_concurrency = 8（根据cpu内核数量来设置,如果是有HT超线程的，则还可以*2）
 skip-bdb 禁用不必要的引擎
 skip-networking（关闭mysql tcp/ip连接方式）
 Log-slow-queries = /var/log/mysqlslowqueries.log
 long_query_time = 4（设定慢查询的时间）
 skip-host-cache(提高mysql速度的)
 wait_timeout = 10（超时时间，可以避免攻击）
 interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
 innodb_flush_log_at_trx_commit=0 (等到innodb_log_buffer_size列队满后再统一储存,默认为1[表示事务提交了就转储缓存]) #指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：SHOW VARIABLES LIKE '%query_cache%'; SHOW STATUS LIKE 'Qcache%';
 #如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
 #如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；
 #如果Qcache_free_blocks的值非常大，则表明缓冲区中碎片很多。
 query_cache_size = 512M            ##32M-512M就可以 
 query_cache_type=1 
 long_query_time=1                ### new 
 max_heap_table_size=256M         ###new 
 tmp_table_size=256M              ### new

性能测试
------------------------------------------

1、mysql 自带测试工具
 shell> perl -MCPAN -e shell 
 cpan> install DBI 
 cpan> install DBD::mysql 
 shell> cd sql-bench
 shell> perl run-all-tests --server=服务器名称

要获得所有选项和支持的服务器，调用命令：shell> perl run-all-tests --help

2、mysqlreport
http://hackmysql.com/mysqlreport

参考文档
------------------------------------------
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html http://hackmysql.com/tools http://www.imysql.cn/

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。