2023年7月11日发(作者：)

MySQL数据库服务器整体规划（思路与步骤）MySQL数据库服务器整体规划(思路与步骤)参考资料：

我们在搭建MySQL数据库服务器的开始阶段就合理的规划，可以避免以后的很多问题的产⽣，⼤⼤节省我们的时间和精⼒，在⼀定幅度上降低成本。当然，这会涉及到多⽅⾯。⽐如机器的选型、业务评估和系统规划等。所有的设计都是跟具体的需求相关的，我们⾸先要做的就是对业务进⾏整体评估。我在下⾯分享⼀个具体的例⼦。⼀、业务需求⼀、业务需求要求响应时间数据总量每秒请求量读写⽐重要程度其他说明查询和操作请求ms级别返回1年内⼤约有500G的数据量每秒有3W的请求量读写⽐是1:1核⼼业务，P1级别故障数据具有时效性，历史数据访问减少，⼀般处理最近7天内的数据，数据总体长度约为1K指标⼆、业务评估⼆、业务评估步骤step1

step2

step3

结果

结果1

结果2

结果3说明1年内数据量⼤约500G每秒产⽣的数据量为：500*1024*1024(365*24*60*60)=17KB每秒3W次请求；读写⽐为1:1每秒的读请求为15000次；每秒的写请求是15000次。记录的长度⼤约为1KB根据step1得出的结果，每秒insert的数据写⼊⼤约为17KB；根据step2得出的结果，每秒写⼊的请求为15000次，可得知14083(15000-17)次为update和delete操作之和。由于MySQL写⼊操作按照页来处理，页⼤⼩为16KB，假设每次操作的页都不相同，那么每秒写操作的数据量为16KB*15000=234MB。每秒读操作的数据量为16KB*15000=234MB。处理最近7天的数据热数据量为：(500/365)*7=21GB操作ms级别返回操作ms级别返回，并且读写基本平衡。需要尽可能多的将数据加载到内存。按照内存命中率接近100%计算的话，那么innodb_buffer⼤约21G，⽽其他的则⼤约需要2-4G，因此按照30%的超配原则，操作系统内存⼤约需要32G(32.5G)。如果带宽也按照30%的超配原则的话，那么写带宽(wBPS)限制为304MB/s，读带宽(rBPS)限制为304MB/s。step4

step5

结果

结果

三、硬盘选择三、硬盘选择HDD类型的硬盘更善于处理⼀些顺序读写的内容，⽽SSD硬盘不管是顺序还是随机的性能都远远优于HDD的硬盘，但是SSD的硬盘价格必将昂贵。因此，我们可以通过合理的分配，降低整体拥有的成本。例如，对于⼀些⽇志⽂件，这些⽇志⽂件主要是顺序IO，我们把这些⽂件放到HDD上，可以考虑使⽤RAID5S级别，提⾼⽇志系统的容错能⼒。对于数据⽂件，我们可以考虑放到SSD上，使⽤RAID10提⾼容错能⼒。四、机型测试四、机型测试性能对⽐测试：对于不同硬件设备在压⼒测试下数据库表现性能指标。对于硬件性能做出整体的评估。稳定性测试：没有抖动现象，可以持续稳定的提供服务。掉电保护测试：这个环节最好需要系统⼯程师配合。内存异常测试：测试内存是否容易出现问题，能否对业务提供稳定的⽀持。此外，还有IO设备和坏盘重构。经过⼀系列的测试，我们可以选出2~3中候选机型，我们应当尽量避免应⽤和设备绑定，防⽌单个机型缺货，供应不⾜影响业务。五、成本评估五、成本评估通过前⾯的⼀些列的准备⼯作，我们可以选出候选的机型，考虑我们的使⽤成本。这包括：设备成本运维成本功耗成本特别注意：我们虽然有的时候单机使⽤成本会上升，但是整体的使⽤成本却下降了。这是因为我们提升了单个机器的性能，可以减少机器的数⽬。降低运维和功耗的成本，甚⾄也降低了整体设备的成本。六、⽂件系统规划六、⽂件系统规划MySQL数据库的特点：(1) 单数据⽬录(单个实例不能执⾏多个数据库)(2) 混合读写(⽇志和数据的读写⽅式是不⼀样的)(3) 请求随机

⽂件系统划分： /dev/sda1 /boot /dev/sda2 / /dev/sda3 /home /dev/sda4 /tmp /dev/sdb1 /data /dev/sdc1 /log根据使⽤经验，建议IO调度策略为deadline的⽅式:echo deadline > /sys/block/sd{b,c}/queue/schedulerMySQL数据库⽇志⽂件是顺序读写的，建议放在普通的SSD硬盘上。-- binlog⽇志⽂件、error⽇志⽂件、slow⽇志⽂件可以放在/log⽇志⽬录中-- tmp⽂件指定为系统的⽬录/tmp-- 其他所有的⽬录指定为数据⽬录/data注意：虽然⽇志⽂件是随机读写的，但是由于把⼏个⽇志⽂件都放在⼀个分区，还是可能产⽣随机读写的现象。七、其他实例七、其他实例业务评估需求 指标相应时间 查询和操作要求ms级别返回数据总量 1年内⼤约1T数据美妙请求量 ⼤约1W次请求读写⽐ 读写⽐例 4：1重要程度 核⼼系统，p1级别故障，其他说明 数据具有时效性，历史数据访问较少，⼀般会处理最近15天内的数据，数据记录总长度⼤约有1kb

⼀年产⽣的数据为1T，每秒产⽣的数据为　　1T*1024*1024*1024/(365*24*3600)=34kB/s　　每秒⼀万次请求 读写⽐⾥4：1（qps） 读请求的⽐较为8000 写请求（tqs） 2000　　然后⼀条数据插⼊⼤约有1kb，每秒就应该是34次插⼊（insert），然后update delete应该该是就是1966次。然后数据库的操作是按照页来处理，页的⼤⼩为16KB，假设每次操作的页都是不相同的，那么每　　秒要操作16kB×2000=32M/s 每秒的读操作应该是128MB/s。　　是⼀个具有时效性的数据，处理近15天的数据，那么需要就需要1T/365*15=42G。　　要求毫秒级返回 需要尽可能的把数据加载到内存中 那么innodb buffer ⾄少42G，其他⼤约需要3G。　　按照30%的超配原则：内存⼤约需要60G内存，标准为64G内存的机器；写带宽因该约等于40MB/s(wbps); 读带宽(rbps)应该是166MB/s。