成为MySQL DBA博客

  • 时间:
  • 浏览:0
  • 来源:大发3D_大发3D官方

max_connections: 一方面你想保持足够高以避免任何传入的连接;买车人面,可能性大多数服务器无法同時 避免数百个或更多的连接,假若您又不希望太高。避免此现象的三种土办法 是在程序池池端实现连接池,可能性使用像HAProxy从前的负载平衡器来限制负载。

(1)确保数据是安全的,即使数据库服务器被杀死 - 在任何状况下需要会丢失数据。一些设置(2和0)表示,整个数据库服务器可能性会崩溃;

完整版地持久性显然是一件好事,但它的价格非常高 - I / O负载要高得多,可能性在每次提交事先,刷新操作需要居于。假若,在一些状况下,降低耐用性和在一些条件下承担数据丢失的风险是非常受欢迎的。对于主 - 多个从属设置来说,这是真实的,通常状况下,在重建过程中含4个从属在崩溃事先通常是完整版正确的,可能性其余每种还都可不可不可不可以轻松避免工作量。Galera群集也是沒有: 整个群集作为单个实例工作,即使有4个节点崩溃并丢失数据。

InnoDB持久化

哪有几个是缓冲池,为哪有几个沒有重要?缓冲池是InnoDB用来缓存数据的内存。它用于缓存读取和写入每个可能性被修改的页面,首先需要加载到缓冲池,假若它变成了有4个脏页面,即有4个可能性被修改的页面,假若还沒有被刷新到表空间。正如让我想象的,从前的缓冲区对于数据库正确执行非常重要。“内存/磁盘”比率越差,你的工作负载将越受限于I / O,I / O约束的工作量往往是缓慢的。

InnoDB缓冲池

您可能性可能性听说过将InnoDB缓冲池设置为系统总内存的100%的经验法则。当8GB的内存是巨大的事先,它是有效的,但现在需要从前。在计算InnoDB缓冲池大小时,需要考虑其余MySQL的内存要求(假设MySQL是服务器上唯一运行的程序池池)。这类,亲戚亲戚我们在这里谈论每个连接,甚至每个查询缓冲区,如连接缓冲区或内存中临时表的最大区间,同時 你还需要考虑允许的最大连接数(更多的连接由于更多的内存使用量)。

内存分配器也还都可不可不可不可以对MySQL性能产生重大影响。让我取舍不同的内存分配器来使用MySQL。它们的性能在版本和工作负载之间有所不同,假若沒有在执行了完整版地测试后,都还都可不可不可不可以取舍在你的环境中哪个版本效果最好。最常见的取舍是默认的glibc malloctcmallocjemalloc

您可能性要查看的另一组设置是控制MySQL应创建的给定价值形式的有几个个实例/分区的变量。亲戚亲戚我们在这里讨论变量:innodb_buffer_pool_instancestable_open_cache_instancesmetadata_locks_hash_instancesinnodb_adaptive_hash_index_partitions。哪有几个选项清楚表明,这类,单个缓冲池或单个自适应散列索引可成为具有高并发性的工作负载的争用点时引入的。一旦你发现其中含4个价值形式成为有4个痛点(亲戚亲戚我们讨论了怎么可不可以在早期的博客文章中捕捉到哪有几个状况)让我想调整变量,但不幸的是,这里沒有经验法则。建议单个缓冲池实例的大小至少应为2GB,假若对于较小的缓冲池,你可能性需要坚持你什儿 限制。在一些变量的状况下,可能性亲戚亲戚我们讨论的是争用现象,沒有你可能性会增加哪有几个数据价值形式的实例/分区的数量,假若沒有关于怎么可不可以实现的规则,假若您需要观察工作量并决定此时争用不再是现象。

一、简介

让我通过安装有4个新的包(用于jemalloctcmalloc)来打上去新的分配器,假若在[mysqld_safe]中使用LD_PRELOAD(即导出LD_PRELOAD =“/ usr / lib / libtcmalloc_minimal.so.4.1.2”)或malloc-lib变量每种my.cnf

默认状况下,哪有几个变量具有相当小的值,这实际上是有意义的 —亲戚亲戚我们可能性看得人,低至256K的设置可能性比像4M从前的较大值快得多。很难说出你什儿 行为的确切由于,很可能性其中含却说。还都可不可不可不可以肯定的是,Linux改变了内存分配的土办法 。最多256KB使用malloc()。对于更大的内存块 - mmap(),重要的是要记住,当涉及到哪有几个变量时,任何变化都需要以基准为后盾,以确认新的设置嘴笨 是正确的。假若,你可能性会降低它的表现,而需要增加它。

#注释:买车人建议还是设置为 5-10左右

查询缓存

innodb_file_per_table 你什儿 变量决定InnoDB表是在单独的表空间(设置为1)还是在共享表空间(设置为0)时创建。当每个InnoDB表需要4个单独的表空间时,管理MySQL会容易得多。这类,对于单独的表空间,还都可不可不可不可以通过删除表或分区来轻松回收磁盘空间。共享表空间不起作用 - 回收磁盘空间的唯一土办法 是转储数据,清理MySQL数据目录,假若重新加载数据。显然,这暂且方便。

本文作者:田帅萌

MySQL使用比InnoDB缓冲池等多个缓冲区,它们是由变量控制:join_buffer_sizesort_buffer_size的值,read_buffer_sizeread_rnd_buffer_size。哪有几个缓冲区是按会话分配的(连接缓冲区除外,每个JOIN分配有4个缓冲区)。亲戚亲戚我们可能性看得人MySQL将哪有几个缓冲区设置为数百兆字节 —通过增加join_buffer_size,让我期望JOIN执行得更慢,更自然。

InnoDB的重做日志的大小也那个她 可能性你还都可不可不可不可以看的东西。它由innodb_log_file_sizeinnodb_log_files_in_group管理。默认状况下,亲戚亲戚我们在有4个组中含有4个日志,每个大小约为100MB。哪有几个日志用于存储写入事务,并按顺序写入。这里的主要现象是,MySQL沒有在日志中用完空间,假若可能性日志几乎已满,则需要停止整个活动并专注于将数据刷新到表空间。当然,这对程序池池来说是非常糟糕的,可能性在这段时间内沒有写入操作。这那个她 们顶端讨论的InnoDB I / O设置非常重要的由于之一。亲戚亲戚我们也还都可不可不可不可以通过改变innodb_log_file_size来增加重做日志的大小。经验法则是将它们设置得足够大,以覆盖至少1小时的写入。

log_bin 可能性您正在使用MySQL克隆qq,则需要启用二进制日志。即使您不使用它们,保留它们也是非常方便的,可能性它们还都可不可不可不可以用来进行时间点恢复。

一些设置

vm.dirty_background_ratio 是系统内存的百分比,在后台刷新过程开始英文英文英文事先,还都可不可不可不可以用来缓存已修改的(“脏”)页面,由于需要做更多的工作来清理缓存。

#买车人强烈建议注释使用row格式

內部争用避免

接下来,让我看得人看磁盘调度器。CFQ(通常是默认的CFQ)针对桌面工作负载进行了调整。这对于数据库工作负载来说效果不佳。大多数状况下,可能性将其更改为noop或截止日期,会看得人更好的结果。这有4个调度器之间几乎沒有哪有几个区别,亲戚亲戚我们发现基于SAN的存储Noop略胜一筹(SAN通常在避免工作负载方面更好,可能性它知道更多关于底层硬件以及与操作系统相比实际存储在缓存中的内容)。

从前重要的系统变量是vm.swappiness。一方面,在使用MySQL的事先,亲戚亲戚我们你还都可不可不可不可以使用交换,除非是急需,将InnoDB缓冲池交换到磁盘将删除内存缓冲池; 买车人面,可能性替代土办法 是启动OOM并杀死MySQL,从前们宁愿不从前做。 从前的行为还都可不可不可不可以通过将vm.swappiness设置为0来实现。 假若, 可能性它是保持MySQL的唯一选项,建议将vm.swappiness设置为1,以允许一些交换居于。当然,这会更慢系统,但MySQL上的OOM非常苛刻。

原文发布时间为:2017-12-23

数据库服务器需要CPU、内存、 磁盘和网络都还都可不可不可不可以运行,了解哪有几个资源对于DBA来说非常重要,可能性任何的超载行为都可能性成为限制因素,由于数据库服务器性能不佳。DBA的主要任务却说调整系统和数据库的配置,避免可用资源的过渡利用和利用过高 。

skip_name_resolve 此变量决定否是在作为传入连接源的主机上执行DNS查找。可能性启用,FQDN可作为主机在MySQL授权中使用。可能性需要,沒有用IP地址定义的用户都还都可不可不可不可以工作。启用DNS查找的现象是,它可能性会引入额外的延迟。DNS服务器也还都可不可不可不可以停止响应(可能性崩溃或网络现象),在你什儿 状况下,MySQL将沒有接受任何新的连接。

InnoDB重做日志

从前对MySQL性能有重大影响的变量是innodb_flush_log_at_trx_commit,这是InnoDB持久性的延伸。默认:

一般来说,亲戚亲戚我们希望看得人这有4个变量设置为低的数值:5 - 10%。

从前与内存相关的设置,确保你将NUMA完整版设置为关闭。让我通过修改启动脚从前启动MySQL: numactl --interleave=all $command ,此设置还都可不可不可不可以平衡NUMA节点之间的内存分配,并最大限度地减少其中含4个节点内存过高 的可能性。

需要检查内存和文件系统缓存避免土办法 相关的操作系统设置。一般来说,亲戚亲戚我们希望保持vm.dirty_ratiovm.dirty_background_ratio都为低。

二、OS系统优化

首先,性能优化是有4个持续的过程,安装MySQL通常是调整操作系统和数据库配置的第一步。而数据库是有4个动态系统,这是有4个永无止境的故事。你的MySQL数据库起初可能性是CPU绑定的,可能性你有足够的内存和很少的数据。随着时间地推移,它可能性会改变,磁盘访问可能性会变得更加频繁。正如让我想象的那样,I / O是主要关心的服务器配置,看起来不同于所有数据都适合内存的服务器的配置。此外,您的查询组合也可能性随时间而改变,假若访问模式或MySQL中可用功能的使用(如自适应哈希索引)也会随之改变。

现在却说从前。正如亲戚亲戚我们在开始英文英文英文时所提到的那样,调整哪有几个设置可能性不必使MySQL数据库的速率单位变得更慢 。但通过调整查询,您有可能性加快你什儿 速率单位。但那个她 们对整体表现仍然有明显的影响。祝你好运,调整工作!

本文来自云栖社区合作土办法 伙伴“老叶茶馆”,了解相关信息还都可不可不可不可以关注“老叶茶馆”微信公众号

一些可能性对一些工作负载有重大影响的变量是innodb_io_capacityinnodb_io_capacity_maxinnodb_lru_scan_depth。哪有几个变量定义了InnoDB的后台程序池还都可不可不可不可以完成的磁盘操作的数量,这类,从InnoDB缓冲池中刷新脏页面。默认设置是保守的,大多数状况下需要好的。可能性您的工作量非常大,您可能性需要调整哪有几个设置,看看您是需要阻止InnoDB完整版使用您的I / O子系统。可能性您拥有快速存储,则尤其沒有:SSD或PCIe SSD卡。

还有一些一些设置可能性需要考虑,一些还都可不可不可不可以在设置时以最有效的土办法 应用,一些还都可不可不可不可以动态更改。哪有几个设置不必对性能产生重大影响(有时影响也可能性是负面影响),但记住它们仍然怪怪的要。

三、MySQL配置调整

现在亲戚亲戚我们正式谈论磁盘,通常文件系统的最佳取舍是EXT4或XFS (过去这可能性改变了有几个),可能性您你还都可不可不可不可以充分利用I / O子系统,沒有你可能性需要对设置进行一些测试。无论使用哪种文件系统,都应该禁用MySQL卷的noatimenodiratime (对元数据的写入越少,整体开销就越低)。

(2)则可能性会丢失多达有4个事务,假若可能性mysqld被终止,则可能性会丢失多达有4个事务。

MySQL查询缓存也经常被“调整” 你什儿 缓存存储SELECT励志的话 的散列及其结果。有有4个现象 :第有4个现象是,缓存可能性会频繁刷新,可能性对给定的表执行任何DML,则将从查询缓存中删除与此表相关的所有结果,这严重影响了MySQL查询缓存的实用性。第三个现象是查询缓存受互斥体保护,访问被序列化。对于任何具有较高并发性的工作负载来说,这是有4个重大的缺点和限制。假若强烈建议通过完整版禁用MySQL查询缓存来“调整”MySQL缓存。让我通过设置`query_cache_type来完成关闭。嘴笨 ,在一些状况下,它可能性有一些用处,但大多数状况下暂且沒有。而需要依靠MySQL查询缓存,您还还都可不可不可不可以利用任何一些內部系统(如Memcached或Redis)来缓存数据。

I / O相关的设置

买车人面,vm.dirty_ratio是可用于缓存脏页面的内存的硬限制。可能性可能性写入活动较高,后台程序池池无法快速刷新数据以跟上新的修改,则还都可不可不可不可以达到该目的。一旦达到vm.dirty_ratio,所有的I/O活动都被锁定,直到脏页被写入磁盘,这里的默认设置通常是40%(在你的发行版中可能性会有所不同,但对于大容量内存的主机来说你什儿 数字是相当高的)。假设对于有4个128GB的实例,即使您使用的是快速固态硬盘,它也至少51GB,这可能性会在相当长的时间内锁定您的I/O。

对于具有24到3有4个内核和128GB内存的MySQL数据库服务器,避免多达20 - 100个同時 运行的连接和多达数百个同時 连接的客户端,亲戚亲戚我们还都可不可不可不可以说10 - 15GB的内存应该足够了。可能性你想保持安全励志的话 ,20GB应该是足够的。一般来说,除非您知道数据库的行为,假若建立理想的缓冲池大小是有4个反复试验的过程。在编写的事先,InnoDB缓冲池需要有4个动态变量,却说需要重新启动。假若,在“太小”方面犯错是比较安全的。随着Oracle引入动态分配的缓冲池,它将随着MySQL 5.7而改变,这将使调整更容易。

调整MySQL配置是整本书的有4个主题,无法在一篇博客文章中将其覆盖。亲戚亲戚我们将尝试在这里提到一些更重要的变量。

说到磁盘,innodb_flush_method是从前你可能性你还都可不可不可不可以看的设置。通过将此设置从默认的fdatasync切换到O_DIRECT,亲戚亲戚我们看得人了可见的性能提升。你什儿 增益在使用BBU备份的硬件RAID控制器的设置中清晰可见。买车人面,当涉及到EBS卷时,亲戚亲戚我们可能性使用`O_DSYNC看得人了更好的结果。在这里进行标记对于了解在特定状况下哪种设置更好是非常重要的。