性能优化

内存

• MySQL 服务器占用的总内存主要包括：

  • 全局共享的内存：

    innodb_buffer_pool
    innodb_log_buffer
    temptable_max_ram
    key_buffer_size       # 用于缓冲索引，默认为 16M
    query_cache
    

  • 每个客户端线程独享一份的内存：

    join_buffer_size      # 默认为 256K
    read_buffer_size      # 默认为 128K
    sort_buffer_size      # 默认为 512K
    read_rnd_buffer_size  # 默认为 256K
    

• performance_schema 数据库的以下数据表，记录了各种事件占用的内存：

  memory_summary_by_account_by_event_name   # 各个 user@host 占用的内存
  memory_summary_by_thread_by_event_name    # 各个客户端线程占用的内存
  memory_summary_global_by_event_name       # 各个 event 占用的内存
  

  其中的主要字段：

  COUNT_ALLOC                   # 累计分配内存的次数
  COUNT_FREE                    # 累计释放内存的次数
  CURRENT_COUNT_USED            # 当前分配但未释放的内存次数，等于 COUNT_ALLOC - COUNT_FREE
  HIGH_COUNT_USED               # CURRENT_COUNT_USED 的历史最大值
  
  SUM_NUMBER_OF_BYTES_ALLOC     # 累计分配的内存量（包括已释放的）
  CURRENT_NUMBER_OF_BYTES_USED  # 当前分配但未释放的内存量，等于 SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE
  HIGH_NUMBER_OF_BYTES_USED     # CURRENT_NUMBER_OF_BYTES_USED 的历史最大值
  

• 相关命令：

  SHOW ENGINE INNODB STATUS;    -- 查看 Innodb 引擎的状态，包括事务、内存、死锁日志等
  SELECT * FROM sys.memory_global_total;    -- 查看 MySQL 占用的内存总量
  SELECT event_name,current_alloc FROM sys.memory_global_by_current_bytes LIMIT 10; -- 查看各种事件占用的内存
  SELECT thread_id,user,current_allocated FROM sys.memory_by_thread_by_current_bytes LIMIT 10;  -- 查看各个客户端占用的内存
  

  • 刚启动且 buf_buf_pool 为空时， MySQL 大概占用 300MB 内存。
  • sys.memory_global_total 是 MySQL 自我观测的内存占用，可能比 MySQL 进程的 RSS 内存偏小，主要原因：
    • MySQL 未统计启动时加载的 C++ 动态链接库。
    • MySQL 默认采用 ptmalloc 内存分配器，调用 free() 可能不会立即释放内存。

mysqlslap

：MySQL 官方提供的一个命令行工具，用于性能测试。

• 工作原理：

  1. 建立单个连接到 MySQL 服务器，创建用于测试的数据库、数据表，插入随机数据。
  2. 建立多个连接到 MySQL 服务器，模拟多个客户端并发。执行自动生成的 SQL 语句，进行测试。
  3. 建立单个连接到 MySQL 服务器，删除用于测试的数据库。

• 命令：

  mysqlslap
          -a                # --auto-generate-sql ，自动生成 SQL 语句进行测试
          -x 1              # --number-char-cols  ，在测试表中定义多少个 int(32) 类型的字段
          -y 1              # --number-int-cols   ，在测试表中定义多少个 varchar(128) 类型的字段
          --auto-generate-sql-add-autoincrement         # 在测试表中增加一个 autoincrement 的主键字段，默认不启用
          --engine=innodb                               # 用于创建测试表的引擎
  
          --auto-generate-sql-write-number 100          # 在测试表中，插入 n-1 行测试数据
          --auto-generate-sql-unique-write-number 10    # 测试数据中，有多少行是不重复的
  
          --auto-generate-sql-execute-number 10         # 插入测试数据之后，执行多少个 query 操作
          --auto-generate-sql-unique-query-number 10    # query 操作中，有多少个是不重复的
          --auto-generate-sql-load-type mixed           # query 操作的类型，可以是 read（即 SELECT 语句）、write（即 INSERT 语句）、mixed（read、write 各占一半）、key（读取主键）、update（修改主键），默认为 mixed
  
          -c 1              # --concurrency ，模拟多少个客户端并发访问，每个客户端都会将上述测试执行一次
          -i 1              # --iterations ，整个测试过程重复执行多少次
  
          --only-print      # 不实际执行测试，而是打印用于测试的所有 SQL 语句
          --no-drop         # 测试之后不要删除测试库。不过重复次测试时，会因为测试库已存在而报错
  

• 例：打印测试过程

  [root@CentOS ~]# mysqlslap -u root -p -a --auto-generate-sql-write-number 3 --auto-generate-sql-execute-number 2 --only-print
  DROP SCHEMA IF EXISTS `mysqlslap`;
  CREATE SCHEMA `mysqlslap`;                                      # 创建测试数据库
  use mysqlslap;
  CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));     # 创建测试数据表
  INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');  # 插入测试数据
  INSERT INTO t1 VALUES (822890675,'97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W');   # 插入测试数据
  INSERT INTO t1 VALUES (100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2BbpzhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6');      # 执行 query
  SELECT intcol1,charcol1 FROM t1;                                                                                                                                      # 执行 query
  DROP SCHEMA IF EXISTS `mysqlslap`;
  

• 例：执行并发测试

  [root@CentOS ~]# mysqlslap -u root -p -a --auto-generate-sql-add-autoincrement --auto-generate-sql-execute-number 40 -c 100 -i 10
  Benchmark
          Average number of seconds to run all queries: 0.160 seconds
          Minimum number of seconds to run all queries: 0.124 seconds
          Maximum number of seconds to run all queries: 0.202 seconds
          Number of clients running queries: 100
          Average number of queries per client: 40