Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 221 Next 30 Older Entries

Displaying posts with tag: Benchmarks (reset)

Using sysbench 0.5 for performing MySQL benchmarks
+0 Vote Up -0Vote Down

Given the recent excitement & interest around OpenStack I wanted to make sure I was ready to conduct appropriate evaluations of system performance.  I generally turn to sysbench since it comes with a variety of different tests (accessed via –test= option interface), including:

  • fileio – File I/O test
  • cpu – CPU performance test
  • memory – Memory functions speed test
  • threads – Threads subsystem performance test
  • mutex – Mutex performance test

As you can see, sysbench lets you stress many of the fundamental components of your hardware and infrastructure, such as your disk subsystem, along with your CPUs and memory. An additional option exists that is designed to perform synthetic stress testing of MySQL, and I was surprised when I didn’t see it in the

  [Read more...]
A closer look at the MySQL ibdata1 disk space issue and big tables
+2 Vote Up -0Vote Down

A recurring and very common customer issue seen here at the Percona Support team involves how to make the ibdata1 file “shrink” within MySQL. I can only imagine there’s a degree of regret by some of the InnoDB architects on their design decisions regarding disk-space management by the shared tablespace* because this has been a big frustration for many MySQL users over the years.

There’s a very old bug (“InnoDB ibdata1 never shrinks after data is removed,” Sept. 8 2003) documenting user dissatisfaction. Shortly before that issue celebrated its 10th anniversary, James Day, MySQL senior principal support engineer at Oracle, posted a comment

  [Read more...]
A closer look at the MySQL ibdata1 disk space issue and big tables
+0 Vote Up -0Vote Down

A recurring and very common customer issue seen here at the Percona Support team involves how to make the ibdata1 file “shrink” within MySQL. I can only imagine there’s a degree of regret by some of the InnoDB architects on their design decisions regarding disk-space management by the shared tablespace* because this has been a big frustration for many MySQL users over the years.

There’s a very old bug (“InnoDB ibdata1 never shrinks after data is removed,” Sept. 8 2003) documenting user dissatisfaction. Shortly before that issue celebrated its 10th anniversary, James Day, MySQL senior principal support engineer at Oracle, posted a comment explaining

  [Read more...]
Measuring failover time for ScaleArc load balancer
+1 Vote Up -0Vote Down

ScaleArc hired Percona to benchmark failover times for the ScaleArc database traffic management software in different scenarios. We tested failover times for various clustered setups, where ScaleArc itself was the load balancer for the cluster. These tests complement other performance tests on the ScaleArc software – sysbench testing for latency and testing for WordPress acceleration.

We tested failover times for Percona XtraDB Cluster (PXC) and MHA (any traditional MySQL replication-based solution works pretty much the same

  [Read more...]
Benchmarking IBM eXFlash™ DIMM with sysbench fileio
+0 Vote Up -0Vote Down

Diablo Technologies engaged Percona to benchmark IBM eXFlash™ DIMMs in various aspects. An eXFlash™ DIMM itself is quite an interesting piece of technology. In a nutshell, it’s flash storage, which you can put in the memory DIMM slots. Enabled by Diablo’s Memory Channel Storage™ technology, this practically means low latency and some unique performance characteristics.

These devices are special, because their full performance potential is unlocked by using multiple devices to leverage the parallelism of the memory subsystem. In a modern CPU there is more than one memory controller (4 is typical nowadays) and spreading eXFlash™ DIMMs across them will provide maximum performance. There are quite some details about the device that are omitted in this post, they can be found in

  [Read more...]
Sysbench Benchmarking of Tesora’s Database Virtualization Engine
+2 Vote Up -0Vote Down

Tesora, previously called Parelastic, asked Percona to do a sysbench benchmark evaluation of its Database Virtualization Engine on specific architectures on Amazon EC2.

The focus of Tesora is to provide a scalable Database As A Service platform for OpenStack. The Database Virtualization Engine (DVE) plays a part in this as it aims at allowing databases to scale transparently across multiple MySQL shards.

DVE was open sourced last

  [Read more...]
Semi-sync replication is not slow!
Employee +2 Vote Up -0Vote Down

If you read Yoshinori's post about Semi-sync at Facebook, he lists the objective of using semi-sync as an alternative to running full durability on a master. That is to say that once you can guarantee writes have safely been shipped across the network, you may not strictly need to guarantee that they are safe locally.

This is something that I have been wanting to benchmark for a long time, and reading Jay's post about Semi-sync replication performance in MySQL 5.7 DMR4 and a conversation in last week's #dbhangops inspired me to explore this in more detail. For my tests, I will be using Master-Slave replication and three alternative definitions of

  [Read more...]
Do not trust vmstat IOwait numbers
+0 Vote Up -0Vote Down

I’ve been running a benchmark today on my old test box with conventional hard drives (no raid with BBU) and noticed something unusual in the CPU utilization statistics being reported.

The benchmark was run like this:

sysbench --num-threads=64 --max-requests=0 --max-time=600000 --report-interval=10 --test=oltp --db-driver=mysql --oltp-dist-type=special  --oltp-table-size=1000000   --mysql-user=root --mysql-password=password  run

Which means: create 64 threads and hammer the database with queries as quickly as possible. As the test was run on the localhost I would expect the benchmark to completely saturate the system – being either using CPU or being blocked on IO nature of this benchmark so it does not spend a lot on database locks, especially as this system has just 2 cores.

Looking at VMSTAT however I noticed

  [Read more...]
How to improve InnoDB performance by 55% for write-bound loads
+2 Vote Up -0Vote Down

During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write buffer is, it is a disk buffer were pages are written before being written to the actual data file. Upon restart, pages in the double write buffer are rewritten to their data files if complete. This is to avoid data file corruption with half written pages. I knew it has an impact on performance, on ZFS since it is transactional I always disable it, but I never realized how important the performance

  [Read more...]
Benchmark: SimpleHTTPServer vs pyclustercheck (twisted implementation)
+0 Vote Up -0Vote Down

Github user Adrianlzt provided a python-twisted alternative version of pyclustercheck per discussion on issue 7.

Due to sporadic performance issues noted with the original implementation in SimpleHTTPserver, the benchmarks which I’ve included as part of the project on github use mutli-mechanize library,

  • cache time 1 sec
  • 2 x 100 thread pools
  • 60s ramp up time
  • 600s total duration
  • testing simulated node fail (always returns 503, rechecks mysql node on cache expiry)
  • AMD FX(tm)-8350 Eight-Core Processor
  • Intel 330 SSD
  • local loop back test (127.0.0.1)

The SimpleHTTPServer instance faired as follows:

  [Read more...]
Tips on benchmarking Go + MySQL
+0 Vote Up -0Vote Down

We just released, as an open source release, our new percona-agent (https://github.com/percona/percona-agent), the agent to work with Percona Cloud Tools. This agent is written in Go.

I will give a webinar titled “Monitoring All MySQL Metrics with Percona Cloud Tools” on June 25 that will cover the new features in percona-agent and Percona Cloud Tools, where I will also explain how it works. You are welcome to

  [Read more...]
ScaleArc: Real-world application testing with WordPress (benchmark test)
+0 Vote Up -0Vote Down

ScaleArc recently hired Percona to perform various tests on its database traffic management product. This post is the outcome of the benchmarks carried out by me and ScaleArc co-founder and chief architect, Uday Sawant.

The goal of this benchmark was to identify ScaleArc’s overhead using a real-world application – the world’s most popular (according to wikipedia) content management system and blog engine: WordPress.

The tests also sought to identify the benefit of caching for this type of workload. The caching parameters represent more real-life circumstances than we applied in the sysbench performance tests

  [Read more...]
ScaleArc: Benchmarking with sysbench
+0 Vote Up -0Vote Down

ScaleArc recently hired Percona to perform various tests on its database traffic management product. This post is the outcome of the benchmarks carried out by Uday Sawant (ScaleArc) and myself. You can also download the report directly as a PDF here.

The goal of these benchmarks is to identify the potential overhead of the ScaleArc software itself and the potential benefits of caching. The benchmarks were carried out with the trunk version of sysbench. For this reason, we used a very small set of data, so the measurements will be fast, and it’s known that caching has huge benefits when the queries themselves are rather expensive. We decided that we would rather show that benefit with a real-world application, which is coming later is this series.

  [Read more...]
Presenting MySQL 5.7 Performance & Benchmarks at Percona Live
Employee_Team +2 Vote Up -0Vote Down

I’ll speak about MySQL 5.7 Performance & Benchmarks during the incoming Percona Live and will be happy to share with you all our latest finding, improvements, benchmark results, open issues and many other stuff keeping our brains in constant activity ) As you know, there is no a “silver bullet” solution for MySQL Performance tuning.. – only by a good understanding of what is going inside of MySQL and InnoDB you may configure your MySQL server in the most optimal way for your workloads. And this topic is endless, and progressing every month with new solutions and features – all parties are working hard here, and I’m very

  [Read more...]
The MySQL ARCHIVE storage engine – Alternatives
+0 Vote Up -0Vote Down

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data

  [Read more...]
16000 active connections – Percona Server continues to work when others die
+0 Vote Up -0Vote Down

We just published results with improvements in Thread Pool in Percona Server:
Percona Server: Thread Pool Improvements for Transactional Workloads
Percona Server: Improve Scalability with Thread Pool

What I am happy to see is that Percona Server is able to handle a tremendous amount of user connections. From our charts you can see it can go to 16000 active connections without a decline in throughput.



  [Read more...]
Percona Server: Thread Pool Improvements for Transactional Workloads
+1 Vote Up -0Vote Down

In a previous thread pool post, I mentioned that in Percona Server we used an open source implementation of MariaDB’s thread pool, and enhanced/improved it further. Below I would like to describe some of these improvements for transactional workloads.

When we were evaluating MariaDB’s thread pool implementation, we observed that it improves scalability for AUTOCOMMIT statements. However, it does not scale well with multi-statement transactions. The UPDATE_NO_KEY test which was run as an AUTOCOMMIT statement and inside a transaction gave the following results:

  [Read more...]
Percona Server: Improve Scalability with Percona Thread Pool
+0 Vote Up -0Vote Down

By default, for every client connection the MySQL server spawns a separate thread which will process all statements for this connection. This is the ‘one-thread-per-connection’ model. It’s simple and efficient until some number of connections N is reached. After this point performance of the MySQL server will degrade, mostly due to various contentions caused by N threads that are trying to access shared resources: either system ones like CPU, IO, memory or MySQL specific: structures/locks/etc. To keep the system stable and avoid degradation in the performance we need to limit the number of active threads, and at the same time we do not want to limit number of the client connections. The ‘Thread Pool’ model helps us to achieve that. It allows mapping N client connections to M number of active threads (actually

  [Read more...]
MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin
Employee_Team +5 Vote Up -0Vote Down

Or I could place in the title – “Yes, we done it!”

After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished

While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:

  [Read more...]
MySQL encryption performance, revisited
+1 Vote Up -0Vote Down

This is part two on a two-part series on the performance implications of in-flight data encryption with MySQL. In the first part, I focused specifically on the impact of using MySQL’s built-in SSL support with some rather surprising results. Certainly it was expected that query throughput would be lower with SSL than without, but I was rather surprised by the magnitude of the performance hit incurred at connection setup time. These results naturally lended themselves to some further investigation; in particular, I wanted to compare performance differences between MySQL’s built-in SSL encryption facilities and external encryption technologies, such as SSH tunneling. I’ll also be using this post to address a couple of questions posed in the comments on my

  [Read more...]
SSL Performance Overhead in MySQL
+1 Vote Up -0Vote Down

NOTE: This is part 1 of what will be a two-part series on the performance implications of using in-flight data encryption.

Some of you may recall my security webinar from back in mid-August; one of the follow-up questions that I was asked was about the performance impact of enabling SSL connections. My answer was 25%, based on some 2011 data that I had seen over on yaSSL’s website, but I included the caveat that it is workload-dependent, because the most expensive part of using SSL is establishing the connection. Not long thereafter, I received a request to conduct some more specific benchmarks surrounding SSL usage in MySQL,

  [Read more...]
A closer look at Percona Server 5.6
+1 Vote Up -0Vote Down

Yesterday we announced the GA release of Percona Server 5.6, the latest release of our enhanced, drop-in replacement for MySQL. Percona Server 5.6 is the best free MySQL alternative for demanding applications. Our third major release, Percona Server 5.6 offers all the improvements found in MySQL 5.6 Community Edition plus scalability, availability, backup, and security features some of which are found only in MySQL 5.6 Enterprise Edition.

Percona Server 5.6 comes with:

  [Read more...]
Percona Live London 2013: an insider’s view of the schedule
+1 Vote Up -0Vote Down

With the close of call for papers earlier this month, the Percona Live London conference committee was in full swing this past week reviewing all of the many submissions for November’s Percona Live London MySQL Conference.

The submissions are far ranging and cover some really interesting topics, making the lineup for Percona Live London really strong! What the committee looks for in a submission is how much “value” a talk will bring to the

  [Read more...]
TokuDB vs InnoDB in timeseries INSERT benchmark
+0 Vote Up -0Vote Down

This post is a continuation of my research of TokuDB’s  storage engine to understand if it is suitable for timeseries workloads.

While inserting LOAD DATA INFILE into an empty table shows great results for TokuDB, what’s more interesting is seeing some realistic workloads.

So this time let’s take a look at the INSERT benchmark.

What I am going to do is to insert data in 16 parallel threads into the table from the previous post:

CREATE TABLE `sensordata` (
  `ts` int(10)
  [Read more...]
Considering TokuDB as an engine for timeseries data
+2 Vote Up -0Vote Down

I am working on a customer’s system where the requirement is to store a lot of timeseries data from different sensors.

For performance reasons we are going to use SSD, and therefore there is a list of requirements for the architecture:

  • Provide high insertion rate
  • Provide a good compression rate to store more data on expensive SSDs
  • Engine should be SSD friendly (less writes per timeperiod to help with SSD wear)
  • Provide a reasonable response time (within ~50 ms) on SELECT queries on hot recently inserted data

Looking on these requirements I actually think that TokuDB might be a good fit for this task.

  [Read more...]
InnoDB Full-text Search in MySQL 5.6: Part 3, Performance
+1 Vote Up -0Vote Down

This is part 3 of a 3 part series covering the new InnoDB full-text search features in MySQL 5.6. To catch up on the previous parts, see part 1 or part 2

Some of you may recall a few months ago that I promised a third part in my InnoDB full-text search (FTS) series, in which I’d actually take a look at the performance of InnoDB FTS in MySQL 5.6 versus traditional MyISAM FTS. I hadn’t planned on quite such a gap between part 2 and part 3, but as they say, better late than never. Recall that we have been working with two data sets, one which I call SEO (8000-keyword-stuffed web pages) and the other which I call

  [Read more...]
When it’s faster to use SQL in MySQL NDB Cluster over memcache API
+3 Vote Up -0Vote Down

Memcache access for MySQL Cluster (or NDBCluster) provides faster access to the data because it avoids the SQL parsing overhead for simple lookups – which is a great feature. But what happens if I try to get multiple records via memcache API (multi-GET) and via SQL (SELECT with IN())? I’ve encountered this a few times now, so I decided to blog about it. I did a very simple benchmark with the following script:

#!/bin/bash
mysql_server="192.168.56.75"
mc_server="192.168.56.75"
mysql_cmd="mysql -h${mysql_server} --silent --silent"
mysql_schema="percona"
mysql_table="memcache_t"
mc_port=11211
mc_prefix="mt:"
function populate_data () {
  nrec=$1
  $mysql_cmd -e "delete from ${mysql_table};" $mysql_schema > /dev/null 2>&1
  for rec in `seq 1 $nrec`
  do
    $mysql_cmd -e "insert into ${mysql_table} values ($rec,
  [Read more...]
Shard-Query 2.0 performance on the SSB with InnoDB on Tokutek’s MariaDB distribution
+2 Vote Up -0Vote Down
Scaling up a workload to many cores on a single host

Here are results for Shard-Query 2.0 Beta 1* on the Star Schema Benchmark at scale factor 10.  In the comparison below the “single threaded” response times for InnoDB are the response times reported in my previous test which did not use Shard-Query.

Shard-Query configuration

Shard-Query has been configured to use a single host.  The Shard-Query configuration repository is stored on the host.  Gearman is also running on the host, as are the Gearman workers.  In short, only one host is involved in the testing.

The

  [Read more...]
TokuDB vs Percona XtraDB using Tokutek’s MariaDB distribution
+1 Vote Up -0Vote Down

Following are benchmark results comparing Tokutek TokuDB and Percona XtraDB at scale factor 10 on the Star Schema benchmark. I’m posting this on the Shard-Query blog because I am going to compare the performance of Shard-Query on the benchmark on these two engines. First, however, I think it is important to see how they perform in isolation without concurrency.

Because I am going to be testing Shard-Query, I have chosen to partition the “fact” table (lineorder) by month. I’ve attached the full DDL at the end of the post as well as the queries again for reference.

I want to note a few things about the results:
First and foremost, TokuDB was configured to use quicklz compression (the default) and InnoDB compression was not used. No tuning of TokuDB was performed, which means it will use up to 50% of memory by

  [Read more...]
MySQL and the SSB – Part 2 – MyISAM vs InnoDB low concurrency
+1 Vote Up -0Vote Down

This blog post is part two in what is now a continuing series on the Star Schema Benchmark.

In my previous blog post I compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings using only the InnoDB storage engine.  In my testing I discovered that innodb_old_blocks_time had an effect on performance of the benchmark.  There was some discussion in the comments and I promised to follow up with more SSB tests at a later date.

I also promised more low concurrency SSB tests when Peter blogged about the importance of performance at low concurrency.

The SSB
The SSB tests a

  [Read more...]
Showing entries 1 to 30 of 221 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.