![[kaneko lab.]](https://www.kkaneko.jp/info/logo_png.png){kind=logo}
リレーショナルデータベースでのテーブルのコピーと性能確認手順
テーブルのコピーとは：リレーショナルデータベースのテーブルを別のテーブルにコピーする．
処理時間の計測では，計測のばらつきを抑えるため，計測のたびにキャッシュのクリアを行い，3回の計測の平均をとる． 計測中のプロセッサ，ストレージの利用状況の確認は，dstat（システム計測ソフト）のページで説明している．
このページで紹介しているソフトウェア類の利用条件等は，利用者で確認すること．
前準備
- テスト用に使う CSV データの合成: 別ページ »で説明
SQLite 3 について: 別ページ »にまとめ
PostgreSQL のインストール
PostgreSQL の利用: 別ページ »にまとめ
- Windows での PostgreSQL のインストール: 別ページ »で説明
- Ubuntu での PostgreSQL のインストール: 別ページ »で説明
SQLite 3 でのテーブルのコピーの性能
テーブルのコピーのテスト実行（SQLite 3 を使用）
次のように設定して実行すること．
- 「T1000M_1.csv」のところは CSV ファイル名
- 「T1000M_1」のところはテーブル名
- 「T」のところはコピー先のテーブル名
エラーメッセージが出ないことを確認．
cat >import.sql<<EOF
.separator ,
pragma journal_mode=off;
.import T1000M_1.csv T1000M_1
.exit
EOF
cat >tablecopy.sql<<EOF
pragma journal_mode=off;
pragma locking_mode=exclusive;
begin transaction;
create table T as select * from T1000M_1;
commit;
.exit
EOF
echo -n > mydb
rm -f mydb
cat import.sql | time sqlite3 mydb
cat tablecopy.sql | time sqlite3 mydb
echo "select * from T limit 10;" | sqlite3 mydb
echo "select region, count(*) from T group by region;" | sqlite3 mydb
echo "select birth_cap from T where birth_cap > 80000 limit 10;" | sqlite3 mydb
テーブルのコピーでの，テーブルのサイズと処理時間の関係（SQLite 3 を使用）
テーブルのサイズを 250M バイトから 12000M バイトまで変えながら，別のデータベースファイルへのテーブルのコピーの処理時間を計測する． 計測のばらつきを抑えるため，計測のたびにキャッシュのクリアを行い，3回の計測の平均をとる．
#!/bin/bash
function elapsed() {
echo `python3 -c "import sys; print( float(sys.argv[2]) - float(sys.argv[1]) )" $1 $2`
return
}
function avg() {
echo `python3 -c 'import sys; print("%.3f" % ((float(sys.argv[1]) + float(sys.argv[2]) + float(sys.argv[3])) / 3.0) )' $1 $2 $3`
return
}
# キャッシュのクリア（PageCache, dentries and inodes）
function cache_clear() {
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
sleep 3
sudo sysctl -w vm.drop_caches=3 > /dev/null
echo 3 | sudo tee -a /proc/sys/vm/drop_caches > /dev/null
return
}
# $1: CSV file name, $2: Table name, $3: SQLite 3 Database name, $4: New table name
function sqlite3_tablecopy() {
echo -n > $3
rm -f $3
echo -n > DB.db
rm -f DB.db
rm -f sqlite3_import.sql
rm -f sqlite3_tablecopy.sql
cat >sqlite3_import.sql<<EOF
.separator ,
pragma journal_mode=off;
.import $1 $2
.exit
EOF
cat sqlite3_import.sql | sqlite3 $3 > /dev/null
cat >sqlite3_tablecopy.sql<<EOF
pragma journal_mode=off;
pragma locking_mode=exclusive;
attach database 'DB.db' as DB;
begin transaction;
create table DB.$4 as select * from $2;
commit;
.exit
EOF
echo "create table DB.$4 as select * from $2;"
start=`date +%s.%N`
cat sqlite3_tablecopy.sql | sqlite3 $3 > /dev/null
current=`date +%s.%N`
rm -f sqlite3_import.sql
rm -f sqlite3_tablecopy.sql
echo `elapsed $start $current`
return
}
# 250M
cache_clear
elapsed1=`sqlite3_tablecopy T250M_1.csv T250M_1 T250M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T250M_1.csv T250M_1 T250M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T250M_1.csv T250M_1 T250M_1.db T`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3`
# 500M
cache_clear
elapsed1=`sqlite3_tablecopy T500M_1.csv T500M_1 T500M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T500M_1.csv T500M_1 T500M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T500M_1.csv T500M_1 T500M_1.db T`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3`
# 1000M
cache_clear
elapsed1=`sqlite3_tablecopy T1000M_1.csv T1000M_1 T1000M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T1000M_1.csv T1000M_1 T1000M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T1000M_1.csv T1000M_1 T1000M_1.db T`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3`
# 2000M
cache_clear
elapsed1=`sqlite3_tablecopy T2000M_1.csv T2000M_1 T2000M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T2000M_1.csv T2000M_1 T2000M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T2000M_1.csv T2000M_1 T2000M_1.db T`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3`
# 4000M
cache_clear
elapsed1=`sqlite3_tablecopy T4000M_1.csv T4000M_1 T4000M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T4000M_1.csv T4000M_1 T4000M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T4000M_1.csv T4000M_1 T4000M_1.db T`
echo 4000M: `avg $elapsed1 $elapsed2 $elapsed3`
# 8000M
cache_clear
elapsed1=`sqlite3_tablecopy T8000M_1.csv T8000M_1 T8000M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T8000M_1.csv T8000M_1 T8000M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T8000M_1.csv T8000M_1 T8000M_1.db T`
echo 8000M: `avg $elapsed1 $elapsed2 $elapsed3`
# 12000M
cache_clear
elapsed1=`sqlite3_tablecopy T12000M_1.csv T12000M_1 T12000M_1.db T`
cache_clear
elapsed2=`sqlite3_tablecopy T12000M_1.csv T12000M_1 T12000M_1.db T`
cache_clear
elapsed3=`sqlite3_tablecopy T12000M_1.csv T12000M_1 T12000M_1.db T`
echo 12000M: `avg $elapsed1 $elapsed2 $elapsed3`
テーブルのコピーの並行処理（SQLite 3 を使用）
並行度を 1, 2, 4, 8, 16 のように変える．コピーするデータの総量は同じとし，性能の違いを見る．
- SQLite 3 は，1つのデータベースファイルへの同時書き込みをロックで直列化する．そこで，並行処理の効果を見るときは，プロセスごとに別のデータベースファイルを使う．
- コピー元の SQLite 3 データベース（T250M_1.db から T250M_16.db）は，リレーショナルデータベースへの CSV ファイルインポートと性能確認手順のページの手順で作成しておくこと．
- 並列実行の仕組み（TODOLIST と xargs による決まり文句）は，コマンドや bash 内の関数を並列実行（bash を使用）のページで説明している．
- ファイル名 tablecopypara.sh で保存し，「chmod 755 tablecopypara.sh」で実行可能にしたのち，「bash ./tablecopypara.sh」で実行する．
#!/bin/bash
function elapsed() {
echo `python3 -c "import sys; print( float(sys.argv[2]) - float(sys.argv[1]) )" $1 $2`
return
}
function avg() {
echo `python3 -c 'import sys; print("%.3f" % ((float(sys.argv[1]) + float(sys.argv[2]) + float(sys.argv[3])) / 3.0) )' $1 $2 $3`
return
}
# キャッシュのクリア（PageCache, dentries and inodes）
function cache_clear() {
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
/usr/bin/sync
sleep 3
sudo sysctl -w vm.drop_caches=3 > /dev/null
echo 3 | sudo tee -a /proc/sys/vm/drop_caches > /dev/null
return
}
# $1: コピー元の SQLite 3 データベース名, $2: コピー元のテーブル名, $3: コピー先の SQLite 3 データベース名
function sqlite3_tablecopy() {
echo -n > $3
rm -f $3
rm -f sqlite3_tablecopy.$$.sql
cat >sqlite3_tablecopy.$$.sql<<EOF
pragma journal_mode=off;
attach database '$3' as DST;
begin transaction;
create table DST.$2 as select * from $2;
commit;
.exit
EOF
echo "create table DST.$2 as select * from $2;"
start=`date +%s.%N`
cat sqlite3_tablecopy.$$.sql | sqlite3 $1 > /dev/null
current=`date +%s.%N`
rm -f sqlite3_tablecopy.$$.sql
echo `elapsed $start $current`
return
}
#########################################################################
# TODOLIST は，実行させたいコマンド，あるいは「taskほにゃらら ＜引数＞」
TODOLIST=$(cat <<EOD
sqlite3_tablecopy T250M_1.db T250M_1 C250M_1.db
sqlite3_tablecopy T250M_2.db T250M_2 C250M_2.db
sqlite3_tablecopy T250M_3.db T250M_3 C250M_3.db
sqlite3_tablecopy T250M_4.db T250M_4 C250M_4.db
sqlite3_tablecopy T250M_5.db T250M_5 C250M_5.db
sqlite3_tablecopy T250M_6.db T250M_6 C250M_6.db
sqlite3_tablecopy T250M_7.db T250M_7 C250M_7.db
sqlite3_tablecopy T250M_8.db T250M_8 C250M_8.db
sqlite3_tablecopy T250M_9.db T250M_9 C250M_9.db
sqlite3_tablecopy T250M_10.db T250M_10 C250M_10.db
sqlite3_tablecopy T250M_11.db T250M_11 C250M_11.db
sqlite3_tablecopy T250M_12.db T250M_12 C250M_12.db
sqlite3_tablecopy T250M_13.db T250M_13 C250M_13.db
sqlite3_tablecopy T250M_14.db T250M_14 C250M_14.db
sqlite3_tablecopy T250M_15.db T250M_15 C250M_15.db
sqlite3_tablecopy T250M_16.db T250M_16 C250M_16.db
EOD
)
#########################################################################
# TODOLIST の記載を，指定された並列度で実行する．
# この先は決まり文句（変更不要）
# 引数が１個のときは，行番号の指定である．TODOLIST のその行番号の１行を実行する．
if [ $# == 1 ]; then
# echo "$TODOLIST" のように「"」を付けると改行が保たれる
eval $(echo "$TODOLIST" | sed -n ${1}p)
exit
fi
# 引数なしで実行するとき，
# seq 1 $(echo "$TODOLIST" | wc -l) は，１から TODOLIST の行数までの整数を順に生成する．
# 使うときは chmod 755 で実行可能にしておくこと．
for i in 1 2 4 8 16; do
# 並列度
NUM_CONCURRENT=$i
#
cache_clear
start=`date +%s.%N`
seq 1 $(echo "$TODOLIST" | wc -l) | xargs -L 1 -P ${NUM_CONCURRENT} bash "$0" > /dev/null
current=`date +%s.%N`
elapsed1=`elapsed $start $current`
#
cache_clear
start=`date +%s.%N`
seq 1 $(echo "$TODOLIST" | wc -l) | xargs -L 1 -P ${NUM_CONCURRENT} bash "$0" > /dev/null
current=`date +%s.%N`
elapsed2=`elapsed $start $current`
#
cache_clear
start=`date +%s.%N`
seq 1 $(echo "$TODOLIST" | wc -l) | xargs -L 1 -P ${NUM_CONCURRENT} bash "$0" > /dev/null
current=`date +%s.%N`
elapsed3=`elapsed $start $current`
#
echo ${NUM_CONCURRENT} `avg $elapsed1 $elapsed2 $elapsed3`
done
PostgreSQL でのテーブルのコピー
次のように設定して実行すること．
- 「T1000M_1.csv」のところは CSV ファイル名
- 「T1000M_1」のところはテーブル名
- 「T1000M_1.sql」のところはテーブル定義（create table 文）の SQL ファイル名
- 「16」のところは，インストールされている PostgreSQL のバージョンに合わせること（Ubuntu 24.04 では 16）
エラーメッセージが出ないことを確認．
sudo service postgresql start
# clean up
echo "drop database testdb" | sudo -u postgres psql -U postgres
sudo service postgresql stop
sudo rm -rf /var/lib/postgresql/data
sudo mkdir /var/lib/postgresql/data
sudo chown -R postgres:postgres /var/lib/postgresql/data
sudo -u postgres /usr/lib/postgresql/16/bin/initdb --encoding='UTF-8' -D /var/lib/postgresql/data
#
sudo service postgresql start
echo "create database testdb owner postgres encoding 'UTF8';" | sudo -u postgres psql -U postgres
cat T1000M_1.sql | sudo -u postgres psql -U postgres -d testdb
echo "\copy T1000M_1 from 'T1000M_1.csv' with csv header;" | sudo -u postgres psql -U postgres -d testdb
# テーブルのコピー（処理時間も表示）
echo "\timing on
create table T as select * from T1000M_1;" | sudo -u postgres psql -U postgres -d testdb
echo "select region, count(*) from T group by region;" | sudo -u postgres psql -U postgres -d testdb
echo "select birth_cap from T where birth_cap > 80000 limit 10;" | sudo -u postgres psql -U postgres -d testdb
考察ポイント
- テーブルのサイズと処理時間はほぼ比例するか．比例から外れるとしたら，どのサイズからか（メモリ量との関係を考える）．
- CSV ファイルインポートとテーブルのコピーの処理時間を比べる．テーブルのコピーはストレージの読みと書きの両方が発生することを，dstat で確認する．
- 並行度を 1, 2, 4, 8, 16 と上げたとき，総処理時間はどう変わるか．改善が止まる（または悪化する）並行度はいくつか．ストレージ帯域との関係を考える．
- SQLite 3 と PostgreSQL とで，テーブルのコピーの処理時間に違いはあるか．