Read from .xlsx in PowerShell (x64) without using Excel

This article shows how to read .xlsx in PowerShell without Excel. In Windows 10 (x64), you will have to do additional work.

• You have to install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe) when you use Windows PowerShell (x64). You will have to install AccessDatabaseEngine.exe when you use Windows PowerShell (x86).
• You have to use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.Jet.OLEDB.4.0.
• You have to use Extended Properties=Excel 12.0 instead of Extended Properties=Excel 8.0.
• ADO can handle unicode characters in .xlsx files. Note that PowerShell ISE, cmd.exe and powershell.exe has some limitations:
  • The console in PowerShell ISE cannot display the characters not included in the default locale.
  • The terminal window for cmd.exe and powershell.exe cannot display the surrogate pairs.

$fileName = "C:\tmp\UnicodeTest.xlsx"
$sheetName = "sheet1$"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$query = "Select * from [$sheetName]"

$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($query)
$sqlCommand.Connection = $conn
$conn.open()
$dataReader = $sqlCommand.ExecuteReader()

$list = New-Object System.Collections.ArrayList
While ($dataReader.read())
{
    $list.Add($dataReader[1].ToString()) > $null
}
$list | % { $_ } | Out-File -Encoding UTF8 c:\tmp\UnicodeTest.txt

$dataReader.close()
$conn.close()

Launch PowerShell process in non-default culture (locale)

In PowerShell on Windows 10, you cannot change the current culture (locale) with chcp command.

If you want to launch a new PowerShell process in another culture, once run cmd.exe from PowerShell, execute chcp, and launch powershell.exe from cmd.exe.

This is useful when you want to get an error message in en-US culture to ask questions in forums like Stack Overflow.

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
ja-JP
PS C:\> cmd.exe
Microsoft Windows [Version 10.0.18362.836]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\>chcp 437
Active code page: 437

C:\>powershell.exe
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\> [Threading.Thread]::CurrentThread.CurrentUICulture.Name
en-US
PS C:\>

pgbenchでINSERT中心のトランザクションのTPSをできるだけ高くする

動機

DBのチューニング方法なんかの書籍を見てみても、性能向上方法として語られているのはSELECTのチューニングが主で、INSERTに関する記載はあまり見当たりませんでした。ですが、大量のデバイスから飛んできたデータをMQTTやらHTTPやらで受けて、それを生のままDBMSに登録する……というワークロードの場合、INSERTがトランザクションの中心になるので、DBMSのベンチマークもINSERT中心のワークロードにする必要があります。

DBMSがPostgreSQLの場合、性能単価の測定にはpgbenchを使うと思いますが、ここで設定を誤っていたりすると、PostgreSQLの実力値を見誤ったりします（結果、DBサーバにオーバースペックなマシンを用意してしまったりする）。ハードウェアのリソース設計はそれはそれで行うとして、ここでは主にpgbenchによる性能単価測定の際にTPSをできるだけ高くする方法（＝pgbenchの設定がマズくてPostgreSQLの性能を過少に見積らない方法）をまとめておきます。

前提とするワークロード

• テーブルはpgbench組込みのトランザクションで使われるpgbench_historyを使います。
• 1回のトランザクションで8レコードをINSERTします。
• INSERT以外のトランザクションは実行しません（無風測定です）。
• 同時接続クライアント数は20、実行時間20秒で確認しました。
• ここではオプションの有効性の確認を目的として、pgbenchとPostgreSQLを同じマシンで実行しています。正確なTPSを知りたい場合はpgbenchはPostgreSQLとは別マシンで実行してください（後述）。
• OSはWindows 10です。
• CPUはIntel(R) Core(TM) i7-8750H CPU @ 2.20GHz（6コア12スレッド）、ストレージはADATA SU800NS38を使っています。

何もしない場合

以下のようなスクリプトファイルを作成して実行します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP);
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

pgbenchの実行オプションは以下の通りです。

pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert.pgbench test

この場合のTPSは3863TPSでした。

transaction type: C:\tmp\insert.pgbench
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
duration: 20 s
number of transactions actually processed: 77323
latency average = 5.177 ms
tps = 3863.290450 (including connections establishing)
tps = 3873.891699 (excluding connections establishing)

いろいろ対策をほどこす

CPUの割り当てコア数を調整する（プロセッサアフィニティを設定している場合・DBと同じマシンでpgbenchを実行する場合）

PostgreSQLインスタンスに対するCPUの割り当てに応じて、TPSが高くなります。プロセッサアフィニティを設定している場合、PostgreSQLに割り当てるCPUコアを増やします。ただし、DBと同じマシンでpgbenchを実行する場合、すべてのCPUをPostgreSQLに割り当てると逆にTPSが下がるようです（pgbenchに割り当てるCPUがなくなるせい？）。そのためCPUアフィニティを調整して、いちばん高い性能が出るポイントを探ります。

Windowsの場合、サービスプロセスにはプロセッサアフィニティは設定できないので、PostgreSQLサービスは停止し、ユーザプロセスとしてPostgreSQLインスタンスを実行します。以下のようなPowerShellスクリプトを作成し、割り当てるCPUのスレッド数を1～12まで変化させて実行しました。

@('1','3','7','F','1F','3F','7F','FF','1FF','3FF','7FF','FFF') | ForEach-Object {
  Write-Host "`nCPU Affinity:" $_
  cmd.exe /c "start /affinity $_ /B c:\opt\PostgreSQL\12\bin\pg_ctl.exe start -w -s -D C:\opt\PostgreSQL\12\data"
  $env:PGPASSWORD='postgres'
  c:\opt\PostgreSQL\12\bin\pgbench.exe -U postgres -c 20 -T 20 -f C:\tmp\insert2.pgbench test
  c:\opt\PostgreSQL\12\bin\pg_ctl.exe stop -D "C:\opt\PostgreSQL\12\data"
}

結果は以下のグラフのようになりました。以下、設定を変えながら上記のスクリプトを実行していきます。 何もしない場合、いちばん性能がよいのは全6コア・12スレッドを使った場合で、3863TPSでした。

設定ごとのpgbench TPS数

バルクインサート

スクリプトファイルを修正し、レコードごとにINSERTを実行していたところを、バルクインサートを行うよう修正します。

\set nbranches 1 * :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\set aid random(1,:naccounts)
\set bid random(1,:nbranches)
\set tid random(1,:ntellers)
\set delta random(-5000,5000)
BEGIN;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+1, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+2, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+3, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+4, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+5, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+6, CURRENT_TIMESTAMP),
                                                                 (:tid, :bid, :aid, :delta+7, CURRENT_TIMESTAMP);
END;

PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、9375TPSになりました。

pgbenchのオプションに--protocol=preparedを指定

pgbenchとPostgreSQLの間の通信プロトコルは、デフォルトでは簡易問い合わせプロトコルになっています。ここで--protocol=extendedを設定すると拡張問い合わせプロトコルが、--protocol=preparedを指定するとプリペアドステートメントを伴う拡張問い合わせプロトコルが使用されます。ここではもっとも性能が高くなる--protocol=preparedを指定します。

バルクインサートと合わせて指定した場合、PostgreSQLに3コア・6スレッドを使った場合が最も性能が高く、11823TPSになりました。

postgresql.confでsynchronous_commit = offを指定

postgresql.confでsynchronous_commit = offを指定することで、DBがクラッシュした際に直近のコミット分のデータがロストするのと引き換えに、PostgreSQLの性能を上げることができます（データ破壊のリスクはありません）。

バルクインサート・--protocol=preparedと合わせて指定した場合、PostgreSQLに4コア・7スレッドを使った場合が最も性能が高く、13349TPSになりました。

pgbenchのオプションに-j nを指定

pgbenchはデフォルトではシングルスレッドで動作します。-jオプションで同時実行スレッド数を変更できます。 pgbenchのスレッド数を変更しながら試した結果、pgbenchに4スレッド、PostgreSQLに4コア・8スレッドを割り当てた場合が最も性能が高く、24007TPSになりました。

pgbenchをDBと別マシン（または別のCPUコア）で実行する

pgbenchでは10000TPS程度で1CPUを1スレッド使いきってしまうようです（パフォーマンスモニタでpgbenchのProcessor %が100程度になる）。上記の通りINSERTの性能はCPU-boundなようです。pgbenchがCPUを食うとそれだけベンチマークの結果に影響するので、（今回は行えませんでしたが）pgbenchはDBとは別マシン（または別のCPUコア）で実行した方がよいTPSが出るようです。上記のグラフが8スレッド以降で伸びていないのは、pgbenchの実行にまわせるCPU数が少なくなってしまっているためと思われます。

常用漢字表を新旧漢字変換の根拠資料に使う場合の制約

文化庁の出している常用漢字表 *1 には康煕字典体と常用漢字の対応が記載されている。ただ詳しく調べてみたら、対応の記載自体に以下のようなコーナーケースがあるみたい。

• 「著しい差異のないものは省」かれているので「頬⇄頰」のような対応は記載がない。
• 康熙字典体と常用漢字が一対一対応しない場合に、記載のないケースが見受けられる。たとえば「辺⇄邊」はあっても「辺⇄邉」はない、「闘⇄鬭」はあっても「闘⇄鬪」はない。
• 当用漢字表と常用漢字表で字体が変わったものは記載がない。たとえば「礼⇄禮」はあっても「礼⇄礼」はない。

ほかにも、出典が常用漢字表なので、新字が常用漢字でないものは記載がない。たとえば「鴈⇄雁」はないし、「禎⇄禎」もない *2 。 新字・旧字の変換に使おうと思ったら、常用漢字表自体を全数として処理する場合以外は注意が必要そう。

*1:https://www.bunka.go.jp/kokugo_nihongo/sisaku/joho/joho/kijun/naikaku/pdf/joyokanjihyo_20101130.pdf

*2:この手の漢字は安岡孝一「人名用漢字の新字旧字」（ https://dictionary.sanseido-publ.co.jp/columncat/%e6%bc%a2%e5%ad%97/%e4%ba%ba%e5%90%8d%e7%94%a8%e6%bc%a2%e5%ad%97%e3%81%ae%e6%96%b0%e5%ad%97%e6%97%a7%e5%ad%97 ）にたくさん例がある。

You cannot set CPU affinity of PostgreSQL instance started through pg_ctl with Start-Process and Process.ProcessorAffinity

Background:

In Windows, you can set the CPU affinity of an user process with Start-Process PowerShell cmdlet and System.Diagnostics.Process.ProcessorAffinity property like this:

$app = Start-Process -FilePath C:\Windows\System32\mspaint.exe -PassThru
$app.ProcessorAffinity = 0x3

Problem:

You cannot set CPU affinity of an PostgreSQL instance with Start-Process cmdlet and System.Diagnostics.Process.ProcessorAffinity property, when you start the instance through pg_ctl.

You will get SetValueInvocationException like this:

$app = Start-Process 'c:\path\to\PostgreSQL\12\bin\pg_ctl.exe' 'start -D C:\path\to\PostgreSQL\12\data' -PassThru -NoNewWindow
$app.ProcessorAffinity = 0x3

"ProcessorAffinity" の設定中に例外が発生しました: "プロセス (15860) が終了したため、要求を処理できません。"
発生場所 行:1 文字:1
+ $app.ProcessorAffinity = 0x3
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting

Cause

In PowerShell, it seems there are no ways to set CPU affinity of an user process when it starts. ProcessorAffinity property sets CPU affinity of an user process through Process object retrieved before it accessed.

In PostgreSQL, pg_ctl command exits immediately after it starts an PostgreSQL instance. If the process is exited before you set ProcessorAffinity, you cannot set CPU affinity.

Solution

You have to use start /affinity command in cmd.exe instead of Start-Process. For example, you can start PostgreSQL instance with CPU affinity from PowerShell like this:

cmd.exe /c "start /affinity 3 /B c:\path\to\PostgreSQL\12\bin\pg_ctl.exe start -w -s -D C:\path\to\PostgreSQL\12\data"