Tag: oracle

Home » Posts tagged "oracle"

MySQL 的 binlog 對效能的影響

Percona 的 CTO Vadim Tkachenko 在比較 InnoDB 與 MyRocks 時意外發現了 binlog 會影響不少效能穩定性，再加上 MySQL 8.0 有改變 binlog 相關的預設值，所以他後續花了不少時間測試，寫了兩篇關於 binlog 對於 MySQL 效能的影響：「How Binary Logs (and Filesystems) Affect MySQL Performance」與「How Binary Logs Affect MySQL 8.0 Performance」。

第一篇是想要知道在 Percona Server 5.7 上開 binlog 的影響，做出來後可以看到明顯的效能波動 (因為 binlog 導致 flush 時效能暴跌)：

而其中的 workaround 就是調整 flush 參數，讓他比較頻繁的小量寫入，而不是突然要寫很多資料。這樣一來對平均效能也許比較差，但對前端應用衝擊會比較小：

在測試可以看到 sync_binlog=1000 是個妥協點。各單位要自己去找出適合的數字：

The strict setting also comes with noted performance penalties. I will also test sync_binlog=1000 and sync_binlog=10000, which means perform synchronous writes of binary logs every 1000 and 10000 transactions, respectively.

另外有測試 ext4 與 XFS 是否有影響，就測試的結果看起來 ext4 比 XFS 好一些，但差距有限：

第二篇則是拿 MySQL 8.0 與 Percona Server 5.7 比較，可以發現在 MySQL 8.0 開啟 binlog 時有時會有不少的效能損失：

It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.

雖是推銷一下自家產品在這塊還不錯 XD

Computer, Database, Murmuring, MySQL, Network, SoftwareNo Comment

MySQL 與 GraalVM 的結合...

Twitter 上看到 Oracle 計畫在 MySQL 內包入 GraalVM：

這能不能解決 MySQL 的 stored procedure 一直以來都很殘的問題就要多花些時間看看了... 另外 GraalVM 用 GPLv2，但沒有明確的專利授權條款也是大家很在意的問題 (參考 Hacker News 上「GraalVM: Run Programs Faster Anywhere | Hacker News」這邊的討論)，因為大家都很清楚，這家公司只要是沒有授權的東西就不要碰...

話說 Percona 都把 PostgreSQL 包進來吃了，來考慮多用用 PostgreSQL 好了... (把 blog 換掉？)

Computer, Database, Murmuring, MySQL, SoftwareNo Comment

MySQL 8.0 正式推出 (GA，General Availability)

Oracle 推出了 MySQL 8.0 (GA，General Availability)：「MySQL 8.0 – Announcing GA of the MySQL Document Store」。在「What’s New in MySQL 8.0? (Generally Available)」這邊也花了一些篇幅介紹 MySQL 8.0 的新功能。

比較感興趣的是：

  • Descending Indexes
  • Information Schema (speed up)
  • Performance Schema (speed up)
  • INVISIBLE Indexes
  • Scaling Read/Write Workloads
  • Utilizing IO Capacity (Fast Storage)
  • Better Performance upon High Contention Loads (“hot rows”)

不過就實用性來說，效能的提昇還是最直接的... 接下來等 Percona 的人 porting 了。

Computer, Database, Murmuring, MySQL, SoftwareNo Comment

MySQL 8.0 的 innodb_dedicated_server

Percona 介紹了 MySQL 8.0 將會推出的 innodb_dedicated_server 參數：「New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go」，Oracle 官方的文件在「15.6.13 Enabling Automatic Configuration for a Dedicated MySQL Server」這邊可以翻到。

這是針對整台機器完全給 MySQL 用的情況所設計的參數。在這種情況下，可以透過 RAM 的大小以及一些簡單的公式，得到還算堪用的系統參數...

依照說明，可以看到系統會依照記憶體的大小自動計算出 innodb_buffer_pool_size、innodb_log_file_size 這兩個參數，並且把 innodb_flush_method 設為 O_DIRECT_NO_FSYNC (如果所在平台有支援這個值)。

不過看了一下公式，依照經驗可以設的更積極一點... 像是 Percona 文章裡提到的，當記憶體夠大時，其實可以考慮從 80% 開始調整大小 (innodb_buffer_pool_size)：

For InnoDB buffer pool size (based on this article), consider allocating 80% of physical RAM for starters. You can increase it to as large as needed and possible, as long as the system doesn’t swap on the production workload.

而 innodb_log_file_size 則應該要分析寫入的 pattern 而不是直接看 RAM 大小。有些機器雖然很大台但幾乎沒有寫入的量，照著公式的值就偏大很多：

For InnoDB log file size, it should be able to handle one hour of writes to allow InnoDB to optimize writing the redo log to disk. You can calculate an estimate by following the steps here, which samples one minute worth of writes to the redo log. You could also get a better estimate from hourly log file usage with Percona Monitoring and Management (PMM) graphs.

不過基本上 tune 出來的值還算堪用，對於剛入手的人頗有幫助。

Computer, Database, Murmuring, MySQL, SoftwareNo Comment

這次 PKCS #1 1.5 的 ROBOT 攻擊，Cisco 沒打算修...

1998 年就發現的 security issue 因為 workaround 也很複雜，所以不是每一家都修對方法，於是 19 年後又被爆破了。這次叫做 ROBOT：「1998 attack that messes with sites’ secret crypto keys is back in a big way」。

可以看到中獎的表：

這次的攻擊在 client 端無法修正，只能在 server 端修正：

Do I need to update my browser?
No. This is an implementation bug in servers, there is nothing clients can do to prevent it.

如果 server 端無法盡快修正的話，想辦法避開 RSA encryption 可以躲開這個問題，而且因為現代瀏覽器都有非 RSA 的替代方案，這樣做應該都還有退路，可以維持連線的可能性：

Disable RSA encryption!
ROBOT only affects TLS cipher modes that use RSA encryption. Most modern TLS connections use an Elliptic Curve Diffie Hellman key exchange and need RSA only for signatures. We believe RSA encryption modes are so risky that the only safe course of action is to disable them. Apart from being risky these modes also lack forward secrecy.

但使用 Cisco ACE 就哭了，因為 Cisco ACE 只支援 RSA encryption，而 Cisco 官方以產品線已經關閉，不再提供維護而沒有提供更新的計畫，所以就進入一個死胡同...

不過 Cisco 自己也還在用 Cisco ACE 就是了，不在意就不會痛的感覺 XD

I have a Cisco ACE device.
Cisco informed us that the ACE product line was discontinued several years ago and that they won't provide an update. Still, we found plenty of vulnerable hosts that use these devices.

These devices don't support any other cipher suites, therefore disabling RSA is not an option. To our knowledge it is not possible to use these devices for TLS connections in a secure way.

However, if you use these products you're in good company: As far as we can tell Cisco is using them to serve the cisco.com domain.

Computer, Murmuring, Network, Security, Service, WWWNo Comment

原來 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…

Computer, Database, Murmuring, SoftwareNo Comment

Amazon RDS 支援更大的硬碟空間與更多的 IOPS

Amazon RDS 的升級：「Amazon RDS Now Supports Database Storage Size up to 16TB and Faster Scaling for MySQL, MariaDB, Oracle, and PostgreSQL Engines」。

空間上限從 6TB 變成 16TB，而且可以無痛升。另外 IOPS 上限從 30K 變成 40K：

Starting today, you can create Amazon RDS database instances for MySQL, MariaDB, Oracle, and PostgreSQL database engines with up to 16TB of storage. Existing database instances can also be scaled up to 16TB storage without any downtime.

The new storage limit is an increase from 6TB and is supported for Provisioned IOPS and General Purpose SSD storage types. You can also provision up to 40,000 IOPS for Provisioned IOPS storage volumes, an increase from 30,000 IOPS.

不過隔壁的 Amazon Aurora 還是大很多啊 (64TB)，而且實際上不用管劃多大，他會自己長大：

Q: What are the minimum and maximum storage limits of an Amazon Aurora database?

The minimum storage is 10GB. Based on your database usage, your Amazon Aurora storage will automatically grow, up to 64 TB, in 10GB increments with no impact to database performance. There is no need to provision storage in advance.

AWS, Cloud, Computer, Database, MariaDB, Murmuring, MySQL, Network, PostgreSQL, Service, SoftwareNo Comment

IEEE P1735 漏洞，又是 Padding Oracle Attack...

在「IEEE P1735 Encryption Is Broken—Flaws Allow Intellectual Property Theft」這邊看到 US-CERT 發表的「IEEE P1735 implementations may have weak cryptographic protections」，裡面提到的主要漏洞：

The methods are flawed and, in the most egregious cases, enable attack vectors that allow recovery of the entire underlying plaintext IP.

主要應該是第一包：

CVE-2017-13091: improperly specified padding in CBC mode allows use of an EDA tool as a decryption oracle.

又是 CBC 的 padding oracle attack 啊... 看起來是標準沒有強制定義好造成的？

The main vulnerability (CVE-2017-13091) resides in the IEEE P1735 standard's use of AES-CBC mode.

Since the standard makes no recommendation for any specific padding scheme, the developers often choose the wrong scheme, making it possible for attackers to use a well-known classic padding-oracle attack (POA) technique to decrypt the system-on-chip blueprints without knowledge of the key.

去年 Cloudflare 寫的「Padding oracles and the decline of CBC-mode cipher suites」這邊有提到 padding oracle attack 的方式，比較一般性的解法是避開要自己決定 Encrypt-then-MAC (IPsec；也是數學上證明安全性) 或 Encrypt-and-MAC (SSH) 或是 MAC-then-Encrypt (SSL)，而是用 AEAD 類的加密元件直接躲開 padding oracle attack 的某些必要條件 (像是 AES-GCM 或是 ChaCha20-Poly1305)。

不過這也是這幾年大家才了解這樣做的重要性，當年在訂規格的時候都比較沒在在意這些...

Computer, Murmuring, Network, Privacy, Programming, SecurityNo Comment

MySQL 8.0-rc 的效能測試

Oracle 的 Dimitri KRAVTCHUK (dim) 做了測試，整理出 MySQL 8.0-rc 與其他版本效能的比較：「MySQL Performance : 2.1M QPS on 8.0-rc」。

不過先不管 MySQL 8.0-rc，這個測試其實也在說 MySQL 在 5.6 到 5.7 的過程中，對於高階伺服器效能改善非常的多 (有非常多 CPU core 的機器)：

就更不用說 5.5 版 (甚至已經沒支援的 5.0 & 5.1)，差距就更大了...

Computer, Database, Murmuring, MySQL, SoftwareNo Comment

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 情況下會怎麼解決。

AWS, Cloud, Computer, Database, MariaDB, Murmuring, MySQL, Network, PostgreSQL, Service, SoftwareNo Comment

Recent Comments

Archives

Blogroll