Back in October I have write about possible ways of running multiple MySQL instances on the same hardware. As the months passing by, the project of splitting our database schemas into standalone instances is closing in, so I started to check the different ways. EDIT: This post is outdated, here is the follow up. I started […]
10 Older Entries »
During the last couple of months I have been involved in an unusually high amount of performance audits for e-commerce applications running with Magento. And although the systems were quite different, they also had one thing in common: the MySQL query cache was very useful. That was counter-intuitive for me as I’ve always expected the query cache to be such a bottleneck that response time is better when the query cache is turned off no matter what. That lead me to run a few experiments to better understand when the query cache can be helpful.
The query cache is well known for its contentions: a global mutex has to be acquired for any read or write operation, which means that any access is serialized. This was not an issue 15 years ago, but with today’s multi-core servers, such serialization is the best way to kill performance.
However from a performance …[Read more]
Last week I hosted a webinar on using MySQL in the cloud for High Availability (HA) alongside 451 Research analyst Jason Stamper. You can watch the recording and also download the slides (free) here. Just click the “Register” button at the end of that page.
We had several excellent questions and we didn’t have time to get to several of them in the allotted time. I’m posting them here along with the answers. Feel free to ask follow-up questions in the comments below.
Q: Can the TokuDB engine be used in a PXC environment?
A: No, TokuDB cannot currently be used in a PXC environment, the only supported engine in …[Read more]
In a post, written a few months ago, I found that using EXT4 transactions with the “data=journal” mount option, improves the write performance significantly, by 55%, without putting data at risk. Many people commented on the post mentioning they were not able to reproduce the results and thus, I decided to further investigate in order to find out why my results were different.
So, I ran sysbench benchmarks on a few servers and found when the InnoDB double-write buffer limitations occur and when they don’t. I also made sure some of my colleagues were able to reproduce the results. Basically, in order to reproduce the results you need the following conditions:
- Spinning disk (no SSD)
- Enough CPU power
- A dataset that fits in the InnoDB buffer pool
- A continuous high write …
An idea for a benchmark based on the “arrival request” rate that I wrote about in a post headlined “Introducing new type of benchmark” back in 2012 was implemented in Sysbench. However, Sysbench provides only a simple workload, so to be able to compare InnoDB with TokuDB, and later MongoDB with Percona TokuMX, I wanted to use more complicated scenarios. (Both TokuDB and TokuMX are part of Percona’s product line, in the case you missed Tokutek now part of the Percona family.)
Thanks to Facebook – they provide LinkBench, a benchmark that emulates the social graph …[Read more]
Enable sampling-based feedback-directed optimizations, and the following optimizations which are generally profitable only with profile feedback available: -fbranch-probabilities, -fvpt, -funroll-loops, -fpeel-loops, -ftracer, -ftree-vectorize,
-finline-functions, -fipa-cp, -fipa-cp-clone, -fpredictive-commoning, -funswitch-loops, -fgcse-after-reload, and -ftree-loop-distribute-patterns.
path is the name of a file containing AutoFDO profile information. If omitted, it defaults to fbdata.afdo in the current directory.
Producing an AutoFDO …
A few years back Deva wrote about how to use tcpdump on very busy hosts. That post sparked my interest about exploring how to measure the impact of tcpdump on very busy hosts. In this post, I wanted to highlight how much of an impact there really is and what options you have to make the query collection much more effective.
Some things you need to know:
- The test is a sysbench read-only workload, 8 tables, 8 threads, 1000000 rows each with 16G of buffer pool. Dataset fully in memory.
- sysbench is ran on the same host, on 1Gbps connection, sysbench can saturate the network and therefore affect my network test with netcat so I decided to run locally.
- There are 13 tests, 5 minutes each with 1 minute interval,
varying on how the dump file is captured.
- First one as …
In this blog post, we will discuss MySQL performance on eXFlash DIMMs. Earlier we measured the IO performance of these storage devices with sysbench fileio.
The benchmarking environment was the same as the one we did sysbench fileio in.
CPU: 2x Intel Xeon E5-2690 (hyper threading enabled)
FusionIO driver version: 3.2.6 build 1212
Operating system: CentOS 6.5
Kernel version: 2.6.32-431.el6.x86_64
In this case, we used a separate machine for testing which had a 10G ethernet connection to this server. This server executed sysbench. The client was not the bottleneck in this case. The environment is described in greater detail at the end of the blog post.
Sysbench OLTP write workload
A few days ago I wrote about MySQL performance implications of InnoDB isolation modes and I touched briefly upon the bizarre performance regression I found with InnoDB handling a large amount of versions for a single row. Today I wanted to look a bit deeper into the problem, which I also filed as a bug.
First I validated in which conditions the problem happens. It seems to happen only in REPEATABLE-READ isolation mode and only in case there is some hot rows which get many row versions during a benchmark run. For example the problem does NOT happen if I run sysbench with “uniform” distribution.
In terms of concurrent selects it also seems to require some very special conditions – you need to have the connection to let some …[Read more]
Recently, I came through with this error. It was weird because I have never seen this error earlier.
[root@percona-pxc55-1 nilnandan]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5537.sock prepare sysbench 0.4.12: multi-threaded system evaluation benchmark FATAL: no database driver specified FATAL: failed to initialize database driver! [root@percona-pxc55-1 nilnandan]#
After some research, found that with Sysbench 0.4.x, we have to use option –db-driver to make it work. So I have used it and it worked.
[root@percona-pxc55-1 nilnandan]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=msandbox --mysql-password=msandbox --mysql-socket=/tmp/mysql_sandbox5537.sock --db-driver=mysql --oltp-auto-inc=off prepare sysbench 0.4.12: multi-threaded system evaluation benchmark Creating table 'sbtest'... Creating …[Read more]
10 Older Entries »