快速產生 SQLite 資料的方式:一分鐘內產生十億筆資料

在「Towards Inserting One Billion Rows in SQLite Under A Minute」這邊看到作者想要在一分鐘內在 MBP 2019 上面寫 1B 筆資料進 SQLite,裡面有些方法還蠻值得玩一下的,這台 MBP 2019 機器的規格是:

The machine I am using is MacBook Pro, 2019 (2.4 GHz Quad Core i5, 8GB, 256GB SSD, Big Sur 11.1)

第一版是 Python 寫的,塞 10M 筆花了 15 分鐘:

In this script, I tried to insert 10M rows, one by one, in a for loop. This version took close to 15 minutes, sparked my curiosity and made me explore further to reduce the time.

加了五個 PRAGMA 的版本變成 100M 筆十分鐘:

The naive for loop version took about 10 minutes to insert 100M rows.


The batched version took about 8.5 minutes to insert 100M rows.

再來是拿經典神器 PyPy 出來用,降到兩分半:

All I had to do was run my existing code, without any change, using PyPy. It worked and the speed bump was phenomenal. The batched version took only 2.5 minutes to insert 100M rows. I got close to 3.5x speed :)

接下來就是跳槽到 Rust 了,中間也有不少 tuning 相關的討論,但直接先跳到最後面好了... 最後 100M 只用了 33 秒:

I created a threaded version, where I had one writer thread that received data from a channel and four other threads which pushed data to the channel. This is the current best version which took about 32.37 seconds.

能用 PyPy 的地方還是可以考慮一下的...

貓叫聲的 Dataset

Hacker News Daily 上看到「CatMeows: A Publicly-Available Dataset of Cat Vocalizations」這個,貓叫聲的 dataset... 對應的討論在「CatMeows: A Publicly-Available Dataset of Cat Vocalizations (2020) (zenodo.org)」這邊。

包括了三種聲音 XDDD

  1. Brushing - Cats were brushed by their owners in their home environment for a maximum of 5 minutes;
  2. Isolation in an unfamiliar environment - Cats were transferred by their owners into an unfamiliar environment (e.g., a room in a different apartment or an office). Distance was minimized and the usual transportation routine was adopted so as to avoid discomfort to animals. The journey lasted less than 30 minutes and cats were allowed 30 minutes with their owners to recover from transportation, before being isolated in the unfamiliar environment, where they stayed alone for maximum 5 minutes;
  3. Waiting for food - The owner started the routine operations that preceded food delivery in the usual environment the cat was familiar with. Food was given at most 5 minutes after the beginning of the experiment.

嚕貓,焦慮與等待餵食?好像是可以想到這組 dataset 的用途...

Google Web Store 裡的黑暗交易

標題只寫了 Google Web Store,主要是因為瀏覽器市占率的問題,其實是包含 Firefox 的 Add-Ons。

這是在 Hacker News 首頁上看到的:「Many temptations of an open-source chrome extension developer」,講一直會有人來接觸,可以付費給開發者,想要在這些專案裡面放一些「東西」,可能是蒐集資料,可能是強制導到特定的 search engine,也有可能更邪惡...

另外是老規矩,在 Hacker News 上的討論也可以翻一翻,還蠻有趣的:「Many temptations of an open-source Chrome extension developer (github.com/extesy)」。

先大概看一下 Hover Zoom+ 這個套件在 Google Web Store 的安裝數量,大約 30 萬人:「Hover Zoom+」,作者公佈的信件內容裡面有一些包括價錢與目的...

話說回來,Brave 上的 CRX Viewer 還是沒修好啊:「Stopped working with Brave」,要裝新的套件都得另外再拉 crx 檔下來看,麻煩不少...

AWS 推出 Amazon Route 53 Resolver DNS Firewall

長久以來的洞總算有比較好的方法補上了,AWS 推出了 Amazon Route 53 Resolver DNS Firewall:「Introducing Amazon Route 53 Resolver DNS Firewall」。

Route 53 Resolver 是 AWS 官方提供的 DNS Resolver,沒有特殊的設定的話通常會在 x.x.x.2 (/24 或是更大的網段),先前一直沒有辦法解決 data leak 的問題,也就是透過 DNS 把敏感資料從 private network 裡丟出去。

以前的作法是透過 security group 擋掉對 Route 53 Resolver 的流量 (或是透過 VPC 的 Firewall 擋),然後自己架設兩台 DNS resolver 過濾,現在 Route 53 Resolver 支援 DNS Firewall,提供 allowlist 與 blocklist 這兩個功能使用,總算是把這件事情解的比較乾淨了:

Route 53 Resolver DNS Firewall lets you create “blocklists” for domains you don’t want your VPC resources to communicate with via DNS. You can also take a stricter, “walled-garden” approach by creating “allowlists” that permit outbound DNS queries only to domains you specify. You can also create alerts for when outbound DNS queries match certain firewall rules, allowing you to test your rules before deploying for production traffic.

另外這次的 DNS Firwall 提供了兩組由 AWS 維護的清單讓人使用,包括了 malware 與 botnet:

Route 53 Resolver DNS Firewall offers two managed domain lists—malware domains and botnet command and control domains—enabling you to get started quickly with managed protections against common threats.


Facebook 5.33 億筆個資外洩,以及 Mark Zuckerberg 的電話...

這兩天應該已經有很多其他媒體報導了 Facebook 5.33 億筆資料外洩的事情:「533 million Facebook users' phone numbers and personal data have been leaked online」。

據稱是用 2019 年的洞撈出來的,不過這份資料看起來也不是完整資料,舉個例子來說,台灣只有 73 萬筆左右,而且也少了很多地區,像是泰國就不在列表內...

另外一個比較特別的是,Mark Zuckerberg 的電話也在這次外洩資料裡面:「Mark Zuckerberg's phone number appeared among the leaked data of Facebook users, according to a researcher」,應該會換號碼了。


Mapbox GL JS 的授權改變,以及 MapLibre GL 的誕生

看到「MapLibre GL is a free and open-source fork of mapbox-gl-JS (github.com/maplibre)」這篇,翻了一下資料發現年初時 Mapbox GL JS 的軟體授權從 v2.0.0 開始變成不是 open source license (本來是 BSD license),而社群也馬上 fork 最後一個 open source 版本並且投入開發,變成 MapLibre GL

MapTiler 在年初的時候有提到這件事情:「MapLibre: Mapbox GL open-source fork」。

The community reacted swiftly: forks of the latest open-source version were made almost immediately by multiple parties. In another positive development, the community came together the next day and agreed to make this a joint effort, rather than splitting energies. A video call was organized and the MapLibre coalition was formed. It includes people working for MapTiler, Elastic, StadiaMaps, Microsoft, Ceres Imaging, WhereGroup, Jawg, Stamen Design, etc.

MapLibre GL 目前與本來 v1.13.0 相容,可以直接抽換過去 (後來在二月的時候有出一個 v1.13.1,不過那是在 v2.0.0 改 license 之後的事情了):

  "dependencies": {
-    "mapbox-gl": "^1.13.0"
+    "maplibre-gl": ">=1.14.0"

記錄一下,以後要在網站上用的話,得注意到 Mapbox GL JS 在沒有註冊的情況下不能使用,而且 SDK 會強制蒐集資料:

Mapbox gl-js version 2.0 or higher (“Mapbox Web SDK”) must be used according to the Mapbox Terms of Service. This license allows developers with a current active Mapbox account to use and modify the Mapbox Web SDK. Developers may modify the Mapbox Web SDK code so long as the modifications do not change or interfere with marked portions of the code related to billing, accounting, and anonymized data collection. The Mapbox Web SDK only sends anonymized usage data, which Mapbox uses for fixing bugs and errors, accounting, and generating aggregated anonymized statistics. This license terminates automatically if a user no longer has an active Mapbox account.

不過如果是抓 OpenStreetMap 資料的話,Leaflet 應該還是目前的首選...

AWS 推出 CloudWatch Metric Streams

AWS 推出了 CloudWatch Metric Streams,把 CloudWatch Metric 的資料往 Kinesis Data Firehose 裡面丟:「CloudWatch Metric Streams – Send AWS Metrics to Partners and to Your Apps in Real Time」。

其中一個賣點是即時性比用 API 去拉好很多:

In order to make it easier for AWS Partners and others to gain access to CloudWatch metrics faster and at scale, we are launching CloudWatch Metric Streams. Instead of polling (which can result in 5 to 10 minutes of latency), metrics are delivered to a Kinesis Data Firehose stream.

格式上可以是 JSON 或是 Open Telemetry

When you set up a stream you choose between the binary Open Telemetry 0.7 format, and the human-readable JSON format.

另外一個賣點是價位,每千次 $0.003:

Pricing – You pay $0.003 for every 1000 metric updates, and for any charges associated with the Kinesis Data Firehose. To learn more, check out the pricing page.

另外算一下 Kinesis Data Firehose 的價錢,是以資料量的大小計費,不過最小計價單位是 5KB (一筆應該是不會到),單價是 $0.029/GB (us-east-1) 或是 $0.037/GB (ap-southeast-1),算了一下跟 CloudWatch Metrics Streams 比起來只是零頭...

之前如果要自己拉出來的話是透過 API call 抓,每 1000 次是 USD$0.01,這個方法相較起來便宜不少,不過數量多的時候還是一筆費用 (而且有不少 metrics 是一分鐘更新一次)。

如果只是要備份起來或是跑分析的話,也許先前用 API 拉的作法可能還是比較好?一個小時拉一次對於備份與分析應該都很夠了,而 alarm 的機制還是掛在 CloudWatch 上。

這次產品的定位看起來是要把 ecosystem 做起來:

We designed this feature with the goal of making it easier & more efficient for AWS Partners including Datadog, Dynatrace, New Relic, Splunk, and Sumo Logic to get access to metrics so that the partners can build even better tools.

Zoom 在分享螢幕時會有資料安全性問題

看到「Zoom Screen-Sharing Glitch ‘Briefly’ Leaks Sensitive Data」這篇,講 Zoom 在分享螢幕時的安全性問題,發現問題的德國資安團隊所寫的 security advisory 可以在「SYSS-2020-044.txt」這邊看到,對應的 CVE 號碼是 CVE-2021-28133


However, “under certain conditions” if a Zoom presenter chooses to share one application window, the share-screen feature briefly transmits content of other application windows to meeting participants, according to German-based SySS security consultant Michael Strametz, who discovered the flaw, and researcher Matthias Deeg, in a Thursday disclosure advisory (which has been translated via Google).


The current Zoom client version, 5.5.4 (13142.0301), for Windows is still vulnerable to the issue, Deeg told Threatpost.


Disclosure Timeline:

2020-12-02: Vulnerability reported to manufacturer
2020-12-02: Manufacturer acknowledges receipt of security advisory
2020-12-02: Manufacturer asks for more information
2020-12-03: SySS provides more information concerning the security issue
2020-12-03: Manufacturer confirms reproducing the security issue in both the Windows and the Linux client and asks further questions
2020-12-04: SySS answers open questions
2020-12-04: Manufacturer responds and will look into the reported security issue
2021-01-21: SySS asks for status update
2021-02-01: SySS asks for status update
2021-03-18: Public release of security advisory


Airtable 的 Open Source 替代方案 Baserow

Hacker News Daily 上看到的產品 Baserow,另外在 Hacker News 的討論也有蠻多有趣的東西:「Baserow.io – Self-hosted Airtable alternative (baserow.io)」。

Baserow 的後端是用 Django 寫的,前端則主要是用 Vue.js

可以看到討論裡 Airtable 的問題,先不講定價問題,只講其他的部份... (因為就如同討論裡面有提到的,有可能是公司故意把定價策略訂成讓不願意花大錢的人使用)

首先是每個月都有「穩定的 downtime」,不過這邊的描述到的數字明顯有問題,一個月當了七個小時也有 99% uptime (兩個九)。

不過先撇開這點,付費服務一般都可以看到 99.9% 的 Monthly SLA (三個九,一個月只允許 43.2 分鐘),這個情況的確是不太符合業界的標準:

Reliable 1-5 hours a month of downtime. There are months where it's literally 1 nine of uptime. Their status page was actually down for a while (I think they forgot to renew it?)


Booleans in the API are `true` or `undefined`. Seriously.

看起來要有蠻多背景知識才有可能知道為什麼這樣搞 XDDD

先丟給 data team 看看,如果他們有興趣的話再找機會架起來...

Akamai 也推出了 Key-Value 服務 EdgeKV

沒介紹過 Akamai 的一些架構,先講到 Akamai 的 Edge 端 Serverless 架構是 EdgeWorkers,跑的是 JavaScript:

EdgeWorkers lets developers just code — integrating into existing CI/CD workflows and enabling multiple teams to work in parallel using JavaScript. EdgeWorkers eliminates the hassle of managing compute resources and building for scale.

然後這次推出的是 EdgeKV,目前還在 Beta 版:「Serverless Storage at the Edge (EdgeKV Beta)」。

如同名字所說的,架構上 Key-Value 架構,放棄了 CAP theorem 裡面的 C,改走 Eventual Consistency:

EdgeKV uses what is known in distributing computing as an eventual consistency model to perform writes and updates. This model achieves high availability with low read latency by propagating data writes globally. The period of time it takes the system to distribute data globally is called the “inconsistency window”.

隔壁 Cloudflare Workers KV 也是 Eventual Consistency (出自「How KV works」這邊):

KV achieves this performance by being eventually-consistent. Changes are immediately visible in the edge location at which they're made, but may take up to 60 seconds to propagate to all other edge locations.