mysql sort_area_size_sort_area_size参数的一些表现

我们来看看该sort_area_size参数对创建索引时排序的具体影响:

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

/* 测试使用版本10.2.0.4 */

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /s01/arch

Oldest online log sequence 27

Current log sequence 34

/* 为了不受影响我们采用非归档模式 */

SQL> conn maclean/maclean

Connected.

SQL> alter session set workarea_size_policy=MANUAL;

Session altered.

/* 只有使用手动PGA管理时sort_area_size等参数才生效 */

SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

/* 加大多块读参数帮助我们节约时间 */

SQL> alter session set "_sort_multiblock_read_count"=128;

Session altered.

/* 10g中sort_multiblock_read_count成为隐式参数，我们尝试手动固定它 */

SQL> set timing on;

SQL> alter session set events '10032 trace name context forever ,level 10';

Session altered.

Elapsed: 00:00:00.00

/* 在session级别设置10032事件，该事件帮助输出排序相关的统计信息*/

SQL> drop index ind_youyus;

alter session set sort_area_size=1048576;

alter session set sort_area_size=1048576;

/* 注意10g目前存在一个bug，sort_area_size需要手动设置2次方能生效，否则无效！ */

create index ind_youyus on youyus(t1,t2) nologging;

Index dropped.

Elapsed: 00:00:00.07

SQL>

Session altered.

Elapsed: 00:00:00.00

SQL>

Session altered.

Elapsed: 00:00:00.00

SQL>

Index created.

Elapsed: 00:00:35.70

/* 以下为对应创建索引排序的10032 trace * /

---- Sort Parameters ------------------------------

sort_area_size 1048576

sort_area_retained_size 1048576

sort_multiblock_read_count 29

max intermediate merge width 2

*** 2010-09-09 21:15:52.703

---- Sort Statistics ------------------------------

Initial runs 1

Input records 10000001

Output records 10000001

Disk blocks 1st pass 58690

Total disk blocks used 58692

Total number of comparisons performed 10104798

Comparisons performed by in-memory sort 10098798

Comparisons while searching for key in-memory 6000

Temp segments allocated 1

Extents allocated 459

Uses version 2 sort

Does not use asynchronous IO

---- Run Directory Statistics ----

Run directory block reads (buffer cache) 2

Block pins (for run directory) 1

Block repins (for run directory) 1

---- Direct Write Statistics -----

Write slot size 49152

Write slots used during in-memory sort 2

Number of direct writes 10011

Num blocks written (with direct write) 58690

Block pins (for sort records) 58690

Cached block repins (for sort records) 667

---- Direct Read Statistics ------

Size of read slots for output 524288

Number of read slots for output 2

Number of direct sync reads 58627

Number of blocks read synchronously 58690

---- End of Sort Statistics -----------------------

/* 可以看到这里实际的sort_multiblock_read_count是29，而非我们设置的128，而max intermediate merge width最大中间运行合并宽度为2；我们知道实际的SMRC有公式MIN(ROUND(SORT_AREA_SIZE/(2*2*BLOCK_SIZE))-1, _sort_multiblock_read_count)=MIN(32-1,128)=31，而此处的实际值为29； merge width = ROUND(SORT_AREA_SIZE/(2*SMRC*BLOCK_SIZE))-1= round( 1048576/(2*29*8k))-1= round(2.2)-1=2-1=1；看起来实际的公式有所修正。*/

SQL> drop index ind_youyus;

alter session set sort_area_size=524288000;

alter session set sort_area_size=524288000;

create index ind_youyus on youyus(t1,t2) nologging;

Index dropped.

Elapsed: 00:00:00.04

SQL>

Session altered.

Elapsed: 00:00:00.00

SQL>

Session altered.

Elapsed: 00:00:00.00

SQL>

Index created.

Elapsed: 00:00:36.82

---- Sort Parameters ------------------------------

sort_area_size 524288000

sort_area_retained_size 524288000

sort_multiblock_read_count 128

max intermediate merge width 225

*** 2010-09-09 21:32:06.517

---- Sort Statistics ------------------------------

Initial runs 2

Number of merges 1

Input records 10000001

Output records 10000001

Disk blocks 1st pass 58690

Total disk blocks used 58692

Total number of comparisons performed 17571986

Comparisons performed by in-memory sort 10098438

Comparisons performed during merge 7473532

Comparisons while searching for key in-memory 16

Temp segments allocated 1

Extents allocated 459

Uses version 2 sort

Does not use asynchronous IO

---- Run Directory Statistics ----

Run directory block reads (buffer cache) 3

Block pins (for run directory) 1

Block repins (for run directory) 2

---- Direct Write Statistics -----

Write slot size 1048576

Write slots used during in-memory sort 50

Number of direct writes 460

Num blocks written (with direct write) 58690

Block pins (for sort records) 58690

Cached block repins (for sort records) 1

---- Direct Read Statistics ------

Size of read slots for output 1048576

Number of read slots for output 500

Number of direct sync reads 58563

Number of blocks read synchronously 58690

---- End of Sort Statistics -----------------------

/* 10g中引入了新的排序算法， 排序初始化运行完成后，会保存初始数据集的键值到内存中，在进行数据集进行合并时，会根据键值来选择数据集。从trace文件中可以看到这样的统计信息:Comparisons while searching for key in-memory 16;*/

/* 可以看到write slot的大小也随sort_area_size变化，sort_area_size增大的同时Number of direct writes由10011次下降到460次,此外read slots的总大小(524288*2=1048576=sort_area_size，1048576*500=sort_area_size) */

/* 在合并merge阶段，因为读取缓存足够大，因此合并次数下降到1 */

to be continued ..............