TechPrufer
Get started
GitHub
GitHub logoGitHub · Feature trail

MySQL Performance Optimization with TCMalloc

3 posts · Feb 2013 – Dec 2023

What this trail covers

This feature thread tracks the evolution of optimizing MySQL performance. Initially, the focus was on addressing rising query times. This post details the successful migration from stock malloc to TCMalloc to reduce lock contention in threaded applications, resulting in a 30% improvement in query performance and a significant decline in thread contention. This post further details the use of MySQL's Performance Schema, specifically `table_io_waits_summary_by_index` and `events_statements_summary` to diagnose performance issues. The upgrade to MySQL 8.0 involved a complex, year-long process of preparing infrastructure, ensuring application compatibility through CI and Codespaces, and executing a gradual, multi-step upgrade plan that prioritized rollback capabilities. Key challenges included managing Vitess clusters, addressing replication delay, and overcoming compatibility issues related to character collations and role management between MySQL 5.7 and 8.0.

All GitHub trails
  1. Upgrading GitHub.com to MySQL 8.0This post details the successful upgrade of GitHub.com's fleet of over 1200 MySQL hosts to MySQL 8.0. The upgrade process, which took over a year, involved extensive preparation including infrastructure readiness, application compatibility testing in CI and GitHub Codespaces, and a phased rollout strategy. The upgrade plan included rolling replica upgrades, updating replication topology, promoting MySQL 8.0 hosts to primary, and finally, cleanup. A significant focus was placed on maintaining rollback capabilities, which required addressing compatibility issues such as character collation differences (`utf8mb4_0900_ai_ci` vs. `utf8mb4_unicode_520_ci`) and the introduction of roles in MySQL 8.0. The post also touches upon challenges with Vitess clusters and replication delay.
  2. Workload Analysis with MySQL's Performance SchemaThis post details the use of MySQL's Performance Schema, specifically `table_io_waits_summary_by_index` and `events_statements_summary_by_digest`, to identify and resolve performance bottlenecks. It highlights an example of an inefficient update query on the `repositories` table that was responsible for a significant portion of updates, and how a simple code change reduced its impact. It also provides an example query to identify the top 10 most frequent UPDATE queries.
  3. TCMalloc and MySQLThis post details the investigation into rising MySQL query times, identifying `kernel_mutex` contention in InnoDB transactions as the root cause. It describes the successful migration from stock malloc to TCMalloc, leveraging its strength in reducing lock contention for threaded applications. This change resulted in a 30% improvement in query performance and a significant reduction in thread contention, as evidenced by a decrease in `pt-stalk` trigger frequency.