Home » Computer » Software » Database » Archive by category "PostgreSQL" (Page 3)

A Billion Taxi Rides 資料分析系列

Mark Litwintschik 最近在連載 A Billion Taxi Rides 的資料分析系列作品:

同樣的資料 (而且這個資料量夠大,拿來 benchmark 比較有參考價值),用不同的工具分析,對於要挑工具的人可以看一看,另外也因為裡面給了很多 command sample,要自己動手測試也是個很棒的資料...

PostgreSQL 的 Parallel Aggreation

PostgreSQL 9.6 將會支援 Parallel Aggreation,在多 CPU core 下 aggreation operation 單一 query 的平行化效能改善 (改善非常多):「pgsql: Support parallel aggregation.」:

Parallel workers can now partially aggregate the data and pass the transition values back to the leader, which can combine the partial results to produce the final answer.

在「Parallel Aggregate – Getting the most out of your CPUs」這邊有測試 worker 數量與執行的速度差異:

We performed some tests on a 4 CPU 64 core server with 256GB of RAM using TPC-H @ 100 GB scale on query 1. This query performs some complex aggregation on just over 600 million records and produces 4 output rows.

由於是 64 cores,所以作者測 1 到 64 workers 的效能,這是測試出來的結果:

要注意 Y 軸是對數比例,紅色是理論值,藍色是實際值,可以看出來平行化的效能頗不錯,雖然開到 64 workers 時效率已經不到一半了。

這對離鋒時間的報表運算超好用啊...

PostgreSQL 對 Vacuum 效能的改善

在「No More Full-Table Vacuums」這邊提到了 PostgreSQL 在 vacuum 時效能的大幅改善,尤其是大型資料庫在 vacuum 時需要對整個表格從頭到尾掃一次以確保 transaction id 的正確性:

Current releases of PostgreSQL need to read every page in the database at least once every 2 billion write transactions (less, with default settings) to verify that there are no old transaction IDs on that page which require "freezing".

這動作在資料量大的機器上就會吃大量資源導致各種討厭的現象:

All of a sudden, when the number of transaction IDs that have been consumed crosses some threshold, autovacuum begins processing one or more tables, reading every page. This consumes much more I/O bandwidth, and exerts much more cache pressure on the system, than a standard vacuum, which reads only recently-modified page.

而作者送了 patch 改成只會讀還沒搞定的部份:

Instead of whole-table vacuums, we now have aggressive vacuums, which will read every page in the table that isn't already known to be entirely frozen.

要注意的是,agreesive vacuum 相較於 vacuum 會多吃很多資源,但可以打散掉 (有點像一次大 GC 導致 lag 與多次 minor GC 讓程式反應時間變得比較順暢的比較):

An aggressive vacuum still figures to read more data than a regular vacuum, possibly a lot more. But at least it won't read the data that hasn't been touched since the last aggressive vacuum, and that's a big improvement.

這個功能預定在 PostgreSQL 9.6 出現,不知道會不會變 default...

AWS Database Migration Service

AWS 正式向所有使用者開放「AWS Database Migration Service」了:「AWS Database Migration Service」。

AWS 把前置作業 (setup & initial backup) 與 replication 的部份都包好,讓使用者可以很輕鬆的轉移。

支援的來源資料庫種類包括了這五種:

Supported database sources include: (1) Oracle, (2) SQL Server, (3) MySQL, (4) Amazon Aurora and (5) PostgreSQL. All sources are supported on-premises, in EC2, and RDS except Amazon Aurora which is available only in RDS.

支援的目的資料庫種類也包括了這五種:

Supported database targets include: (1) Amazon Aurora, (2) Oracle, (3) SQL Server, (4) MySQL, and (5) PostgreSQL. All Oracle, SQL Server, MySQL and Postgres targets are supported on-premises, in EC2 and RDS.

所以不只可以搬進 AWS,也透過在 EC2 instance 上架 Proxy 的方式搬出 AWS。比較特別的是可以不同 database 互轉?這好像可以玩玩看...

轉移的機器包括 t2.* 與 c4.* 兩種,一般來說 t2 系列的機器應該夠用,但如果要拼轉移速度的話可以拿 c4 出來撐場面。

PostgreSQL 9.5 釋出,UPSERT!

PostgreSQL 9.5 正式發行,這次新增了大家期待已久的 UPSERT 功能:「PostgreSQL 9.5: UPSERT, Row Level Security, and Big Data」。

SQL:2003 正式定義出 UPSERT,被稱為 Merge,不過看網路上一般還是比較習慣 UPSERT 這個用法:

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches.

也就是當沒資料的時候就 INSERT,有資料的時候就 UPDATE 的語法。常見的使用情境是拿來當 counter 用 (雖然這很傷資料庫的效能)。

沒有 UPSERT 的時候只能用 transaction 或是 store procedure 搭出來,效能上會比在 database engine 裡實作來的差,所以 UPSERT 還是被實作出來了。

PostgreSQL 9.5 將會有 Parallel Sequential Scan

在「Parallel Sequential Scan is Committed!」這邊看到 PostgreSQL 9.5 (還沒出) 將會有 Parallel Sequential Scan 的功能。

文章的作者直接拿了一個大家超常用的惡搞來示範,也就是經典的 LIKE '%word%'

rhaas=# \timing
Timing is on.
rhaas=# select * from pgbench_accounts where filler like '%a%';
 aid | bid | abalance | filler
-----+-----+----------+--------
(0 rows)

Time: 743.061 ms
rhaas=# set max_parallel_degree = 4;
SET
Time: 0.270 ms
rhaas=# select * from pgbench_accounts where filler like '%a%';
 aid | bid | abalance | filler
-----+-----+----------+--------
(0 rows)

Time: 213.412 ms

這功能真不錯 XD

PostgreSQL 9.5 預定提供的 Row Locking 改善

在「More Concurrency: Improved Locking In PostgreSQL」這邊提到 PostgreSQL 的 Row Locking 的改善,也就是 SELECT ... FOR UPDATESELECT ... FOR SHARE

查了一下 SELECT 的文件,在 7.2 開始提供 FOR UPDATE (PostgreSQL: Documentation: 7.2: SELECT),在 8.1 開始提供 FOR SHARE (PostgreSQL: Documentation: 8.1: SELECT),以維基百科上的紀錄來看,7.2 是 2002 年二月,8.1 是 2005 年十一月,都是已經提很久的功能了。

FOR UPDATEFOR SHARE 可以降低對 transaction 的依賴程度,PostgreSQL 的預設值是 READ COMMITTED,配合 Row Locking 就已經可以做到不少效果了,不需要用到 SERIALIZABLE 等級。

而在最新的 PostgreSQL 9.5 (目前還是開發版),則又多提供了 FOR UPDATE SKIP LOCKED 功能,以官方提供的範例來說,就可以直接避開選位造成的 lock 問題了:

This makes sense because 100 users checking for a free seat concurrently will get 100 different rows. The consequence is that you are not stuck with 1 CPU but you can nicely scale out to all CPUs in the system. As conflicts cannot happen anymore, nobody has to wait on somebody else.

對 locking 控制的更細微。

Amazon Redshift 的 Data Ingestion

AWS 放出的「Amazon redshift migration and load data 20150722」這份投影片則是解釋了 data ingestion 時的建議行為。

其實這張就道盡目前 Amazon Redshift 架構上的最佳作法,也就是每次都 TRUNCATE 掉重新 import。後面的所有方法其實都是 workaround,效能不會太好... XD

另外後面是介紹倒資料進 Amazon Redshift 的方式,官方是還蠻推 AWS Data Pipeline,但你用過就會知道有多麻煩與痛苦...

PostgreSQL 9.5 的 GROUPING SETS 以及 CUBE 與 ROLLUP

Zite 上看到的「Postgres finally has CUBE / ROLLUP / GROUPING SETS !」。

直接看 PostgreSQL 的文件「7.2.4. GROUPING SETS, CUBE, and ROLLUP」就可以知道用法:

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

結果就是分次 GROUP BY 的聯集。而 CUBEROLLUP 則是提供列舉的方式。

ROLLUP 的部份:

ROLLUP ( e1, e2, e3, ... )

表示階層式的列舉:

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 )
    ( e1 )
    ( )
)

CUBE

CUBE ( a, b, c )

則是表示 power set (所有的組合):

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         ),
)

也有更複雜的 CUBE ( (a,b), (c,d) )GROUP BY a, CUBE(b,c), GROUPING SETS ((d), (e)) 可以用,參考文件裡的範例即可 :p

Archives