求: 高IO数据库 硬件配置方案, 解决方案 -- 数据库硬件优化,实时同步备份HA方案
本帖最后由 Vinsion 于 2012-07-27 02:02 编辑求： 硬件配置方案，实时同步备份HA方案，或其它解决方案
-------------------------
情况如下：
数据库： POSTGRESQL 9
IO 应用(3个方面)： 后台新增数据， 前端页面查询数据， 统计数据程序COPY 源数据/写统计数据
-------------------------
数据结构 / IO应用：
高峰每秒增加 1100条数据，高峰时间大概是 5-8个小时，其它时间每秒大概 新增 400-800条数据
每天新增加数据大概 3000至5000万条
每条数据大概 850字节
这个表有 110个 字段，每天自动建立附表，数据存当天附表，主表不存数据
每个小时从这个表COPY出前一个小时的数据进行统计，统计后写回数据库
前端页面多人(2-5)同时查询数据，查询数据大概每次 select 10000笔
查询的热数据 集中在1个星期内，
每个星期一把上个星期的全部数据COPY出来统计，生成报表，写回数据库
每个月初把上个月的部分数据COPY出来统计，生成报表，写回数据库
-------------------------
现在用的：
现在非常卡，在没有COPY大量数据出来时，新增写入I/O 负载 20-40%
在有大量数据 COPY出来时，I/O 长时间持续 100% （有时超过10个小时）
CPU : Intel Xeon X5450 x2
RAM: 32G
RAID CARD: DELL PERC 6/I , 256M Cache
RAID 现在使用： RAID 5
HD 6个1T SATA:
scsi 0:0:0:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:1:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:2:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:3:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:4:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:5:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
-------------------------
-------------------------
现有以下硬件配置，但感觉 I/O 还是不够用
Minimum Requirements :
HD: SAS 15K 3.5" 6Gbps 600G x4 + SATA 3.5" Enterprise 6Gbps 1T x4
Memory: 32G
CPU: Intel Xeon E5620 x2
RAID: PERC H700 (或同等性能的其它 Hardware-RAID卡), 512M Cache,BBU,support RAID 0/1/10/5
RAID LEVEL: RAID 5
Good server:
HD: SAS 15K 3.5" 6Gbps 600G x8 + SATA 3.5" Enterprise 6Gbps 1T x6
Memory: 32G
CPU: Intel Xeon X5650 x2
RAID: PERC H700 (或同等性能的其它 Hardware-RAID卡), 512M Cache,BBU,support RAID 0/1/10/5
RAID LEVEL: RAID 10
Very good Server:
HD: SAS 15K 3.5" 6Gbps 600G x16
Memory: 64G
CPU: Intel Xeon X5660 x2
RAID: PERC H700 (或同等性能的其它 Hardware-RAID卡), 1G Cache,BBU,support RAID 0/1/10/5
RAID LEVEL: RAID 10
System HD: SATA 250G x2 (RAID 1) iostat:
avg-cpu:%user %nice %system %iowait%steal %idle
13.30 0.00 21.38 1.35 0.00 63.97
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz awaitsvctm%util
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda 0.00 578.50 0.00100.00 0.005428.00 54.28 0.19 1.93 1.4013.95
drbd0 0.00 0.00 0.00676.00 0.005408.00 8.00 16.85 2.60 1.2182.05
drbd1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu:%user %nice %system %iowait%steal %idle
12.24 0.00 20.87 1.22 0.00 65.67
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz awaitsvctm%util
sdb 0.00 311.50 0.00 12.50 0.002592.00 207.36 0.02 1.72 0.28 0.35
sda 0.00 227.00 0.00 56.00 0.002264.00 40.43 0.12 2.19 2.1612.10
drbd0 0.00 0.00 0.00283.00 0.002264.00 8.00 32.86 3.00 2.6474.80
drbd1 0.00 0.00 0.00324.00 0.002592.00 8.00 46.51 2.57 1.0333.45
avg-cpu:%user %nice %system %iowait%steal %idle
11.98 0.00 20.88 1.23 0.00 65.91
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz awaitsvctm%util
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda 0.00 561.00 0.00 77.50 0.005108.00 65.91 0.17 2.15 1.6212.55
drbd0 0.00 0.00 0.00633.50 0.005068.00 8.00 62.97 3.38 1.5799.40
drbd1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu:%user %nice %system %iowait%steal %idle
9.48 0.00 17.61 0.92 0.00 71.98
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz awaitsvctm%util
sdb 0.00 338.00 0.00 13.50 0.002812.00 208.30 0.02 1.59 0.30 0.40
sda 0.00 160.00 0.00 52.50 0.001700.00 32.38 0.10 1.92 1.60 8.40
drbd0 0.00 0.00 0.00195.00 0.001560.00 8.00 20.14 2.71 5.0798.85
drbd1 0.00 0.00 0.00351.50 0.002812.00 8.00 92.49 1.85 2.3783.45
本帖最后由 Vinsion 于 2012-07-27 02:37 编辑
简单测试写，生产环境，不能做更多测试dd if=/dev/zero of=dd_test1 bs=8k count=10000000
10000000+0 records in
10000000+0 records out
81920000000 bytes (82 GB) copied, 451.168 s, 182 MB/sdmesg|grep ATA
NODE_DATA
view.php?id=2 TEXT DATA BSS ==>
scsi 0:0:0:0: Direct-Access ATA ST31000340NS MA0D PQ: 0 ANSI: 5
scsi 0:0:1:0: Direct-Access ATA Hitachi HUA72101 A9N1 PQ: 0 ANSI: 5
scsi 0:0:2:0: Direct-Access ATA Hitachi HUA72101 A9N1 PQ: 0 ANSI: 5
scsi 0:0:3:0: Direct-Access ATA Hitachi HUA72101 A9N1 PQ: 0 ANSI: 5
scsi 0:0:4:0: Direct-Access ATA Hitachi HUA72101 A9N1 PQ: 0 ANSI: 5
scsi 0:0:5:0: Direct-Access ATA Hitachi HUA72101 A9N1 PQ: 0 ANSI: 5
ata1: SATA max UDMA/133 cmd 0xcc78 ctl 0xcc70 bmdma 0xcc40 irq 23
ata2: SATA max UDMA/133 cmd 0xcc60 ctl 0xcc58 bmdma 0xcc48 irq 23
ata2.00: ATAPI: TSSTcorpDVD-ROM TS-L333A, D100, max UDMA/33
cat /proc/scsi/scsi
Attached devices:
Host: scsi0 Channel: 00 Id: 32 Lun: 00
Vendor: DP Model: BACKPLANE Rev: 1.05
Type: Enclosure ANSISCSI revision: 05
Host: scsi0 Channel: 02 Id: 00 Lun: 00
Vendor: DELL Model: PERC 6/i Rev: 1.21
Type: Direct-Access ANSISCSI revision: 05
Host: scsi0 Channel: 02 Id: 01 Lun: 00
Vendor: DELL Model: PERC 6/i Rev: 1.21
Type: Direct-Access ANSISCSI revision: 05
Host: scsi2 Channel: 00 Id: 00 Lun: 00
Vendor: TSSTcorp Model: DVD-ROM TS-L333A Rev: D100
Type: CD-ROM ANSISCSI revision: 05
Host: scsi3 Channel: 00 Id: 00 Lun: 00
Vendor: Dell Model: VirtualCDROM Rev: 123
Type: CD-ROM ANSISCSI revision: 00
Host: scsi4 Channel: 00 Id: 00 Lun: 00
Vendor: Dell Model: VirtualFloppyRev: 123
Type: Direct-Access ANSISCSI revision: 00 本帖最后由 Vinsion 于 2012-07-27 02:36 编辑
简单测试写，生产环境，不能做更多测试dd if=/dev/zero of=dd_test1 bs=8k count=10000000
10000000+0 records in
10000000+0 records out
81920000000 bytes (82 GB) copied, 279.391 s, 293 MB/sdmesg | grep ATA
NODE_DATA
view.php?id=2 TEXT DATA BSS ==>
ata1: SATA max UDMA/133 cmd 0xcc98 ctl 0xcc90 bmdma 0xcc60 irq 23
ata2: SATA max UDMA/133 cmd 0xcc80 ctl 0xcc78 bmdma 0xcc68 irq 23
ata2.00: ATAPI: TSSTcorpDVD-ROM TS-L333A, D100, max UDMA/33
scsi 0:0:0:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:1:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:2:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:3:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:4:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
scsi 0:0:5:0: Direct-Access ATA TOSHIBA MK1002TS MT2A PQ: 0 ANSI: 5
cat /proc/scsi/scsi
Attached devices:
Host: scsi0 Channel: 00 Id: 32 Lun: 00
Vendor: DP Model: BACKPLANE Rev: 1.05
Type: Enclosure ANSISCSI revision: 05
Host: scsi0 Channel: 02 Id: 00 Lun: 00
Vendor: DELL Model: PERC 6/i Rev: 1.21
Type: Direct-Access ANSISCSI revision: 05
Host: scsi0 Channel: 02 Id: 01 Lun: 00
Vendor: DELL Model: PERC 6/i Rev: 1.21
Type: Direct-Access ANSISCSI revision: 05
Host: scsi2 Channel: 00 Id: 00 Lun: 00
Vendor: TSSTcorp Model: DVD-ROM TS-L333A Rev: D100
Type: CD-ROM ANSISCSI revision: 05
Host: scsi3 Channel: 00 Id: 00 Lun: 00
Vendor: Dell Model: VirtualCDROM Rev: 123
Type: CD-ROM ANSISCSI revision: 00
Host: scsi4 Channel: 00 Id: 00 Lun: 00
Vendor: Dell Model: VirtualFloppyRev: 123
Type: Direct-Access ANSISCSI revision: 00
DELL R510
CPU: Intel XEON E5620 x2
RAM: 64G
HD: 1T 7.2K SATA 3Gbps 3.5" x8
RAID: PERC H700, 512MB cache, BBU
dd if=/dev/zeroof=./test.dd-1 bs=8k count=10000000
10000000+0 records in
10000000+0 records out
81920000000 bytes (82 GB) copied, 119.107 s, 688 MB/smegasas: 00.00.05.40-rh2 Thu. Aug. 4 17:00:00 PDT 2011
megasas: 0x1000:0x0079:0x1028:0x1f17: bus 2:slot 0:func 0
alloc irq_desc for 32 on node -1
alloc kstat_irqs on node -1
megaraid_sas 0000:02:00.0: PCI INT A -> GSI 32 (level, low) -> IRQ 32
megaraid_sas 0000:02:00.0: setting latency timer to 64
megasas: FW now in Ready state
alloc irq_desc for 61 on node -1
alloc kstat_irqs on node -1
megaraid_sas 0000:02:00.0: irq 61 for MSI/MSI-X
megasas_init_mfi: fw_support_ieee=67108864
megasas: INIT adapter done
megaraid_sas: fw state:c0000000
megasas: fwstate:c0000000, dis_OCR=0
scsi0 : LSI SAS based MegaRAID driver
scsi 0:0:0:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:1:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:2:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:3:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:4:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:5:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:6:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
scsi 0:0:7:0: Direct-Access ATA ST1000NM0011 PA03 PQ: 0 ANSI: 5
ata_piix 0000:00:1f.2: version 2.13
alloc irq_desc for 23 on node -1
alloc kstat_irqs on node -1
ata_piix 0000:00:1f.2: PCI INT C -> GSI 23 (level, low) -> IRQ 23
ata_piix 0000:00:1f.2: MAP [ P0 P2 P1 P3 ]
scsi 0:0:32:0: Enclosure DP BACKPLANE 1.10 PQ: 0 ANSI: 5
scsi 0:2:0:0: Direct-Access DELL PERC H700 2.10 PQ: 0 ANSI: 5
页:
[1]