MySQL Sakila Sample Database
https://dev.mysql.com/doc/sakila/en/sakila-installation.html
https://dev.mysql.com/doc/index-other.html
---------
download , and tar -zxvf sakila-db.tar.gz
(root) [(none)]> source sakila-schema.sql;
(root) [sakila]> source sakila-data.sql;
(root) [sakila]> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila | Table_type |
+----------------------------+------------+
| actor | BASE TABLE |
| actor_info | VIEW |
| address | BASE TABLE |
| category | BASE TABLE |
| city | BASE TABLE |
| country | BASE TABLE |
| customer | BASE TABLE |
| customer_list | VIEW |
| film | BASE TABLE |
| film_actor | BASE TABLE |
| film_category | BASE TABLE |
| film_list | VIEW |
| film_text | BASE TABLE |
| inventory | BASE TABLE |
| language | BASE TABLE |
| nicer_but_slower_film_list | VIEW |
| payment | BASE TABLE |
| rental | BASE TABLE |
| sales_by_film_category | VIEW |
| sales_by_store | VIEW |
| staff | BASE TABLE |
| staff_list | VIEW |
| store | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)
(root) [sakila]> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
(root) [sakila]> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
| 1000 |
+----------+
------------------------
grant all privileges on sakila.* to 'miku'@'%';
flush privileges;
------
|=> mysql -u miku -p -D sakila
(miku) [sakila]> show FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila | Table_type |
+----------------------------+------------+
| actor | BASE TABLE |
| actor_info | VIEW |
| address | BASE TABLE |
| category | BASE TABLE |
| city | BASE TABLE |
| country | BASE TABLE |
| customer | BASE TABLE |
| customer_list | VIEW |
| film | BASE TABLE |
| film_actor | BASE TABLE |
| film_category | BASE TABLE |
| film_list | VIEW |
| film_text | BASE TABLE |
| inventory | BASE TABLE |
| language | BASE TABLE |
| nicer_but_slower_film_list | VIEW |
| payment | BASE TABLE |
| rental | BASE TABLE |
| sales_by_film_category | VIEW |
| sales_by_store | VIEW |
| staff | BASE TABLE |
| staff_list | VIEW |
| store | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.00 sec)
(miku) [sakila]>
-------------------
https://github.com/EnterpriseDB/mysql_fdw
--------
-- create role and database for MySQL Sakila Sample Database
-- create tablespace
cd /usr/local/var
mkdir sakila
-- tablespace path : /usr/local/var/sakila
-- login with superuser privilege
create tablespace sakila location '/usr/local/var/sakila';
create role sakila with login superuser password 'sakila';
commit;
select rolsuper
, rolpassword
from pg_authid
where rolname = 'sakila';
rolsuper | rolpassword
----------+-------------------------------------
t | md50317187bad27817ed0bcc145db708d33
create database sakila owner sakila tablespace sakila encoding UTF8;
\l+ sakila
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
--------+--------+----------+-------------+-------------+-------------------+---------+------------+-------------
sakila | sakila | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | | 7849 kB | sakila |
-----------
-- install mysql_fdw
unzip mysql_fdw-master.zip
cd mysql_fdw-master
export PATH=/usr/local/mysql/bin/:$PATH
make USE_PGXS=1
make USE_PGXS=1 install
---------
-- login as sakila
select current_schema;
current_schema
----------------
public
show search_path;
search_path
-----------------
"$user", public
create schema sakila;
create schema pakila;
commit;
alter database sakila set search_path="$user", public, pakila;
alter role sakila set search_path="$user", public, pakila;
commit;
-- quit and login again
show search_path;
search_path
-------------------------
"$user", public, pakila
select current_schema;
current_schema
----------------
sakila
-- create mysql_fdw extension
create extension mysql_fdw with schema public;
commit;
sakila[sakila]# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+--------------------------------------------------
mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
---------------
# in PostgreSQL
# 先建立一個 連線的 server , 取名為 mysql_server
create server mysql_server
foreign data wrapper mysql_fdw
options (host '127.0.0.1', port '3306');
commit;
# 建立一個 mapping user, for 現在登入的 pg user
# 密碼以及 mysql 的 user, 要記得置換.
create user mapping for sakila
server mysql_server
options (username 'miku', password 'YourPasswd');
commit;
-----------
-- import foreign schema
import foreign schema sakila
from server mysql_server
into pakila;
-- 因為 MySQL Sakila 有自定義資料型態或者新的 空間資料型態, 我們需要將這些table先排除掉.
-- 後面再手動補上. geometry 是 SRID , 先用 bit(32) 以利傳輸, 但是實際上使用時資料會都是 0.
-- view 我們也排除.
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-argument-handling.html
import foreign schema sakila
EXCEPT (address, film, film_list, nicer_but_slower_film_list)
from server mysql_server
into pakila;
commit;
\d
List of relations
Schema | Name | Type | Owner
--------+------------------------+---------------+--------
pakila | actor | foreign table | sakila
pakila | actor_info | foreign table | sakila
pakila | category | foreign table | sakila
pakila | city | foreign table | sakila
pakila | country | foreign table | sakila
pakila | customer | foreign table | sakila
pakila | customer_list | foreign table | sakila
pakila | film_actor | foreign table | sakila
pakila | film_category | foreign table | sakila
pakila | film_text | foreign table | sakila
pakila | inventory | foreign table | sakila
pakila | language | foreign table | sakila
pakila | payment | foreign table | sakila
pakila | rental | foreign table | sakila
pakila | sales_by_film_category | foreign table | sakila
pakila | sales_by_store | foreign table | sakila
pakila | staff | foreign table | sakila
pakila | staff_list | foreign table | sakila
pakila | store | foreign table | sakila
(19 rows)
---
CREATE TYPE rating_t AS enum('G','PG','PG-13','R','NC-17');
commit;
CREATE FOREIGN TABLE film (
film_id smallint NOT NULL,
title varchar(128) NOT NULL,
description text,
release_year smallint,
language_id smallint NOT NULL,
original_language_id smallint,
rental_duration smallint NOT NULL,
rental_rate decimal NOT NULL,
length smallint,
replacement_cost decimal NOT NULL,
rating rating_t,
special_features text,
last_update timestamp NOT NULL
) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'film');
commit;
select * from film limit 1;
-[ RECORD 1 ]--------+-------------------------------------------------------------------------------------------------
film_id | 1
title | ACADEMY DINOSAUR
description | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
release_year | 2006
language_id | 1
original_language_id | NULL
rental_duration | 6
rental_rate | 0.99
length | 86
replacement_cost | 20.99
rating | PG
special_features | Deleted Scenes,Behind the Scenes
last_update | 2006-02-15 05:03:42
CREATE FOREIGN TABLE address (
address_id smallint NOT NULL,
address varchar(50) NOT NULL,
address2 varchar(50),
district varchar(20) NOT NULL,
city_id smallint NOT NULL,
postal_code varchar(10),
phone varchar(20) NOT NULL,
location bit(32) NOT NULL,
last_update timestamp NOT NULL
) SERVER mysql_server OPTIONS (dbname 'sakila', table_name 'address');
commit;
select * from address limit 1;
-[ RECORD 1 ]--------------------
address_id | 1
address | 47 MySakila Drive
address2 | NULL
district | Alberta
city_id | 300
postal_code |
phone |
location | 0
last_update | 2014-09-25 22:30:27
\d
List of relations
Schema | Name | Type | Owner
--------+------------------------+---------------+--------
pakila | actor | foreign table | sakila
pakila | actor_info | foreign table | sakila
pakila | category | foreign table | sakila
pakila | city | foreign table | sakila
pakila | country | foreign table | sakila
pakila | customer | foreign table | sakila
pakila | customer_list | foreign table | sakila
pakila | film_actor | foreign table | sakila
pakila | film_category | foreign table | sakila
pakila | film_text | foreign table | sakila
pakila | inventory | foreign table | sakila
pakila | language | foreign table | sakila
pakila | payment | foreign table | sakila
pakila | rental | foreign table | sakila
pakila | sales_by_film_category | foreign table | sakila
pakila | sales_by_store | foreign table | sakila
pakila | staff | foreign table | sakila
pakila | staff_list | foreign table | sakila
pakila | store | foreign table | sakila
sakila | address | foreign table | sakila
sakila | film | foreign table | sakila
(21 rows)
-----------
-- pg_dump --include-foreign-data
pg_dump -U sakila -W -d sakila --include-foreign-data=mysql_server -f mysakila.sql
-- 會建立 FOREIGN SERVER ,以及各 table 的 schema, data.
-- 以下為部分
---------------
--
-- Name: country; Type: FOREIGN TABLE; Schema: pakila; Owner: sakila
--
CREATE FOREIGN TABLE pakila.country (
country_id smallint NOT NULL,
country character varying(50) NOT NULL,
last_update timestamp without time zone NOT NULL
)
SERVER mysql_server
OPTIONS (
dbname 'sakila',
table_name 'country'
);
ALTER FOREIGN TABLE pakila.country OWNER TO sakila;
---------------
-- 資料部分
--
-- Data for Name: actor; Type: TABLE DATA; Schema: pakila; Owner: sakila
--
COPY pakila.actor (actor_id, first_name, last_name, last_update) FROM stdin;
1 PENELOPE GUINESS 2006-02-15 04:34:33
2 NICK WAHLBERG 2006-02-15 04:34:33
3 ED CHASE 2006-02-15 04:34:33
4 JENNIFER DAVIS 2006-02-15 04:34:33
5 JOHNNY LOLLOBRIGIDA 2006-02-15 04:34:33
6 BETTE NICHOLSON 2006-02-15 04:34:33
7 GRACE MOSTEL 2006-02-15 04:34:33
8 MATTHEW JOHANSSON 2006-02-15 04:34:33
9 JOE SWANK 2006-02-15 04:34:33
10 CHRISTIAN GABLE 2006-02-15 04:34:33
11 ZERO CAGE 2006-02-15 04:34:33
12 KARL BERRY 2006-02-15 04:34:33
13 UMA WOOD 2006-02-15 04:34:33
14 VIVIEN BERGEN 2006-02-15 04:34:33
---------------
PostgreSQL 13 現在的功能更完整了!
請問大師,有沒有postgresql 使用GPU 加速的經驗可以分享?
PostgreSQL 使用 GPU 加速, 主要是 PG-Strom
https://heterodb.github.io/pg-strom/
這是日本的 海外 浩平 KaiGai Kohei開發的
https://kaigai.hatenablog.com/
目前有跟 Apache Arrow 整合, 還有開發了 pg2arrow, mysql2arrow.
PostgreSQL 要加速,除了使用 GPU, 還有其他方式.
我會再安排適當的場合介紹.