Tag Archives: table

performance_schema 的簡易用法

Mark Callaghan 寫了篇關於 performance_schema 的用法 (很短),讓大家先把這個參數開習慣,雖是入門推廣班:「Short guide on using performance_schema for user & table stats」。 他推薦的兩個資訊是: select * from table_io_waits_summary_by_table select * from events_statements_summary_by_account_by_event_name 當使用 5.7+ 時,可以考慮這兩個: SELECT * FROM sys.schema_table_statistics SELECT * FROM sys.user_summary 簡單到不行,但卻可以幫不少忙... 很棒的入門推廣班 XDDD

Posted in Computer, Database, Murmuring, MySQL, Software|Tagged , , , , , , , , |Leave a comment

Amazon Redshift 壓縮率的改善

Amazon Redshift 對壓縮率的改善:「Data Compression Improvements in Amazon Redshift Bring Compression Ratios Up to 4x」。 首先是引入了 Zstandard: First, we added support for the Zstandard compression algorithm, which offers a good balance between a high compression ratio and speed in build 1.0.1172. … Continue reading

Posted in AWS, Cloud, Computer, Database, Murmuring, Network, Software|Tagged , , , , , , , , , , , , , , , , , |Leave a comment

Amazon Aurora 改善 ALTER TABLE 時增加 column 的速度

Amazon Aurora (MySQL) 提昇了增加 column 操作的速度:「Amazon Aurora Supports Fast DDL Operations」,細節可以在「Amazon Aurora Under the Hood: Fast DDL」這邊看到。 這次加速是限制在 nullable 欄位: We’re addressing this mess, starting with the most common DDL operation we’ve seen: adding a nullable column at the end … Continue reading

Posted in AWS, Cloud, Computer, Database, Murmuring, MySQL, Network, Software|Tagged , , , , , , , , , , , , , |Leave a comment

線上測試 SQL Injection 喔喔喔

在「An SQL Injection Attack Is a Legal Company Name in the UK」這邊看到英國的這家公司:「; DROP TABLE "COMPANIES";-- LTD」,根本就是在幫大家測試 XDDD 當然,大家也都馬上聯想到這則 xkcd 漫畫:「Exploits of a Mom」。 來招喚 QQ 姊翻譯這則 xkcd 漫畫?

Posted in Computer, Database, Joke, Murmuring, Network, Recreation, Security, Social, Software, WWW|Tagged , , , , , , , , , , , , , , , |Leave a comment

Ruby 2.4 中 Hash Table 的效能改善

前幾天 Ruby 推出了 2.4.0 (Ruby 2.4.0 Released),其中特別被拿出來提的:「Introduce hash table improvement (by Vladimir Makarov)」。 討論串很長而且歷時很久,但可以看出來方向是提高 CPU cache 效率: Modern processors have several levels of cache. Usually,the CPU reads one or a few lines of the cache from memory (or another level … Continue reading

Posted in Computer, Murmuring, Programming, Software|Tagged , , , , , , , , |Leave a comment

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,也是類似的作法,只是拿的是 … Continue reading

Posted in Computer, Database, Murmuring, MySQL, Software|Tagged , , , , , , , , , , , |Leave a comment

GitHub 發展出來的 ALTER TABLE 方式

GitHub 解釋了他們在 MySQL 上 ALTER TABLE 的方式:「gh-ost: GitHub's online schema migration tool for MySQL」。 GitHub 的舊方式是使用 pt-online-schema-change,會遇到的問題有幾個,其中看起來只有 Non pausability 這個是真正的痛點: Non pausability: when load on the master turns high, you wish to throttle or suspend your pending migration. However a … Continue reading

Posted in Computer, Database, Murmuring, MySQL, Software|Tagged , , , , , , , |2 Comments

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 … Continue reading

Posted in Computer, Database, Murmuring, PostgreSQL, Software|Tagged , , , , , , , , , , |Leave a comment

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) … Continue reading

Posted in Computer, Database, Murmuring, PostgreSQL, Software|Tagged , , , , , , , , , , |Leave a comment

MySQL 的 Index 設計技巧

Percona 的「Indexing 101: Optimizing MySQL queries on a single table」這篇講了最基本的 index 設計技巧,雖然文章裡沒提到,但最好是需要 B-tree 與 B+ tree 的背景知識。 MySQL 的 query 大致分成幾個階段。先決定要使用哪些 index (或是完全不用),然後透過 index 抓出符合條件資料 (或是 table scan),最後再細部過濾。 以文章裡提到的「Multiple inequalities」範例裡這樣的 SQL query 來討論: SELECT * FROM t WHERE c > … Continue reading

Posted in Computer, Database, Murmuring, MySQL, Software|Tagged , , , , , , , , |Leave a comment