mysql数据库的安装以及常见优化设置

标签: mysql 数据库 常见 | 发表时间:2014-06-18 01:05 | 作者:bianbiancheng
出处:http://blog.csdn.net

原文请详见: http://www.ucai.cn/blogdetail/7036?mid=1&f=5

可以在线运行查看效果哦!


        本文根据优才网课程整理,面向web开发者,内容以实用为主,专业DBA可以绕行。

        如果你在大公司,可能有专门的DBA来做这些事情,如果你在一个小公司当架构师或者技术总监,或者你自己创业,那DBA的活你也得干了。咱们来讲一下基本的mysql安装和优化。

 

一: MYSQL安装和基本配置

在linux上安装,可以用包管理工具来安装,比较简单:

RedHat 系列:yum -yinstall  mysql mysql-server 

Debian系列:sudo apt-getinstall  mysql mysql-server

 

安装之后不知道mysql装到哪了怎么办,用whereis mysql 命令来找一下。先找到mysql的默认配置文件。一般来说,安装后有这么几个备选的配置:my-huge.cnf my-innodb-heavy-4G.cnf my-large.cnf my-medium.cnf my-small.cnf

 

就2014年的机器配置来说,咱们直接用my-huge.cnf。把my-huge.cnf 复制到/etc/下,改名my.cnf。配置文件就有了,然后启动mysql: /etc/init.d/mysqld start 。

 

安装之后默认的帐号是root, 密码为空。咱们要做的第一件事是改root密码。

进入mysql:mysql -uroot -p

选择数据库: use mysql

改密码: UPDATE user SET Password = PASSWORD('xxxx') WHERE user = 'root';

刷新权限: FLUSH PRIVILEGES;

 

现在数据库装好了,帐号和权限也设置了,是不是就可以使用了呢,还要检查几个配置。打开配置文件 vim /etc/my.cnf

skip-networking 要关闭。

bind-address = 127.0.0.1 这一行要关闭或者修改成允许的IP

skip-name-resolve 禁止dns解析,只能用IP连,这个可以打开。

       如果你的mysql经过一段时间运行,挺过了访问高峰,咱们再来检查一下配置是否合适。下面说的配置,都必须是在运行一段时间后检查才有意义。如果刚启动没多久,mysql的运行状态没有代表性,不能作为参考。检查配置参数也没意义。

 

二:连接数(connection)配置


       max_connections 可以设置最大并发连接数。当MySql的并发连接达到这个设定值时,新的连接将会被拒绝(“Can not connect to MySQL server. Too many connections”-mysql 1040错误,)。当发现MySql有能力处理更多的并发的时候, 建议调大这个值,相应给服务器带来更高的负载(CPU/IO/内存)。

 

查看设置的最大连接是多少:

mysql> show variables like 'max_connections';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| max_connections | 151   | 允许的最大连接数

+-----------------+-------+

 

 看当前连接数:showstatus like 'threads_connected';

最大连接数: show status like "max_used_connections";

如果max_used_connections已经接近 max_connections了,就说明max_connections太小。不合适了。

 

还有一些跟连接数相关的配置:

back_log=50

MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用。如果MySQL的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。

        back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。

        不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

如果链接数超过max_connections+back_log ,才会出错。

max_connect_errors=10

        当客户端连接服务端超时(超过connect_timeout), 服务端就会给这个客户端记录一次error,当出错的次数达到max_connect_errors的时候,这个客户端就会被锁定。除非执行FLUSH HOSTS命令。

connect_timeout=5

连接超时的秒数

 

三:查询缓存(query_cache)配置

 

       查询缓存就是内存中的一块存储区域,其存储了用户的SQL文本以及相关的查询结果。通常情况下,用户下次查询时,如果所使用的SQL文本是相同的,并且自从上次查询后,相关的纪录没有被更新过,此时数据库就直接采用缓存中的内容。从内存中读取要比从硬盘上速度要快好几百倍。MYSQL的查询缓存用于缓存select查询结果,并在下次接收到同样的查询请求时,不再执行实际查询处理而直接返回结果,有这样的查询缓存能提高查询的速度,使查询性能得到优化.

       要使用缓存,有几个条件:一是所采用的SQL语句是相同的。每次查询的语句不一样,肯定不能用到缓存。比如语句里带当前秒数 where ctime > xxx;二是表数据没有改过。没有改过结构,没有update,insert;三、客户端与服务器的默认字符集得一样。所以可以看出,要利用好缓存,有大量的相同的查询,而很少改变表里的数据,否则没有必要使用此功能。

 

查看查询缓存的设置:

SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |  如果单个查询结果大于这个值,则不Cache
| query_cache_min_res_unit     | 4096    |  每次给QC结果分配内存的大小
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

 

query_cache_type=1

        如果设置为1,将会缓存所有的结果,除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。如果设置为2,则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。query_cache_size 默认是32M,太小了,可调到128M或者256M。 可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。

        query_cache_size的工作原理:一个SELECT查询在DB中工作后,DB会把该语句缓存下来,当同样的一个SQL再次来到DB里调用时,DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点,就是DB在利用Query_cache工作时,要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时,Query_cache里的数据又怎么处理呢?

        首先要把Query_cache和该表相关的语句全部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的系统,建系把该功能禁掉。 

       Qcache_lowmem_prunes可以检查是否设置的太小。query_cache_limit 默认是1M,根据你的常用查询的数据结果大小来定。如果返回的数据小,可以设置小一点。设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费。

 

查看缓存使用效果如何:

 showstatus like '%Qcache%';

 +-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 160     | 目前还处于空闲状态的 Query Cache中内存 Block 数目,数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。

| Qcache_free_memory      | 23147296 | 缓存中的空闲内存总量。

| Qcache_hits             | 52349    | 缓存命中次数。

| Qcache_inserts          | 8827     | 缓存失效次数。

| Qcache_lowmem_prunes    | 0       | 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。

| Qcache_not_cached       | 2446     | 没有被cache和不适合进行缓存的查询的数量,通常是由于这些查询不是SELECT语句以及由于query_cache_type设置的不会被Cache的查询。show,use,desc

| Qcache_queries_in_cache | 5234     | 当前被cache的SQL数量。

| Qcache_total_blocks     | 10796   | 缓存中块的数量。

+-------------------------+----------+

 

show global status like 'Com_select'; 

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select    | 12592 | com_select 变量记录的是无缓存的查询次数+错误查询+权限检查查询。

+---------------+-------+

 

Mysql的查询缓存命中率没有官方算法,只有前人的经验总结

命中率≈ qcache_hits / (qcache_hits + com_select)

 

缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% ,如果碎片率太高,20% ,可以FLUSHQUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。

 

查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes> 50的话说明query_cache_size可能有点小,要不就是碎片太多。

如何提高命中率:

1、字符集相同;2、SQL语句尽量固定(SQL语句避免随机数,秒数等);3、 加大缓存空间; 4、适当分表,动静分离。


四、临时表缓存(tmp_table_size)配置

 

       mysql进行复杂查询或者 做高级GROUP BY操作的时候,系统为了优化查询,生成一些临时表。通过设置tmp_table_size选项来设置临时表占用空间的大小。我们使用explain分析SQL,如果在Extra列看到Using temporary就意味着使用了临时表。

        MySQL临时表分为“内存临时表”和“磁盘临时表”,其中内存临时表使用MySQL的MEMORY存储引擎,磁盘临时表使用MySQL的MyISAM存储引擎。一般情况下,MySQL会先创建内存临时表,但内存临时表超过配置指定的值后,MySQL会将内存临时表导出到磁盘临时表。临时表将在你连接MySQL期间存在。当你断开时,MySQL将自动删除表并释放所用的空间。

 

mysql> SHOW VARIABLES LIKE'%tmp_table_size%';
+----------------+----------+
| Variable_name  | Value   |
+----------------+----------+
| tmp_table_size | 33554432 | 
+----------------+----------+
1 row in set (0.00 sec)
 
mysql> show global status like'created_tmp%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+

| Created_tmp_disk_tables | 690421   |  服务器执行语句时在硬盘上自动创建的临时表的数量;

| Created_tmp_files       | 755473   | mysqld已经创建的临时文件的数量;

| Created_tmp_tables      | 14372959 | 服务器执行语句时自动创建的内存中的临时表的数量。如果Created_tmp_disk_tables较大,你可能要增加tmp_table_size值使临时 表基于内存而不基于硬盘;

+-------------------------+----------+

 

       每次创建临时表,Created_tmp_tables增加,如果临时表大小超过tmp_table_size,则是在磁盘上创建临时表,Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数,比较理想的配置是:Created_tmp_disk_tables /Created_tmp_tables * 100% <= 25%

比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%,应该相当好了。默认大小是 32M,可调到64-256最佳,线程独占,太大可能内存不够I/O堵塞。

        跟临时表相关的另一配置是max_heap_table_size ,用户可以创建的独立的内存表所允许的最大容量.这个变量不适用与用户创建的内存表(memory table).SHOW VARIABLES LIKE'%max_heap_table_size%'; 咱们希望临时表是放到内存的。所以这个值设置的临时表缓存的空间一样就行。

(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果临时表大于这两个的任何一个,都会存硬盘缓存:自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下。

 

五、索引缓冲区(key_buffer_size)配置

 

        key_buffer_size是对MyISAM表性能影响最大的一个参数.key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好。

       key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。


mysql> show variables like'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value   |
+-----------------+----------+
| key_buffer_size | 67108864 | 索引缓冲区的大小
+-----------------+----------+
show variables like 'key_cache_block_size';
mysql> show global status like 'key%';
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+

| Key_blocks_not_flushed | 0          |索引缓存内已经更改,但还没有清空到硬盘上的索引的数据块数量。

| Key_blocks_unused      | 0          | 索引缓存内未使用的块数量。你可以使用该值来确定使用了多少键缓存

| Key_blocks_used        | 53585      | 索引缓存内使用的块数量。该值为高水平线标记,说明已经同时最多使用了多少块。

| Key_read_requests      | 4952122733 | 一共有XXX个索引读取请求,

| Key_reads              | 11879      | 索引读取请求在内存中没有找到,直接从硬盘读取索引

| Key_write_requests     | 10508455   | 将索引的数据块写入缓存的请求数。

| Key_writes             | 6042774    |  将索引向硬盘写入数据块的物理写操作的次数。

+------------------------+------------+


比例key_reads /key_read_requests应该尽可能的低,至少是1:100,1:1000更好;

如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests计算出cache失败率;

Key_writes/Key_write_requests:比例接近1较好。

       别人的经验是内存在4GB左右的服务器该参数可设置为384M或512M。可以自己算一下自己数据库的索引文件大小。注意:该参数值设置的过大反而会是服务器整体效率降低!

 

Cache命中比率:

1 - Key_reads / Key_read_requests 

Key buffer的使用率

100 – ( (Key_blocks_unused *key_cache_block_size) * 100 / key_buffer_size )


作者:bianbiancheng 发表于2014-6-17 17:05:54 原文链接
阅读:56 评论:0 查看评论

相关 [mysql 数据库 常见] 推荐:

mysql数据库的安装以及常见优化设置

- - CSDN博客互联网推荐文章
原文请详见: http://www.ucai.cn/blogdetail/7036?mid=1&f=5.         本文根据优才网课程整理,面向web开发者,内容以实用为主,专业DBA可以绕行.         如果你在大公司,可能有专门的DBA来做这些事情,如果你在一个小公司当架构师或者技术总监,或者你自己创业,那DBA的活你也得干了.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

MySQL数据库的IO操作

- - haohtml's blog
         淘宝丁奇分享的PPT:MySQL数据库的IO操作,详细分享了四块的内容,并且告诉大家如何调整MySQL数据库IO操作相关的参数,给出了详细的选择策略,现替其整理成文章分享与此. 4.影响io行为的一些参数和选择策略. 一个简单的查询 select * from t where id>=(  select id from t where k1=100 limit 100000,1) limit 2;.

MySQL数据库优化总结

- - CSDN博客推荐文章
        对于一个以数据为中心的应用,数据库的好坏直接影响到程序的性能,因此数据库性能至关重要. 一般来说,要保证数据库的效率,要做好以下四个方面的工作:数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统,这个顺序也表现了这四个工作对性能影响的大小.        一、数据库设计   适度的反范式,注意是适度的.

Google数据库产品LevelDB对决MySQL

- - HTML5研究小组
去年一月份,Google发布了LevelDB. LevelDB是Key-Value嵌入式数据库管理系统编程库,目前的版本能够支持Billion级别的数据量. LevelDB是一个C++库,可按照字符串键值顺序映射. 源于其本身的良好设计,特别是LSM算法,LevelDB性能非常之高. 在一台4个Q6600的CPU机器上,每秒钟写数据超过40w,而随机读的性能每秒钟超过10w.

excel数据导入mysql数据库

- - 互联网 - ITeye博客
1、excel另存为txt.       选中将要导出的数据列,然后另存为选择其它格式=>文本文件(制表符分割). E:\项目\fblike\game_code_san.txt. 2、txt导入到mysql数据库. load data infile 'E:\\项目\\fblike\\game_code_san.txt' into table game_code_san(code).

c/c++连接mysql数据库

- - CSDN博客数据库推荐文章
        由于项目需要,要用c/c++链接mysql数据库. 网上很多类似的解说,但是大部分都需要在本机器上安装完整版的msyql. 其实,有时候我们并不想在改变自己电脑上原有的环境,但是我们却希望通过我们的程序链接数据库. 比如:我在本机上已经安装了一个mysql,但并不是完整版的(比如appserv集成mysql或者wamp集成mysql),或者我的工作在局域网中,我只需要链接另外一台机器上的mysql.

理解MySQL数据库覆盖索引

- - haohtml's blog
看AUTO_INCREMENT就知道数据并不多,75万条. 很简单对不对?怪异的地方在于:. 如果换成MyISAM做存储引擎的时候,查询耗时只需要0.01s,用InnoDB却会是0.15s左右. 如果只是就这么点差距其实不是什么大不了的事,但是真实的业务需求比这个复杂,造成的差距也很大:MyISAM只需要0.12s,InnoDB则需要2.2s.,最终定位到问题症结是在这条SQL.

MySQL数据库设置主从同步

- - CSDN博客架构设计推荐文章
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力. 1、可以作为一种备份机制,相当于热备份. 2、可以用来做读写分离,均衡数据库负载. 1、主从数据库版本一致,建议版本5.5以上. # 日志文件名 log-bin = mysql-bin # 日志格式,建议mixed binlog_format = mixed # 主数据库端ID号 server-id = 1.

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.