Home » Posts tagged "sql"

原來 Oracle 與 Microsoft 裡的條款是這樣來的...

看到「That time Larry Ellison allegedly tried to have a professor fired for benchmarking Oracle」這篇文章的講古,想起很久前就有聽過 Microsoft 有這樣的條款 (禁止未經原廠同意公開 benchmark 結果),原來是 Oracle 在三十幾年前創出來的?而且這種條款還有專有名詞「DeWitt Clauses」,出自當初被搞的教授 David DeWitt...

Microsoft 的條款是這樣:

You may not disclose the results of any benchmark test … without Microsoft’s prior written approval

Oracle 的則是:

You may not disclose results of any Program benchmark tests without Oracle’s prior consent

IBM 的反而在 license 裡面直接允許:

Licensee may disclose the results of any benchmark test of the Program or its subcomponents to any third party provided that Licensee (A) publicly discloses the complete methodology used in the benchmark test (for example, hardware and software setup, installation procedure and configuration files), (B) performs Licensee’s benchmark testing running the Program in its Specified Operating Environment using the latest applicable updates, patches and fixes available for the Program from IBM or third parties that provide IBM products (“Third Parties”), and © follows any and all performance tuning and “best practices” guidance available in the Program’s documentation and on IBM’s support web sites for the Program…

Amazon S3 的流量,以及 S3 與 Glacier 都推出 Select 功能

Twitter 上看到會場的照片,Amazon S3 單一 region 就有 37 Tb/sec 的量:

在這種量下面對 DDoS 沒什麼感覺 XDDD

另外是 Amazon S3 與 Amazon Glacier 都推出了 Select 功能:「S3 Select and Glacier Select – Retrieving Subsets of Objects」。

看示範的程式碼就可以看出用途了,原文中間那段有 sytax error,我這邊就幫忙修掉了:

handler = PrintingResponseHandler()
s3 = boto3.client('s3')
response = s3.select_object_content(
    Bucket="super-secret-reinvent-stuff",
    Key="stuff.csv",
    SelectRequest={
        'ExpressionType': 'SQL',
        'Expression': 'SELECT s._1 FROM S3Object AS s',
        'InputSerialization': {
            'CompressionType': 'NONE',
            'CSV': {
                'FileHeaderInfo': 'IGNORE',
                'RecordDelimiter': '\n',
                'FieldDelimiter': ',',
            }
        },
        'OutputSerialization': {
            'CSV': {
                'RecordDelimiter': '\n',
                'FieldDelimiter': ',',
            }
        }
    }
)

這樣可以大幅降低 I/O,節省成本:

Glacier Select 也是類似的想法,不需要整包拉出來再處理,可以在一開始就設定條件。

AWS 推出可以在 Red Hat Enterprise Linux 上跑 Microsoft SQL Server 的 AMI

自從 Microsoft SQL Server 宣佈可以在 Linux 上跑後 (參考「Microsoft SQL Server 出 Linux 版...」),就沒看到什麼 Linux 上跑 SQL Server 的消息了... 結果在這波 AWS 的活動上推出了 RHEL 上跑 SQL Server 的消息:「Amazon EC2 now offers SQL Server 2017 with Red Hat Enterprise Linux 7.4」。

SQL Server 2017 is now available for Amazon EC2 instances running Red Hat Enterprise Linux (RHEL) 7.4 as an Amazon Machine Image (AMI) from the AWS Marketplace. With this release, you can now launch RHEL instances on-demand using SQL Server 2017 Enterprise License Included AMIs without having to bring your own license. SQL Server 2017 on RHEL 7.4 AMI is available in all public AWS regions starting today.

這個消息看到的時候嚇了一跳...

Amazon Aurora (MySQL) 推出的 Asynchronous Key Prefetch

Amazon Aurora (MySQL) 推出新的效能改善,可以改善 JOIN 時的效能:「Amazon Aurora (MySQL) Speeds Join Queries by More than 10x with Asynchronous Key Prefetch」。

看起來像是某個情況的 optimization,將可能的 random access 換成 sequential access 而得到大量的效能:

This feature applies to queries that require use of the Batched Key Access (BKA) join algorithm and Multi-Range Read (MRR) optimization, and improves performance when the underlying data set is not in the main memory buffer pool or query cache.

其實記憶體還是最好用的加速器,能加大硬拼就先硬拼... XD

InnoDB 的 MVCC 繁忙時的效能問題

Facebook 上看到 Percona 的人修正了 InnoDB 的 MVCC 在繁忙時會有 O(n^2) 的效能問題:

MySQL 官方的 bug tracking system 是「InnoDB's MVCC has O(N^2) behaviors」這個,可以看到給的重製範例是在 transaction 內大量塞 INSERT 進去後,另外一個 transaction 使用 secondary index 就會受到影響。

裡面也有提到「Secondary index updates make consistent reads do O(N^2) undo page lookups」,雖然修正了,但看起來跟當時實做的規劃有關?所以導致許多地方都是 O(n^2)...

這個 bug 感覺是批次作業的行為?因為批次作業可能會用 transaction 包起來,一次寫入萬筆資料後再 COMMIT 進去。而這個行為很有機會觸發這個 bug,導致影響到線上的服務...

PostgreSQL 10 發表

PostgreSQL 10 發表,有不少重要的功能 (進步):「PostgreSQL 10 Released」。

首先提到的是 Logical Replication:

Logical Replication - A publish/subscribe framework for distributing data

以往內建的 replication 是 block level change (同步哪個 block 改變的內容),對於版本不同的 PostgreSQL 就會痛。所以在 10 之前,想要處理 PostgreSQL 版本不同的問題都會使用第三方套件 (一種常見的情境就是資料庫的版本升級)。在 10 內建支援 Logical Replication 後就不需要掛其他套件了:

Logical replication extends the current replication features of PostgreSQL with the ability to send modifications on a per-database and per-table level to different PostgreSQL databases. Users can now fine-tune the data replicated to various database clusters and will have the ability to perform zero-downtime upgrades to future major PostgreSQL versions.

於是就可以達到 zero-downtime upgrade,這對於商業維運考量是個很重要的進展。

另外一個是 Improved Query Parallelism (在 9.6 就有,現在又再改善了),針對可平行化的 CPU-bounded SQL query 可以利用多 CPU 大幅加速,這點也是目前在 MySQL 上還沒看到的:

PostgreSQL 10 provides better support for parallelized queries by allowing more parts of the query execution process to be parallelized. Improvements include additional types of data scans that are parallelized as well as optimizations when the data is recombined, such as pre-sorting. These enhancements allow results to be returned more quickly.

上面提到這兩點其實對於某些需求是相輔相成的。

因為很多報表分析是可平行化的 CPU-bounded SQL query,但以前在 RDBMS 都不能被平行運算,於是很多單位就會想要倒出來到其他類型的資料庫運算 (以現在比較紅的產品,像是 Amazon RedshiftAmazon Athena,或是 BigQuery,甚至是丟進 ELK 裡)。但你用 PostgreSQL 又會痛在沒辦法很方便的把資料同步拉出來... (於是就會稍微妥協,用 cron job 每天倒資料)

現在 10 的這兩個功能剛好從兩個面向解決:一個是對於剛開使用 PostgreSQL 的人,他們可以繼續只用 PostgreSQL 撐久一點,因為報表需求的 SQL query 快很多;另外一方面也讓目前用 cron job 每天倒資料的人有了同步的選擇 (用 replication 同步到其他系統上)。

再來是 Quorum Commit for Synchronous Replication 這個功能,把分散式架構中需要「正確性」的底層技術做起來:

PostgreSQL 10 introduces quorum commit for synchronous replication, which allows for flexibility in how a primary database receives acknowledgement that changes were successfully written to remote replicas. An administrator can now specify that if any number of replicas has acknowledged that a change to the database has been made, then the data can be considered safely written.

整體來說,PostgreSQL 10 有非常多進步,而且這些進步對於商業營運考量都很有幫助...

Heimdall Data:自動 Cache RDBMS 資料增加效能

看到 AWS 的「Automating SQL Caching for Amazon ElastiCache and Amazon RDS」這篇裡面介紹了 Heimdall Data – SQL caching and performance optimization 這個產品。

從官網的介紹也可以看出來是另外疊一層 proxy,但自動幫你處理 cache invalidation 的問題:

But what makes Heimdall Data unique in industry is its auto-cache AND auto-invalidation capability. Our machine learning algorithms determine what queries to cache while invalidating to ensure maximum performance and data integrity.

看起來支援了四個蠻常見的 RDBMS:

Heimdall Data supports most all relational database (e.g. MySQL, Postgres, Amazon RDS, Oracle, SQL Server, MariaDB).

看起來是一個花錢直接買效能的方案... 不過 cache invalidation 的部分不知道要怎麼跨機器做,在 FAQ 沒看到 cluster 情況下會怎麼解決。

對於按讚數排名的方法

前幾天看到一篇 2009 年的老文章,在討論使用者透過「喜歡」以及「不喜歡」投票後,要怎麼排名的方法:「How Not To Sort By Average Rating」。

基本的概念是當使用者投票數愈多時就會愈準確,透過統計方法可以算一個信賴區間,再用區間的下限來排... 但沒想到公式「看起來」這麼複雜 XDDD

Score = Lower bound of Wilson score confidence interval for a Bernoulli parameter

但實際的運算其實沒那麼複雜,像是 Ruby 的程式碼可以看出大多都是系統內的運算就可以算出來。其中的 z 在大多數的情況下是常數。

require 'statistics2'

def ci_lower_bound(pos, n, confidence)
    if n == 0
        return 0
    end
    z = Statistics2.pnormaldist(1-(1-confidence)/2)
    phat = 1.0*pos/n
    (phat + z*z/(2*n) - z * Math.sqrt((phat*(1-phat)+z*z/(4*n))/n))/(1+z*z/n)
end

The z-score in this function never changes, so if you don't have a statistics package handy or if performance is an issue you can always hard-code a value here for z. (Use 1.96 for a confidence level of 0.95.)

作者後來在 2012 年與 2016 年也分別給了 SQL 以及 Excel 的範例程式碼出來,裡面 hard-code 了 95% 信賴區間的部份:

SELECT widget_id, ((positive + 1.9208) / (positive + negative) - 
                   1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
                          (positive + negative)) / (1 + 3.8416 / (positive + negative)) 
       AS ci_lower_bound FROM widgets WHERE positive + negative > 0 
       ORDER BY ci_lower_bound DESC;
=IFERROR((([@[Up Votes]] + 1.9208) / ([@[Up Votes]] + [@[Down Votes]]) - 1.96 * 
    SQRT(([@[Up Votes]] *  [@[Down Votes]]) / ([@[Up Votes]] +  [@[Down Votes]]) + 0.9604) / 
    ([@[Up Votes]] +  [@[Down Votes]])) / (1 + 3.8416 / ([@[Up Votes]] +  [@[Down Votes]])),0)

而更多的說明在維基百科的「Binomial proportion confidence interval」可以翻到,裡面也有其他的方法可以用。

MySQL 總算要拔掉 mysql_query_cache 了

半官方的 MySQL blog 上宣佈了拔掉 mysql_query_cache 的計畫:「MySQL 8.0: Retiring Support for the Query Cache」。

作者開頭引用了 ProxySQL 的人對 MySQL Query Cache 的說明:

Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck.

主要問題在於 MySQL Query Cache 在多 CPU 環境下很難 scale,很容易造成一堆 thread 在搶 lock。而且作者也同意 ProxySQL 的說法,將 cache 放到 client 的效能比較好:

We also agree with Rene’s conclusion, that caching provides the greatest benefit when it is moved closer to the client:

可以看到 Query Cache 在複雜的環境下對效能極傷。而之前也提到過類似的事情了:「Percona 對 mysql_query_cache 的測試 (以 Magento 為例)」、「關閉 MySQL 的 Query Cache」。

一般如果要 cache 的話,透過 InnoDB 裡良好的 index 應該還可以撐不少量起來。

Archives