针对Mysql数据库服务器的优化

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

Mysql 数据库的优化

  最近一直在搞Mysql数据库的配置和优化,下面记录一下这些天研究的结果,以方便以后查看。
 
一、服务器的硬件的优化
 
对mysql服务器来说主要从以下几个方面来考虑:
1、磁盘寻道能力(磁盘I/O)
  因为Mysql每一秒都在进行着大量的、复杂的查询操作,对磁盘的读写可想而知,所以通常认为磁盘的I/O是制约Mysql性能的最大因素之一。如果磁盘的I/O性能不好,造成的直接后果就是MySqL的性能非常低下,对于这种情况可以考虑使用RAID1+0。对于硬盘来说最好选那种转速比较快的,这样数据的操作的效率也会有大的提高。
2、cpu对于Mysqlr 影响也是不容忽视的,要选择运算能力强悍的cpu.
3、内存不要小于2GB,最好使用4GB以上的物理内存。
4、服务器选择上可以dell的R710 2颗cpu(双四核) 16G内存(这个可以扩展的)硬盘有两种选择:一个是300G、15000r的,一种是500G的7200r的 可以根据自己的需要来选择。也可选HP的DL580G5。我在工作中使用的dell的R710。
 
二、MySqL配置文件的优化
 
  MySqL在服务器上安装最好是选择源码编译安装。对于MySqL的优化主要是在/etc/my.cnf这个文件中来修改一些参数。
  在这个文件中主要的参数是在[mysqld]这部分中,这部分中主要包括了mysqld服务启动参数,它涉及的方面很多,其中有MysqL的目录文件和文件、通信、网络、信息安全、内存管理、优化、查询缓存区,还有MySql的日志设置等。下面就来看一下这个有文件中的参数:
 
port = 3306   
// mysql服务的运行时的端口
socket = /tmp/mysql.sock  
//socket文件是在Linux/Unix环境下特有的,用户在Linux/Unix环境下客户端连接可以不通过TCP/IP网络而直接使用unix socket连接MySqL。
skip-locking  
//避免MySqL的外部锁定,减少出错率,增强稳定性。
skip-name-resolve  
//禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意的是,如果开启该选项,财所有远程主机连接授权都要使用IP地址的方式了,否则MySQL将无法正常处理连接请求!
back_log = 256 
//back_log参数的值指出在MySqL暂时停止响应新请求之前,短时间内多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。不同操作系统在这个队列的大小上有自己的限制。如果试图将back_log设定得高于操作系统的限制将是无效的。其默认值为50。对于Linux系统而言,推荐设置为小于512的整数。
key_buffer_size = 256 
//指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存4GB左右的服务器来说,该参数可设置为256MB或者384MB。
max_allowed_packet = 1M
//设定在网络传输中一次消息量的最大值。系统默认为1MB,最大值1GB,必须设定为1024的倍数,单位为字节。
thread_stack = 192K 
//设置MySQL每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128KB至4GB。默认为192KB。
table_cache = 614K  (mysql 5.1.3之后这个设置就变为了table_open_cache)
//指示表高速缓冲区的大小。当MySqL访问一个表时,如果在MySQL表缓冲区中还有空间,那么这个表就被打开并放入表缓冲区,这样做的好处是可以更快速地访问表中的内容。一般来说,可以查看数据库运行峰值时间的状态值Open_tables和Opened_tables,用以判断是否需要增加table_cache的值,即如果Open_cache的接近table_cache的时候,并且Opened_tables这个值在逐渐增加,那就要考虑增加这个值的大小了。
sort_buffer_size = 6M  
//设定查询排序时使用的缓冲区大小,系统默认大小为2MB。注意::这个参数对应的分配内存是每个连接独占的,如果有100个连接,那么实际分配的总排序缓冲区大小为100x6=600MB。所以,对于内存在4GB左右的服务器来说,推荐将其设置为6MB到8MB
read_buffer_size = 2M
//读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
join_buffer_size = 4M  
//联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。
myisam_sort_buffer_size = 8M  
//设置在REPAIR TABLE或用CAREATE INDEX创建索引或ALTER TABLE 的过程中排序索引所分配的缓冲区大小,可设置范围4Bytes到4GB,默认为8MB。
thread_cache_size = 64  
//设置Thread Cache池中可以缓存的连接线程的最大数量,可以设置为0~16384,默认为0。这个值表示可以重新利用保存在缓存中线程的数量。当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中;如果线程重新被请求,那么请求将从缓存中读取;如果缓存中是空的或者是新的请求,那么这个线程将被重新创建;如果有很多新的线程,增加这个值可以改善系统性能。通过比较Connections和Threads_created状态的变量,可以看到这个变量的作用。我们可以根据物理内存设置规则如下:1GB内存我们配置为8,2GB内存我们配置为16,3GB我们配置为32,4GB或更大的我们给此值为64或更大的数值。
query_cache_size=256M
//指定MySQL查询缓冲的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况;如果Qcache_hits的值非常大,则表明查询缓冲使用得非常频繁。另外,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲。对于Qcache_free_block,如果该值非常大,则表明缓冲区中碎片很多。
tmp_table_size = 256M
//设置内存临时表最大值。如果超过该值,则将会把临时表写入磁盘,其范围为1KB到4GB。
max_connections = 5000  
//指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示,则需要增大该参数值。
max_connect_errors = 6000
//设置每个主机的连接请求异常中断的最大次数,当超过该次数,MySQL服务器将禁止host的连接请求,直到MySQL服务器重新启动或通过flush hosts 命令清空此host的相关信息。
wait_timeout = 120
//指定一个请求的最大连接时间,对于4GB左右内存的服务器来说,可以将其设置为5~10.
thread_concurrency = 8  
//该参数取值为服务器逻辑CPU数量X2,在本例中,服务器有两个物理CPU,而每个物理CPU又支持H.T超线程,所以实际取值为4X2=8。这也是目前双四核主流服务器的配置。
skip-networking  
//开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果Web服务器是以远程连接的方式访问MySQL数据库服务器的,则不要开启该选项,否则将无法正常连接!
table_cache = 614 (这个在5.1.3后就变成了table_open_cache)
//给经常访问的表分配的内存,物理内存越大,设置就越大。调大这个值,一般情况下可以降低磁盘IO,但是相应的会占用更多的物理内存,这这里设置为614
innodb_addition_mem_pool_size = 1M  
//默认为1MB
innodb_flush_log_at_trx_commit = 1  
//设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1,也是最安全的设置。
innodb_log_buffer_size = 2M  
//默认为1MB,通常设置为8~16MB就足够了。
innodb_thread_concurrency = 8  
//你的服务器有几个CPU就设置为几,建议用默认设置,一般为8.
read_rnd_buffer_size = 16M  
//设置进行随机读的时候所使用的缓冲区。此参数和read_buffer_size所设置的Buffer相反,一个是顺序读的时候使用,一个是随机读的时候使用。但是两者都是针对线程的设置,每个线程都可以产生两种Buffer中的任何一个。read_rnd_buffer_size的默认值256KB,最大值4GB。
 
  以上只一些理论参考值,很多时候需要具体情况具体分析,其他参数的变更我们可以等MySQL上线稳定一段时间后再根据status值进行调整。
  
  
 
本文转自 ZhouLS 51CTO博客,原文链接:http://blog.51cto.com/zhou123/1152994

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
存储 缓存 关系型数据库
百度搜索:蓝易云【MySQL之优化SELECT语句】
通过以上优化方法,可以显著提高MySQL SELECT语句的查询性能和效率。
25 5
|
22天前
|
缓存 关系型数据库 MySQL
百度搜索:蓝易云【CentOS8服务器安装MySQL报错:no match mysql-community-server】
现在,你已经成功安装了MySQL服务器并解决了"no match mysql-community-server"的报错问题。祝你使用愉快!
27 1
|
5天前
|
SQL 存储 关系型数据库
MySQL索引(二)索引优化方案有哪些
MySQL索引(二)索引优化方案有哪些
24 0
|
2天前
|
SQL 监控 Java
Java Web应用中数据库连接池的配置与优化
Java Web应用中数据库连接池的配置与优化
|
3天前
|
运维 前端开发 Java
探索新一代数据库技术——无服务器数据库
【2月更文挑战第2天】本文将介绍一种创新的数据库技术——无服务器数据库。通过分析其原理、特点和应用场景,我们将揭示无服务器数据库在解决传统数据库面临的挑战方面的优势。同时,我们还将探讨无服务器数据库对于前端、后端以及Java等技术领域的影响,并展望其未来的发展趋势。
13 0
|
4天前
|
存储 缓存 NoSQL
《优化数据库性能的关键技巧》
在当今信息爆炸的时代,数据库扮演着至关重要的角色。本文将分享一些关键的技巧,帮助开发人员优化数据库性能,提升系统的响应速度和稳定性。
|
6天前
|
弹性计算 网络安全
幻兽帕鲁服务器搭建实践及优化经验分享
Pocketpair出品的生存类游戏《幻兽帕鲁》最近非常火,最高在线人数已逼近200万,是有可能打破CSGO和PUBG记录的一款现象级游戏。官方服务器亚历山大,游戏开发商也提供了搭建私人专用服务器的方案,既可以保证稳定的游戏体验,也可以和朋友一起联机游戏,而且还能自定义经验翻倍等游戏里的一些选项。 那么如何快速拥有一个可以跟小伙伴们愉快玩耍的服务器呢?本文将为您详细介绍如何在阿里云上快速搭建幻兽帕鲁联机服务器。您只需按照页面提示选择相关配置,即可实现以下步骤的自动化:服务器创建、网络端口设置、防火墙配置以及游戏环境安装。整个过程仅需2-3分钟,即可轻松完成,无需手动配置。
|
7天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
39 0
|
17天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
58 0
|
19天前
|
存储 监控 关系型数据库
mysql 主备延迟的原因及解决思路,优化方法
MySQL 主备延迟(replication delay)是指主服务器(master)上的数据更新操作与备服务器(slave)上相同数据更新操作之间的时间差。这种延迟可能会导致数据不一致,影响系统的可用性和可靠性。以下是主备延迟的常见原因、解决思路和优化方法: ### 常见原因 1. **网络延迟**:主备服务器之间的网络不稳定或带宽不足。 2. **硬件性能**:备服务器的硬件性能不足,如 CPU、内存、磁盘 I/O 等。 3. **大量数据写入**:主服务器上的大量数据写入操作导致备服务器难以同步。 4. **复杂的查询**:备服务器执行复杂的查询操作,影响同步速度。 5. **二进制