您的位置:68399皇家赌场 > 虚拟主机 > 利用sysbench实行MySQL OLTP基准测试

利用sysbench实行MySQL OLTP基准测试

发布时间:2019-05-03 21:45编辑:虚拟主机浏览(81)

     

      *  We usually conifuge the parameter in layer of memory,and then load them into layer of runtime to make it take effect.In the end,we should save them to disk for durability storage.It also provides some simple syntax to transfer configurations between those layers as below:*

        sysbench is a common tool used in database bentchmark to test MySQL database(of cource,it supports PostgreSQL and Oracle,too).It provide many test case through several built-in lua scripts by specifying option of "testname".We can use the tool to get performance statistics about CPU,IO,Memory,etc.

    Web page analysis

    Page Speed - Open source Firefox/Firebug Add-on that evaluates the performance of web pages and gives suggestions for improvement.

    Chrome Developer Tools - Tools included in Google Chrome that let you edit, debug, and monitor CSS, HTML, and JavaScript live in any web page. You can also use them to optimize web page performance by profiling CPU and memory usage.

    Speed Tracer - Google Chrome extension that helps you debug performance problems with AJAX applications.

     

    Preface

    • sysbench is more powerful than mysqlslap(built-in program of MySQL).
    • Be ware of  the diffrence between database benchmark test and presure test.
    • There're also some other tools can be used to test database performance such as mysql-tpcc,YCSB,etc.
    • Database benchmark really helps us to have a explicit overview about our newly arrived server.More test is necessary.

    Performance benchmarking

    httperf - Tool for generating HTTP workloads and measuring web server performance, and constructing micro- and macro-level benchmarks.

    mon.itor.us - Provides a personalized Ajax dashboard interface, checks server performance and availability, generates uptime reports, tracks visitors, checks CPU, memory and other systems resources, and alerts when it detects abnormalities.

    Pylot - Open source tool for testing the performance and scalability of web services. It runs HTTP load tests, verifies server responses, and produces reports with metrics.

    Wbox - Performs various tests, including page load benchmarking, web server and web application stress testing, and verifies correct configuration of virtual domains configuration, redirects, and HTTP compression.

    Preface

    The comparison with other popular middleware tools.

    **Install sysbench.**

    Web page analysis

    AOL Page Test - Open source tool for measuring and analyzing web page performance using Internet Explorer.

    BrowserMob - Tool for website performance monitoring and alerting.

    IBM Page Detailer - Graphical tool that assesses web page performance and provides details include the timing, size, and identity of each item in a page.

    IntroSpectrum - Web-based performance monitor which simulates users using actual web browsers.

    Microsoft VRTA - Tool that visualizes web page download, identifies areas for performance improvements, and recommends solutions.

    MySpace Performance Tracker - Internet Explorer browser plugin that helps improve web page performance by capturing and measuring possible bottlenecks.

    WebPagetest - Tool that provides a waterfall of your page load performance as well as a comparison against an optimization checklist.

    Yahoo! YSlow - Firefox/Firebug Add-on that analyzes web pages and suggests ways to improve their performance, based on a set of rules for high performance web pages.

     

     

     

     

    [原创]Web开垦测试帮忙理工程师具介绍

    **Main parameter(they're almost entire)**

      1 [root@zlm2 08:05:09 ~]
      2 #rpm -ql proxysql
      3 /etc/init.d/proxysql
      4 /etc/proxysql.cnf //This is the configuration file of ProxySQL.
      5 /usr/bin/proxysql
      6 /usr/share/proxysql/tools/proxysql_galera_checker.sh //This is the script to check the status of hostgroups.
      7 /usr/share/proxysql/tools/proxysql_galera_writer.pl
      8 
      9 [root@zlm2 08:08:13 ~]
     10 #cat /etc/proxysql.cnf
     11 #file proxysql.cfg
     12 
     13 ########################################################################################
     14 # This config file is parsed using libconfig , and its grammar is described in:        
     15 # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar 
     16 # Grammar is also copied at the end of this file                                       
     17 ########################################################################################
     18 
     19 ########################################################################################
     20 # IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:                             
     21 ########################################################################################
     22 # On startup, ProxySQL reads its config file (if present) to determine its datadir. 
     23 # What happens next depends on if the database file (disk) is present in the defined
     24 # datadir (i.e. "/var/lib/proxysql/proxysql.db").
     25 #
     26 # If the database file is found, ProxySQL initializes its in-memory configuration from 
     27 # the persisted on-disk database. So, disk configuration gets loaded into memory and 
     28 # then propagated towards the runtime configuration. 
     29 #
     30 # If the database file is not found and a config file exists, the config file is parsed 
     31 # and its content is loaded into the in-memory database, to then be both saved on-disk 
     32 # database and loaded at runtime.
     33 #
     34 # IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
     35 #            ProxySQL initializes its in-memory configuration from the persisted on-disk
     36 #            database ONLY. In other words, the configuration found in the proxysql.cnf
     37 #            file is only used to initial the on-disk database read on the first startup.
     38 #
     39 # In order to FORCE a re-initialise of the on-disk database from the configuration file 
     40 # the ProxySQL service should be started with "service proxysql initial".
     41 #
     42 ########################################################################################
     43 
     44 datadir="/var/lib/proxysql" //This is the position of SQLite database of ProxySQL.
     45 
     46 admin_variables=
     47 {
     48     admin_credentials="admin:admin" //The default user/password is admin/admin.
     49 #    mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
     50     mysql_ifaces="0.0.0.0:6032" //The default admin port is 6032.
     51 #    refresh_interval=2000
     52 #    debug=true
     53 }
     54 
     55 mysql_variables=
     56 {
     57     threads=4
     58     max_connections=2048
     59     default_query_delay=0
     60     default_query_timeout=36000000
     61     have_compress=true
     62     poll_timeout=2000
     63 #    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
     64     interfaces="0.0.0.0:6033" //The default service port is 6033.
     65     default_schema="information_schema"
     66     stacksize=1048576
     67     server_version="5.5.30"
     68     connect_timeout_server=3000
     69 # make sure to configure monitor username and password
     70 # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
     71     monitor_username="monitor"
     72     monitor_password="monitor"
     73     monitor_history=600000
     74     monitor_connect_interval=60000
     75     monitor_ping_interval=10000
     76     monitor_read_only_interval=1500
     77     monitor_read_only_timeout=500
     78     ping_interval_server_msec=120000
     79     ping_timeout_server=500
     80     commands_stats=true
     81     sessions_sort=true
     82     connect_retries_on_failure=10
     83 }
     84 
     85 
     86 # defines all the MySQL servers
     87 mysql_servers =
     88 (
     89 #    {
     90 #        address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
     91 #        port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
     92 #        hostgroup = 0            # no default, required
     93 #        status = "ONLINE"     # default: ONLINE
     94 #        weight = 1            # default: 1
     95 #        compression = 0       # default: 0
     96 #   max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
     97 #    },
     98 #    {
     99 #        address = "/var/lib/mysql/mysql.sock"
    100 #        port = 0
    101 #        hostgroup = 0
    102 #    },
    103 #    {
    104 #        address="127.0.0.1"
    105 #        port=21891
    106 #        hostgroup=0
    107 #        max_connections=200
    108 #    },
    109 #    { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
    110 #    { address="127.0.0.1" , port=21892 , hostgroup=1 },
    111 #    { address="127.0.0.1" , port=21893 , hostgroup=1 }
    112 #    { address="127.0.0.2" , port=3306 , hostgroup=1 },
    113 #    { address="127.0.0.3" , port=3306 , hostgroup=1 },
    114 #    { address="127.0.0.4" , port=3306 , hostgroup=1 },
    115 #    { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
    116 )
    117 
    118 
    119 # defines all the MySQL users
    120 mysql_users:
    121 (
    122 #    {
    123 #        username = "username" # no default , required
    124 #        password = "password" # default: ''
    125 #        default_hostgroup = 0 # default: 0
    126 #        active = 1            # default: 1
    127 #    },
    128 #    {
    129 #        username = "root"
    130 #        password = ""
    131 #        default_hostgroup = 0
    132 #        max_connections=1000
    133 #        default_schema="test"
    134 #        active = 1
    135 #    },
    136 #    { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
    137 )
    138 
    139 
    140 
    141 #defines MySQL Query Rules
    142 mysql_query_rules:
    143 (
    144 #    {
    145 #        rule_id=1
    146 #        active=1
    147 #        match_pattern="^SELECT .* FOR UPDATE$"
    148 #        destination_hostgroup=0
    149 #        apply=1
    150 #    },
    151 #    {
    152 #        rule_id=2
    153 #        active=1
    154 #        match_pattern="^SELECT"
    155 #        destination_hostgroup=1
    156 #        apply=1
    157 #    }
    158 )
    159 
    160 scheduler=
    161 (
    162 #  {
    163 #    id=1
    164 #    active=0
    165 #    interval_ms=10000
    166 #    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
    167 #    arg1="0"
    168 #    arg2="0"
    169 #    arg3="0"
    170 #    arg4="1"
    171 #    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
    172 #  }
    173 )
    174 
    175 
    176 mysql_replication_hostgroups=
    177 (
    178 #        {
    179 #                writer_hostgroup=30
    180 #                reader_hostgroup=40
    181 #                comment="test repl 1"
    182 #       },
    183 #       {
    184 #                writer_hostgroup=50
    185 #                reader_hostgroup=60
    186 #                comment="test repl 2"
    187 #        }
    188 )
    189 
    190 
    191 
    192 
    193 # http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
    194 #
    195 # Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here. 
    196 #
    197 # configuration = setting-list | empty
    198 #
    199 # setting-list = setting | setting-list setting
    200 #     
    201 # setting = name (":" | "=") value (";" | "," | empty)
    202 #     
    203 # value = scalar-value | array | list | group
    204 #     
    205 # value-list = value | value-list "," value
    206 #     
    207 # scalar-value = boolean | integer | integer64 | hex | hex64 | float
    208 #                | string
    209 #     
    210 # scalar-value-list = scalar-value | scalar-value-list "," scalar-value
    211 #     
    212 # array = "[" (scalar-value-list | empty) "]"
    213 #     
    214 # list = "(" (value-list | empty) ")"
    215 #     
    216 # group = "{" (setting-list | empty) "}"
    217 #     
    218 # empty =
    

     

    Development

    Cuzillion - Tool for quickly constructing web pages to see how components interact and how behavior differs across browsers, sometimes in unexpected ways. Also lets you share sample pages with others.

    Hammerhead - Firebug Add-on for measuring the load time of web pages.

    OOCSS - Development environment and framework for creating fast, reusable CSS objects and modules.

     

    3. Test dml operations

    Example

    Development tools

    Closure Tools - Use the Closure Compiler, Closure Library, and Closure Templates to build rich web applications with JavaScript that is faster, more powerful, and more optimized.

    Google Web Toolkit - Toolkit that allows you to build rich web applications in Java, and then compile into highly optimized JavaScript.

    1 https://www.percona.com/doc/percona-toolkit/3.0/release_notes.html
    2 ...
    3 Fixed bug 925778: pt-ioprofile doesn’t run without a file
    4 ...
    

    Start ProxySQL and check tables of it.

     1 [root@zlm1 14:37:31 ~/sysbench-1.0/src/lua]
     2 #sysbench oltp_read_write.lua --mysql-host=192.168.56.100 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --threads=3 --time=60 --warmup-time=30 --report-interval=10 --rand-type=uniform run
     3 sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
     4 
     5 invalid option: --warmup-time=30 //It's not supported in my version.
     6 
     7 [root@zlm1 14:37:34 ~/sysbench-1.0/src/lua]
     8 #sysbench oltp_read_write.lua --mysql-host=192.168.56.100 --mysql-port=3306 --mysql-user=zlm --mysql-password=aaron8219 --mysql-db=sysbench --threads=3 --time=60 --report-interval=10 --rand-type=uniform run
     9 sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
    10 
    11 Running the test with following options:
    12 Number of threads: 3 //There're three threads.
    13 Report intermediate results every 10 second(s) //Show report every 10s.
    14 Initializing random number generator from current time
    15 
    16 
    17 Initializing worker threads...
    18 
    19 Threads started!
    20 
    21 [ 10s ] thds: 3 tps: 134.07 qps: 2684.74 (r/w/o: 1879.80/536.59/268.34) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00 //Notice the low value here.
    22 [ 20s ] thds: 3 tps: 273.74 qps: 5473.50 (r/w/o: 3831.29/1094.64/547.57) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
    23 [ 30s ] thds: 3 tps: 273.30 qps: 5467.40 (r/w/o: 3827.60/1093.20/546.60) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
    24 [ 40s ] thds: 3 tps: 250.50 qps: 5009.82 (r/w/o: 3506.61/1002.20/501.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
    25 [ 50s ] thds: 3 tps: 261.07 qps: 5222.57 (r/w/o: 3656.33/1044.09/522.15) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
    26 [ 60s ] thds: 3 tps: 264.52 qps: 5289.24 (r/w/o: 3702.14/1058.07/529.03) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
    27 SQL statistics:
    28     queries performed:
    29         read:                            203980 //Executions of writing.
    30         write:                           58280 //Executions of reading.
    31         other:                           29140 //Operations excetp CURD.
    32         total:                           291400
    33     transactions:                        14570  (242.76 per sec.) //TPS is 242.
    34     queries:                             291400 (4855.30 per sec.) //QPS is 4855.
    35     ignored errors:                      0      (0.00 per sec.)
    36     reconnects:                          0      (0.00 per sec.)
    37 
    38 General statistics:
    39     total time:                          60.0155s
    40     total number of events:              14570 //Total transactions.
    41 
    42 Latency (ms):
    43          min:                                    3.17
    44          avg:                                   12.35
    45          max:                                  887.76
    46          95th percentile:                       16.41 //Response time of former 95%
    47          sum:                               179957.65
    48 
    49 Threads fairness:  //Statistics of concurrency.
    50     events (avg/stddev):           4856.6667/21.23
    51     execution time (avg/stddev):   59.9859/0.00
    52 
    53 
    54 [root@zlm1 14:39:06 ~/sysbench-1.0/src/lua]
    55 #
    

    From Google

    **Erase the latest Porcona-Toolkit 3.10.0 and change the old version 2.2.20.**

     

     

     

     

        As we all know,it's a common sense that separate reading and writing operations can immensely increse the performance of MySQL database.Especially the query operations by executing select statement relevant with large tables.Therefore,we usually choose a proxy tool to deal with it.There're a lot of tools can be used nowadays such as mycat(by Apache),dble(based on mycat by Action),atlas,dbproxy(based on atlas of Qihoo360 by MeituanDianping),cetus(by NetEase) and so forth.I'm not going to compare who's the better tool to use.I'm just prefer to having a test on another popular tool which is called "ProxySQL".

     

    PHP profiling

    Xdebug - Extension for PHP that provides profiling and code coverage analysis, as well as debugging information including stack and function traces, and memory allocation.

    XHProf by Facebook - Instrumentation-based hierarchical profiler for PHP.

     

    Procedure

    **Parameters**

    From other developers

     

     

     1 [root@zlm1 12:24:27 ~/sysbench-1.0]
     2 #vim /etc/ld.so.conf.d/mysql.conf
     3 
     4 [root@zlm1 12:26:17 ~/sysbench-1.0]
     5 #cat /etc/ld.so.conf.d/mysql.conf
     6 /usr/local/mysql/lib
     7 
     8 [root@zlm1 12:26:22 ~/sysbench-1.0]
     9 #ldconfig
    10 
    11 [root@zlm1 12:26:41 ~/sysbench-1.0]
    12 #ldd /usr/local/bin/sysbench
    13     linux-vdso.so.1 =>  (0x00007fff767fe000)
    14     libmysqlclient.so.20 => /usr/local/mysql/lib/libmysqlclient.so.20 (0x00007fb2ca8a8000)
    15     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb2ca68b000)
    16     librt.so.1 => /lib64/librt.so.1 (0x00007fb2ca483000)
    17     libdl.so.2 => /lib64/libdl.so.2 (0x00007fb2ca27f000)
    18     libaio.so.1 => /lib64/libaio.so.1 (0x00007fb2ca07d000)
    19     libm.so.6 => /lib64/libm.so.6 (0x00007fb2c9d7a000)
    20     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fb2c9b64000)
    21     libc.so.6 => /lib64/libc.so.6 (0x00007fb2c97a3000)
    22     libstdc  .so.6 => /lib64/libstdc  .so.6 (0x00007fb2c949b000)
    23     /lib64/ld-linux-x86-64.so.2 (0x00007fb2caec7000)
    24 
    25 [root@zlm1 12:26:53 ~/sysbench-1.0]
    26 #sysbench --version
    27 sysbench 1.0.14
    

    Tools and downloads

    There are many variables that affect a site's performance. The tools listed below can help you discover those variables and improve your site. We recommend that you experiment with these tools. Multiple simple changes can improve the experience for your users around the world by several seconds.

     

     

     

    Resource optimization

    CSS Sprite Generator - Generates a CSS sprite out of a number of images.

    JSLint - Tool that looks for code quality problems in JavaScript programs.

    JSMin - Filter which removes comments and unnecessary whitespace from JavaScript files.

    Smush It - Online tool that allows you to upload images for lossless compression and optimization. Provides a report of bytes saved and downloads a zip file containing the optimized versions of the files.

    SpriteMe! - Tool that determines background images to sprite, groups and sprites them, and generates resultant modified CSS.

    YUI Compressor - JavaScript minifier designed to yield a higher compression ratio than other tools.

    澳门皇家赌场55533网址,**There're percona release notes below which describes the bug is fixed in v2.0.3.**

        

    Download it from github.

    JavaScript profiling

    dynaTrace AJAX - Full tracing analysis of Internet Explorer 6-8 (including JavaScript, rendering, and network traffic). (Related blogpost)

        As a matter of fact,disk IO is the most important factor which tremendously influence performance of database especially in OLTP which is usually called IO-intensive system.The high concurrency is always in company with high IOPS as well.Furthermore,it's also a bottleneck in many scenarios.Thus,we have to monitor the IO cost of MySQL db by different tools such as zabbix,lepus.They usually provide a entire db performance monitoring not merely IO status.

    Introduce

     

    Web debugging

    Fiddler 2 - Web debugging proxy which logs all HTTP/S traffic between your computer and the Internet. Inspect HTTP/S traffic, set breakpoints, and "fiddle" with incoming or outgoing data.

    Firebug - Firefox Add-on that lets you edit, debug, and monitor CSS, HTML, and JavaScript live in any web page.

    HttpWatch - HTTP viewer and debugger integrated with IE and Firefox to provide HTTP/S monitoring without leaving the browser window.

     

    https://github.com/sysown/proxysql/releases/

     

    Resource Optimization

    Closure Compiler - Optimize the speed and size of your JavaScript.

    Execute pt-ioprofile to have a test.

     

     

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:利用sysbench实行MySQL OLTP基准测试

    关键词: 68399皇家赌场 测试工具 Web测试工具 Web开发工具