• sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。本文只是简单演示一下几种测试的用法,后续准备利用sysbench来对MySQL进行一...

    sysbench是一款开源的多线程性能测试工具,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL。本文只是简单演示一下几种测试的用法,后续准备利用sysbench来对MySQL进行一系列的测试。具体的一些参数设置,需要根据不同的测试要求来进行调整.

    编译安装
    # wget -c http://sourceforge.net/projects/sysbench/files/sysbench/0.4.12/sysbench-0.4.12.tar.gz/download
    #tar zxvf sysbench-0.4.12.tar.gz
    #cd sysbench-0.4.12

    #./autogen.sh
    #./configure && make && make install

    strip /usr/local/bin/sysbench

    以上方法适用于 MySQL 安装在标准默认目录下的情况,如果 MySQL 并不是安装在标准目录下的话,那么就需要自己指定 MySQL 的路径了。比如我的 MySQL 喜欢自己安装在 /usr/local/mysql 下,则按照以下方法编译

    ./configure --with-mysql-includes=/usr/local/mysql5.5.23/include --with-mysql-libs=/usr/local/mysql5.5.23/lib
    如果需要测试Oracle/PostgreSQL,则在configure时需要加上–with-oracle或者–with-pgsql参数
    参数
    [root@localhost sysbench]# ./sysbench
    测试用例:
    sysbench [general-options]… –test=<test-name> [test-options]… command
    通用选项:
    –num-threads=N 创建测试线程的数目。默认为1.
    –max-requests=N 请求的最大数目。默认为10000,0代表不限制。
    –max-time=N 最大执行时间,单位是s。默认是0,不限制。
    –forced-shutdown=STRING 超过max-time强制中断。默认是off。]
    –thread-stack-size=SIZE 每个线程的堆栈大小。默认是32K。
    –init-rng=[on|off] 在测试开始时是否初始化随机数发生器。默认是off。
    –test=STRING 指定测试项目名称。
    –debug=[on|off] 是否显示更多的调试信息。默认是off。
    –validate=[on|off] 在可能情况下执行验证检查。默认是off。
    测试项目:
    fileio – File I/O test
    cpu – CPU performance test
    memory – Memory functions speed test
    threads – Threads subsystem performance test
    mutex – Mutex performance test(互斥性能测试)
    oltp – OLTP test
    指令: prepare(测试前准备工作) run(正式测试) cleanup(测试后删掉测试数据) help version
    See 'sysbench –test=<name> help’ for a list of options for each test. 查看每个测试项目的更多选项列表。
    [root@localhost bin]# ./sysbench –test=fileio help
    –file-num=N 创建测试文件的数量。默认是128
    –file-block-size=N 测试时文件块的大小。默认是16384(16K)
    –file-total-size=SIZE 测试文件的总大小。默认是2G
    –file-test-mode=STRING 文件测试模式{seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)}
    –file-io-mode=STRING 文件操作模式{sync(同步),async(异步),fastmmap(快速map映射),slowmmap(慢map映射)}。默认是sync
    –file-extra-flags=STRING 使用额外的标志来打开文件{sync,dsync,direct} 。默认为空
    –file-fsync-freq=N 执行fsync()的频率。(0 – 不使用fsync())。默认是100
    –file-fsync-all=[on|off] 每执行完一次写操作就执行一次fsync。默认是off
    –file-fsync-end=[on|off] 在测试结束时才执行fsync。默认是on
    –file-fsync-mode=STRING 使用哪种方法进行同步{fsync, fdatasync}。默认是fsync
    –file-merged-requests=N 如果可以,合并最多的IO请求数(0 – 表示不合并)。默认是0
    –file-rw-ratio=N 测试时的读写比例。默认是1.5


    [root@localhost bin]# ./sysbench –test=cpu help
    –cpu-max-prime=N 最大质数发生器数量。默认是10000
    [root@localhost bin]# ./sysbench –test=memory help
    –memory-block-size=SIZE 测试时内存块大小。默认是1K
    –memory-total-size=SIZE 传输数据的总大小。默认是100G
    –memory-scope=STRING 内存访问范围{global,local}。默认是global
    –memory-hugetlb=[on|off] 从HugeTLB池内存分配。默认是off
    –memory-oper=STRING 内存操作类型。{read, write, none} 默认是write
    –memory-access-mode=STRING存储器存取方式{seq,rnd} 默认是seq

    [root@localhost bin]# ./sysbench –test=threads help
    –thread-yields=N 每个请求产生多少个线程。默认是1000
    –thread-locks=N 每个线程的锁的数量。默认是8

    [root@localhost bin]# ./sysbench –test=mutex help
    –mutex-num=N 数组互斥的总大小。默认是4096
    –mutex-locks=N 每个线程互斥锁的数量。默认是50000
    –mutex-loops=N 内部互斥锁的空循环数量。默认是10000

    [root@localhost bin]# ./sysbench –test=oltp help
    oltp options:
    –oltp-test-mode=STRING 执行模式{simple,complex(advanced transactional),nontrx(non-transactional),sp}。默认是complex
    –oltp-reconnect-mode=STRING 重新连接模式{session(不使用重新连接。每个线程断开只在测试结束),transaction(在每次事务结束后重新连接),query(在每个SQL语句执行完重新连接),random(对于每个事务随机选择以上重新连接模式)}。默认是session
    –oltp-sp-name=STRING 存储过程的名称。默认为空
    –oltp-read-only=[on|off] 只读模式。Update,delete,insert语句不可执行。默认是off
    –oltp-skip-trx=[on|off] 省略begin/commit语句。默认是off
    –oltp-range-size=N 查询范围。默认是100
    –oltp-point-selects=N number of point selects [10]
    –oltp-simple-ranges=N number of simple ranges [1]
    –oltp-sum-ranges=N number of sum ranges [1]
    –oltp-order-ranges=N number of ordered ranges [1]
    –oltp-distinct-ranges=N number of distinct ranges [1]
    –oltp-index-updates=N number of index update [1]
    –oltp-non-index-updates=N number of non-index updates [1]
    –oltp-nontrx-mode=STRING 查询类型对于非事务执行模式{select, update_key, update_nokey, insert, delete} [select]
    –oltp-auto-inc=[on|off] AUTO_INCREMENT是否开启。默认是on
    –oltp-connect-delay=N 在多少微秒后连接数据库。默认是10000
    –oltp-user-delay-min=N 每个请求最短等待时间。单位是ms。默认是0
    –oltp-user-delay-max=N 每个请求最长等待时间。单位是ms。默认是0
    –oltp-table-name=STRING 测试时使用到的表名。默认是sbtest
    –oltp-table-size=N 测试表的记录数。默认是10000
    –oltp-dist-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special
    –oltp-dist-iter=N 产生数的迭代次数。默认是12
    –oltp-dist-pct=N 值的百分比被视为’special’ (for special distribution)。默认是1
    –oltp-dist-res=N 'special’的百分比值。默认是75

    General database options:
    –db-driver=STRING 指定数据库驱动程序('help’ to get list of available drivers)
    –db-ps-mode=STRING编制报表使用模式{auto, disable} [auto]

    Compiled-in database drivers:
    mysql – MySQL driver
    mysql options:
    –mysql-host=[LIST,...] MySQL server host [localhost]
    –mysql-port=N MySQL server port [3306]
    –mysql-socket=STRING MySQL socket
    –mysql-user=STRING MySQL user [sbtest]
    –mysql-password=STRING MySQL password []
    –mysql-db=STRING MySQL database name [sbtest]
    –mysql-table-engine=STRING storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]
    –mysql-engine-trx=STRING whether storage engine used is transactional or not {yes,no,auto} [auto]
    –mysql-ssl=[on|off] use SSL connections, if available in the client library [off]
    –myisam-max-rows=N max-rows parameter for MyISAM tables [1000000]
    –mysql-create-options=STRING additional options passed to CREATE TABLE []

    ##########################

    测试

    1、CPU测试
    [root@localhost bin]# sysbench --test=cpu --cpu-max-prime=20000 run


    sysbench 0.4.12: multi-threaded system evaluation benchmark

    Number of threads: 1
    Doing CPU performance benchmark
    Threads started!
    Done.
    Maximum prime number checked in CPU test: 20000
    Test execution summary:
    total time: 31.9364s
    total number of events: 10000
    total time taken by event execution: 31.9334
    per-request statistics:
    min: 3.18ms
    avg: 3.19ms
    max: 3.60ms
    approx. 95 percentile: 3.20ms
    Threads fairness:
    events (avg/stddev): 10000.0000/0.00
    execution time (avg/stddev): 31.9334/0.00

    2、线程数测试 
    thread-locks小于线程数除以2,lock越少,处理时间越长。
    # sysbench --num-threads=64 --test=threads --thread-yields=100 --thread-locks=2 run

    sysbench 0.4.12: multi-threaded system evaluation benchmark
    Running the test with following options:Number of threads: 512
    Doing thread subsystem performance test
    Thread yields per test: 100 Locks used: 2
    Threads started!
    WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.00000
    WARNING: Percentile statistics will be inaccurateDone.
    Test execution summary:
    total time: 4.1759s
    total number of events: 10000
    total time taken by event execution: 2038.5959
    per-request statistics:
    min: 0.00ms
    avg: 203.86ms
    max: 2735.47ms
    approx. 95 percentile: 829.60ms
    Threads fairness:
    events (avg/stddev): 19.5312/8.26
    execution time (avg/stddev): 3.9816/0.04

    3、磁盘IO性能测试
    首先生成需要的测试文件,文件总大小3G,16个并发线程,随机读写模式。执行完后会在当前目录下生成一堆小文件。
    sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw prepare

    sysbench 0.4.12: multi-threaded system evaluation benchmark
    128 files, 24576Kb each, 3072Mb total
    Creating files for the test…

    开始测试:
    sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw run

    sysbench 0.4.12: multi-threaded system evaluation benchmark
    Running the test with following options:Number of threads: 16
    Extra file open flags: 0
    128 files, 24Mb each
    3Gb total file size 
    Block size 16Kb
    Number of random requests for random IO: 10000
    Read/Write ratio for combined random IO test: 1.50
    Periodic FSYNC enabled, calling fsync() each 100 requests
    Calling fsync() at the end of test, Enabled.
    Using synchronous I/O modeDoing random r/w test
    Threads started!
    WARNING: Operation time (0.000000) is less than minimal counted value, counting as 1.00000
    WARNING: Percentile statistics will be inaccurateDone.
    Operations performed: 6004 Read, 3996 Write, 12800 Other = 22800 TotalRead 93.812Mb Written 62.438Mb Total transferred 156.25Mb (6.472Mb/sec) 414.21 Requests/sec executed
    Test execution summary:
    total time: 24.1426s
    total number of events: 10000
    total time taken by event execution: 164.4243
    per-request statistics:
    min: 0.00ms
    avg: 16.44ms
    max: 821.08ms approx. 95 percentile: 102.40ms
    Threads fairness:
    events (avg/stddev): 625.0000/63.99 e
    xecution time (avg/stddev): 10.2765/0.62

    清理现场
    sysbench --num-threads=16 --test=fileio --file-total-size=3G --file-test-mode=rndrw cleanup

    4、内存测试

    sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run
    上述参数指定了本次测试整个过程是在内存中传输 4G 的数据量,每个 block 大小为 8K。

    5、Mutex测试
    [root@localhost ]# sysbench –test=mutex –num-threads=1500 –max-requests=2000 –mutex-num=10240 –mutex-locks=100000 –mutex-loops=15000 run


    sysbench 0.4.12: multi-threaded system evaluation benchmark
    Running the test with following options:
    Number of threads: 1500
    Doing mutex performance test
    Threads started!
    Done.
    Test execution summary:
    total time: 74.0710s
    total number of events: 1500
    total time taken by event execution: 5410.6925
    per-request statistics:
    min: 485.77ms
    avg: 3607.13ms
    max: 13770.44ms
    approx. 95 percentile: 12489.67ms
    Threads fairness:
    events (avg/stddev): 1.0000/0.00
    execution time (avg/stddev): 3.6071/3.97

    6、MySQL数据库测试

    首先需要创建默认的sbtest数据库,或者使用–mysql-db指定一个已经存在的数据库
    生成测试数据,引擎为myisam,表大小为1000000条记录
    [root@localhost ]# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --mysql-socket=/usr/local/mysql5.5.23/mysql.sock --num-threads=15 prepare

    sysbench 0.4.12: multi-threaded system evaluation benchmark

    No DB drivers specified, using mysql
    Creating table 'sbtest'...
    Creating 1000000 records in table 'sbtest'...

    sbtest.sbtest;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+------------------+------+-----+---------+----------------+
    | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | k | int(10) unsigned | NO | MUL | 0 | |
    | c | char(120) | NO | | | |
    | pad | char(60) | NO | | | |
    +-------+------------------+------+-----+---------+----------------+ 
    执行测试
    [root@localhost ]# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --mysql-socket=/usr/local/mysql5.5.23/mysql.sock --num-threads=15 run

    sysbench 0.4.12: multi-threaded system evaluation benchmark

    No DB drivers specified, using mysql
    Running the test with following options:
    Number of threads: 1

    Doing OLTP test.
    Running mixed OLTP test
    Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
    Using "LOCK TABLES WRITE" for starting transactions
    Using auto_inc on the id column
    Maximum number of requests for OLTP test is limited to 10000
    Threads started!
    Done.

    OLTP test statistics:
    queries performed:
    read: 140000
    write: 50000
    other: 20000
    total: 210000
    transactions: 10000 (336.64 per sec.)
    deadlocks: 0 (0.00 per sec.)
    read/write requests: 190000 (6396.11 per sec.)
    other operations: 20000 (673.27 per sec.)

    Test execution summary:
    total time: 29.7056s
    total number of events: 10000
    total time taken by event execution: 29.6301
    per-request statistics:
    min: 2.27ms
    avg: 2.96ms
    max: 43.88ms
    approx. 95 percentile: 3.36ms

    Threads fairness:
    events (avg/stddev): 10000.0000/0.00
    execution time (avg/stddev): 29.6301/0.00


    清理现场
    [root@localhost ]# sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --mysql-socket=/usr/local/mysql5.5.23/mysql.sock --num-threads=15 cleanup

    展开全文
  • Linux 平台基础常用的性能收集工具 1. top — Linux 系统进程监控 top命令是Linux下常用的性能分析工具,能够实时显示系统中各个进程的资源占用状况,类似于Windows的任务管理器。并且它也是 Linux 系统管理员经常...

    Linux 平台基础常用的性能收集工具

    1. top — Linux 系统进程监控
    top命令是Linux下常用的性能分析工具,能够实时显示系统中各个进程的资源占用状况,类似于Windows的任务管理器。并且它也是 Linux 系统管理员经常使用的监控系统性能的工具。Top命令可以定期显示所有正在运行和实际运行并且更新到列表中,它显示出 CPU 的使用、内存的使用、交换内存、缓存大小、缓冲区大小、过程控制、用户和更多命令。它也会显示内存和 CPU 使用率过高的正在运行的进程。

    在这里插入图片描述
    2. vmstat — 虚拟内存统计
    vmstat 命令是用于显示虚拟内存、内核线程、磁盘、系统进程、I/O 模块、中断、CPU 活跃状态等更多信息。
    在这里插入图片描述
    3. lsof — 打开文件列表
    lsof 命令对于很多 Linux/Unix 系统都可以使用,主要以列表的形式显示打开的文件和进程。打开的文件主要包括磁盘文件、网络套接字、管道、设备和进程。这个命令很容易看出哪些文件正在使用。
    可能需要额外去运行yum install lsof 去下载安装对应的工具
    在这里插入图片描述
    3. tcpdump — 网络数据包分析器
    tcpdump 是一种使用最广泛的命令行网络数据包分析,将网络中传送的数据包完全截获下来提供分析。它支持针对网络层、协议、主机、网络或端口的过滤 并提供and、or、not等逻辑语句来帮助你去掉无用的信息。
    在这里插入图片描述
    4. netstat — 网络统计
    netstat 命令是一个监控网络数据包传入和传出的统计界面的命令行工具。它对于许多系统管理员去监控网络性能和解决网络相关问题是一个非常有用的工具。
    如果无法找到相应工具,则执行yum install net-tools安装对应软件包
    在这里插入图片描述
    5. iostat — 输入/输出统计
    iostat 是收集和展示系统输入和输出存储设备统计的简单工具。这个工具通常用于查找存储设备性能问题,包括设备、本地磁盘、例如 NFS 远程磁盘。
    如果无法找到相应工具,则执行yum install sysstat安装对应软件包
    在这里插入图片描述
    除了上述 还有 其他一些Linux 常用的工具sar,htop, IPTraf , iotop ,iftop ,iptraf 等。

    MySQL常用性能突发事件分析命令

    1. SHOW PROCESSLIST; —当前MySQL数据库的运行的所有线程
    在这里插入图片描述
    2. INNODB_TRX; — 当前运行的所有事务
    当前运行的所有事务 ,还有具体的语句
    在这里插入图片描述
    3. SHOW OPEN TABLES where In_use >0; — 当前打开表
    查看哪些表在使用中,In_use列表示有多少线程正在使用某张表,Name_locked表示表名是否被锁,这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题:当前某张表是否有死锁,谁拥有表上的这个锁等。
    在这里插入图片描述
    4. SHOW ENGINE INNODB STATUS \G; —Innodb状态
    显示除了大量的内部信息,输出内容比较复杂难懂,输出内容中包含了一些平均值的统计信息,这些平均值是自上次输出结果生成以来的统计数。

    具体分析如下:

    ①.Header

    这部分简单的打印,输出的时间,以及自从上次输出的间隔时间。
    在这里插入图片描述
    ②.BACKGROUND THREAD
    在这里插入图片描述

    参数 说明
    Srv_master_thread loops Master线程的循环次数,master线程在每次loop过程中都会sleep,sleep的时间为1秒。而在每次loop的过程中会选择active、shutdown、idle中一种状态执行。Master线程在不停循环,所以其值是随时间递增的。
    Srv_active Master线程选择的active状态执行。Active数量增加与数据表、数据库更新操作有关,与查询无关,例如:插入数据、更新数据、修改表等。
    Srv_shutdown 这个参数的值一直为0,因为srv_shutdown只有在mysql服务关闭的时候才会增加。
    Srv_idle 这个参数是在master线程空闲的时候增加,即没有任何数据库改动操作时。
    Log_flush_and_write Master线程在后台会定期刷新日志,日志刷新是由参数innodb_flush_log_at_timeout参数控制前后刷新时间差。

    注:Background thread部分信息为统计信息,即mysql服务启动之后该部分值会一直递增,因为它显示的是自mysqld服务启动之后master线程所有的loop和log刷新操作。通过对比active和idle的值,可以获知系统整体负载情况。Active的值越大,证明服务越繁忙。
    ③. SEMAPHORES 信号量
    在这里插入图片描述
    OS WAIT ARRAY INFO 操作系统等待数组的信息,它是一个插槽数组,innodb使用了多少次操作系统的等待

    保留统计(reservation count)显示了innodb分配插槽的频度

    信号计数(signal count) 衡量的是线程通过数组得到信号的频度

    RW-shared spins:#这行显示读写的共享锁的计数器

    RW-excl spins:#这行显示读写的排他锁的计数器

    RW-sx spins:#这行显示共享排它锁计数器

    ④. TRANSACTIONS
    包含Innodb 事务(transactions)的统计信息,还有当前活动的事务列表。
    在这里插入图片描述
    transaction id: 这个ID是一个系统变量随时每次新的transaction产生而增加。

    Purge done:正在进行清空(purge)操作的transaction ID。你可以通过查看第transaction id和第Purge done ID的区别,明白没有被purge的事务落后的情况。

    History listlength:记录了undo spaces内unpurged的事务的个数。

    ⑤. FILE I/O

    显示了I/O Helper thread的状态,包括一些统计信息
    在这里插入图片描述
    pending operations, pending的log和buffer pool thread的fsync()调用

    17731 OS file:行显示了reads, writes, and fsync()调用次数。

    0.00 reads/s…… : 显示了每秒的统计信息

    备注:“aio”表示“ 异步I/O(asynchronous I/O).”

    ⑥. INSERT BUFFER AND ADAPTIVE HASH INDEX
    在这里插入图片描述
    Ibuf:insertbuffer的一些信息,包括free list, segment size

    Hash table:显示了hash table的一些信息最后一行显示了每秒进行了多少次hash搜索,以及非hash搜索

    ⑦. LOG
    在这里插入图片描述
    Log sequence number表示的是redo log buffer中的lsn

    Log flushed up to表示的是redo log file中的lsn

    Pages flushed up to表示的缓冲池最旧脏页的lsn

    Last checkpoint at 指的就是最近一个物理页刷新到磁盘时,它的fil_page_lsn的变量值。

    ⑧. BUFFER POOL AND MEMORY
    在这里插入图片描述
    Pages read ahead:显示了每秒线性预读跟随机预读的次数

    备注:InnoDB 提供了两种预读的方式,一种是 Linear read ahead,由参数innodb_read_ahead_threshold控制,当你连续读取一个 extent 的 threshold 个 page 的时候,会触发下一个 extent 64个page的预读。另外一种是Random read-ahead,由参数innodb_random_read_ahead控制,当你连续读取设定的数量的page后,会触发读取这个extent的剩余page。InnoDB 的预读功能是使用后台线程异步完成。

    ⑨. ROW OPERATIONS

    在这里插入图片描述

    0 queries inside InnoDB, 0 queries in queue:显示了有多少线程在Innodb内核

    read views open inside InnoDB:显示了有多少read view被打开了,一个read view是一致性保证的MVCC “snapshot”

    备注:innodb多版本并发(MVCC)通过read view来确定一致性读时的数据库snapshot, innodb的read view确定一条记录能否看到,

    在RC隔离级别下,是每个SELECT都会获取最新的read view;

    在RR隔离级别下,则是当事务中的第一个SELECT请求才创建read view

    5. SHOW STATUS LIKE ‘innodb_row_lock_%’; — 锁性能状态
    在这里插入图片描述
    解释如下:

    Innodb_row_lock_current_waits:当前等待锁的数量

    Innodb_row_lock_time:系统启动到现在、锁定的总时间长度

    Innodb_row_lock_time_avg:每次平均锁定的时间

    Innodb_row_lock_time_max:最长一次锁定时间

    Innodb_row_lock_waits:系统启动到现在、总共锁定次数

    展开全文
  • Mysql 性能测试分析与优化 基础操作: linuxmysql 登录: mysql -h 192.168.1.149 -u root-p  -h:指定所连接的数据库服务器位置,可以是IP地址,也可以是服务器域名。  -u:指定连接数据库服务器使用的...

    Mysql 性能测试分析与优化

    基础操作:

    linux mysql 登录:  mysql -h 192.168.1.149 -u root-p

      -h:指定所连接的数据库服务器位置,可以是IP地址,也可以是服务器域名。
            -u:指定连接数据库服务器使用的用户名,例如root为管理员用户具有所有权限。
             -p:连接数据库服务器使用的密码,但-p和其后的参数之间不要有空格。最后是在该参数后直接回车,然后以密文的形式输入密码。

     

    基础知识:

    1、服务端发送一条查询给服务器

    2、服务器先检查查询缓存,如果命中缓存,则立刻返回存储中的结果。否则进入下一阶段

    3、服务器进行SQL解析、预处理,再由优化器生成对应的执行计划

    4MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询

    5、将结果返回给客户端


     

    MySQL常见性能问题

    连接数不够用

    不合理的SQL导致CPU

    慢查询

    缓存命中率低

    死锁

    系统架构及实现对性能的影响

    Sql对性能的影响

     

    一 连接数不够

    1.1 监控指标

    连接数使用率(connections_used_ratio)

    mysql > show global status like 'Max_used_connections';
    mysql > show variables like 'max_connections%';
    使用率 = Max_used_connections ÷ max_connections
    判断方法
    10% < 连接数使用率 < 85% . <10%,则表明最大连接数上限可能设置过高;若>85%,则表明连接数 不够用,或连接建立未释放.

     

    连接数不够的原因

    1. 连接数过低或过高(默认100),均由配置不合理导致

    修改连接数:  

    1  命令修改:msyql > set global max_connections = 1024;

    2  配置修改(/etc/my.cnf   修改后需要重启):max_connections = 1024

    2. 慢查询导致IO阻塞,导致连接长时间不释放;

    3. SQL执行完,连接未释放;

     

    不合理的SQL导致CPU高

    逻辑上存在死循环或者SQL执行会发生大量表扫描,均会导致CPU.

    监控方式:

    msyql > show global status like 'handler_read%';

    msyql > show global status like 'com_select';

    表扫描数 = Handler_read_rnd_next ÷ Com_select

    --若 表扫描数 > 4000,则表明存在大量全表扫描,即索引建立不合理或未正确使用索引;若 表扫描数 < 4000,且CPU高,则建议查看存储过程中循环分支是否进入死循环.

     

    慢查询

    执行耗时超过预期阈值的SQL,均为慢查询. 阈值最大不应超过1s,且原则上应该不超过100ms , 具体根据业务来定

    监控方式

    msyql > show global status like 'Slow_queries’;

     

    出现慢查询的原因:

    1 索引不合理或没有索引

    2 查询数据量大

    3 存在锁

    当存在锁时,可以修改锁的等待时间

    my.cnf

    slow_query_log=1

    slow_query_log_file=/data/mysqldata/slow-query.log

    long-query-time=1

    log-queries-not-using-indexes

    锁等待时间:mysql > show status like '%lock%';

     

     

    缓存命中率低

    什么是查询缓存(QueryCache)

    当查询接收到一个和之前同样的查询,服务器将会从查询缓存中检索结果,而不是再次分析和执行相同的查询,这样就能大大提高查询性能 .

    开启:

    mysql> set session query_cache_type=on;

    关闭:

    mysql> set session query_cache_type=off;

     

    监控方法

    mysql > show global status like 'Com_select';

    mysql > show global status like 'QCache%';

    查询缓存命中率= Qcache_hits ÷ (Qcache_hits+ Com_select)

    – 判断方法

    若查询缓存命中率 < 95%,则表明缓存大小可能设置过低(query_cache_sizequery_cache_limit)或存在大量缓存碎片;

    死锁

    死锁:两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象.

    监控方法

    1. 启用 innodb_status_file

    /etc/my.cnf 添加如下:

    [mysqld]

    innodb_status_file =1

    /var/lib/mysql/下查看.err 日志

     

    监控方法

    2. 启用 innodb_monitor

    建立监视表:

    mysql>use mysql;

    mysql>create table innodb_monitor ( id int ) engine = innodb;

    mysql>show innodb status\G;

     

    MySQL

    对于MySQL,按照锁的粒度划分,将数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)、页级锁.

    MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种.

    如果一条SQL语句操作(UPDATEDELETE)了主键索引,MySQL就会锁定其主键索引;如果操作了非主键索引,就会先锁定该非主键索引,再锁定相关的主键索引.

     

    系统架构及实现对性能的影响

    以下几类数据都是不适合在数据库中存放的:

    二进制多媒体数据

    流水队列数据

    超大文本数据

     

     mysql 性能优化

    1. 为查询缓存优化你的查询 

    大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。 

    这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例: 

    // 查询缓存不开启 $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

    // 开启查询缓存 $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

    上面两条SQL语句的差别就是 CURDATE() MySQL的查询缓存对这个函数不起作用。所以,像 NOW() RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。 


    2. EXPLAIN 你的 SELECT 查询 

    使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。 

    EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。 

    挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:

    当我们为 group_id 字段加上索引后:

     


    3. 当只要一行数据时使用 LIMIT 1 

    当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。 

    在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。 

    下面的示例,只是为了找一下是否有中国的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1 
    我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 16 行。查看rows列可以让我们找到潜在的性能问题。 

     

    // 没有效率的: $r = mysql_query("SELECT * FROM user WHERE country = 'China'"); if (mysql_num_rows($r) > 0) { // ... }

    // 有效率的: $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1"); if (mysql_num_rows($r) > 0) { // ...

    }

     

     

    4. 为搜索字段建索引 

    索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。

    从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”,一个是建了索引,一个是没有索引,性能差了4倍左右。 

    另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%'”,索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引(比如说:搜索关键词或是Tag什么的) 



    5. Join表的时候使用相当类型的例,并将其索引 

    如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化JoinSQL语句的机制。 

    而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT 字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样) 

    // state中查找company $r = mysql_query("SELECT company_name FROM users

    LEFT JOIN companies ON (users.state = companies.state)

    WHERE users.id = $user_id");

    // 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。

     

    6. 千万不要 ORDER BY RAND() 

    想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。 

    如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序) 

    下面的示例是随机挑一条记录 

     

    // 千万不要这样做: $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

    // 这要会更好: $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d[0] - 1);

    $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

     

     

    7. 避免 SELECT * 

    从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。 

    所以,你应该养成一个需要什么就取什么的好的习惯。 

     

    // 不推荐 $r = mysql_query("SELECT * FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome {$d['username']}";

    // 推荐 $r = mysql_query("SELECT username FROM user WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Welcome {$d['username']}";

     

     

    8. 永远为每张表设置一个ID 

    我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的 AUTO_INCREMENT标志。 

    就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。 

    而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区…… 

    在这里,只有一个情况是例外,那就是关联表外键,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做外键。比如:有一个学生表有学生的ID,有一个课程表有课程ID,那么,成绩表就是关联表了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID外键其共同组成主键。 

     

    9. 使用 ENUM 而不是 VARCHAR 

    ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。 

    如果你有一个字段,比如性别国家民族状态部门,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR 

    MySQL也有一个建议(见第十条)告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时,这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。 

     

    10. PROCEDURE ANALYSE() 取得建议 

    PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。 

    例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。 

    phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些建议

    一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。 

     

    11. 尽可能的使用 NOT NULL 

    除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议,请往下看。 

    首先,问问你自己“Empty”“NULL”有多大的区别(如果是INT,那就是0NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在 Oracle 里,NULL Empty 的字符串是一样的!

    不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。 

    下面摘自MySQL自己的文档: 

    “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.” 

    12. Prepared Statements 

    Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是安全问题。 

    Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式攻击。当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候,这样的问题会好一些。 

    在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。 

    虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。 

    当然,也有一些情况下,我们需要避免使用Prepared Statements,因为其不支持查询缓存。但据说版本5.1后支持了。 

    PHP中要使用prepared statements,你可以查看其使用手册:mysqli 扩展 或是使用数据库抽象层,如: PDO. 

     

    // 创建 prepared statement if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {

    // 绑定参数 $stmt->bind_param("s", $state);

    // 执行 $stmt->execute();

    // 绑定结果 $stmt->bind_result($username);

    // 移动游标 $stmt->fetch();

    printf("%s is from %s\n", $username, $state);

    $stmt->close();

    }

     

     

    13. 无缓冲的查询 

    正常的情况下,当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。 

    关于这个事情,在PHP的文档中有一个非常不错的说明: mysql_unbuffered_query() 函数: 

    “mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed.” 

    上面那句话翻译过来是说,mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存,尤其是那些会产生大量结果的查询语句,并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。 

    然而,这会有一些限制。因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且, mysql_num_rows() mysql_data_seek() 将无法使用。所以,是否使用无缓冲的查询你需要仔细考虑。 

     

    14. IP地址存成 UNSIGNED INT 

    很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2 

    我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形。 

    而你的查询,你可以使用 INET_ATON() 来把一个字符串IP转成一个整形,并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long() long2ip() 
    1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id"; 

     

    15. 固定长度的表会更快 

    如果表中的所有字段都是固定长度的,整个表会被认为是 “static” “fixed-length”。 例如,表中没有如下类型的字段: VARCHARTEXTBLOB。只要你包括了其中一个这些字段,那么这个表就不是固定长度静态表了,这样,MySQL 引擎会用另一种方法来处理。 

    固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。 

    并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。 

    使用垂直分割技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。 

     

    16. 垂直分割 

    垂直分割是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖) 

    示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。 

    示例二: 你有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户 ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。 

    另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。 

     

    17. 水平分割

    例:QQ的登录表。假设QQ的用户有100亿,如果只有一张表,每个用户登录的时候数据库都要从这100亿中查找,会很慢很慢。如果将这一张表分成100份,每张表有1亿条,就小了很多,比如qq0,qq1,qq1...qq99表。

    用户登录的时候,可以将用户的id%100,那么会得到0-99的数,查询表的时候,将表名qq跟取模的数连接起来,就构建了表名。比如123456789用户,取模的89,那么就到qq89表查询,查询的时间将会大大缩短。

    这就是水平分割。

    18.  拆分大的 DELETE INSERT 语句 

    如果你需要在一个在线的网站上去执行一个大的 DELETE INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。 

    Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。 

    如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上掛了。 

    所以,如果你有一个大的处理,你定你一定把其拆分,使用 LIMIT 条件是一个好的方法。下面是一个示例:

     

    while (1) { //每次只做1000mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000"); if (mysql_affected_rows() == 0) { // 没得可删了,退出! break;

    } // 每次都要休息一会儿 usleep(50000);

    }

     

     

    19. 越小的列会越快 

    对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。 

    参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 

    如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。 

    当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(20091106 日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。 

     

    20. 选择正确的存储引擎 

    MySQL 中有两个存储引擎 MyISAM InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。 

    MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。 

    InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持行锁,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。 

    下面是MySQL的手册 

    * target=”_blank”MyISAM Storage Engine 
    * InnoDB Storage Engine 

    21. 使用一个对象关系映射器(Object Relational Mapper 

    使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨。一个ORM可以做的所有事情,也能被手动的编写出来。但是,这需要一个高级专家。 

    ORM 的最重要的是“Lazy Loading”,也就是说,只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用,因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。 

    ORM 还可以把你的SQL语句打包成一个事务,这会比单独执行他们快得多得多。 

    目前,个人最喜欢的PHPORM是:Doctrine 

     

    22. 小心永久链接 

    永久链接的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了。而且,自从我们的Apache开始重用它的子进程后——也就是说,下一次的HTTP请求会重用Apache的子进程,并重用相同的 MySQL 链接。 

    * PHP手册:mysql_pconnect() 

    在理论上来说,这听起来非常的不错。但是从个人经验(也是大多数人的)上来说,这个功能制造出来的麻烦事更多。因为,你只有有限的链接数,内存问题,文件句柄数,等等。 

    而且,Apache 运行在极端并行的环境中,会创建很多很多的了进程。这就是为什么这种永久链接的机制工作地不好的原因。在你决定要使用永久链接之前,你需要好好地考虑一下你的整个系统的架构。

     

    23. 其他要点:

    1)存放图片、文件等大文件用文件系统存储。数据库只存储路径,图片和文件存放在文件系统,甚至单独存放在一台服务器(图床)。

    2)数据参数配置。

    最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大:

    innodb_additional_mem_pool_size=64M

    innodb_buffer_pool_size=1G

    对于MyISAM,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定该调整哪些参数。


    展开全文
  • linux mysql5.6性能调优

    2018-02-09 18:48:45
    1、max_connections = 1500 最大连接数,根据数据库服务器性能来调整大小2、skip-name-resolve 关闭外部链接查询dns功能,提高数据库性能3、关闭日志 生产环境关闭提高性能测试环境可以打开4、lower_case_table...

    1、max_connections = 1500

        最大连接数,根据数据库服务器性能来调整大小

    2、skip-name-resolve

        关闭外部链接查询dns功能,提高数据库性能

    3、关闭日志

        生产环境关闭提高性能,测试环境可以打开

    4、lower_case_table_names = 1 

        linux上是表名大小写敏感的,看需要关闭,1为关闭,默认为0

    展开全文
  • 安装:简单,装了mysql就有了作用:模拟并发测试数据库性能。优点:简单,容易使用。不足:不能指定生成的数据规模,测试过程不清楚针对十万级还是百万级数据做的测试,感觉不太适合做综合测试,比较适合针对既有...

    一.Mysql数据库的几种压力测试工具:

     

    1.mysqlslap

     

    安装:简单,装了mysql就有了

    作用:模拟并发测试数据库性能。

    优点:简单,容易使用。

    不足:不能指定生成的数据规模,测试过程不清楚针对十万级还是百万级数据做的测试,感觉不太适合做综合测试,比较适合针对既有数据库,对单个sql进行优化的测试。

     

    2.tpcc-mysql

     

    安装:
    如果从原网站上下载源码比较麻烦,需要工具、注册、生成证书等。这里提供一个下载包http://blog.chinaunix.net/blog/downLoad/fileid/8532.html

    作用:测试mysql数据库的整体性能

    优点:符合tpcc标准,有标准的方法,模拟真实的交易活动,结果比较可靠。

    不足:不能单独测试读或者写的性能,对于一些以查询为主或者只写的应用,就没有这么大的意义了。

     

    3.sysbench

     

    安装:下载rpm包:ck-0.5.2-2.el7.x86_64.rpm,luajit-2.0.4-3.el7.x86_64.rpm,sysbench-1.0.9-2.el7.x86_64.rpm

    作用:模拟并发,可以执行CPU/内存/线程/IO/数据库等方面的性能测试。数据库目前支持MySQL/Oracle/PostgreSQL

    优点:可以指定测试数据的规模,可以单独测试读、写的性能,也可以测试读写混合的性能。

    不足:测试的时候,由于网络原因,测试的非常慢,但是最终给的结果却很好,并发支持很高。

     

    二.实验环境(rhel7.3版本)

    1.selinux和firewalld状态为disabled

     

    2.各主机信息如下:(因为server1,server2,server3之前是做过MHA架构的,最后的结果是:server2是主库。因为从库上进行操作,会导致主从不一致,所以这里的实验环境是server2,而不是server1。)

     

    主机 ip
    server2(装有mysql数据库的主机) 172.25.83.2


     

    三.Mysql数据库的压力测试过程演示:Sysbench-OLTP

     

    在oltp测试中,基本分为四个阶段:

     

    1.安装sysbench:

     

    下载sysbench需要的rpm包:ck-0.5.2-2.el7.x86_64.rpm,sysbench-1.0.9-2.el7.x86_64.rpm,luajit-2.0.4-3.el7.x86_64.rpm。并使用yum进行安装

     

     

    安装完成之后,会生成相应的目录和文件。其中最为重要的是/usr/share/sysbench/tests/include/oltp_legacy下的oltp.lua文件

     

     

    2.初始化数据:

     

    <1>登陆数据库,创建压力测试需要的数据库sbtest:

     

    [root@server2 ~]# mysql -uroot -pXinjiaojiao+523
    

     

     

    <2>在该主机(172.25.83.2)上进行sysbench数据生成,在sbtest库下生成sbtest表,共100W记录。

     

    [root@server2 ~]# sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=1800 --mysql-user=root --mysql-socket=/var/lib/mysql/mysql.sock --mysql-password=Xinjiaojiao+523 --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex prepare

     

    上面命令中各参数的含义如下:

    • --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua        #oltp.lua文件所在的绝对路径
    • --oltp-table-size=1000000     #每个表产生的记录行数;还有一个参数--oltp-tables-count=用来指定产生表的数量。这里没有指定,表示默认产生一张表。
    • --oltp-read-only=off       #表示不止产生只读SQL,也就是使用oltp.lua时会采用读写混合模式。默认是off,如果设置为on,则不会产生update,delete,insert的sql。
    • --init-rng=on        #在测试开始时是初始化随机数发生器。默认是off。
    • --num-threads=16     #并发线程数,可以理解为模拟的客户端并发连接数
    • --max-requests=0     #压力测试产生请求的总数,如果以下面的max-time来记,这个值设为0
    • --oltp-dist-type=uniform     #指定随机取样类型,可选值有 uniform(均匀分布), Gaussian(高斯分布), special(空间分布)。默认是special
    • --max-time=180     #压力测试的持续时间,这里是180s(3分钟)。
    • --mysql-user=root   #数据库的登陆名
    • --mysql-socket=/var/lib/mysql/mysql.sock    #数据库的执行入口
    • --mysql-password=Xinjiaojiao+523      #数据库的登陆密码
    • --db-driver=mysql      #数据库类型,指定数据库驱动程序为mysql
    • --mysql-table-engine=innodb     #设置mysql中表的引擎为innodb类型的,除此之外,还有一个选项为myisam。
    1. isam是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于,它不支持事务处理,也不能够容错:如果你的硬盘崩溃了,那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里,那就必须经常备份你所有的实时数据,通过其复制特性,MySQL能够支持这样的备份应用程序。MyIASM是IASM表的新版本
    2. innodb它提供了事务控制能力功能,它确保一组命令全部执行成功,或者当任何一个命令出现错误时所有命令的结果都被回退,可以想像在电子银行中事务控制能力是非常重要的。支持COMMIT、ROLLBACK和其他事务特性。最新版本的Mysql已经计划移除对BDB的支持,转而全力发展InnoDB。
    • --oltp-test-mode=complex     #执行模式,这里是事务式的。可选值有simple,complex,nontrx。默认是complex
    1. simple:简单查询,SELECT c FROM sbtest WHERE id=N
    2. complex (advanced transactional):事务模式在开始和结束事务之前加上begin和commit, 一个事务里可以有多个语句,如点查询、范围查询、排序查询、更新、删除、插入等,并且为了不破坏测试表的数据,该模式下一条记录删除后会在同一个事务里添加一条相同的记录。
    3. nontrx (non-transactional):与simple相似,但是可以进行update/insert等操作,所以如果做连续的对比压测,你可能需要重新cleanup,prepare。
    • prepare     #注意最后一行。一项测试开始前需要用prepare来准备好表和数据;run执行真正的压测;cleanup用来清除数据和表。

     

     

    3.执行测试

     

    在主库上(172.25.83.2)进行压力测试,持续时间为3分钟,产生大量的binlog。

     

    [root@server2 ~]# sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=180 --mysql-user=root --mysql-socket=/var/lib/mysql/mysql.sock --mysql-password=Xinjiaojiao+523 --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex run

     

     

     

    结果解读

     

    WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
    WARNING: --num-threads is deprecated, use --threads instead
    WARNING: --max-time is deprecated, use --time instead
    sysbench 1.0.9 (using system LuaJIT 2.0.4)
    
    Running the test with following options:
    Number of threads: 16
    Initializing random number generator from current time
    
    
    Initializing worker threads...
    
    Threads started!
    
    SQL statistics:
        queries performed:
            read:                            388976           //总select数量
            write:                           111114           //总update、insert、delete语句数量
            other:                           55560             //commit、unlock tables以及其他mutex的数量
            total:                           555650            //全部总数  
        transactions:                        27776  (154.23 per sec.)  //精确的说是这一项后面的TPS (即每秒事务数)。但如果使用了-oltp-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps)        
        queries:                             555650 (3085.40 per sec.)     ////精确的说是这一项后面的QPS (即每秒请求、查询次数)。
        ignored errors:                      8      (0.04 per sec.)      //忽略的错误数
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:        //一些统计结果
        total time:                          180.0876s     //即max-time指定的压测实际
        total number of events:              27776         //总的事件数,一般与transactions相同
    
    Latency (ms):
             min:                                 24.15    //最小耗时
             avg:                                103.72    //95%的语句的平均响应时间  
             max:                                557.76    //最大耗时
             95th percentile:                    150.29
             sum:                            2881056.92
    
    Threads fairness:           //线程的稳定性
        events (avg/stddev):           1736.0000/5.16     //事件(平均值/偏差) 
        execution time (avg/stddev):   180.0661/0.01      //执行时间(平均值/偏差)
    

     

    我们一般关注的用于绘图的指标主要有:

    • response time avg: 平均响应时间。(后面的95%的大小可以通过--percentile=98的方式去更改)
    • transactions: 精确的说是这一项后面的TPS 。但如果使用了-oltp-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps)
    • read/write requests: 用它除以总时间,得到吞吐量QPS
    • 当然还有一些系统层面的cpu,io,mem相关指标

    sysbench还可以对文件系统IO测试,CPU性能测试,以及内存分配与传输速度测试,这里就不介绍了。

    总结起来sysbench的缺点就是,模拟的表结构太简单,不像tpcc-mysql那样完整的事务系统。但对于性能压测对比还是很有用的,因为sysbench使用的环境参数限制是一样的。

     

     

    4.清理数据:(在这儿而言,就是删除了初始化数据阶段产生的数据)

     

    [root@server2 ~]# sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 --oltp-read-only=off --init-rng=on --num-threads=16 --max-requests=0 --oltp-dist-type=uniform --max-time=180 --mysql-user=root --mysql-socket=/var/lib/mysql/mysql.sock --mysql-password=Xinjiaojiao+523 --db-driver=mysql --mysql-table-engine=innodb --oltp-test-mode=complex cleanup

     

    展开全文
  • Oracle表示,按照内部的标准,在Linux 上,MySQL 5.5的读写速度比5.1版本提升了360%。在Windows Server机器上,这方面性能的提升超过了1500%。MySQL 5.5版本的吞吐量更高。如果真如Oracle所言,MySQL5.5相对于MySQL...
  • 这几天,笔者在了解有关性能测试方面的一些知识,看到了MySQL性能测试,觉得有点意思,和大家一起学习一下.也给自己做了笔记,以后忘了或者想来看了,可以复习.配置配置以下配置选项开启记录慢查询和没有使用索引的查询...
  • 通过网上收集和对LINUX服务器性能的分析和体验,整理了几个比较实用,功能比较强大的性能测试工具供亲们下载,虽然网络上都对每个工具做了详细的功能介绍和文档说明,但分得很散,所以, 在此本人将各个比较实用的...
  • 1. 同主频Xeon下64位性能反而不如32位,Opteron下64bit性能提高大约30%左右,此测试可能与编译器优化有关。2. 双核比双cpu单核比较性能大约提高6%3. 超线程开启性能下降10%左右(MySQL read)4. InnoDB性能大约是...
  • MySQL性能测试大总结

    2011-06-24 11:16:00
    以下的文章主要是介绍MySQL性能测试的结论,我们大家都知道MySQL数据库在实际实用度主要是取决于MySQL数据库的性能,以下的文章主要就是对MySQL性能测试的一个总结,望你看完之后会有所收获。好像是zdnet的实验室做...
  • https://www.cnblogs.com/shanyou/p/3256906.html
  •   网上有写的好的模板, 直接复链接了:  1. http://blog.csdn.net/mituan1234567/article/details/45247989  2. https://msdn.microsoft.com/zh-cn/library/dd380714.aspx
  • 通过根据服务器目前状况,修改Mysql的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。   2、服务器参数: 32G内存、4个CPU,每个CPU 8核。 3、MySQL目前安装状况。  MySQL目前...
  • 蒸汽机的改进不是一蹴而就的,MySQL性能的改进也是贯穿整个MySQL发展史的。MySQL之父Monty在1981年写了MySQL的第一行代码以后,在开源的帮助下MySQL成长为目前最流行的开源数据库,同样其也凝聚了非常多的开发者、...
  • mysql&redis性能测试

    2018-11-30 10:03:44
    测试环境 机器: Redis: Redis server v=3.0.5 sha=00000000:0 malloc=libc bits=64 build=3145a5e1a81fd3f7 Mysql: Server version: 5.7.9 Homebrew 提示:以下测试结果均在单线程环境下运行 redis get set ...
  • 首先下载mysql 地址:https://dev.mysql.com/downloads/mysql/ 下载社区版mysql 下载完成后;ftp上传到服务器 /user/local/目录...[root@instance_4dbde0 local]# tar -zxvf mysql-5.7.20-linux-glibc2.12-x86...
  • MySQL8.0出来已经有很长时间了,目前最新版本是MySQL8.0.20 ,那么MySQL8.0到底比MySQL5.7 性能有多大的提升呢,今天就做了一组压力测试来实际测试一下。为了使结果更具有说服性,测试环境是完全一样,具体测试结果...
  • Linux yum 升级mysql

    2020-04-15 16:23:52
    有一些虚拟机、云主机提供商仍然使用的是老版本的安装套件。预装的应用软件版本很低。 ...而最新的MySQL版本在性能、功能、安全性等方面都有了很多的改进。...有相关测试数据说明从5.1到5.5+,MySQL性能会有明显
  • Linuxmysql操作

    2019-05-07 17:28:21
      本文转自:...  1.linux下启动mysql的命令:mysqladmin start/ect/init.d/mysql start (前面为mysql的安装路径) 2.linux下重启mysql的命令:mysqladmin re...
1 2 3 4 5 ... 20
收藏数 72,954
精华内容 29,181