PostgreSQL修炼之道之数据库优化（十八）-Toy模板网

这篇具有很好参考价值的文章主要介绍了PostgreSQL修炼之道之数据库优化（十八）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

12.1 数据库优化准则和方法

12.1.1 数据库优化准则

数据库优化的思路有很多种。比较常用的是下面两种优化思路。

第一种思路：有人说过，“The fastest way to do something is don't do it”，意思是说，“做得最快的方法就是不做”。从这个思路上来说，把一些无用的步骤或作用不大的步骤去掉就是一种优化。
第二种思路：做同样一件事情，要想更快有多种方法，最简单的方法就是换硬件，让数据库跑在更快的硬件上。但换硬件一般都是最后的选择，除此之外，最有效的方法是优化算法，如让SQL走到更优的执行计划上。

在数据库优化中，主要有以下优化指标。

响应时间：衡量数据库系统与用户交互时多久能够发出响应。
吞吐量：衡量在单位时间内可以完成的数据库任务。进行数据库优化时，笔者都是围绕着上述指标进行优化的。数据库优化工作中，第一项就是确定优化目标。
性能目标：如CPU利用率或IOPS需要降到多少。
响应时间：需要从多少毫秒降到多少毫秒。
吞吐量：每秒处理的SQL数或QPS需要提高到多少。

一个已运行的数据库系统，如果前期设计不合理、性能不高，后期在优化时会非常困难，有可能永远无法达到高性能，因此，在新建一套数据库系统前，首要的事应该是设计优化。良好的设计能最大限度地发挥系统的性能。

12.1.2 优化方法

优化的第一件事是确定目标，那么要如何确定一个合理的目标呢？这就需要使用测试工具。熟练使用常用的测试工具是做数据库优化的基础。下面是一些常用的测试工具。

memtest86+：内存测试工具。
STREAM：内存测试工具。
sysbench：综合测试工具，可以测试CPU、I/O、数据库等。
pgbench：PostgreSQL自带的测试工具，可以仿真TPC-B的测试模型。
fio：最强大的免费I/O测试工具。
orion：Oracle的I/O测试工具，测试裸设备的I/O能力，功能比fio要少，但使用简单。

熟练掌握以上几种测试工具的使用方法，对数据库的优化很有帮助。

在数据库优化中，首先需要了解一些常用硬件的相关知识，熟悉这些硬件的特性和性能，才能知道目前数据库系统使用的硬件是否到达了瓶颈、更换硬件是否能提高数据库的性能。

12.2 硬件知识

CPU、内存、网络、硬盘的响应时间和吞吐量都是不一样的，了解这些知识，有助于理解如何优化硬件。

PostgreSQL修炼之道之数据库优化（十八）

12.2.1 CPU及服务器体系结构

服务器系统可以分为以下几种体系结构。
（1）SMP/UMA -Symmetric Multi Processing/Uniform Memory
Architecture

优点：服务器中多CPU对称工作，无主次关系。各CPU共享相同的物理内存，访问内存任何地址所需的时间相同，因此程序设计较为简单。
缺点：因多CPU无主次关系，需要解决内存访问冲突，所以硬件实现成本高。

（2）NUMA-Non-Uniform Memory Access

优点：多CPU模块，每个CPU模块具有独立的本地内存（快），但访问其他CPU内存（慢），硬件实现成本低。
缺点：全局内存访问性能不一致；设计程序时需要特殊考虑。

（3）MPP-Massive Parallel Processing·

优点：由多个SMP服务器通过节点互联网络连接而成，每个节点都可访问本地资源（内存、存储等），完全无共享（Share-Nothing）。最易扩展，软件层面即可实现。
缺点：数据重分布；程序设计复杂。

12.2.2 内存

内存是CPU与外部沟通的桥梁。CPU运算时所需的数据都临时保存在内存中，计算机的所有程序也都运行在内存中，内存通常也用于硬盘等外部存储器的数据缓存。
内存从硬件上分为以下几种。

SRAM：静态随机存储器。随机是指数据不是线性依次存储的，而是自由指定地址进行数据读写的。CPU的cache一般使用这种存储方式，特点是速度快但造价高，不能大规模使用。
DRAM：动态随机存储器。动态是指存储阵列需要不断刷新来保证数据不丢失。造价比SRAM低得多，但速度也慢一些。
SDRAM：同步动态随机存储器，同步是指工作时需要同步时钟，内部命令的发送与数据的传输都以它为基准。
DDR SDRAM：双倍数据传输率的SDRAM，DDR是“Double Data Rate”的缩写。普通的SDRAM在一个时钟周期内只传输一次数据，即它在时钟的上升期进行数据传输；而DDR内存则在一个时钟周期的上升期和下降期各传输一次数据，因此称为双倍速率同步动态随机存储器。DDR内存又分DDR1、DDR2、DDR3、DDR4几种，分别对应第一代、第二代、第三代、第四代DDR。目前主流的内存为DDR4内存。

12.2.3 硬盘

硬盘按接口可以分为以下3种。

ATA系列：包括较早的硬盘接口，比如IDE（Integrated DriveElectronics）、PATA（Parallel ATA）及SATA（Serial ATA）。
SCSI系列：包括早期的并行SCSI和现在使用较广泛的SAS（串行SCSI）。
FC接口：支持FC协议接口的硬盘。

FC接口的硬盘一般只在专用存储上使用，通常见到的硬盘都是SATA或SAS接口的。
硬盘按存储介质来区分，可以分为以下两种。

HDD：普通机械硬盘。
SSD：固态硬盘。

机械硬盘和SSD硬盘都有SATA和SAS接口的这两种。

硬盘通常通过SAS或SATA接口的卡连接到主机上。SAS卡既能接SAS硬盘，也能接SATA硬盘，但SATA卡只能接SATA硬盘。
目前SAS的接口速度一般是3Gb/s或6Gb/s。

12.3 文件系统及I/O调优

目前PostgreSQL数据库还不支持直接在裸设备上存储数据，也就是说，PostgreSQL的数据必须存储在文件系统上，故而选择一个合适的文件系统对PostgreSQL数据库来说非常重要。

12.3.1 文件系统的崩溃恢复

        文件系统中除记录文件内容信息外，还记录了一些元数据（如目录树、文件名、文件的块分配列表），以及和文件相关的一些属性（如文件名、文件的创建时间等），还有磁盘的空间分配信息（如哪些块已被分配、哪些块是空闲的）。
        在写一个文件时，除了写文件的内容信息外，还会写一些元数据。为了保证数据的可靠性，在出现宕机等异常情况后，文件系统除了要保证元数据本身一致，还要求文件内容的数据与元数据之间也是一致的。
        元数据一致性当然是最重要的，不能将同一个数据块分配给两个文件，这会导致一个文件的内容被另一个文件覆盖。分配出去的数据块必须有文件在使用，否则会导致明明现有文件并未占用多少空间，但文件系统上却没有空间了。
        当向一个文件的末尾添加数据时，文件会扩大，如果元数据记录了该文件的扩大，但新数据没有实际写入，就会导致新扩大的数据块中存在垃圾数据，这有可能导致问题产生。
        早期的文件系统并不能保证元数据与数据的一致性，如Windows下的FAT文件系统和Ext2文件系统。当一个操作需要多次写元数据或一次写元数据一次写数据时，操作中的多个步骤通常不是原子性的，要保证一致性就必须要有类似数据库中的事务的概念。要有事务就需要有日志，也就是说，要通过日志来保证整个操作的一致性。
        所以现在流行的文件系统都被设计成有日志的，如Ext3、Ext4及Windows下的NTFS文件系统。
        但写日志相当于原先的一次写变成了两次写，可能会降低写的性能。为了降低对性能的影响，多数文件系统通常只是把元数据写入日志，而实际数据块内容的变更并不会写入日志。
        如果一个文件已被写过，再写以前的数据块时，不会分配新的数据块；关于空间分配的元数据也不会被更新，通常只更新文件上的时间戳。对于数据库来说，这种情况下通常不会产生不一致，所以数据库使用重写会更安全一些，PostgreSQL中WAL日志的写就是这样的。文章来源地址https://www.toymoban.com/news/detail-499857.html

到了这里，关于PostgreSQL修炼之道之数据库优化（十八）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！