MySQL、MariaDB、PostgreSQLのオプティマイザ/エクゼキュータ比較用スクリプト群

PostgreSQL MySQL MariaDB

こちらの実験を手元で再試行できるように、スクリプト群と実験方法を示す。


ここで示した例以外にも、こちらでMariaDB vs MysSQLのオプティマイザ機能比較があるので、各自試してみるとよいと思う。
https://mariadb.com/kb/en/optimizer-switch/

スクリプトの準備

/tmp以下あたりに置く。

MySQL & MariaDB用のテーブルスキーマ
# cat /tmp/pre-mysql.sql
CREATE TABLE country (code int primary key, name text, continent text, INDEX (continent(10)), INDEX(name(10)));
CREATE TABLE city (code int primary key, country int, population int, INDEX (population));
CREATE TABLE hospital (code int primary key, num int);
CREATE TABLE school (code int primary key, num int);
PostgreSQL用のテーブルスキーマ
#cat /tmp/pre-pgsql.sql
CREATE TABLE country (code int, name text, continent text);
CREATE TABLE city (code int, country int, population int);
CREATE TABLE hospital (code int, num int);
CREATE TABLE school (code int, num int);

# cat /tmp/post-pgsql.sql
ALTER TABLE country ADD PRIMARY KEY (code);
ALTER TABLE city ADD PRIMARY KEY (code);
ALTER TABLE hospital ADD PRIMARY KEY (code);
ALTER TABLE school ADD PRIMARY KEY (code);
CREATE INDEX country_cont_idx ON country (continent);
CREATE INDEX country_name_idx ON country (name);
CREATE INDEX city_pop_idx ON city (population);
データ生成用スクリプト

スクリプト自体はデカイので、末尾に置く。

./dummy.pl > /tmp/dummy.sql

MySQL & MariaDBのセットアップ

MariaDB [(none)]>  CREATE DATABSE testdb;
Query OK, 1 row affected (0.23 sec)

MariaDB [(none)]> use testdb
Database changed

MariaDB [testdb]>  -- テーブル作成
MariaDB [testdb]> \. /tmp/pre-mysql.sql
Query OK, 0 rows affected (1.60 sec)

…

MariaDB [testdb]>  -- INSERTを高速に処理するため
MariaDB [testdb]> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [testdb]>  -- INSERTでデータ読み込み
MariaDB [testdb]> \. /tmp/dummy.sql
…..

PostgreSQLのセットアップ

postgresql.conf設定修正

データ読み込みを高速化するため。

sync = off
# pg_ctl -D /usr/local/pgsql/data restart
データベース定義

# createdb testdb

テーブル定義とデータインポート
testdb=# -- テーブル定義
testdb=# \i /tmp/pre-pgsql.sql
CREATE TABLE
…

testdb=# -- データ読み込み
testdb=# \i /tmp/dummy.sql


…

testdb=# -- インデックス定義
testdb=# \i /tmp/post-pgsql.sql
...
postgresql.conf設定直し

値を元に戻す。

sync = on
# pg_ctl -D /usr/local/pgsql/data restart
測定のやりかた

ターミナル内で"\timing"を実行すると、以降の実行時間を表示できる。

$ ./bin/psql  testdb
psql (9.3.1)
Type "help" for help.

testdb=# \timing
Timing is on.
testdb=# SELECT count(*) FROM city;
 count  
--------
 409337
(1 row)

Time: 187.046 ms
testdb=# SELECT count(*) FROM city;
 count  
--------
 409337
(1 row)

Time: 97.336 ms

データ生成スクリプト: dummy.pl

#!/usr/bin/perl

$country = [
    ["Afghanistan", "Asia"],["Albania", "Europe"],["Algeria", "Africa"],["Andorra", "Europe"],
    ["Angola", "Africa"],["Antigua", "North America"],["Argentina", "South America"],["Armenia", "Asia"],
    ["Australia", "Oceania"],["Austria", "Europe"],["Azerbaijan", "Asia"],["Bahamas", "North America"],
    ["Bahrain", "Asia"],["Bangladesh", "Asia"],["Barbados", "North America"],["Belarus", "Europe"],
    ["Belgium", "Europe"],["Belize", "North America"],["Benin", "Africa"],["Bhutan", "Asia"],
    ["Bolivia", "South America"],["Bosnia", "Europe"],["Botswana", "Africa"],["Brazil", "South America"],
    ["Brunei", "Asia"],["Bulgaria", "Europe"],["Burkina", "Africa"],["Burundi", "Africa"],
    ["Cambodia", "Asia"],["Cameroon", "Africa"],["Canada", "North America"],["Cape", "Africa"],
    ["Central", "Africa"],["Chad", "Africa"],["Chile", "South America"],["China", "Asia"],
    ["Colombia", "South America"],["Comoros", "Africa"],["Congo", "Africa"],["Costa rica", "North America"],
    ["Croatia", "Europe"],["Cuba", "North America"],["Cyprus", "Asia"],["Czech", "Europe"],
    ["Côte", "Africa"],["Democratic", "Africa"],["Denmark", "Europe"],["Djibouti", "Africa"],
    ["Dominica", "North America"],["East", "Asia"],["Ecuador", "South America"],["Egypt", "Africa"],
    ["Equatorial", "Africa"],["Eritrea", "Africa"],["Estonia", "Europe"],["Ethiopia", "Africa"],
    ["Fiji", "Oceania"],["Finland", "Europe"],["France", "Europe"],["Gabon", "Africa"],["Gambia", "Africa"],
    ["Georgia", "Asia"],["Germany", "Europe"],["Ghana", "Africa"],["Greece", "Europe"],["Grenada", "North America"],
    ["Guatemala", "North America"],["Guinea", "Africa"],["Guinea-Bissau", "Africa"],["Guyana", "South America"],
    ["Haiti", "North America"],["Honduras", "North America"],["Hungary", "Europe"],["Iceland", "Europe"],
    ["India", "Asia"],["Indonesia", "Asia"],["Iran", "Asia"],["Iraq", "Asia"],["Ireland", "Europe"],["Israel", "Asia"],
    ["Italy", "Europe"],["Jamaica", "North America"],["Japan", "Asia"],["Jordan", "Asia"],["Kazakhstan", "Asia"],
    ["Kenya", "Africa"],["Kiribati", "Oceania"],["Kuwait", "Asia"],["Kyrgyzstan", "Asia"],
    ["Laos", "Asia"],["Latvia", "Europe"],["Lebanon", "Asia"],["Lesotho", "Africa"],["Liberia", "Africa"],
    ["Libya", "Africa"],["Liechtenstein", "Europe"],["Lithuania", "Europe"],["Luxembourg", "Europe"],
    ["Macedonia", "Europe"],["Madagascar", "Africa"],["Malawi", "Africa"],["Malaysia", "Asia"],
    ["Maldives", "Asia"],["Mali", "Africa"],["Malta", "Europe"],["Marshall", "Oceania"],
    ["Mauritania", "Africa"],["Mauritius", "Africa"],["Mexico", "North America"],["Micronesia", "Oceania"],
    ["Moldova", "Europe"],["Monaco", "Europe"],["Mongolia", "Asia"],["Montenegro", "Europe"],
    ["Morocco", "Africa"],["Mozambique", "Africa"],["Myanmar", "Asia"],["Namibia", "Africa"],
    ["Nauru", "Oceania"],["Nepal", "Asia"],["Netherlands", "Europe"],["Nicaragua", "North America"],
    ["Niger", "Africa"],["Nigeria", "Africa"],["Norway", "Europe"],["Oman", "Asia"],["Pakistan", "Asia"],
    ["Palau", "Oceania"],["Palestine", "Asia"],["Panama", "North America"],["Papua", "Oceania"],
    ["Paraguay", "South America"],["Peru", "South America"],["Philippines", "Asia"],["Poland", "Europe"],
    ["Portugal", "Europe"],["Qatar", "Asia"],["Romania", "Europe"],["Russia", "Europe"],["Rwanda", "Africa"],
    ["Samoa", "Oceania"],["Saudi", "Asia"],["Senegal", "Africa"],["Serbia", "Europe"],["Seychelles", "Africa"],
    ["Sierra", "Africa"],["Singapore", "Asia"],["Slovakia", "Europe"],["Slovenia", "Europe"],["Solomon", "Oceania"],
    ["Somalia", "Africa"],["South Africa", "Africa"],["South", "Asia"],
    ["Spain", "Europe"],["Sri", "Asia"],["Sudan", "Africa"],["Suriname", "South America"],["Swaziland", "Africa"],
    ["Sweden", "Europe"],["Switzerland", "Europe"],["Syria", "Asia"],["Tajikistan", "Asia"],
    ["Tanzania", "Africa"],["Thailand", "Asia"],["Togo", "Africa"],["Tonga", "Oceania"],["Tunisia", "Africa"],
    ["Turkey", "Asia"],["Turkmenistan", "Asia"],["Tuvalu", "Oceania"],["Uganda", "Africa"],["Ukraine", "Europe"],
    ["United States", "North America"],["Uruguay", "South America"],["Uzbekistan", "Asia"],["Vanuatu", "Oceania"],
    ["Vatican", "Europe"],["Venezuela", "South America"],["Vietnam", "Asia"],["Yemen", "Asia"]
    ];

$total_city = 0;

for ($i=0; $i < 180; $i++) {
    printf ("INSERT INTO country VALUES (%d, \'%s\', \'%s\')\;\n", $i, $country->[$i]->[0], $country->[$i]->[1]);

    my($num_city) = 100 + 200 * (rand 10);

    for ($j = 0; $j < $num_city; $j++) {
    	my($population) = 4000 +  rand 1000 * rand 1000 * rand 100;
	my($num_h) = $population  * (1 + rand 10) / 1000000 + (1 + rand 20);
	my($num_s) = $population  * (1 + rand 10) / 1000000 + (1 + rand 20);

	printf("\tINSERT INTO city VALUES (%d, %d, %d)\;\n", $total_city, $i, $population);
	printf("\tINSERT INTO hospital VALUES (%d, %d)\;\n", $total_city, $num_h);
	printf("\tINSERT INTO school VALUES (%d, %d)\;\n", $total_city, $num_s);

	$total_city++;	
    }
}