• mysqlslap执行基准测试


    查看mysqlslap所支持的主要参数配置及说明如下

    -a, --auto-generate-sql 由系统自动生成SQL脚本进行测试
                          Generate SQL where not supplied by file or command line.
      --auto-generate-sql-add-autoincrement 生成的表中增加自增的ID
                          Add an AUTO_INCREMENT column to auto-generated tables.--auto-generate-sql-load-type=name 制定测试过程中使用的查询类型
                          Specify test load type: mixed, update, write, key, or
                          read; default is mixed.--auto-generate-sql-write-number=# 制定所生成的初始化数据的条数
                          Number of row inserts to perform for each thread (default
                          is 100).-c, --concurrency=name 制定并发线程的数量
                          Number of clients to simulate for query to run.
      --create=name       File or string to use create tables.
      --create-schema=name 创建一个测试数据库的schema名称
                          Schema to run tests in.-T, --debug-info    This is a non-debug version. Catch this and exit.指定输出额外的内存及CPU统计信息-e, --engine=name   Storage engine to use for creating the table. 指定所测试的存储引擎,用逗号可以分割以便测试多个引擎
      -h, --host=name     Connect to host. 链接远程主机的IP
      -i, --iterations=#  Number of times to run the tests. 指定本次测试需要运行的次数
      --no-drop           Do not drop the schema after the test. 指定测试完成后,不清理过程数据
      -x, --number-char-cols=name 指定测试表中生成的varchar类型的数据数量
                          Number of VARCHAR columns to create in table if
                          specifying --auto-generate-sql.
      -y, --number-int-cols=name 指定测试表中生成的int类型的数据数量
                          Number of INT columns to create in table if specifying
                          --auto-generate-sql.
      --number-of-queries=# 指定每一个线程所执行的查询数量
                          Limit each client to this number of queries (this is not
                          exact).
      --only-print        Do not connect to the databases, but instead print out 并不运行测试脚本,而是把生成的脚本打印出来
                          what would have been done.
      -p, --password[=name] 指定测试所用的链接数据库的密码
                          Password to use when connecting to server. If password is
                          not given it's asked from the tty.-q, --query=name    Query to run or file containing query to run.自定义测试用的sql
      -u, --user=name     User for login if not current user. 指定测试所用的链接数据库的用户名

    其中,完整的运行脚本如下

    mysqlslap -S /tmp/mysql3306.sock --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10 --create-schema=test -uroot -p

    待输入密码后,测试信息如下

    Enter password: 
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 0.097 seconds
            Minimum number of seconds to run all queries: 0.093 seconds
            Maximum number of seconds to run all queries: 0.107 seconds
            Number of clients running queries: 1
            Average number of queries per client: 10
    
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 0.506 seconds
            Minimum number of seconds to run all queries: 0.447 seconds
            Maximum number of seconds to run all queries: 0.570 seconds
            Number of clients running queries: 50
            Average number of queries per client: 0
    
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 2.204 seconds
            Minimum number of seconds to run all queries: 1.595 seconds
            Maximum number of seconds to run all queries: 3.257 seconds
            Number of clients running queries: 100
            Average number of queries per client: 0
    
    mysqlslap: Error when connecting to server: 1040 Too many connections
    mysqlslap: Error when connecting to server: 1040 Too many connections
    mysqlslap: Error when connecting to server: 1040 Too many connections
    mysqlslap: Error when connecting to server: 1040 Too many connections

    此时可以看出来,当并发达到了200的时候,出现了Too many connections的异常,这是因为mysql默认配置的最大链接数为100,故需要对my.cnf做如下的修改

    添加max_connections=1024,之后运行正常

    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 0.093 seconds
            Minimum number of seconds to run all queries: 0.087 seconds
            Maximum number of seconds to run all queries: 0.098 seconds
            Number of clients running queries: 1
            Average number of queries per client: 10
    
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 0.514 seconds
            Minimum number of seconds to run all queries: 0.462 seconds
            Maximum number of seconds to run all queries: 0.545 seconds
            Number of clients running queries: 50
            Average number of queries per client: 0
    
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 1.209 seconds
            Minimum number of seconds to run all queries: 1.173 seconds
            Maximum number of seconds to run all queries: 1.241 seconds
            Number of clients running queries: 100
            Average number of queries per client: 0
    
    Benchmark
            Running for engine innodb
            Average number of seconds to run all queries: 2.174 seconds
            Minimum number of seconds to run all queries: 1.978 seconds
            Maximum number of seconds to run all queries: 2.402 seconds
            Number of clients running queries: 200
            Average number of queries per client: 0
  • 相关阅读:
    学习Kubernetes,这些负载均衡知识点得知道!
    Nginx请求处理流程
    字节跳动面试题+答案,答对了30+
    Cache 和 Buffer 的区别在哪里
    优化你的HTTPS(下),你需要这么做
    优化你的HTTPS(上),你需要这么做
    swift之Mac中NSSplitView的简单实用
    oc之mac中- NSBox
    Mac之NSImageView的简单实用
    swift之预防 Timer 的循环引用
  • 原文地址:https://www.cnblogs.com/qizhelongdeyang/p/6402579.html
Copyright © 2020-2023  润新知