PlanetMysql.ru — информация о СУБД MySQL

I’ll be speaking on April 11th at 4:30 pm in Room 4 in at the Percona Conference and Expo Talk. The topic will be “Creating a Benchmark Infrastructure That Just Works.”

Throughout my career I’ve been involved with maintaining the performance of database applications and therefore created many benchmark frameworks. At Tokutek, an important part of my role is measuring the performance of our storage engine over time and versus competing solutions. There is nothing proprietary about what I’ve created, it can be used anywhere.

My presentation will cover how I created the benchmark infrastructure at Tokutek:

• Hardware and software considerations (including physical vs. virtual)
• Selecting benchmarks
• Capturing detailed information during the benchmark
• Automation
• Storing results
• Visualization
• Trend analysis
• Continuous integration (monitoring the performance of future versions)
• Self-service (let people get the information they want)

Track: Tools
Experience level: Intermediate

Tokutek is also a sponsor of the show and will have an expo booth. So, I hope to see you at my talk and/or at our booth.

PlanetMySQL Voting: Vote UP / Vote DOWN

iiBench measures the rate at which a database can insert new rows while maintaining several secondary indexes. We ran this for 1 billion rows with TokuDB and InnoDB starting last week, right after we launched TokuDB v5.2. While TokuDB completed it in 15 hours, InnoDB took 7 days.

The results are shown below. At the end of the test, TokuDB’s insertion rate remained at 17,028 inserts/second whereas InnoDB had dropped to 1,050 inserts/second. That is a difference of over 16x. Our complete set of benchmarks for TokuDB v5.2 can be found here.

Benchmark Details: Ubuntu 10.10; 2x Xeon X5460; 16GB RAM; 8x 146GB 10k SAS in RAID10. Each data point is the average insertion rate for the last 2 million rows.

We developed the iiBench benchmark to measure performance for a use case that occurs commonly in production applications, such as online advertising, social media, and network management.

iiBench simulates a pattern of usage for always-on applications that:

• Require fast query performance and hence require indexes
• Have high data insert rates
• Cannot wait for offline batch processing and hence require the indexes be maintained as data comes in

Note that iiBench was created as an open-source benchmark, which allows others to freely use it, extend it, and contribute their changes back. We originally unveiled the benchmark in the context of a challenge issued at the 2008 OpenSQL camp. Since then, iiBench has been downloaded and used many times, and ported by the community (in this case, Mark Callaghan) to a Python Script.

Please let us know any feedback you have on iiBench. For additional information on…

• iibench overview click here
• TokuDB version 5.2 Overview click here
• TokuDB version 5.2 Performance, including iibench, SysBench, Compression, and TPCC-like, click here

PlanetMySQL Voting: Vote UP / Vote DOWN

I’m creating a library of benchmarks and test suites that will run as part of a Continuous Integration (CI) process here at Tokutek. My goal is to regularly measure several aspects of our storage engine over time: performance, correctness, memory/CPU/disk utilization, etc. I’ll also be running tests against InnoDB and other databases for comparative analysis. I plan on posting a series of blog entries as my CI framework evolves, for now I have the results of my first benchmark.

Compression is an always-on feature of TokuDB. There are no server/session variables to enable compression or change the compression level (one goal of TokuDB is to have as few tuning parameters as possible). My compression benchmark uses iiBench to measure the insert performance and compression achieved by TokuDB and InnoDB. I tested InnoDB compression with two values of key_block_size (4k and 8k) and with compression disabled.

As you can see in the above graph, compression allows for the database to use significantly less disk space. TokuDB achieved 51% compression, InnoDB achieved 50% for key_block_size=4 and and 47% compression for key_block_size=8. [Note: The random nature of iiBench makes it difficult to compress]

Traditionally there is a “size versus speed” trade-off when compressing data. Data compression utilities have long offered variable levels of aggressiveness, spending more time compressing files usually results in smaller files. The InnoDB benchmarks bear this out, as the compression level increases the insert performance declines. On the other hand, TokuDB achieves the highest level of compression while out-performing InnoDB in all scenarios, even InnoDB without compression. TokuDB is running 33.4x faster than InnoDB configured to achieve similar levels of compression. Note, “Inserts per Second” was measured as the exit velocity of the benchmark run (the average of the last million inserts).

How much compression can be achieved?

To answer this I decided to load some web application performance data (log style data with stored procedure names, database instance names, begin and ending execution timestamps, duration row counts, and parameter values). TokuDB achieved 18x compression, far more than InnoDB. It also loaded the data much faster but that is a blog entry for another day…

---

Benchmark details

Application

• iiBench, insert 25mm rows, 1000 rows per commit

Environment

• Intel Core-i7/920 @ 3.6GHz, 12GB DDR3 @ 1600MHz, 2 x SATA II
• Ubuntu 11.04, TokuDB 5.0.4, MySQL 5.1.52, InnoDB plug-in 1.0.13

Server/Session Variables

• unique_checks=1
• tokudb_commit_sync=0
• tokudb_cache_size=2G
• innodb_buffer_pool_size=2G
• innodb_flush_method=O_DIRECT
• innodb_doublewrite=false
• innodb_flush_log_at_trx_commit=0
• innodb_log_file_size=1000M
• innodb_file_per_table=true
• innodb_log_buffer_size=16M
• innodb_file_format=barracuda

PlanetMySQL Voting: Vote UP / Vote DOWN

I’m creating a library of benchmarks and test suites that will run as part of a Continuous Integration (CI) process here at Tokutek. My goal is to regularly measure several aspects of our storage engine over time: performance, correctness, memory/CPU/disk utilization, etc. I’ll also be running tests against InnoDB and other databases for comparative analysis. I plan on posting a series of blog entries as my CI framework evolves, for now I have the results of my first benchmark.

Compression is an always-on feature of TokuDB. There are no server/session variables to enable compression or change the compression level (one goal of TokuDB is to have as few tuning parameters as possible). My compression benchmark uses iiBench to measure the insert performance and compression achieved by TokuDB and InnoDB. I tested InnoDB compression with two values of key_block_size (4k and 8k) and with compression disabled.

As you can see in the above graph, compression allows for the database to use significantly less disk space. TokuDB achieved 51% compression, InnoDB achieved 50% for key_block_size=4 and and 47% compression for key_block_size=8. [Note: The random nature of iiBench makes it difficult to compress]

Traditionally there is a “size versus speed” trade-off when compressing data. Data compression utilities have long offered variable levels of aggressiveness, spending more time compressing files usually results in smaller files. The InnoDB benchmarks bear this out, as the compression level increases the insert performance declines. On the other hand, TokuDB achieves the highest level of compression while out-performing InnoDB in all scenarios, even InnoDB without compression. TokuDB is running 33.4x faster than InnoDB configured to achieve similar levels of compression. Note, “Inserts per Second” was measured as the exit velocity of the benchmark run (the average of the last million inserts).

How much compression can be achieved?

To answer this I decided to load some web application performance data (log style data with stored procedure names, database instance names, begin and ending execution timestamps, duration row counts, and parameter values). TokuDB achieved 18x compression, far more than InnoDB. It also loaded the data much faster but that is a blog entry for another day…

---

Benchmark details

Application

• iiBench, insert 25mm rows, 1000 rows per commit

Environment

• Intel Core-i7/920 @ 3.6GHz, 12GB DDR3 @ 1600MHz, 2 x SATA II
• Ubuntu 11.04, TokuDB 5.0.4, MySQL 5.1.52, InnoDB plug-in 1.0.13

Server/Session Variables

• unique_checks=1
• tokudb_commit_sync=0
• tokudb_cache_size=2G
• innodb_buffer_pool_size=2G
• innodb_flush_method=O_DIRECT
• innodb_doublewrite=false
• innodb_flush_log_at_trx_commit=0
• innodb_log_file_size=1000M
• innodb_file_per_table=true
• innodb_log_buffer_size=16M
• innodb_file_format=barracuda

PlanetMySQL Voting: Vote UP / Vote DOWN

So I’ve been doing a fair number of automated load tests these past six months. Primarily with Sysbench, which is a fine, fine tool. First I started using some simple bash based loop controls to automate my overnight testing, but as usually happens with shell scripts they grew unwieldy and I rewrote them in python. Now I have some flexible and easily configurable code for sysbench based MySQL benchmarking to offer the community. I’ve always been a fan of giving back to such a helpful group of people – you’ll never hear me complain about “my time isn’t free”. So, let me know what you want in an ideal testing environment (from a load testing framework automation standpoint) and I’ll integrate it into my existing framework and then release it via the BSD license. The main goal here is to have a standardized modular framework, based on sysbench, that allows anyone to compare their server performance via repeatable tests. It’s fun to see other people’s benchmarks but it’s often difficult to repeat and compare since most tests aren’t fully documented in their blog posts – this could be a solution to that.

Currently I have the harness doing iterations based on:

• incrementing (choose a global dynamic variable, ie: sync_binlog=0-1000) system values
• storage engine vs storage engine for the same workload
• thread quantity increments for read-only or read+write
• N-nodes in a cluster workloads with WRR traffic distribution (need to code WLC and others)
• QPS testing for connection pool vs open/close connection
• multi-table vs single-table workloads

Outputs available: CSV, XML, JSON for easy integration into any number of the various graphing frameworks available. I’ll probably code up a light weight python http server preloaded with Highcharts and Sparklines so you can see your benchmarks easily without having to roll your own graphs.

Quick now, tell me what you’d like me to code for you!

PlanetMySQL Voting: Vote UP / Vote DOWN

Benchmarking is liking running your system through it’s paces.  You don’t know how fast your software and hardware are until you’ve put some pressure on them.  Benchmarking tools allow you to do just that.  We use sysbench to look at the operating system and mysqlslap to run queries in the MySQL database.

Database Journal – MySQL Server Benchmarking 101

PlanetMySQL Voting: Vote UP / Vote DOWN

There’s a new version of the dbbenchmark tool available. Now we’re creating the MySQL connection pool thread count based on automatic reporting of core quantity. There is still the same method to set the thread count manually if you are interested in finding your system’s thread limits. Download the MySQL benchmarking script now and add your server performance to the community database of results!

PlanetMySQL Voting: Vote UP / Vote DOWN

In this latest release I’ve added a basic MySQL connection pool to the benchmarking script which improves the method in which connections to MySQL are handled and reused. In addition, there have been some optimizations made to the thread handler functions for better debug reporting. Download the latest release now and see how your MySQL server performs against the rest of the community! Download here: download page.

PlanetMySQL Voting: Vote UP / Vote DOWN

As previously mentioned, Darren Cassar has been working on a new automated installer for the DBbenchmark program. It’s now available for download: click here. All you need to do is save it to the directory that you want to install to and then make sure it’s executable: “chmod 700 installer.sh”, then run it “./installer.sh”.

PlanetMySQL Voting: Vote UP / Vote DOWN

So far the benchmarking script supports Linux, FreeBSD, and OSX. I’m installing virtual machines today to get ready for development on the next OS that the community wants to have supported. Vote today for your choice. Development will begin Friday 2010-09-03.

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
PlanetMySQL Voting: Vote UP / Vote DOWN