mysql服务i o一直在读_SQLSERVER I/O问题

99 --现在运行对这个表的查询,看看SQL需要读取多少个页面

100 SET STATISTICS IO ON

101 GO

102 SELECT * FROM dbo.demo

103 go

104 SET STATISTICS IO OFF

105 GO

106

107 --做一次索引重建,消除表上的碎片(defragment)

108 USE tempdb

109 ALTER INDEX demo_index ON dbo.demo

REBUILD

110 GO

111 --再运行同样的查询

112 SET STATISTICS IO ON

113 GO

114 SELECT * FROM dbo.demo

115 go

116 SET STATISTICS IO OFF

117 GO

118

119 --结果:重建索引前是127次,重建索引后是18次逻辑读

120

121

122

123

124 --4、表格上的索引结构

125 --聚集索引和没有聚集索引的差别，有聚集索引的时候，在存储同样多的数据时，明显管理得好一些

126

127

128

129

130 --5、数据压缩

131 --在SQL2008以后，只读文件组可以使用NTFS的压缩技术，以节省磁盘空间的消耗。

132 --代价是Windows需要花费额外的CPU与内存资源将数据解压缩，然后返回给SQL。

133 --所以如果CPU能力跟不上的话，对整体性能可能会造成负面影响

134

135

136

137 --6、数据文件和日志文件是否放在同一个硬盘上

138 --SQL对数据文件的读写不是时刻在进行的，总的来说，大部分的SQL读得多，写的少。

139 --对于日志文件，只要数据库发生修改，日志记录就需要不断地写入日志文件。如果写的

140 --动作不能及时完成，事务就不能提交，所以日志文件的写入性能对SQL的整体性能影响

141 --比较大。两类文件对SQL同等重要，但是要求各有不同

142

143 --通常建议：把数据文件和日志文件放在不同的硬盘上。如果有可能，日志文件放在写入速度

144 --比较快的磁盘上，例如，一个RAID10的分区。如果放在比较慢的RAID5分区上，就比较容易

145 --遇到I/O问题

146

147

148 --7、一个数据文件组是否有多个文件，并且放在不同的物理硬盘上

149 --对于数据文件，SQL会将新数据按照同一个文件组的每个文件剩余空间的大小，按比例

150 --写入所有有空余空间的文件里。所以如果这些文件分布在不同的物理硬盘上，那么I/O工作

151 --也会分布到不同的硬盘。这样对整体性能有帮助

152

153 --但是，对于日志文件，在一个时间点，SQL只会写一个日志文件。在不同的硬盘上创建多个日志

154 --文件对性能基本无帮助

155

156 --多个文件组，不同的硬盘

157 --一个文件组，多个数据文件，不同的硬盘

158 --日志文件不属于任何文件组

159

160 --总而言之，能够直接影响SQL做不做I/O配置是比较少的。但是DBA可以通过很多设定，间接地影响

161 --SQL，从而达到调整I/O的目的

162 --另外，和内存与CPU不同，SQL对硬盘的读写基本上是放手交给Windows管理的。SQL主要是调用不同

163 --的WindowsAPI，以完成各种读写需求。这方面，SQL跟一般的应用程序没什么差别

164

165

166

167

168 --系统级I/O问题判断

169 --从Windows层面分析，将主要依赖性能监视器里的各个计数器。Windows可以在一个物理硬盘上

170 --划分出若干个逻辑分区，赋给每个分区一个逻辑盘符，也可以将整个磁盘做单独一个分区使用

171 --对于分配在同一块物理磁盘的逻辑分区，其实他们的操作都共享磁盘的读写带宽，相当于在

172 --一块磁盘上工作。而Windows上的应用，只认逻辑盘符。逻辑盘在哪一块物理磁盘上，是否和

173 --其他逻辑盘共享同一块磁盘，应用程序是不知道的

174

175

176 --logicaldisk：记录的是按照逻辑盘符，每个逻辑盘的读写计数器。可以了解系统以及应用向不同的盘符

177 --发出的工作量各有多大

178

179

--physicaldisk：以物理磁盘为单位，如果某个物理磁盘是有RAID组成的磁盘阵列，那么Windows只会列出

180 --一整块物理磁盘。如果物理磁盘上有若干个逻辑分区，这些分区将以一组为单位出现。

181

--可以了解磁盘的响应速度。如果要分析磁盘性能问题，主要分析physicaldisk下的计数器，logicaldisk

182 --仅能起到参考作用

183

184 --常用磁盘计数器：

185

186 --%idle time

187 --%disk time

188 --%disk read time

189 --%disk wirte time

190 --avg.disk sec/read

191 --avg.disk sec/write

192 --avg.disk sec/transfer

193 --avg.disk bytes/transfer

194 --avg.disk queue length

195 --avg.disk read queue length

196 --avg.disk write queue length

197 --disk bytes/sec

198 --disk transfer/sec

199 --disk read/sec

200 --disk writes/sec

201 --disk read bytes/sec

202 --disk write bytes/sec

203 --current disk queue length

204

205 --关键计数器如下：

206

207 --%disk time

208 --在采样期间，磁盘处于读写状态的百分比。理论上，这个值应该小于100%，而且应该是%disk read

time和

209 --%disk wirte

time的和。但是由于算法限制，在磁盘很忙的时候，这个计数器往往会大于100%。所以这个

210 --计数器能够给DBA看到一个趋势线，但是值本身是不准确的

211

212

213 --%idle time

214 --在采样期间，磁盘处于空闲状态的百分比。相对于经常大于100%的%disk time，这个值却是准确的。当

215 --磁盘处于空闲状态时，他的值是100%。当磁盘在满载做操作时，他的值是0%。所以建议读者通过这个值

216 --，反推出%disk time的真实值

217

218 --disk bytes/sec

219 --每秒钟磁盘读写的数量，以bytes为单位。他是disk read bytes/sec和disk write

bytes/sec的和

220 --一个很保守的参考值是：

221 --好：20MB~40MB

222 --一般：10MB~20MB

223

224

225 --avg.disk sec/transfer

226 --磁盘每一次读写的动作所花的平均时间

227

228

229 --avg.disk sec/read

230 --磁盘每一次读所花的平均时间

231

232

233

234 --avg.disk sec/write

235 --磁盘每一次写所花的平均时间

236

237 --这三个值经常用来衡量磁盘速度：参考值:

238 --很好：<10MS

239 --一般：10~20MS

240 --有点慢：20~50MS

241 --非常慢：>50MS

242

243

244

245

246 --avg.disk queue length

247 --在某个时间点，磁盘队列的长度，也就是发出的磁盘操作正在等待被磁盘处理的请求数目。

248 --例如有一个应用发出一条读请求，但是目标磁盘当时正在处理其他任务。那么这个新的读

249 --请求就会被放在磁盘队列里。这时候磁盘队列的值就是1。理论上讲，这个值不应该长时间地

250 --大于2

251

252 --分析者要综合看这些性能计数器不能从某一个计数器的值确定磁盘有没有瓶颈

253

254

255

256 --看计数器的值，要看磁盘繁忙的那些时间段，而不是整体时间的平均值。整体时间的平均值

257 --基本上没什么意义

258

259 --案例：7.1

260 --当SQL在做checkpoint的时候，disk queue是很长的，最长值在40以上。这时候，disk

bytes/sec

261 --的最大值基本是在7MB，而avg.disk

sec/write却经常超过50ms。所以这块硬盘的吞吐量是比较低的

262 --只要用户一直在修改数据库，就需要不停写日志，这个工作量无法减少，要解决问题，一定要解决

263 --磁盘的写入速度问题

264

265

266

267

268 --833错误

269 --案例：SQL已经向磁盘发出读写请求，但是该请求返回所用时间已超过15秒。就是说，某个磁盘请求读写

270 --过了15秒还没做完

271

272 --SQL对日志文件的操作一般都是小量多次的，不会有超大量的I/O请求。现在居然对日志文件的某个操作

273 --都花了15秒没有返回，可见磁盘的响应速度一定出了大问题

274

275

276 --I/O问题的SQL内部分析

277 --(1)从sys.sysprocesses或者sys.dm_os_wait_stats的waittype字段的值

278 --SQL先从内存里的buffer pool里找页面，如果没找到设置等待状态如下：

279

280 --等待状态：pageiolatch_ex(写)或pageiolatch_sh(读)

281

282 --然后发起一个异步I/O，将页面读入buffer pool。在I/O没做完之前，连接都会保持在

283

--pageiolatch_ex(写)或pageiolatch_sh(读)，I/O消耗的时间越长，等待的时间也会越长

284

285

286

287 --查看所有的等待累计值：

288 SELECT wait_type,

289 waiting_tasks_count,

290 wait_time_ms

291 FROM sys.dm_os_wait_stats

292 WHERE wait_type LIKE'pageiolatch%'

293 ORDER BY wait_type

294

295 --各个字段定义分别如下：

296 --wait_type:等待类型的名称

297 --waiting_tasks_count:该等待类型的等待数。该计数器在每开始一个等待时便会增加

298 --wait_time_ms:该等待类型的总等待时间(毫秒)。该时间包括single_wait_time_ms

299 --max_wait_time_ms:该等待类型的最长等待时间

300 --single_wait_time_ms:正在等待的线程从收到信号通知到其开始运行时间之间的时差

301

302

303

--所以当pageiolatch_ex(写)或pageiolatch_sh(读)的waiting_tasks_count和wait_time_ms比较高

304 --的时候，DBA就可以大致估计，SQL经常要等待I/O，可能磁盘速度跟不上SQL的需要

305

306 --pageiolatch_ex(写)或pageiolatch_sh(读)针对数据文件的I/O等待

307

308 --日志文件的I/O等待是 writelog ,当SQL要写日志文件而磁盘来不及完成时，SQL不得不进入等待状态

309 --直到日志记录被写入，才能提交当前事务。

310 --一般一次日志写入数量不会很多，磁盘应当能够按时完成，如果SQL经常要等writelog，说明磁盘的瓶颈

311 --还是比较严重的

312

313 SELECT wait_type,

314 waiting_tasks_count,

315 wait_time_ms

316 FROM sys.dm_os_wait_stats

317 WHERE wait_type LIKE'writelog'

318 ORDER BY wait_type

319

320

321

322

323 --(2)DBA要搞清楚是哪个数据库哪个文件老做I/O,是数据文件还是日志文件,是读还是写

324 --动态管理函数

325

--sys.dm_io_virtual_file_stats({database_id|null},{file_id|null})

326 --如果参数都是null，就返回所有文件的统计信息

327 --这个动态管理函数是SQL自上次启动以来的历史统计信息

328

329 --字段含义：

330 --database_id:数据库ID

331 --file_id:文件的ID

332 --sample_ms:自从计算机启动以来的毫秒数.可以此列来比较该函数的不同输出

333 --num_of_reads:对文件发出的读取次数

334 --num_of_bytes_read:在此文件中读取的总字节数

335 --io_stall_read_ms:用户等待文件中发出读取所用的总时间

336 --num_of_writes:在该文件中写入的次数

337 --num_of_bytes_written:写入文件的总字节数

338 --io_stall_write_ms:用户等待在该文件中完成写入所用的总时间

339 --io_stall:用户等待在文件中完成I/O操作所用的总时间

340

--size_on_disk_bytes:该文件在磁盘上占用的字节数,对于稀疏文件,此数字是数据库快照在磁盘上所占用的实际字节数

341 --file_handle:用于此文件的Windows文件句柄

342

343

--可以通过下面的查询了解哪个文件经常要做读(num_of_reads/num_of_bytes_read)，经常要做写

344

--(num_of_writes/num_of_bytes_written)，经常读写要等待(io_stall_write_ms/io_stall_read_ms/io_stall)

345

346 SELECT

347 db.name AS databasename,

348 f.fileid AS fileid,

349 f.filename AS FILENAME,

350 i.num_of_reads AS numofread,

351 i.num_of_bytes_read AS numofbyteread,

352 i.io_stall_read_ms AS ioinstallread,

353 i.num_of_writes AS numofwrite,

354 i.num_of_bytes_written AS numofbytewrite,

355 i.io_stall_write_ms AS iostallwrite,

356 i.io_stall AS iostall,

357 i.size_on_disk_bytes AS sizeondiskbyte

358 from sys.databases db INNER JOIN

359 sys.sysaltfiles f ON db.database_id=f.dbid

360 INNER JOIN sys.dm_io_virtual_file_stats(NULL,null) i

361 ON i.database_id=f.dbid AND i.file_id=f.fileid

362

363

----------------------------------------------------------------------------------------------------

364 SELECT

365 db.name AS databasename,

366 f.fileid AS fileid,

367 f.filename AS FILENAME,

368 i.num_of_reads AS numofread,

369 i.num_of_bytes_read AS numofbyteread,

370 i.io_stall_read_ms AS ioinstallread,

371 i.num_of_writes AS numofwrite,

372 i.num_of_bytes_written AS numofbytewrite,

373 i.io_stall_write_ms AS iostallwrite,

374 i.io_stall AS iostall,

375 i.size_on_disk_bytes AS sizeondiskbyte

376 from sys.databases db INNER JOIN

377 sys.sysaltfiles f ON db.database_id=f.dbid

378 INNER JOIN sys.dm_io_virtual_file_stats(DB_ID('gposdb'),null)

i

379 ON i.database_id=f.dbid AND i.file_id=f.fileid

380

381

382 --动态管理视图sys.dm_io_pending_io_requests

383 --当前SQL中每个处于挂起状态的I/O请求

384 SELECT

385 database_id AS dbid,

386 file_id AS fileid,

387 io_stall AS iostall,

388 io_pending_ms_ticks AS iopendingmstick,

389 scheduler_address AS scheduleraddress

390 from sys.dm_io_virtual_file_stats(NULL,null) as t1,

391 sys.dm_io_pending_io_requests AS t2

392 WHERE t1.file_handle=t2.io_handle

393

394

395 --(3)性能监视器

396 --相关计数器：

397 --buffer

manager---------------------------------------------------------------

398 --下面的计数器能够反映和buffer pool有关的I/O动作

399

400 --page reads/sec和page writes/sec

401 --反映SQL每秒钟读写了多少页面。可以清楚了解由于buffer pool的行为带来了多少磁盘读写

402

403 --lazy writes/sec

404 --反映lazy writer 为了清空buffer pool每秒钟做了多少页面写入动作

405

406 --checkpoint writes/sec

407 --每秒钟从buffer pool里写入到磁盘上的dirty page数目

408

409 --readahead pages/sec

410 --每秒钟SQL做的预读read ahead数目

411

412

413 --access

methods-----------------------------------------------------------------------------------

414 --除了读取和写入指令需要访问的页面，SQL还要做一些辅助工作，帮助指令完成，这些工作也会带来I/O动作

415

416 --freespace scans/sec

417 --在堆heap 结构里找到能够使用的空间。对没有聚集索引的表，SQL会以堆的形式存储。如果这个计数器很高

418 --说明SQL在堆的管理上花费很多资源，应该考虑多建聚集索引

419

420 --page splits/sec

421 --当表上有很多插入动作时，一些页面会被放满。为了维护索引上的顺序，SQL需要把一页劈成两页，这个动作叫

422 --“page split”。当数据库的修改比较多，尤其是插入比较多时，page

split是难免的。如果这个值比较高，而你

423 --又觉得他的确对性能有影响，可以考虑定期重建索引，使用比较小的填充值(fill factor)

424

425 --page allocations/sec

426 --当SQL需要创建对象，例如：表，索引时，分配给新对象的页面数量

427

428 --workfiles/sec

429 --当SQL为了完成某些操作而在内存中建立一个hash结构时(例如用hash算法作join)

430 --该计数器就加一。如果某些hash结构比较大，SQL可能会将一部分数据写到硬盘里。

431 --所以这个值其实并不直接意味着磁盘读写。但是由于SQL通常只是在没有合适索引的时候才选择hash算法

432 --所以DBA可以通过这个值了解数据库索引是不是有优化的必要。很多情况下索引优化能够大大降低SQL的读

433 --数量。做I/O问题时，这个计数器有必要看一下

434

435 --worktables/sec

436 --每秒创建的工作表数。例如，工作表可用于存储查询假脱机(query

spool)，LOB变量，XML变量，表变量，

437 --游标的临时结果

438

439 --full scans/sec

440 --每秒钟SQL做的全表扫描数目。如果一个表设计良好，就应该没有很多的全表扫描。而全表扫描通常意味着

441 --比较大的内存使用和比较多的I/O请求。所以在一个SQL系统，尤其是一个OLTP系统，这个值越小越好

442

443 --index searches/sec

444 --每秒钟检索索引的次数，也就是利用索引完成指令的数目

445

446 --database(log

activity)--------------------------------------------------------------------------

447 --在这个计数器下，我们能看到一些和日志写入有关的计数器

448

449 --log flushes/sec

450 --SQL每秒钟在这个数据库上做的日志写的次数

451

452 --log bytes flushed/sec

453 --SQL每秒钟在这个数据库上做的日志写的数量(bytes)

454

455 --log flush wait time

456 --写入日志的动作曾经因为磁盘来不及响应而遇到的等待时间。这种等待会导致前端事务不能提交，所以会

457 --严重影响SQL的性能。正常情况下，这个值大多数时间都是0

458

459 --log flush waits/sec

460 --在每秒钟提交的事务里，有多深个事务曾经等待过日志写入完成。正常情况下，不应该有等待，如果有等待

461 --检查存放日志文件的磁盘性能

462

463 --两个计数器，粗略分析SQL的整体性能

464 --MSSQL:SQL Statistics -Batch Request/sec :SQL每秒钟完成的批处理数目

465 --MSSQL:Databases Active Transactions:SQL里打开的，还没有提交的事务数目

466

467

468

469

-------------硬盘压力测试------------------------------------------------------------------------------------

470 --SAN(storage area network),raid这样的存储技术在SQL服务器上已经被广泛使用

471

472 --关于测试

473

--1、不同类型的磁盘读写操作，每次读写transfer的数据量会不一样。而对于磁盘来讲，每秒钟做的读写量不但跟完成

474 --的读写次数有关，还和每次读写的数据量有很大关系。SQL不同的操作，会有不同的读写量。像index

seek这样的

475 --操作，可能以8KB为单位比较多；像read ahead,table

scan这样的动作，可能64KB甚至更大的单位更多。

476 --所以管理员要测试磁盘在不同的transfer size情况下，他的表现是不是都很好

477

478 --2、有些磁盘的读写速度并不一样。例如：raid5，他的写会比读要慢。所以读和写的速度都要测试到

479

480 --3、现在的SAN和其他物理存储设备会有很大的读写缓存，类似于服务器上的内存。如果每次读写的量比较小

481 --读写缓存就可以处理了，可能不会发生真正到存储介质上的读写。这样的速度，会比真正读写速度快很多。

482 --所以在测试时，一定要保证读写数量是读写缓存大小的2到4倍

483

484 --4、SQL的不同数据文件，可以放在不同的磁盘上。SQL的读写工作可以由若干个磁盘共同完成。整体的速度

485 --会有所提升。但是提升多少呢？做硬盘压力测试的时候，要先测试单个磁盘的速度，再测多个磁盘共同工作

486 --时的速度

487

488 --5、有些磁盘如果工作压力一直比较大，会调整他的行为来适应。所以压力测试一定要持续一段时间。对于一个

489 --初步测试，5到10分钟的满负荷运作是至少的

490

491

492

--SQLIO工具------------------------------------------------------------------------------------------

493 --虽然叫SQLIO，但是这个工具跟SQL没有什么直接关系，可以在任何一台SQL的机器上运行

494 --功能：

495 --能够指定压力测试是读还是写

496 --指定每次是连续的读/写，还是随机的读/写

497 --指定每次读/写的大小

498 --指定每个测试的时间长短

499 --指定读/写的磁盘位置，路径和文件大小。支持多个磁盘同时读/写测试

500

501 --两个文件：

502 --param.txt

503 --sqlio.exe

504 --运行参数：

505 -- -o:测试文件在磁盘上产生的队列长度disk queue length

506 -- -LS:记录磁盘反应时间，这是一个推荐使用的选项

507 -- -k:指定是读还是写(R\W)

508 -- -s:测试持续多少秒，一般至少5到10分钟

509 -- -b:每次I/O请求的大小

510 -- -f:I/O的种类，是随机random 还是连续 sequential

511 -- -F:参数文件的名字，默认就是param.txt