Home » Posts tagged "sql" (Page 3)

SQL 的各種 JOIN

SQL 的各種 JOIN 其實是個很好玩的主題,不是很難,但沒有仔細練習過一次通常只能答出常用的那幾種... 而且過一陣子又會忘記 XD

這幾天不知道哪邊看到的舊文章「Say NO to Venn Diagrams When Explaining JOINs」,這篇作者大聲疾呼不要用 Venn diagram 解釋 SQL 的 JOIN,把比較特別的 JOIN 拿出來舉例...

因為 Venn diagram 是講集合交聯集這類的操作,但 JOIN 不僅僅是如此... 舉例來說,CROSS JOIN 對應到 cartesian product 就沒辦法用 Venn diagram 簡單的表示出來,而必須畫的更「具體」:

Google 的 Cloud Spanner

GoogleCloud Spanner 這個服務拿出來賣了:「Introducing Cloud Spanner: a global database service for mission-critical applications」,以及說明的「Inside Cloud Spanner and the CAP Theorem」。

Cloud Spanner 的規劃上是希望有 RDBMS 的能力 (像是 ACID 特性),又有強大的擴充能力 (scalability) 與可用性 (availability):

Today, we’re excited to announce the public beta for Cloud Spanner, a globally distributed relational database service that lets customers have their cake and eat it too: ACID transactions and SQL semantics, without giving up horizontal scaling and high availability.

在說明裡有提到 Cloud Spanner 是做到 CAP theorem 裡面的 CP:

The purist answer is “no” because partitions can happen and in fact have happened at Google, and during some partitions, Spanner chooses C and forfeits A. It is technically a CP system.

然後把 A 拉高到使用者不會在意 downtime 的程度:

However, no system provides 100% availability, so the pragmatic question is whether or not Spanner delivers availability that is so high that most users don't worry about its outages.

當然,比較讓人爭議的是 Twitter 上 Google Cloud 官方帳號的 tweet,直接講同時解決了 CAP 三個條件:

價錢不算便宜,不過對於想要找方案的人至少有選擇...

Swap 對 InnoDB 的影響

Percona 的老大拿 5.7 版做實驗,確認 swap 對 InnoDB 的影響:「The Impact of Swapping on MySQL Performance」。

測試的機器是 32GB RAM,作業系統 (以及 swap) 裝在已經有點年紀的 Intel 520 SSD 上,而 MySQL 則是裝在 Intel 750 NVMe 上。透過對 innodb_buffer_pool 的調整來看情況。

可以看到設為 24GB (記憶體 75% 的量) 時很穩定的在 44K QPS 與 3.5ms (95%):

This gives us about 44K QPS. The 95% query response time (reported by sysbench) is about 3.5ms.

而當設成 32GB 的時候開始可以觀察到 swap i/o,掉到 20K QPS 與 9ms (95%):

We can see that performance stabilizes after a bit at around 20K QPS, with some 380MB/sec disk IO and 125MB/sec swap IO. The 95% query response time has grown to around 9ms.

當拉到 48GB 的時候就更掉更多,6K QPS 與 35ms (95%):

Now we have around 6K QPS. Disk IO has dropped to 250MB/sec, and swap IO is up to 190MB/sec. The 95% query response time is around 35ms.

作者發現掉的比率沒有想像中大:

When I started, I expected severe performance drop even with very minor swapping. I surprised myself by getting swap activity to more than 100MB/sec, with performance “only” halved.

這邊測試用的是 SSD,如果是傳統用磁頭的硬碟,對 random access 應該會很敏感而掉更多:

This assumes your swap space is on an SSD, of course! SSDs handle random IO (which is what paging activity usually is) much better than HDDs.

基本上還是要避免碰到 swap 啦,另外 comment 的地方剛好有提到前陣子在猜測的 best practice,測試時的 vm.swappiness 是設成 1,這應該是作者的 best practice:

Swappiness was set to 1 in this case. I was not expecting this to cause significant impact as swapping is caused by genuine (intended) missconfiguration with more memory required than available.

MySQL GTID Replication 的惡搞修復

Percona 的「Database Daily Ops Series: GTID Replication」這篇在講當 MySQL 的 GTID Replication 爛掉時可能的修法,算是頗惡搞的方法,修好後還是要跑 pt-table-checksum 確認兩邊的資料是否一致,如果有狀況的話還是得拿出 pt-table-sync 同步。

第一招是用 pt-slave-restart,跳過會造成問題 SQL,讓他強制同步 (唔):

This passes the master’s UUID and it skips all global transactions breaking replication on a specific slave server[.]

第二招是 mysqlslavetrx,也是類似的作法,只是拿的是 MySQL 官方的工具來惡搞...

第三招是 Inject a Fake Transaction,其實就是手動自己做 XDDD

所以不管是哪招,做完後還是要記得跑 pt-table-{checksum,sync} 收尾,不然還是會爛掉...

Amazon Athena:直接在 S3 上進行分析

Amazon Athena 提供另外一種選擇,讓分析的便利性增加了許多:「Amazon Athena – Interactive SQL Queries for Data in Amazon S3」。

以往都需要開 server 起來分析,這個新的服務直接使用就好:

Athena is based on the Presto distributed SQL engine and can query data in many different formats including JSON, CSV, log files, text with custom delimiters, Apache Parquet, and Apache ORC.

果然是用 Presto 改出來的... XDDD

指定好各種資料來源之後直接下 SQL query 分析,然後依照分析的量來算錢... 而 FAQ 的地方也有提到可以透過 JDBC 接上去,這樣看起來跑報表的場合直接丟給他處理了:

Amazon Athena can be accessed via the AWS management console and a JDBC driver. You can programmatically run queries, add tables or partitions using the JDBC driver.

隔壁 Amazon Redshift 的立場變得很尷尬啊,Amazon Athena 不需要養機器而且又可以直接從 Amazon S3 拉資料,如果之後把 Presto 對 RDBMS 的部分再補上來的話就更棒了... (應該是下一階段的任務,把 RDS 補上)

MySQL 8.0 將會實作「真正的」Descending Indexes

在「MySQL 8.0 Labs – Descending Indexes in MySQL」這邊看到 MySQL 打算在 8.0 時實作出真正的 Descending Indexes。在 5.7 以及之前的版本,可以從「14.1.14 CREATE INDEX Syntax」看到這個參數是~假~的~XDDD

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

所以當 8.0 建立了 a_desc_b_asc (a DESC, b ASC) 這樣的 index,可以看到對於不同 ORDER BY 時效能的差異:(一千萬筆資料)

有些變快可以理解,但有些結果不太清楚造成的原因...

Anyway,對於變慢的兩個 query,他提了一個不算解法的解法,就是加上對應的 index XDDD:

If user wants to avoid filesorts for Query 5 and Query 6, he/she can alter the table to add a key (a ASC, b ASC) . Further to this, if the user wants to avoid backward index scans too, he/she can add both ( a ASC, b DESC) and (a DESC, b DESC).

這樣就會變快,但寫入的 overhead 會增加啊... XD

但不管怎樣,總算是把這個功能生出來了...

PostgreSQL 上,直接將 SSD 的內容送到 GPU 上,加速讀取速度

PostgreSQL 上針對讀取檔案到 GPU 上的成果:「GpuScan + SSD-to-GPU Direct DMA」(日文版)、「(EN) GpuScan + SSD-to-GPU Direct DMA」(英文版)。

主要的原因在於雖然已經有 PGStorm 讓 PostgreSQL 把運算丟到 GPU 上加速,但從 disk 讀到 GPU 這段還是有改善的空間:

PG-Strom that is an extension of PostgreSQL to off-load multiple SQL workloads on GPU devices, transparently. It has been developed for four years, and now supports simple scan, tables join, aggregation and projection.
Its prime focus is CPU intensive workloads, on the other hands, it didn't touch storage subsystem of PostgreSQL because the earlier version of PG-Strom assumes all the data set shall be pre-loaded onto physical memory. No need to say, we had a problem when we want to process a data-set larger than physical RAM.

這是成果,可以看到速度快了一倍以上:

這對資料量超過 RAM 大小時的處理會非常有幫助 (因為會有大量的 disk i/o 發生)。

Archives