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 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 216 Next 30 Older Entries

Displaying posts with tag: Benchmarks (reset)

Testing the Micron P320h
+3 Vote Up -0Vote Down

The Micron P320h SSD is an SLC-based PCIe solid-state storage device which claims to provide the highest read throughput of any server-grade SSD, and at Micron’s request, I recently took some time to put the card through its paces, and the numbers are indeed quite impressive.

For reference, the benchmarks for this device were performed primarily on a Dell R720 with 192GB of RAM and two Xeon E5-2660 processors that yield a total of 32 virtual cores. This is the same machine which was used in my previous benchmark run. A small handful of additional tests were also performed using the Cisco UCS C250. The operating system in use was CentOS 6.3, and for the sysbench fileIO tests, the EXT4 filesystem was used. The card itself is the 700GB model.

So let’s take

  [Read more...]
Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2
+2 Vote Up -0Vote Down

In addition to the problem with trx_list scan we discussed in Friday’s post, there is another issue in InnoDB transaction processing that notably affects MySQL performance – for every transaction InnoDB creates a read view and allocates memory for this structure from heap. The problem is that the heap for that allocation is destroyed on each commit and thus the read view memory is reallocated on the next transaction.

There

  [Read more...]
trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
+1 Vote Up -0Vote Down

One major problem in terms of MySQL performance that still stands in the way of InnoDB scalability is the trx_list scan on consistent read view creation. It was originally reported as a part of MySQL bug #49169 and can be described as follows. Whenever a connection wants to create a consistent read, it has to make a snapshot of the transaction states to determine which transactions are seen in the view later. To this end, InnoDB scans trx_list (i.e. the list of currently open transactions) and copies IDs of transactions that have not yet been committed at the

  [Read more...]
Why MySQL Performance at Low Concurrency is Important
+0 Vote Up -0Vote Down

A few weeks ago I wrote about “MySQL Performance at High Concurrency” and why it is important, which was followed up by Vadim’s post on ThreadPool in Percona Server providing some great illustration on the topic. This time I want to target an opposite question: why MySQL performance at low concurrency is important for you.

I decided to write about this topic as a number of recent blog

  [Read more...]
Facebook at Percona Live MySQL Conference and Expo and Advanced Registration Ending Soon
+1 Vote Up -0Vote Down

Facebook is a major user of MySQL and has pushed the performance limits of the technology. Their MySQL experts have deep, hands on knowledge of the technology. I’m pleased to welcome Mark Callaghan, Software Engineer for Database Infrastructure at Facebook, back again this year to the Percona Live MySQL Conference & Expo to share his expertise. Mark was a keynote speaker at last

  [Read more...]
Oracle Technical Experts at the Percona Live MySQL Conference and Expo
+12 Vote Up -0Vote Down

I’m pleased to announce that Oracle is sending some of their top technical people to speak at the Percona Live MySQL Conference and Expo. The conference takes place April 22-25, 2013 at the Santa Clara Convention Center and Hyatt Santa Clara.

Tomas Ulin, VP, MySQL Engineering for Oracle, will present an invited keynote talk on “Driving MySQL Innovation” during the Tuesday morning opening keynotes. With the recent release of MySQL 5.6, conference attendees will hear about the latest developments of this

  [Read more...]
MySQL performance: Impact of memory allocators (Part 2)
+3 Vote Up -0Vote Down

Last time I wrote about memory allocators and how they can affect MySQL performance in general. This time I would like to explore this topic from a bit different angle: What impact does the number of processor cores have on different memory allocators and what difference we will see in MySQL performance in this scenario?

Let me share a conclusion first: If you have a server with more than 8 cores you should use something different than the default glibc memory allocator.
We recommend jemalloc or tcmalloc
.

In my test I will use Dell R720 box(spec), Centos 6.3, upcoming Percona Server 5.5.30 and 3


  [Read more...]
Investigating MySQL Replication Latency in Percona XtraDB Cluster
+2 Vote Up -0Vote Down

Investigating MySQL Replication Latency in Percona XtraDB Cluster

I was curious to check how Percona XtraDB Cluster behaves when it comes to MySQL replication latency — or better yet, call it data propagation latency. It was interesting to see whenever I can get stale data reads from other cluster nodes after write performed to some specific node. To test it I wrote quite a simple script (you can find it in the end of the post) which connects to one node in the cluster, performs an update and then immediately does the read from second node. If the data has been

  [Read more...]
Mystery Performance Variance with MySQL Restarts
+4 Vote Up -0Vote Down

Based on a lot of surprising comments about my MySQL 5.5 vs 5.6 performance post I decided to perform deeper investigation to see where my results could go possibly wrong. I had set up everything to be as simple as possible to get maximally repeatable results. I did Read Only ran which is typically a lot more repeatable (though also less relevant for production like workload). I had done number of iterations for benchmark run and I used dedicated physical hardware box so external environment impact often causing problems in Virtualized environments can be eliminated. Still I found there could be large variance between the runs.

I set up the benchmarks run to go over night in the loop, doing the benchmark run for 5 runs when restarting MySQL server and

  [Read more...]
L2 cache for MySQL
+0 Vote Up -0Vote Down

The idea to use SSD/Flash as a cache is not new, and there are different solutions for this, both OpenSource like L2ARC for ZFS and Flashcache from Facebook, and proprietary, like directCache from Fusion-io.
They all however have some limitations, that’s why I am considering to have L2 cache on a database level, as an extension to InnoDB buffer pool.
Fortunately, there is a project in progress Flash_Cache_For_InnoDB by David which implements this.
David helped us to port his work to the latest Percona Server and you can get it from our launchpad Percona Server


  [Read more...]
MySQL 5.6.7-RC in tpcc-mysql benchmark
+2 Vote Up -0Vote Down

MySQL 5.6.7 RC is there, so I decided to test how it performs in tpcc-mysql workload from both performance and stability standpoints.
I can’t say that my experience was totally flawless, I bumped into two bugs:

But at the end, is not this why RC for? And Oracle asked for a feedback, so I do my part.

  • Benchmark date: Oct-2012
  • Benchmark goal: Test how MySQL 5.6.7 performs
  • Hardware specification
    • Server: Dell PowerEdge R710
    • CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz

  [Read more...]
A (prototype) lower impact slow query log
+1 Vote Up -0Vote Down

Yesterday, over at my personal blog, I blogged about the impact of the MySQL slow query log. Since we’re working on Percona Server 5.6, I did wonder if this was a good opportunity to re-examine how we could provide slow query log type functionality to our users.

The slow query log code inside the MySQL server does several things that aren’t good for concurrency on modern systems. For starters, it takes a mutex while both doing formatting of what to write and while doing the write(2) system call itself. This is, in fact, entirely unneccesary.

From the man page for the write system call:

If the file was open(2)ed with O_APPEND, the file offset is first set to the end of the file before writing. The adjustment of the file offset

  [Read more...]
Record performance with PCIe Micron RealSSD™ P320h
+1 Vote Up -0Vote Down

I have a chance to test Micron RealSSD™ P320h. Initially I was expecting a good performance, but you know, how big could be a difference with other products on market? PCIe SSD market is getting crowded, and every company is trying to show the best performance. And at the end, there is a single PCIe slot, single controller, we are probably about to reach limits of these components.
However I was really surprised to see performance numbers with Micron P320h.
In random reads the throughput is 3200 MiB/sec, while the best results I’ve seen so far was 1450 MiB/sec on single card and 2300 MiB/sec on duo.

And this is 16KiB blocksize, which gives us 200.000 random reads IOP/sec, again in 16K blocks



  [Read more...]
Intel SSD 910 vs HDD RAID in tpcc-mysql benchmark
+1 Vote Up -0Vote Down

I continue my benchmarks of Intel SSD 910, previous time I compared it with Fusion-io ioDrive http://www.mysqlperformanceblog.com/2012/09/07/intel-ssd-910-in-tpcc-mysql-benchmark/. Now I want to test this card against RAID over spinning disks.

  • Benchmark date: Sep-2012
  • Benchmark goal: Test Intel SSD 910 under tpcc-mysql workload and compare with HDD RAID10
  • Hardware specification
    • Server: Dell PowerEdge R710
    • CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
    • Memory: 192GB
    • Storage: Hardware RAID10 over 8 disks, card: Perc H710, disks: Seagate ST9750420AS 750GB, 2.5″, 7200RPM, 16MB, SATA. Intel SSD 910 (software RAID over 2x200GB devices)
    • Filesystem: ext4
  •   [Read more...]
    Adaptive flushing in MySQL 5.6 – cont
    +1 Vote Up -0Vote Down

    This is to continue my previous experiments on adaptive flushing in MySQL 5.6.6. Now I am running Ubuntu 12.04, which seems to provide a better throughput than previous system (CentOS 6.3), it also changes the profile of results.

    So, as previous I run tpcc-mysql 2500W, against MySQL 5.6.6 with innodb_buffer_pool_size 150GB, and now I vary innodb_buffer_pool_instances as was advised in comments to previous post. I also tried to vary innodb_flushing_avg_loops, but it does not affect results significantly.

    So, let’s see throughput with 10 sec averages.



      [Read more...]
    Intel SSD 910 in tpcc-mysql benchmark
    +1 Vote Up -0Vote Down

    I continue my benchmarks of Intel SSD 910, the raw IO results are available in my previous experiment. Now I want to test this card under MySQL workload to see if the card is suitable to use with MySQL.

  • Benchmark date: Sep-2012
  • Benchmark goal: Test Intel SSD 910 under tpcc-mysql workload and compare with baseline Fusion-io ioDrive card
  • Hardware specification
    • Server: Dell PowerEdge R710
    • CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
    • Memory: 192GB
    • Storage: Fusion-io ioDrive 640GB, Intel SSD 910 (software RAID over 2x200GB devices)
    • Filesystem: ext4
  • Software
    • OS: Ubuntu 12.04.1
    • MySQL Version: Percona Server 5.5.27-28.1
  • Benchmark specification
    •   [Read more...]
      Testing Intel® SSD 910
      +3 Vote Up -0Vote Down

      Intel came on PCI-e SSD market with their Intel SSD 910 card. With a slogan “The ultimate data center SSD” I assume Intel targets rather a server grade hardware, not consumer level.
      I’ve got one of this card into our lab. I should say it is very price competitive, comparing with other enterprise level PCIe vendors. For a 400GB card I paid $2100, which gives $5.25/GB. Of course I’ve got some performance numbers I’d like to share.

      But before that, few words on the card internals. Intel puts separate 200GB modules, so 400GB card is visible as 2 x 200GB devices in operation system, and 800GB card is visible as 4 different devices. After that you can do software raid0, raid1 or raid10, whatever you prefer.

      For my tests I used


        [Read more...]
      Adaptive flushing in MySQL 5.6
      +3 Vote Up -0Vote Down

      As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e.
      http://www.mysqlperformanceblog.com/2011/09/18/disaster-mysql-5-5-flushing/
      http://www.mysqlperformanceblog.com/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/
      http://www.mysqlperformanceblog.com/2011/01/03/mysql-5-5-8-in-search-of-stability/

      In MySQL 5.6 there was implemented a new flushing logic, so I decided to check what do we have now.


      For experiment I took tpcc-mysql workload (3 hours runs), 2500W ( ~250GB of data), on Dell




        [Read more...]
      Impact of memory allocators on MySQL performance
      +4 Vote Up -0Vote Down

      MySQL server intensively uses dynamic memory allocation so a good choice of memory allocator is quite important for the proper utilization of CPU/RAM resources. Efficient memory allocator should help to improve scalability, increase throughput and keep memory footprint under the control. In this post I’m going to check impact of several memory allocators on the performance/scalability of MySQL server in the read-only workloads.

      For my testing i chose following allocators: lockless, jemalloc-2.2.5, jemalloc-3.0, tcmalloc(gperftools-2.0), glibc-2.12.1(new malloc)(CentOS 6.2), glibc-2.13(old malloc), glibc-2.13(new malloc), glibc-2.15(new malloc).


        [Read more...]
      DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
      +2 Vote Up -1Vote Down

      Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the tables had to be dropped more frequently (like every other hour), or when there was no such thing as off-peak hours, in such cases you need consistent performance. But dropping tables is known to cause stalls that exhibit themselves as reduced QPS during the table drops or stalls. Percona Server since version 5.1 has a feature know as “Lazy Drop Table” that alleviates the problem to a great extent

        [Read more...]
      Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
      +3 Vote Up -0Vote Down

      Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb team has chosen to use very small (just 1MB) and hard coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process.
      Mark Callaghan and Facebook Team has fixed this in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date.

      Here is example of gains you can expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench


        [Read more...]
      Comparing Percona XtraDB Cluster with Semi-Sync replication Cross-WAN
      +2 Vote Up -0Vote Down

      I have a customer who is considering Percona XtraDB Cluster (PXC) in a two colo WAN environment.  They wanted me to do a test comparing PXC against semi-synchronous replication to see how they stack up against each other.

      Test Environment

      The test environment included AWS EC2 nodes in US-East and US-West (Oregon).  The ping RTT latency between these nodes was right around 100ms.

      All environments used Percona Server or Percona XtraDB Cluster server 5.5.24.  Innodb durability was disabled for all tests (innodb_flush_log_at_trx_commit=2).  All other settings were the same kept the same unless otherwise noted.

      I tested against the following

        [Read more...]
      How expensive is USER_STATISTICS?
      +4 Vote Up -0Vote Down

      One of our customers asked me whether it’s safe to enable the so-called USER_STATISTICS features of Percona Server in a heavy-use production server with many tens of thousands of tables.

      If you’re not familiar with this feature, it creates some new INFORMATION_SCHEMA tables that add counters for activity on users, hosts, tables, indexes, and more. This is very helpful information. It lets you run queries to see which tables get the most reads and writes, for example. Another popular use for it is to find unused indexes.

      I knew that some of our customers were using the feature enabled in production all the time, and I knew that Google and Facebook and others (the

        [Read more...]
      A case for MariaDB’s Hash Joins
      +2 Vote Up -0Vote Down

      MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks for different queries and explain the results so that you have a better understanding of when using the Hash Join will be best and when not. Although Hash Joins are available since MariaDB 5.3, but I will be running my benchmarks on the newer MariaDB 5.5.

      Overview

      Hash Join is a new algorithm introduced in MariaDB 5.3/5.5 that can be used for joining tables that have a equijoin conditions of the form tbl1.col1 = tbl2.col1, etc. As I mentioned above that what is actually implemented is the Classic Hash Join. But its known as Block Nested

        [Read more...]
      btrfs – probably not ready yet
      +5 Vote Up -0Vote Down

      Every time I have a conversation on SSD, someone mentions btrfs filesystem. And usually it is colored as a solution that will solve all our problems, improve overall performance and SSD in particular, and it is a saviour. Of course it caught my curiosity and I decided to perform a benchmark similar to what I did on ext4 filesystem over Intel 520 SSD.
      I was prepared for surprises, as even on formatting stage, mkfs.btrfs says that filesystem is EXPERIMENTAL. In case with filesystems I kind of agree with Stewart, so question #1, what you should ask deciding on what filesystem to use, is “Was this filesystem used in a production more than 5 years?”, so from this point, btrfs has a

        [Read more...]
      Intel 520 SSD in MySQL sysbench oltp benchmark
      +1 Vote Up -0Vote Down

      In my raw IO benchmark of Intel 520 SSD we saw that the drive does not provide uniform throughput and response time, but it is interesting how does it affect workload if it comes from MySQL.
      I prepared benchmarks results for Sysbench OLTP workload with MySQL running on Intel 520.
      You can download it there.

      There I want to publish graphs to compare Intel 520 vs regular RAID10.

      Throughput:

      Response time:






        [Read more...]
      Benchmarking single-row insert performance on Amazon EC2
      +0 Vote Up -0Vote Down

      I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.

      OK, let’s start off with the configuration details.

      Configuration

      First of all let me describe the EC2 instance type that I used.

      EC2 Configuration

      I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.

      High-Memory Quadruple Extra Large
        [Read more...]
      Testing Fusion-io ioDrive2 Duo
      +1 Vote Up -0Vote Down

      I was lucky enough to get my hands on new Fusion-io ioDrive2 Duo card. So I decided to run the same series of tests I did for other Flash devices. This is ioDrive2 Duo 2.4TB card and it is visible to OS as two devices (1.2TB each), which can be connected together via software RAID. So I tested in two modes: single drive, and software RAID-0 over two drives.

      I should note that to run this card you need to have an external power, by the same reason I mentioned in the previous post: PCIe slot can provide only 25W power, which is not enough for ioDrive2 Duo to provide full performance. I mention this, as it may be challenge for some servers: some models may not

        [Read more...]
      New distribution of random generator for sysbench – Zipf
      +2 Vote Up -0Vote Down

      Sysbench has three distribution for random numbers: uniform, special and gaussian. I mostly use uniform and special, and I feel that both do not fully reflect my needs when I run benchmarks. Uniform is stupidly simple: for a table with 1 mln rows, each row gets equal amount of hits. This barely reflects real system, it also does not allow effectively test caching solution, each row can be equally put into cache or removed. That’s why there is special distribution, which is better, but to other extreme – it is skewed to very small percentage of rows, which makes this distribution good to test cache, but it is hard to emulate high IO load.

      That’s why I was looking for alternatives, and Zipfian distribution seems decent one. This distribution has a parameter θ

        [Read more...]
      Testing Fusion-io ioDrive – now with driver 3.1
      +0 Vote Up -0Vote Down

      In my previous post with results for Fusion-io ioDrive we saw some instability in results, I was pointed that it may be fixed in new drivers VSL 3.1.1. I am not sure if this driver is available for everyone – if you are interested, please contact your Fusion-io support representative. I installed new drivers and firmware, and in fact, the result improved.

      Information about driver and firmware: Firmware v6.0.0, rev 107006. Fusion-io driver version: 3.1.1 build 172.

      Actually an upgrade was not flawless, after a firmware upgrade I had to perform low-level formatting, which erase all data. So if you want to do the same – make sure you copy your data.

      So there are results for driver 3.1 (with comparison to previous driver 2.3)



        [Read more...]
      Previous 30 Newer Entries Showing entries 31 to 60 of 216 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.