什么是索引,索引怎么建这些基本的就跳过不谈了,整理一些前段时间优化线上 SQL 查询时碰到的一些问题. 主要解决下面几个问题:
- 建立索引怎样选择合适的列.
- 怎样让 SQL 能有效利用索引.
- 如果对 SQL 效率进行评估(即设置索引前后是否真的有性能提升).
Glow 的 server infrastructure 全部搭建在 AWS 上，一般要选择一些基础服务的时候，总是先看 AWS, 只要功能和成本符合要求，不会特意选择开源方案。
数据仓库我们选择了 AWS 的 Redshift.
在一年多的使用过程中 Redshift 的性能和稳定性都不错, 当然也有一些坑, 这里整理下在使用 redshift 的过程中的一些经验和遇到的坑.
......最近在对公司的 MySQL 服务器做性能优化, 一直对 innodb 的内存使用方式不是很清楚, 乘这机会做点总结.
在配置 MySQL 的时候, 一般都会需要设置 innodb_buffer_pool_size, 在将 MySQL 设置在单独的服务器上时, 一般会设置为物理内存的80%.
之前一直疑惑 MySQL 是怎么缓存数据的(不是指query cache), 直觉应该是LRU, 但如果 query 一下从磁盘上读取大量的数据的话(全表扫描或是 mysqldump), 是不是很容易就会把热数据给踢出去?
......Work with tech, belive in magic, live on fun :) Side projects https://github.com/monsterxx03/gospy non-Invasive goroutine inspector https://github.com/monsterxx03/snet transparent tcp proxy(mainly used in China), works on Linux & MacOS Work expenrience 2021.6 ~ Now XSky Ltd (https://www.xsky.com/) Work as senior architect, participated in the design and development of HCI(Hyperconverged infrastructure) system from 0 to 1. It’s a vm scheduling and management system. Main tech stack: Postgresql as DB, use logical replication as msg queue. VictoriaMetrics for monitoring. Etcd for leader election. code in golang Major achievements: Write a terraform provider for the product, which generates the terraform schema definition from an OpenAPI spec using code generation, uses reflection to implement generic Terraform CRUD. Designed and developed a HA solution for Postgresql based on Patroni(by client side service discovering) Build alerting system based on VictoriaMetrics, support both metrics & event based alerting. Design and implement the upgrade path for the product. 2014.11 ~ 2021.5 Glow Inc (https://glowing.com) Work as company’s infrastructure architect: Responsible for AWS-based infrastructure design, selection, maintenance, and refactoring. System capacity planning, cost management. Identify bottlenecks in system performance and architecture scalability, refactor existing code, increase user base by 10X while keeping IT cost increase to only two times. Regular team training and technical sharing, as well as code quality control.......