- 浏览: 851040 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
zjhzwx1212:
为什么用threadLocal后,输出值是从20开始的,而定义 ...
j2ee的线程安全--threadlocal -
aeoluspu:
不错 mysql 测试部分感觉不详细
用sysbench(或者super-smack)测试mysql性能 -
nanPrivate:
有没有例子,只理论,实践起来还是不会啊
JMS可靠消息传送 -
lwclover:
一个网络工程师 装什么b
postfix 如何删除队列中的邮件 -
maimode:
我也欠缺不少啊
理想的计算机科学知识体系
Test Suite
The test suite uses mysqlslap
to benchmark the overhead of MySQL Proxy
itself in real life scenario as well as the different components of
HSCALE - query analyzing and query rewriting. The complete test suite
is available in the svn trunk at http://svn.hscale.org
under hscale/test/performance/mysqlslap
. There you find a build.xml
- an Ant
buildfile that is used to set up the test environment and perform the tests.
Test Strategy
There are several things we want to find out using this benchmark:
- How much overhead adds MySQL Proxy in a multiple server setup?
- Does using Lua scripts add substantial overhead?
- How much resources does the proxy.tokenizer use?
- How does HSCALE perform on unpartitioned tables?
- How does HSCALE perform on partitioned tables?
As stated above mysqlslap
is used to generate multi-threaded load. mysqlslap is used to fire this statement:
SELECT
id, category
FROM small
WHERE
small.category='books'
/* Added */ /* some */ /* comments */
/* to */ /* produce */ /* a */ /* higher */
/* tokenizer */ /* load */
against this table and content:
CREATE TABLE small (
id INT UNSIGNED NOT NULL,
category ENUM('books', 'hardware', 'software') NOT NULL,
PRIMARY KEY(id)
) ENGINE=HEAP;
INSERT INTO small (id, category) VALUES (1, 'books');
INSERT INTO small (id, category) VALUES (2, 'hardware');
INSERT INTO small (id, category) VALUES (3, 'software');
Each run sends 10,000 queries to the MySQL Server or MySQL Proxy respectively.
Test Setup
- A MySQL server instance (5.0.54-enterprise-gpl-log) on a DELL PowerEdge 2850, 2xQuadCore 2.8GHz, 12GB RAM
- A server running MySQL Proxy (version 0.6.1) instances exclusively (DELL PowerEdge 2950, 2xQuadCore 2.33GHz, 8GB RAM)
- A test runner on a DELL PowerEdge 1950, 2xQuadCore 1.8GHz, 8GB RAM.
The test suite is totally CPU and memory bound so the IO system doesn’t matter here.
Results
40 | 217 | 1302 | 7667 | 7091 | 6162 | 7552 | 7577 |
20 | 217 | 557 | 2536 | 4532 | 4524 | 4325 | 4564 |
10 | 287 | 641 | 675 | 1179 | 1813 | 738 | 2711 |
1 | 1906 | 3914 | 4574 | 5299 | 5411 | 4465 | 6957 |
Each test means:
- MySQL: Test ran directly against a mysql server
- MySQL Proxy: Test ran directly against a MySQL Proxy server with no additional configuration / script
- Empty Lua: A Lua script with an empty
function read_request(packet)
has been used - Tokenizer: Each query has been tokenized using
proxy.tokenizer
- QueryAnalyzer: Tokenizer and query analyzer are used but no query rewriting
- HSCALE w/o partitions: HSCALE is used but the table is not partitioned
- HSCALE w/ partitions: HSCALE is used against a partitioned table
Conclusions
First of all: Please note that these benchmarks measure the maximum overhead of each component and that overhead is constant meaning that a statement that takes 1 minute to complete on the MySQL server does not take 2 minutes when using MySQL Proxy.
CPU As Limiting Factor
As you can see with a concurrency of 20 or more everything gets worse and worse. This is because the MySQL Proxy / Lua performance becomes CPU bound. In addition to that you can see that the time is spent anywhere but within the Lua scripts: While we see quite distinct performance values for lower concurrencies (HSCALE w/ and w/o partitions show a huge difference) every benchmarks takes almost the same time at 20 or 40 parallel threads.
Looking at top
the MySQL Proxy seems to be using a
single CPU out of 8 available. If this is the case it would be
extremely desirable to have MySQL Proxy use all available resources.
MySQL Proxy Overhead
As we can see putting a plain MySQL Proxy between application and MySQL server adds about 100% to 150% to the average overall performance. This is what we could have expected because of the added latency - packets are going through 2 hops instead of 1.
With higher concurrency the overhead grows until it totally drops at 40 parallel threads. Here CPU seems to be the limiting factor.
Lua Scripts
Adding an empty Lua script to the configuration results in little overhead up to a concurrency of 10. With higher concurrency everything gets worse. Again CPU seems to be the limiting factor.
Tokenizer
The SQL tokenizer adds about 75% compared to an empty Lua script. So we should avoid it as much as we can. With the results of this benchmark we were able to improve the overall HSCALE performance for non-partitioned tables (see this Issue ).
QueryAnalyzer
Since the QueryAnalyzer utilizes the tokenizer it implies its overhead and adds additional 50% (at a concurrency of 10). Here is a lot of room for improvement. Currently the analyzer is almost complete so we can concentrate on performance. First of all the algorithm could be optimized (anticipating the fastest path) and then more hinting could be added.
HSCALE w/o Partitions
After implementing an improvement for this Issue (avoiding tokenizer) we see that performance for queries against non-partitioned tables is almost as good as for empty Lua scripts.
HSCALE w/ Partitions
Looking at the concurrency level of 10 we see that HSCALE performs 10 times slower that the MySQL server and 5 times slower than an empty MySQL Proxy. Needless to say that this is quite a huge number. With performance improvements we might lower this to a factor of 2 or 3 times slower than MySQL Proxy itself. This is ok since we are still able to perform more than 3,000 statements / s. And finally we are able to use multiple proxies to spread the load.
Final Thoughts
This benchmark showed us mainly 3 things:
- MySQL Proxy adds the expected latency overhead - but not more. Average is about 0.035 milliseconds per query.
- Scaling of MySQL Proxy could be improved - using all CPUs
- HSCALE adds a maximum overhead of about 0.24 ms per query (against a partitioned table).
Please feel free to comment on the results or run the tests on your own.
UPDATE: Corrected the number of milliseconds MySQL Proxy and HSCALE add per query: Old were 0.35 ms for proxy and 2.4 ms for HSCALE. The correct numbers are 0.035 ms for proxy and 0.24 ms for HSCALE.
发表评论
-
找出mysql中最大的表
2011-08-04 12:41 1686SELECT concat(round(table_rows/ ... -
find 10 largest table in mysql
2010-07-20 11:09 1110SELECT concat(round(table_rows/ ... -
MySQL Back to Basics: Analyze, Check, Optimize, and Repair
2010-07-14 11:35 1189http://www.pythian.com/news/111 ... -
找出mysql中无用的索引
2010-07-13 14:49 1889select t.TABLE_SCHEMA , ... -
xtrabackup timeout bug
2010-06-13 10:21 1090I modified /usr/bin/innobac ... -
Compiling sysbench 0.4.12 for Debian
2010-06-09 10:16 985http://www.randombugs.com/linux ... -
mysql 实用工具集
2010-06-04 00:08 1110这些工具都是从网上搜集来的,对mysql的管理,调优和恢复有很 ... -
gearman for mysql
2010-05-10 18:26 1005http://www.slideshare.net/datac ... -
几个应该被修改的mysql默认值
2010-05-06 15:24 1494wait_timeout = 20 (不适合持久连接) in ... -
How to Perform a Healthcheck on the Database
2010-02-26 10:05 1401http://bbs.chinaunix.net/thread ... -
PostgreSQL与Innodb并发控制大比拼
2010-01-14 11:38 2089http://wangyuanzju.blog.163.com ... -
关于mysql的很好网站
2009-10-21 10:40 949http://www.mysqlperformanceblog ... -
Should you move from MyISAM to Innodb ?
2009-10-17 02:37 1038There is significant portion of ... -
最好的mysql备份工具
2009-08-19 16:45 1215Xtrabackup https://launchpad.n ... -
mysql 增量备份脚本
2009-08-18 09:44 3656根据网上脚本修改而成 mysqlFullBackup.sh ... -
mysql-zrm备份mysql数据库
2009-07-22 13:58 2776MySQL-zrm是用perl脚本写的 ... -
Base: An Acid Alternative
2009-06-18 15:55 1406http://queue.acm.org/detail.cfm ... -
关于innodb插入性能
2009-04-07 10:54 1599根据某网友的测试,innodb在以下条件下插入性能是稳定的: ... -
MySQL主从服务器的一些技巧
2009-03-25 15:18 959http://www.sunnyu.com/?p=150 -
mytop1.6补丁
2009-03-13 17:27 1006mytop是一个实时监控mysql状态的工具,很好用,但是有一 ...
相关推荐
《MySQL支持的BenchmarkSQL 5.0优化版详解》 BenchmarkSQL是一款开源的数据库基准测试工具,主要用于评估各种关系型数据库管理系统(RDBMS)在处理标准OLTP工作负载时的性能。然而,原版的BenchmarkSQL并不直接支持...
标题“MySQL CIS community server 5.7 benchmark”指的是MySQL数据库社区服务器版本5.7的CIS(Center for Internet Security,互联网安全中心)基准测试,这是由CIS的社区为MySQL 5.7版本制定的一套安全标准,用于...
BenchmarkSQL 5.0版本增加了对PostgreSQL、Oracle和MySQL这三种流行数据库系统的支持,使得测试更加全面。 标题中的"benchmarksql-5.0-pg-oracle-mysql.tar.gz"表明这是一个压缩文件,包含了BenchmarkSQL 5.0版本,...
The COST Simulation Benchmark:Description and Simulator M。 MATLAB建模。本出版物的重点是COST“模拟基准”,它是作为一个由两个成本行动促成的合作的直接结果。成本行动682 '综合废水管理'(1992-1998)侧重于...
ECCV2014最新论文RGBD Salient Object Detection A Benchmark and Algorithms。
标题《CIS_Mysql_Benchmark_v1.0.2》和描述《Security Configuration Benchmark For MySQL 4.1, 5.0, 5.1 Community Editions Version 1.0.2》所描述的是一份由互联网安全中心(The Center for Internet Security, ...
MySQL Benchmark Tool是一个轻量级的命令行工具,用于从先前编写MySQL-Log重复执行SQL语句。 下载 您可以在bin文件夹中找到预编译的jar: : 它做什么以及如何使用 的博客文章,其中有更详细的说明。 这是一个小...
总结来说,"Benchmark.rar_benchmark_benchmark三代_benchmark模型_三代benchmark_主动控制"是一个专注于主动控制算法评估的资源包,它提供了一个先进的三代Benchmark模型,用于比较和优化控制策略的性能。...
apache benchmark 独立文件 ab.exe 可以直接使用 Version 2.3。一般用户压力测试用。参数如下 .\ab.exe --help Options are: -n requests Number of requests to perform -c concurrency Number of multiple ...
2016-08-08 The data and labels of the attribute prediction benchmark are released without encription (password). If Dropbox are not accessable, please download the dataset using Google Drive or Baidu ...
### MySQL Benchmark分析报告 #### 一、背景与动机 在《且将新火试新茶-MySQL Benchmark》这篇文档中,作者FULLSAIL基于对MySQL性能的强烈兴趣和对公司内部流传的各种关于MySQL性能的观点,决定亲自进行一系列的...
CIS_Apple_iOS_13_and_iPadOS_13_Benchmark_v1.0.0.pdf CIS_CentOS_Linux_6_Benchmark_v2.1.0.pdf CIS_CentOS_Linux_8_Benchmark_v1.0.0.pdf CIS_Debian_Linux_10_Benchmark_v1.0.0.pdf CIS_Debian_Linux_8_...
A Benchmark Dataset and Evaluation Methodology for Video Object Segmentation.zip
除了达梦数据库,BenchmarkSQL还兼容其他主流的数据库系统,如Oracle、DB2、SQL Server、MySQL和PostgreSQL。这种广泛的兼容性使得用户可以将多个数据库系统放在一起进行对比测试,找出最佳的性能指标,这对于数据库...
Benchmark etcd性能测试工具
cd mysql-benchmark ./mysql-benchmark.sh *-----------------------------------* Mysql Benchmark Test Read the README first befor you start! *-----------------------------------* 1 - install sysbench 2 ...
BenchmarkSQL支持多种数据库系统,包括MySQL、PostgreSQL、Oracle等,通过自定义配置,可以适应不同数据库的特性。 核心功能方面,BenchmarkSQL提供了以下几点: 1. **工作负载模拟**:它可以根据TPC-C...
《数据库压力测试利器:BenchmarkSQL 5.0与Oracle及达梦数据库的深度结合》 BenchmarkSQL是一款广泛使用的开源数据库压力测试工具,尤其在评估和比较不同数据库系统的性能时,它的价值尤为突出。该工具以其易用性...
《BenchmarkSQL5.0:深度解析与应用指南》 BenchmarkSQL是一款广泛应用的数据库性能测试工具,专为评估和比较各种数据库管理系统(DBMS)的性能而设计。它以开源的形式提供,支持包括Oracle、MySQL以及人大金仓在内...