MSSQL网站制作之Use bonnie to Test system IO speed

Federated将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
UsebonnietoTestsystemIOspeed

Friday,2004-09-1711:33Eygle


原文链接:

http://www.eygle.com/unix/Use.Bonnie.To.Test.IO.speed.htm

Bonnie是一款极小的测试体系IO功能的工具,源代码公然.作者主页:
http://www.textuality.com/bonnie/
你也能够点击这里下载

编译Bonnie极为复杂,可是必要你安装了make及gcc(或cc)



#makeOptionsare"makebsd"and"makeSysV"-thedefaultis"bsd".Ifyougetmessagesaboutmissingfunctions,try"makeSysV."makeBonniemake[1]:Enteringdirectory`/export/home/gqgai/bonniecc-OBonnie.c-oBonniemake[1]:Leavingdirectory`/export/home/gqgai/bonnie

其利用手册在:

http://www.textuality.com/bonnie/advice.html

测试IO速率:

1.T3阵列速率测试(raid5)



#df-kFilesystemkbytesusedavailcapacityMountedon/dev/dsk/c0t0d0s0101838211771183956913%//dev/dsk/c0t0d0s62055705117760281643260%/usr/proc0000%/procfd0000%/dev/fdmnttab0000%/etc/mnttab/dev/dsk/c0t0d0s1144891177214861880756%/varswap4770120847701121%/var/runswap495892818881647701124%/tmp/dev/dsk/c5t1d0s051634648351544041596389869%/data1/dev/dsk/c5t1d1s12581614421498881405910285%/data3/dev/dsk/c5t1d1s025816144146507231090726058%/data2/dev/dsk/c0t2d0s1154836181074317142544658%/temp/dev/dsk/c0t2d0s071368862824586424093240%/varlink/dev/dsk/c0t2d0s582585975868224230778872%/opt/dev/dsk/c0t2d0s72053605128134271065565%/export/home/dev/dsk/c0t2d0s6205360517815118138469%/usr/local#formatSearchingfordisks...doneAVAILABLEDISKSELECTIONS:0.c0t0d0/pci@1f,4000/scsi@3/sd@0,01.c0t1d0/pci@1f,4000/scsi@3/sd@1,02.c0t2d0/pci@1f,4000/scsi@3/sd@2,03.c3t0d0/pci@4,4000/scsi@4/sd@0,04.c3t1d0/pci@4,4000/scsi@4/sd@1,05.c3t2d0/pci@4,4000/scsi@4/sd@2,06.c3t3d0/pci@4,4000/scsi@4/sd@3,07.c5t1d0/pci@1f,2000/SUNW,qlc@1/fp@0,0/ssd@w50020f23000083a2,08.c5t1d1/pci@1f,2000/SUNW,qlc@1/fp@0,0/ssd@w50020f23000083a2,1Specifydisk(enteritsnumber):^D#./Bonnie-d/data1-s2046-mbilling-centerFile/data1/Bonnie.8273,size:2145386496Writingwithputc()...doneRewriting...doneWritingintelligently...doneReadingwithgetc()...doneReadingintelligently...adoneSeeker2...Seeker1...Seeker3...startem...done...done...done...-------SequentialOutput-----------SequentialInput----Random---PerChar---Block----Rewrite---PerChar---Block-----Seeks---MachineMBK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPU/sec%CPUbilling-20461025187.53243055.71404839.51262599.54876367.361.58.3

扼要先容一下输入:

这里SequentialOutput指写进速率,Char指按字符体例写进,Block指按块体例写进,Rewrite指按次改写速率(也就是数据修正)
SequentialInput指读取速率

CPU,指该操纵占用的CPU资本.

此处T3Raid5的写进到达10251K/sec

2.当地磁盘速率

#./Bonnie-d/opt/oracle-s1024-mbilling-centerFile/opt/oracle/Bonnie.8545,size:1073741824Writingwithputc()...doneRewriting...doneWritingintelligently...doneReadingwithgetc()...doneReadingintelligently...doneSeekerSeeker2...1...Seeker3...startem...done...done...done...-------SequentialOutput-----------SequentialInput----Random---PerChar---Block----Rewrite---PerChar---Block-----Seeks---MachineMBK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPU/sec%CPUbilling-1024958285.01592829.8690020.31173596.54609867.2256.68.8

前面会渐渐增加一些其他硬件的测试数据.

3.EMCCLARiiONCX500测试数据

体系情况:

#/usr/platform/sun4u/sbin/prtdiag-vSystemConfiguration:SunMicrosystemssun4uSunFire480RSystemclockfrequency:150MHzMemorysize:4096Megabytes=========================CPUs===============================================RunE$CPUCPUBrdCPUMHzMBImpl.Mask------------------------A010508.0US-III+11.0A210508.0US-III+11.0=========================MemoryConfiguration===============================LogicalLogicalLogicalMCBankBankBankDIMMInterleaveInterleavedBrdIDnumsizeStatusSizeFactorwith------------------------------------------------------A00512MBno_status256MB8-way0A01512MBno_status256MB8-way0A02512MBno_status256MB8-way0A03512MBno_status256MB8-way0A20512MBno_status256MB8-way0A21512MBno_status256MB8-way0A22512MBno_status256MB8-way0A23512MBno_status256MB8-way0=========================IOCards=========================BusMaxIOPortBusFreqBusDev,TypeIDSideSlotMHzFreqFuncStateNameModel---------------------------------------------------------------------------------------PCI8B233332,0okSUNW,XVR-100SUNW,375-3126PCI8B433334,0okfibre-channel-pci10df,f900.10df.+PCI8B533335,0okfibre-channel-pci10df,f900.10df.+PCI8A066661,0okpci-pci8086,b154.0/pci108e,1000PCI-BRIDGEPCI8A066660,0okpci108e,1000-pci108e,1000.1deviceonpci-bridgePCI8A066660,1okSUNW,qfe-pci108e,1001SUNW,pci-qfe/pci-bridg+PCI8A066661,0okpci108e,1000-pci108e,1000.1deviceonpci-bridgePCI8A066661,1okSUNW,qfe-pci108e,1001SUNW,pci-qfe/pci-bridg+PCI8A066662,0okpci108e,1000-pci108e,1000.1deviceonpci-bridgePCI8A066662,1okSUNW,qfe-pci108e,1001SUNW,pci-qfe/pci-bridg+PCI8A066663,0okpci108e,1000-pci108e,1000.1deviceonpci-bridgePCI8A066663,1okSUNW,qfe-pci108e,1001SUNW,pci-qfe/pci-bridg+=========================EnvironmentalStatus=========================SystemTemperatures(Celsius):-------------------------------DeviceTemperatureStatus---------------------------------------CPU050OKCPU252OKDBP028OK=================================FrontStatusPanel:-------------------Keyswitchposition:NORMALSystemLEDStatus:LOCATORFAULTPOWER---------------------[OFF][OFF][ON]=================================DiskStatus:------------DISK0:[NO_FAULT]DISK1:[NO_FAULT]=================================FanStatus:-----------BankRPMStatus---------------CPU05660[NO_FAULT]CPU13896[NO_FAULT]CPU23797[NO_FAULT]IO03947[NO_FAULT]IO14166[NO_FAULT]=================================PowerSupplies:---------------SupplyStatus------------PS0[NO_FAULT]PS1[NO_FAULT]==========================================================HWRevisions=======================================SystemPROMrevisions:----------------------OBP4.10.82003/07/2508:44IOASICrevisions:------------------PortModelIDStatusVersion-------------------------Schizo8ok7Schizo9ok7

阵列为CLARiiONCX500,以下分离是4块盘的Raid1+0及Raid5的测试数据:

Raid1+0测试

#./Bonnie-d/eygle-s2046-mjumpFile/eygle/Bonnie.460,size:2145386496Writingwithputc()...doneRewriting...doneWritingintelligently...doneReadingwithgetc()...doneReadingintelligently...doneSeekerSeeker2...1...Seeker3...startem...done...done...done...-------SequentialOutput-----------SequentialInput----Random---PerChar---Block----Rewrite---PerChar---Block-----Seeks---MachineMBK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPU/sec%CPUjump20463743888.811117263.87594090.04349599.732215399.917747.8177.5

这里EMCCX500的速率到达了,37438K/sec,比T3的功能强了良多

上面是Raid5的测试数据:

#./Bonnie-d/eygle-s2046-mjumpFile/eygle/Bonnie.473,size:2145386496Writingwithputc()...doneRewriting...doneWritingintelligently...doneReadingwithgetc()...doneReadingintelligently...doneSeekerSeeker2...1...Seeker3...startem...done...done...done...-------SequentialOutput-----------SequentialInput----Random---PerChar---Block----Rewrite---PerChar---Block-----Seeks---MachineMBK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPUK/sec%CPU/sec%CPUjump20464065295.510676856.17567390.94335699.431438199.817347.3160.5



Raid5这里的字符写速率到达了40652K/sec,较Raid1+0稍快,而Block体例写Raid1+0较Raid5稍快.

实践上EMC一向夸大其Raid5读写功能较Raid1+0相差无几.


对于update操作，event中依次记录旧行，新行的值。

原来公司用过MYSQL自己也只是建个表写个SQL

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

也可谈一下你是怎么优化存储过程的?

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

我个人认为就是孜孜不懈的学习

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

大家注意一点。如下面的例子：