(5.7)
innodb_fill_factor
※インデックスに対して有効。最小：10、最大：100、デフォルト：100
※グローバル設定のみ
select @@global.innodb_fill_factor;
drop table tab1;
create table tab1(col1 int not null primary key,col2 varchar(30) );
create index ind1 on tab1(col2);
drop procedure proc1;
delimiter //
create procedure proc1()
begin
declare i bigint;
set i = 1;
while i <= 1000000 do
insert into tab1 values(i,lpad(i,30,'0'));
set i = i + 1;
end while;
end
//
delimiter ;
call proc1();
select count(*) from tab1;
analyze table tab1;
-- サイズ確認
select
table_name
,floor( (data_length+index_length)/1024/1024) as allmb
,floor( (data_length)/1024/1024) as dmb
,floor( (index_length)/1024/1024) as imb
from information_schema.tables
where table_name ='tab1';
innodb_fill_factor= 100の場合→ | tab1 | 97 | 56 | 40 |
innodb_fill_factor= 10の場合→ | tab1 | 97 | 56 | 40 |
→なぜか相違なし、原因不明
-- 参照速度確認
pager cat /dev/null
select * from tab1 use index (ind1);
nopager
innodb_fill_factor= 100の場合→ 0.51 sec
innodb_fill_factor= 10の場合→ 0.52 sec
-- 更新速度確認
update tab1 set col2 = concat(substring(col2,1,29),'Y');
innodb_fill_factor= 100の場合→ 25.90 sec
innodb_fill_factor= 10の場合→ 27.80 sec
(19c)
PCTFREEの値は、0から99の値にする必要があります。
値に0を指定した場合は、ブロック全体が一杯になるまで新しい行を挿入できます。デフォルト値は10です。
※インデックスについても指定可能。最小：0、最大：99、デフォルト：10
drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int not null primary key,col2 varchar2(30) ) pctfree 0;
create table tab2(col1 int not null primary key,col2 varchar2(30) ) pctfree 90;
select table_name,pct_free,pct_used from user_tables where table_name in ('TAB1','TAB2');
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC');
insert into tab2 values(i,'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC');
commit;
end loop;
end;
/
select count(*) from tab1;
select count(*) from tab2;
exec dbms_stats.gather_table_stats('TEST','TAB1');
exec dbms_stats.gather_table_stats('TEST','TAB2');
-- サイズ確認
select sum(bytes/1024/1024) mb from user_segments where segment_name = 'TAB1';
select sum(bytes/1024/1024) mb from user_segments where segment_name = 'TAB2';
tab1→ 41MB
tab2→ 416MB
-- 参照速度確認
set time on
set timing on
set feed only
select * from tab1;
select * from tab2;
tab1→ 経過: 00:00:00.93
tab2→ 経過: 00:00:01.08
-- 更新速度確認
set time on
set timing on
set feed only
update tab1 set col2 = substr(col2,1,29)||'Y';
update tab2 set col2 = substr(col2,1,29)||'Y';
tab1→ 経過: 00:00:07.35
tab2→ 経過: 00:00:11.50
(13)
https://kyabatalian.hatenablog.com/entry/2017/12/16/160754
テーブルのフィルファクタ(fillfactor)は10から100までの間の割合（パーセント）です。 100（すべて使用）がデフォルトです
※インデックスについても指定可能。最小：10、最大：100、デフォルト：90
drop table tab1 cascade;
drop table tab2 cascade;
create table tab1(col1 int not null primary key,col2 varchar(30) ) with (fillfactor=100);
create table tab2(col1 int not null primary key,col2 varchar(30) ) with (fillfactor=10);
\d+ tab1
\d+ tab2
do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC');
insert into tab2 values(i,'AAAAAAAAAABBBBBBBBBBCCCCCCCCCC');
end loop;
end
$$ language plpgsql;
select count(*) from tab1;
select count(*) from tab2;
analyze tab1;
analyze tab1;
-- サイズ確認
select pg_size_pretty(pg_relation_size('tab1'));
select pg_size_pretty(pg_relation_size('tab2'));
tab1→ 65MB
tab2→ 710MB
-- 参照速度確認
\timing 1
explain analyze
select * from tab1;
explain analyze
select * from tab2;
tab1→ 108.293 ms
tab2→ 275.059 ms
-- 更新速度確認
\timing 1
explain analyze
update tab1 set col2 = substr(col2,1,29)||'Y';
explain analyze
update tab2 set col2 = substr(col2,1,29)||'Y';
tab1→ 7461.973 ms
tab2→ 4736.785 ms
(2019)
http://b00k.jp/2018/09/12/post-0-46/
https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-ver15
※インデックスのみ指定可能。最小：1、最大：100、デフォルト：0
※FILL FACTOR 値 0 と 100 の機能は、まったく同じです。
drop table tab1;
drop table tab2;
create table tab1(col1 int not null primary key,col2 varchar(30) );
create table tab2(col1 int not null primary key,col2 varchar(30) );
create index ind1 on tab1(col2) with( fillfactor = 100);
create index ind2 on tab2(col2) with( fillfactor = 10);
select name,fill_factor from sys.indexes where name in ('ind1','ind2');
declare @i int = 1;
while (@i <= 1000000)
begin
insert into tab1 values(@i,right('000000000000000000000000000000' + convert(varchar,@i), 30));
insert into tab2 values(@i,right('000000000000000000000000000000' + convert(varchar,@i), 30));
set @i = @i + 1;
end
select count(*) from tab1;
select count(*) from tab2;
update statistics tab1;
update statistics tab2;
-- サイズ確認
exec sp_spaceused 'dbo.tab1';
exec sp_spaceused 'dbo.tab2';
tab1→ index_size = 44240 KB
tab2→ index_size = 44240 KB
→なぜか相違なし、原因不明
-- 参照速度確認
set statistics time on
select * from tab1 with (index(ind1));
select * from tab2 with (index(ind2));
tab1→ 経過時間 = 経過時間 = 4540 ミリ秒
tab2→ 経過時間 = 経過時間 = 4534 ミリ秒
-- 更新速度確認
set statistics time on
update tab1 set col2 = substring(col2,1,29)+'Y';
update tab2 set col2 = substring(col2,1,29)+'Y';
tab1→ 経過時間 = 経過時間 = 35567 ミリ秒
tab2→ 経過時間 = 経過時間 = 37100 ミリ秒
