TPCC-MySQL基准测试-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

TPCC-MySQL基准测试

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:50 PM

TPCC-MySQL是Percona公司研发的基准测试工具。下载TPCC-MySQLyum install bzr -ybzr branch lp:~percona-dev/perconatools/tpcc-

TPCC-MySQL是Percona公司研发的基准测试工具。

下载TPCC-MySQL

yum install bzr -y

bzr branch lp:~percona-dev/perconatools/tpcc-mysql

下载之后可以看到tpcc-mysql目录
进入这个目录下的src目录,执行make
在上层目录中，可以看到已经有了tpcc_load tpcc_start命令

如果有如下错误,则可能是源码安装的MySQL,所以它的库不在默认的位置

error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

创建软链接可以解决这个问题。

ln -s /home/lihuilin/mysql/lib/libmysqlclient.so.18 /usr/lib64/libmysqlclient.so.18

创建一个测试数据库,然后使用如下配置进行基准测试

首先加载数据,执行创建表和约束的SQL

这个工具是模拟仓库的系统,
可以选择初始化仓库的数量,数量越大,数据量也越大。

初始化10个仓库

开始进行基准测试,其中w指仓库数量,c指并发数量,r是预热时间,l是测试运行时间

TPCC-MySQL输出结果包括五个业务逻辑，这五个业务逻辑构成了TPCC-MySQL测试的整个事务处理过程。具体如下所示：
New-Order：新订单
Payment：支付
Order-Status：订单查询
Delivery：发货
Stock-Level：库存

预定义变量：
为了能够清晰的说明以下内容，首先定义一些变量，便于以下的计算和说明。具体如下所示：
success = 执行成功的记录数
late=执行延迟的记录数
pre_success=上一次执行成功的记录数
pre_late=上一次执行失败的记录数

根据以上定义的变量，，计算相应字段的结果和说明相应字段的含义。
1、时间间隔内成功的事务(包括成功和延迟的事务)：sl=success+late-pre_success-pre_late
2、时间间隔内延迟的事务：l=late-pre_late
3、时间间隔内前90%记录(实际为99%)的平均响应时间：rt90
4、时间间隔内最大的响应时间：max_rt

查看TPCC的结果

sc:success,lt:late,rt:retry,fl:failure

//指标如下
New-Order 5ms
Payment 5ms
Order-Status 5ms
Delivery 80ms
Stock-Level 20ms

然后修改MySQL配置,再次运行相同测试

innodb_buffer_pool_size=512m

可以看到性能有明显下降。

--------------------------------------分割线 --------------------------------------

基准测试工具之tpcc-mysql

MySQL基准测试工具TPCC-MySQL使用笔记

MySQL 5.6.7-RC 的 tpcc-mysql 基准测试结果

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址：

linux

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles