数据库热点问题的产生和避免

前言传统关系型数据库的性能问题，主要出现在CPU、磁盘、数据库本身（锁，Latch）这几个方面。而这些问题的产生很多时候都是因为存在热点，比如大量的全表扫描，会产生IO热点；对一张表的频繁修改，会产生锁冲突的热点；对一个数据库页的频繁访问会造成数据库内部（比如Latch）的热点。本文将从服务器资源，数据库配置，应用设计等几个方面来讨论热点的产生及如何避免。补充说明，因为本文作者具有DB2技术背景，所以有些术语主要参照DB2，对于其他数据库产品可能名称不一样，但基本原理相似，希望为同行带来参考。一、概述作为DBA，最关心和平时做的最多的就是数据库的性能。定位性能问题首先要根据系统资源使用率来分析。如果出现CPU、IO等出现100%繁忙现象，可以定义为热点问题。数据库的性能问题主要发生在几个层面，首先是硬件，即CPU、内存、磁盘这些因素，其次是数据库的参数配置，之后是数据库设计，包括表结构，索引等，最后是应用设计，包括程序接口、编程方式、程序设计等。下面将分别从这几个方面进行探讨。二、硬件对数据库性能的影响数据库的热点，最终都会在操作系统层面反映出来，表现为CPU高或者IO高。所以这里先讨论一下硬件对数据库性能的影响。数据库服务器的硬件配置决定了单台服务器的服务能力。硬件的性能越好，出问题的频率越低，问题持续时间越短，高性能的硬件也可以掩盖一些数据库配置和应用设计上的问题。硬件的基本配置要满足应用正常的需求，在系统上线前一定要进行压力测试，来找到应用系统对硬件的最低要求。下面从存储、CPU、内存几个方面进行讨论。一般而言，最容易出现瓶颈的是磁盘。磁盘的IO速度相对是最慢的，所以也最容易出问题。在几年前，大多通过使用高性能存储来提高IO吞吐能力，随着固态硬盘的出现，对一些小型应用，使用固态盘也能满足部分场景的需求。现在使用固态盘代替机械盘的存储也已经非常的成熟了，在存储小型化，高性能方面出现了质的飞跃。超过百万IOPS的产品越来越多，性价比也非常好，极大减少了IO瓶颈的问题。在实际生产环境中，也有直接使用固态硬盘而不使用存储的，这种情况建议使用多块磁盘来提高性能和可靠性。CPU一般不会成为瓶颈，在服务器选型的时候，CPU一般来说都是高配的，OLTP类的应用平时的CPU使用率一般在20%以下，峰值50-70%，在CPU这块会留很大的余量。但是CPU满的问题，还是非常常见的，这主要是因为CPU被滥用导致的，比如一个SQL语句占满一颗CPU，那么并发高的时候，就会把所有CPU资源用尽。即使有再多的CPU，也只是延迟问题发生而已。所以在CPU这块根据业务规模和压力测试结果选择即可。内存这块变化很大，以前内存还是稀缺资源，采用32G，64G内存的服务器比较常见，现在的服务器在内存一般都很大，256G、512G的配置很常见了。所以对于大内存的服务器，充分利用服务器的内存资源，不要拿以前的32G服务器上数据库参数照搬，要按照新服务器内存大小对参数进行相应的调整。对于数据库独用服务器，内存可以使用到总内存的70%。网络一般不会成为瓶颈，服务器一般都配置多块网卡，注意和应用服务器一定要使用万兆网卡相连。另外需要注意，应用服务器和数据库服务器需要在同一个机房，不能跨城市访问。三、数据库参数配置对性能的影响数据库的参数分为几类：与共享内存相关、与私有内存相关、与进程（线程）数相关。这些参数都要根据应用特点，数据库压力情况进行适当的调整。目前传统的数据库都能做到参数的自动调整，大大减少问题发生的概率。但是一些新兴的数据库，在这块做的还不好，需要DBA继续关注。数据库最重要的参数就是缓冲池的大小，各种数据库对缓冲池的命名不一样，但本质是一样的，就是缓存数据的一块内存区域，是整个数据库的一块共享内存区域。当缓冲池过小时，会发生频繁的内存与磁盘之间的数据交换，形成热点，表现为CPU繁忙或者IO繁忙。数据库缓冲池的内存从资源角度看可以使用到整个服务器的50%左右，从数据库容量的角度看不应低于数据库大小的5%。数据库访问计划缓存，首先访问计划内存要足够大，尽量提高访问计划命中率；其次通过参数化等手段，减少需要缓存的访问计划数量，这个和程序编写方式有关，也和数据库参数配置有关。数据库的私有内存主要关注工作空间这块，一个数据库连接，在处理SQL语句的时候，需要一个工作空间，就像一个人在工作时使用的办公桌一样，这个空间占用的内存属于这个连接的私有内存。这个内存也要足够大，否则处理的数据量过大时，容易成为热点。数据库的排序内存，哈希内存空间，有的数据库使用一个参数控制，有的数据库使用不同的参数控制，这个内存也非常重要，一般在线交易系统不大会有问题，分析性的数据库这方面需求比较大，可以用的总内存的20%以上，可以根据实际情况调整。四、数据库设计对性能的影响在设计数据库表的时候，谨慎使用Lob字段类型，这种类型的数据不能使用缓冲池，容易成为热点。有些数据库为了提高性能，提供了Lob字段表内存储的功能，虽然有长度限制，只要大部分实际数据都小于这个限制，数据就可以和普通字段一样，存在表中，可以使用缓冲池，这样就大大提高了性能，避免成为热点。再谈一下数据库的索引设计。一种情况是没有索引，一种情况是索引选择的字段不合适。如果表没有合适的索引，那么只能对该表进行全表扫描，当高并发时，对单张表的频繁扫描就会成为热点。这是一个非常普遍的现象，所以平时的数据库监控中关注全表扫描情况非常重要。索引的字段要用那些选择性高（即键值多）的字段，复合索引要把选择性高的字段放在索引的前面。如果用选择性很低的字段，而且放在索引的第一个字段，就非常容易形成热点。还有一种情况需要注意，就是对于高频访问的小表，数据可能集中在一个页面上，这个很容易成为热点，可以通过调整建表参数，尽量让数据分布在多个页面，可有效避免热点。五、应用程序设计对性能的影响应用程序对数据库性能有着根本的影响，良好的程序设计可以避免很多的性能问题。有些问题是无法通过优化数据库，增加资源能够解决的，最终只能去修改应用程序。开发人员要有基本的SQL能力，掌握基本的聚合函数等。否则用程序来实现SQL可以实现的功能，很容易出现热点问题。例如用一个循环来实现统计的功能，会造成大量SQL同时执行，非常容易产生热点。SQL语句要避免全表扫描，对于可以分页展示的查询，按照展示的页面进行查询，不要一下子把所有数据都查出来。应用设计上避免单表瓶颈。比如在秒杀场景中，一个商品的库存是有限的，每一笔交易都要进行库存的更新，那么这里就会形成热点，一旦阻塞，数据库层面是无法解决的。必须从设计上进行解决。对SQL语句进行参数化，例如Java程序中要使用PreparedStatement，C程序中使用静态语句，参数化的语句在数据库中会生成访问计划缓存，可以复用，避免每次都进行硬解析，大量的语句硬解析也很容易成为热点。插入性能是应用非常关心的，由于插入需要记录日志，是成本非常高的操作，所以对于大量的数据插入操作，要进行适当的设计，比如采用一个语句插入多条记录的方式，或者一个事务提交多条记录的方式减少日志瓶颈。六、数据库日常维护对性能的影响日常维护强调一下数据库统计信息收集和历史数据清理。统计信息不准确容易导致生成错误的访问计划，出现性能问题。最常见的是一个大表有很多记录，但是统计信息却是零条记录，这种情况