Home Database Mysql Tutorial MySQL 5.6.7-RC 的 tpcc-mysql 基准测试结果_MySQL

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

Jun 01, 2016 pm 01:48 PM
stability

bitsCN.com

 


MySQL 5.6.7 RC 前些天发布了,因此我决定使用 tpcc-mysql 对其表现进行测试,包括性能和稳定性方面。

我不能说我的测试过程是完美无瑕的,因为发现了两个 bug :

  • MySQL 5.6.7 在 CREATE INDEX 时锁住了
  • MySQL 5.6.7-rc 在使用 tpcc-mysql 工作负载测试时崩溃

不晓得是不是因为是 RC 版本的原因,后来向 Oracle 提交一些反馈,下面是详细的测试环境:

 

  • 测试日期: Oct-2012
  • 测试目的: 测试 MySQL 5.6.7 的表现
  • 硬件换
    • 服务器: Dell PowerEdge R710
    • CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
    • 内存: 192GB(这个内存太猛了)
    • 存储: Very Fast PCIe Flash Card
    • 文件系统: ext4
  • 软件
    • 操作系统: CentOS 6.3
    • MySQL 版本: 5.6.7-RC
  • 测试规范
    • 测试工具: tpcc-mysql
    • 测试数据: 2500W (~250GB of data)
    • 测试时间: 总共测试 4000 秒,但只取最后的 2000 秒,避免因为冷启动的问题导致测试结果不准确
  • 不同的测试参数: 使用几组不同的 innodb_buffer_pool_size:13, 25, 50, 75, 100, 125GB ,innodb_buffer_pool_instances: 1 and 8, and innodb_log_file_size: 2x4GB and 2x8GB.

测试结果:

第一个结果使用的事 2x4GB 的 InnoDB 日志文件:

/

我们可看出当 innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小时有很大的不同,而使用大的 buffer_pool 时,innodb_buffer_pool_instances=1 的表现最棒。

测试结果在大的 buffer_pool 时是很稳定的,原因是 InnoDB 使用异步 flush 模式,在新的 InnoDB flush 机制下以前的问题已经修复。不过 Dimitry 告诉我需要一个更大的 InnoDB 日志文件来获得更稳定的结果。

下面是 2x4GB vs 2x8GB innodb 日志文件大小的比较:
/

很显然,使用更大的日志文件,测试结果更稳定!

结论:

innodb_buffer_pool_instances 参数显著的影响测试结果,特别是非常高的 I/O 负载时。

在 MySQL 5.6 ,最终是可以获得非常稳定的吞吐,但自适应的 flush 机制仍需较大的日志文件。

MySQL 配置如下: 
01 [mysqld] 02 gdb 03   04 innodb_file_per_table = true 05 innodb_data_file_path = ibdata1:100M:autoextend 06 innodb_flush_method = O_DIRECT 07 innodb_log_buffer_size = 256M 08   09 innodb_flush_log_at_trx_commit = 1 10 innodb_buffer_pool_size = 125G 11 innodb_buffer_pool_instances=8 12   13 innodb_log_file_size = 4G 14 innodb_log_files_in_group = 2 15 #####plugin options 16 innodb_read_io_threads = 16 17 innodb_write_io_threads = 16 18 innodb_io_capacity = 20000 19 innodb_io_capacity_max = 40000 20   21   22 #not innodb options (fixed) 23 port = 3306 24 back_log = 50 25 max_connections = 2000 26 max_prepared_stmt_count=500000 27 max_connect_errors = 10 28 table_open_cache = 2048 29 max_allowed_packet = 16M 30 binlog_cache_size = 16M 31 max_heap_table_size = 64M 32 sort_buffer_size = 4M 33 join_buffer_size = 4M 34 thread_cache_size = 1000 35 query_cache_size = 0 36 query_cache_type = 0 37 ft_min_word_len = 4 38 thread_stack = 192K 39 tmp_table_size = 64M 40   41 server-id = 10 42 #*** MyISAM Specific options 43 key_buffer_size = 8M 44 read_buffer_size = 1M 45 read_rnd_buffer_size = 4M 46 bulk_insert_buffer_size = 8M 47 myisam_sort_buffer_size = 8M 48 myisam_max_sort_file_size = 10G 49 myisam_repair_threads = 1 50 myisam_recover 51 user=root 52 skip-grant-tables

英文原文,OSCHINA原创翻译

bitsCN.com
Statement of this Website
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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Introduction to the differences between win7 home version and win7 ultimate version Introduction to the differences between win7 home version and win7 ultimate version Jul 12, 2023 pm 08:41 PM

Everyone knows that there are many versions of win7 system, such as win7 ultimate version, win7 professional version, win7 home version, etc. Many users are entangled between the home version and the ultimate version, and don’t know which version to choose, so today I will Let me tell you about the differences between Win7 Family Meal and Win7 Ultimate. Let’s take a look. 1. Experience Different Home Basic Edition makes your daily operations faster and simpler, and allows you to access your most frequently used programs and documents faster and more conveniently. Home Premium gives you the best entertainment experience, making it easy to enjoy and share your favorite TV shows, photos, videos, and music. The Ultimate Edition integrates all the functions of each edition and has all the entertainment functions and professional features of Windows 7 Home Premium.

Which version of win11 is the smoothest and most stable? Which version of win11 is the smoothest and most stable? Jan 06, 2024 pm 09:48 PM

The overall operation feel of win11 is still very good, and there are many versions to choose and use. Here are a few very easy-to-use, stable and smooth system versions recommended for you. You can directly choose to download, install and use them. Which version of win11 is the smoothest and most stable? 1. The original win11 image supports one-click backup and recovery services, so there is no need to worry about accidental deletion of computer data! Faster system operation and usage features allow you to experience high-quality operation and gaming experience! 2. The Chinese version of the win11 system has simple and convenient operations and gameplay, making it easier to install the system! A variety of security maintenance tools are waiting for you to use to create better system security! 3. Win11 Russian Master Lite version has comprehensive functional gameplay to meet your various needs and provide a more complete experience.

How PHP implements automated testing and improves code quality and stability How PHP implements automated testing and improves code quality and stability Jun 27, 2023 am 08:27 AM

In the modern software development process, automated testing has become one of the necessary means to ensure software quality and stability. Among them, automated testing technology developed for PHP is becoming more and more mature and widely used. This article will start with the basic concepts of automated testing, explain the implementation methods and application scenarios of PHP automated testing, and how to improve code quality and stability through automated testing. 1. Introduction to automated testing Automated testing refers to the automation of tedious and time-consuming tasks in the software testing process, including test cases.

What is the performance of Kirin 9000s? What is the performance of Kirin 9000s? Mar 22, 2024 pm 03:21 PM

As a flagship mobile phone that has attracted much attention, Kirin 9000s has attracted widespread discussion and attention since its launch. It is equipped with the latest flagship chip of the Kirin 9000 series, and its performance is very strong. So, what is the performance of Kirin 9000s? Let’s explore it together. First of all, Kirin 9000s is manufactured using a new 5nm process, which greatly improves the performance and power consumption control of the chip. Compared with previous Kirin processors, Kirin 9000s has significantly improved performance. Whether running big games, multitasking or

How Scrapy improves crawling stability and crawling efficiency How Scrapy improves crawling stability and crawling efficiency Jun 23, 2023 am 08:38 AM

Scrapy is a powerful web crawler framework written in Python, which can help users quickly and efficiently crawl the information they need from the Internet. However, in the process of using Scrapy to crawl, you often encounter some problems, such as crawling failure, incomplete data or slow crawling speed. These problems will affect the efficiency and stability of the crawler. Therefore, this article will explore how Scrapy improves crawling stability and crawling efficiency. Set request headers and User-Agent when crawling the web,

An in-depth analysis of why Linux is as stable as a mountain: the combination of technology and philosophy An in-depth analysis of why Linux is as stable as a mountain: the combination of technology and philosophy Mar 14, 2024 am 11:33 AM

Title: An in-depth analysis of why Linux is as stable as a mountain: the combination of technology and philosophy. As one of the representatives of open source operating systems, Linux is widely praised for its stability and powerful functions. Many people are very interested in the reason why Linux systems are so stable. Behind this is the combination of technology and philosophy. This article will provide an in-depth analysis of the stability of the Linux system from both technical and philosophical perspectives, and demonstrate it through specific code examples. On the technical level, the reason why the Linux system is as stable as a mountain is due to its exquisite design and

Discover the secrets of Linux stability: the secrets behind it Discover the secrets of Linux stability: the secrets behind it Mar 14, 2024 pm 09:03 PM

Exploring the Mysteries of Linux Stability: The Secrets Behind Linux As an open source operating system, Linux is world-famous for its stability and reliability. Many people are curious about the stability of Linux and want to know the secrets behind it. This article will explore the reasons for Linux stability and reveal the secrets through specific code examples. 1. Stability of kernel design The stability of Linux comes from its kernel design. The Linux kernel has undergone long-term evolution and optimization, and has a good design architecture and modular programming style.

Optimize the performance and stability of Python web applications using Gunicorn Optimize the performance and stability of Python web applications using Gunicorn Jan 03, 2024 am 11:26 AM

Learn how to use Gunicorn to improve the performance and stability of Python web applications Introduction: In modern web development, performance and stability are very important factors. Python, as a popular programming language, provides many frameworks and tools for building web applications. However, in high concurrency situations, Python's default web server may not be able to meet demand. At this time, Gunicorn (GreenUnicorn) can be used to improve performance and stability.

See all articles