I’m going to deliver MySQL Training next week (May 21-24) in London.
This is a rare opportunity as I do not personally deliver a lot of Training, especially outside of US. There are still some places left if you want to sign up.
You will also get a signed copy of High Performance MySQL 3rd edition as an attendee.
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.
First of all let me describe the EC2 instance type that I used.
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 Instance 68.4 GB of memory 26 EC2 Compute Units (8 virtual cores with 3.25 EC2 Compute Units each) 1690 GB of instance storage 64-bit platform I/O Performance: High API name: m2.4xlarge
As for the IO configuration I chose 8 x 200G EBS volumes in software RAID 10.
Now let’s come to the MySQL configuration.
I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used:
## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive_flushing = 1 innodb_adaptive_flushing_method = estimate innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0
You can see that the buffer pool is sized at 55G and I am using 4 buffer pool instances to reduce the contention caused by buffer pool mutexes. Another important configuration that I am using is that I am using “estimate” flushing method available only on Percona Server. The “estimate” method reduces the impact of traditional InnoDB log flushing, which can cause downward spikes in performance. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload.
OK, so that was all about the configuration of the EC2 instance and MySQL.
Now as far as the benchmark itself is concerned, I made no code changes to iiBench, and used the version available here. But I changed the table to use range partitioning. I defined a partitioning scheme such that every partition would hold 100 million rows.
The table structure of the table with no secondary indexes is as follows:
CREATE TABLE `purchases_noindex` ( `transactionid` int(11) NOT NULL AUTO_INCREMENT, `dateandtime` datetime DEFAULT NULL, `cashregisterid` int(11) NOT NULL, `customerid` int(11) NOT NULL, `productid` int(11) NOT NULL, `price` float NOT NULL, PRIMARY KEY (`transactionid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (500000000) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (600000000) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (700000000) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
While the structure of the table with secondary indexes is as follows:
CREATE TABLE `purchases_index` ( `transactionid` int(11) NOT NULL AUTO_INCREMENT, `dateandtime` datetime DEFAULT NULL, `cashregisterid` int(11) NOT NULL, `customerid` int(11) NOT NULL, `productid` int(11) NOT NULL, `price` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (200000000) ENGINE = InnoDB, PARTITION p2 VALUES LESS THAN (300000000) ENGINE = InnoDB, PARTITION p3 VALUES LESS THAN (400000000) ENGINE = InnoDB, PARTITION p4 VALUES LESS THAN (500000000) ENGINE = InnoDB, PARTITION p5 VALUES LESS THAN (600000000) ENGINE = InnoDB, PARTITION p6 VALUES LESS THAN (700000000) ENGINE = InnoDB, PARTITION p7 VALUES LESS THAN (800000000) ENGINE = InnoDB, PARTITION p8 VALUES LESS THAN (900000000) ENGINE = InnoDB, PARTITION p9 VALUES LESS THAN (1000000000) ENGINE = InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
Also, I ran 5 instances of iiBench simultaneously to simulate 5 concurrent connections writing to the table, with each instance of iiBench writing 200 million single row inserts, for a total of 1 billion rows. I ran the test both with the table purchases_noindex which has no secondary index and only a primary index, and against the table purchases_index which has 3 secondary indexes. Another thing I would like to share is that, the size of the table without secondary indexes is 56G while the size of the table with secondary indexes is 181G.
Now let’s come down to the interesting part.
With the table purchases_noindex, that has no secondary indexes, I was able to achieve an avg. insert rate of ~25k INSERTs Per Second, while with the table purchases_index, the avg. insert rate reduced to ~9k INSERTs Per Second. Let’s take a look at the graphs have a better view of the whole picture.
Note, in the above graph, we have “millions of rows” on the x-axis and “INSERTs Per Second” on the y-axis.
The reason why I have chosen to show “millions of rows” on the x-axis so that we can see the impact of growth in data-set on the insert rate.
We can see that adding the secondary indexes to the table has decreased the insert rate by 3x, and its not even consistent. While with the table having no secondary indexes, you can see that the insert rate is pretty much constant remaining between ~25k to ~26k INSERTs Per Second. But on the other hand, with the table having secondary indexes, we can see that there are regular spikes in the insert rate, and the variation in the rate can be classified as large, because it varies between ~6.5k to ~12.5k INSERTs per second, with noticeable spikes after every 100 million rows inserted.
I noticed that the insert rate drop was mainly caused by IO pressure caused by increase in flushing and checkpointing activity. This caused spikes in write activity to the point that the insert rate was decreased.
As we all now there are pros and cons to using secondary indexes. While secondary indexes cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and hence having secondary indexes is an obvious choice. But for those applications that are write mostly or that rely a lot on write performance, reducing the no. of secondary indexes or even going away with secondary indexes causes a write throughput increase of 2x to 3x. In this particular case, since I was mostly concerned with write performance, so I went ahead to choose a table structure with no secondary indexes. Other important things to consider when you are concerned with write performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query cache.
Percona is glad to announce the release of Percona Server 5.5.23-25.3 on May 16, 2012 (Downloads are available here and from the Percona Software Repositories).
Based on MySQL 5.5.23, including all the bug fixes in it, Percona Server 5.5.23-25.3 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.23-25.3 milestone at Launchpad.
Bugs Fixed:
Release notes for Percona Server 5.5.23-25.3 are available in our online documentation.
Our previous GA release of Percona XtraDB Cluster caused a lot of interest and feedback. I am happy to announce next version Percona XtraDB Cluster 5.5.23, which comes with bug fixes and improvements.
List of changes:
Binaries are available from downloads area or from our repositories.
For this release we will provides binaries for Ubuntu 12.04, they are coming soon.
If you want to know more how to migrate to XtraDB Cluster, we will be giving a free webinar on June 6th.
This is an General Availability release. We did our best to eliminate bugs and problems during alpha and beta testing release, but this is a software, so bugs are expected. If you encounter them, please report to our bug tracking system.
Links:
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 have connector for external power, and for some you may need special “power kit”. So you need to make sure you have compatible hardware before getting Duo card. I personally ended up with setup like this: I use a separate power supply.
Fusion-io ioDrive2 Firmware v6.0.0, rev 107004 Public, Fusion-io driver version: 3.1.1.
Now to the results.
For this test I also use Cisco UCS C250 server, and on the graph I show the results for both single card and raid (Duo).
Random writes, async:
We see stable and predictable write performance, with throughput: 660 MiB/s for single, and 1300 MiB/s for Duo
Random reads:
Again both modes provides stable level of throughput. 1350 MiB/s for single and 2300 MiB/s for Duo.
Now with separation per thread for random read synchronous IO:
There is also excellent response time characteristics. 0.25ms and 0.19ms for 8 threads, single and Duo cases.
In general ioDrive2 seems to provide better and more stable performance results comparing to previous generation ioDrive.
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 ? (theta), which defines how skewed the distribution is. A physical sense of this parameter, if to apply to database tables, is following: say row 1 accessed N, then row 2 is accessed 2^? less times, row 3 is accessed 3^? less, …, row X is accessed X^? less times.
Say ?=1.1, then if row 1 accessed 1,000,000 times, then row 2 is : 1,000,000/(2^1.1)=466,516 times, row 3: 1,000,000/(2^1.1)=298,652 times, …, row id=10000 : 1,000,000/(10,000^1.1) = 39 times.
Obviously with ?=0 we are getting uniform distribution – each row is accessed equal times ( for row X: 1/(X^0) ).
There is a research that shows that user behavior can be described by this distribution: Zipf, Power-laws, and Pareto – a ranking tutorial
To see distribution on graphs, I took tables with 1mln rows and run row lookup 1 million times.
There are histograms on how many times each row selected for different ?: 0.5, 0.9, 1.1:
The curve is very skewed, so I zoomed graphs to show only 0-100k level:
I implemented Zipf for sysbench, right now it is in a separate tree https://code.launchpad.net/~vadim-tk/sysbench/zipf-distribution, you are welcome to try if it sounds interesting.
I am going to run couple incoming benchmarks with this distribution.
The recording and slides for my webinar on zero-downtime schema changes with MySQL are available now. Don’t miss Vadim’s webinar tomorrow!
Just as a reminder – tomorrow, May-9, 2012, at 11am PDT I will be giving a free webinar “MySQL and SSD”. This is the same talk I gave on Percona Live MySQL Conference, so if you were there – you probably will find nothing new. Otherwise, you are welcome to join!
You might have heard that Oracle made the decision not to support MySQL for IBM i any longer. This is certainly understandable. However, there are still users who want to continue running IBM i and MySQL.
That’s why we’re happy to announce that we have assisted Zend to introduce Zend DBi as a drop-in replacement for MySQL on the IBM i platform. Zend approached us to say that they want to ensure there’s a way forward for IBM i users, and asked if we’d help them. We’re delighted to do so.
The result is Zend DBi. It is basically a renamed build of MySQL for IBM i. It’s a 100% compatible drop-in replacement. Everything works on Zend DBi just as it works on MySQL, with no need to rewrite applications, management scripts, or anything else. There is no difference in query syntax, client-server protocol, or data storage on disk. Anything that runs on MySQL will run identically on Zend DBi with no modification, and vice versa.
In addition, Zend DBi will remain compatible with MySQL in the future, so it is a no-lock-in solution. If you want to leave the IBM i platform and switch to Oracle’s MySQL on another platform, it will work seamlessly.
Why is Percona involved? Because providing quality server builds is a substantial engineering effort that requires a lot of expertise to do right, and Percona has that expertise, as we’ve proven by providing our own Percona Server variant of the MySQL server. We have a history of improving the MySQL server and finding and solving bugs in it — we’ve even found and solved bugs on the IBM i platform.
We think that Zend DBi will be a great service to IBM i MySQL users who want to remain on their chosen platform. We’re happy that Zend took this initiative, and even happier that we can play a role.
Zend DBi is available under GPL license from Zend website.
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)
Random writes:
For random writes there is not much improvements, the throughput is about the same.
Random reads:
But there is a significant improvement for random reads. The results is stable on 640 MiB/sec level and it is higher than previously.
Sync random reads per threads, throughput:
Response time:
Again, there is improvement in throughput, in both in quality and absolute value. For response time – in some cases, there is 2x improvement.
So it seems for Fusion-io ioDrive it is worth to consider upgrade to 3.1 Driver (remember to copy your data before).
Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com
