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 and the write operation are performed as an atomic step.

So we can …

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 (not 512 or 4096 usually used in public benchmarks).

The write random …

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.

• 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

• OS: Ubuntu 12.04.1
• MySQL Version: Percona Server 5.5.27-28.1

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.

Obviously with innodb_buffer_pool_instances=1 the result is better and more …

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.

• 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

• OS: Ubuntu 12.04.1
• MySQL Version: Percona Server 5.5.27-28.1

• Benchmark name: tpcc-mysql
• Scale factor: 2500W (~250GB of data)
• Benchmark …

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 single 200GB device and pair combined in software raid0 (Duo).

…

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 PowerEdge 720 box with …

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).

Let me clarify a bit about malloc …

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 but does not get rid of it completely. In the new releases of MySQL (versions >= 5.5.23) work has been done on reducing …

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 table.

Buffer Length   |  alter table sbtest add key(c)
1MB                      34 sec
8MB …