Yesterday I posted results from some MySQL benchmarks I had been doing on a large server. In this post I'd like to list 4 important fixes that were done to avoid bad performance:
DRBD and semi-sync benchmarks on a 2x8 132 GB server
I recently had the opportunity to run some benchmarks against a relatively large server, to learn how it was behaving in its specific configuration. I got some interesting results that I'll share here.
Mat Keep’s blog post on InnoDB-vs-MyISAM benchmarks that Oracle recently published prompted me to do some mathematical modeling of InnoDB’s scalability as the number of cores in the server increases. Vadim runs lots of benchmarks that measure what happens under increasing concurrency while holding the hardware constant, but not as many with varying numbers of cores, so I decided to use Mat Keep’s data for this. The modeling I performed is Universal Scalability Law modeling, which can predict both software and hardware scalability, depending on how it is used.
In brief, the benchmarks are sysbench’s read-only and read-write tests, and the server has two Intel SSDs, 64GB of memory, and 4 x 12-core …[Read more]
Recently I tested our new segmented key cache feature for MyISAM in MariaDB 5.2.2-gamma for performance gains. You can check our new features in MariaDB 5.2 in our Ask Monty Knowledge Base
You will also find the details about the segmented key cache feature in our Knowledge Base at:
We wrote a test in LUA for SysBench v0.5 called select_random_points.lua, to figure out the performance gain of splitting the key cache’s global mutex into several mutex under multi user load.
You can find all the details about the benchmark in our Knowledge Base article here:
These are some of my notes from some sysbench in-memory r/o testing in past day or so:
- At ‘fetch data by primary key’ benchmark with separate read snapshots at each statement, MySQL shines until ~200 concurrent threads, then performance starts dropping slightly faster than one would want, I think mostly from table cache LOCK_open contention
- auto-commit cost (establishing read snapshot per statement) for SELECTs is ~10% for MySQL, but for PG it can be +50% in plain SQL mode and +130% (!!!!!!!) when using prepared statements (this can be seen in a graph – obviously the global …
In this post I will share results on some "benchmarking" I did on the database created in the previous post: MariaDB 5.2: Using MariaDB as a document store and Virtual Columns for indexing. In addition to just playing with the new syntax, I wanted to actually benchmark using virtual columns against some other techniques. If you didn't read that previous post yet, please do so that you know the schema that is being used and the whole point of what we are doing.
The premise for this benchmark was already given last week:
Before I write the next blog, I invite you to guess the result of the benchmark. I had two conflicting rules of thumb as hypothesis:[Read more]
Benchmarking is liking running your system through it’s paces. You don’t know how fast your software and hardware are until you’ve put some pressure on them. Benchmarking tools allow you to do just that. We use sysbench to look at the operating system and mysqlslap to run queries in the MySQL database.
A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
- sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex …
We were discussing the recommendations we issue each quarter
around MySQL and the question of using InnoDB plugin came up. We
usually follow Planet MySQL closely, so we read what the blogs
had to say and it was all good, but we decided to provide our
users some data of our own. We used our own sysbench
tests on to get the information we needed.
A Word About BenchmarksI don't trust most of the benchmarks that are published online because they really apply to the use case of whomever is writing the article. They are usually many factors that can influence them and I find it difficult to apply them as-is to our environment.
I do trust the benchmarks published online as a reference on how to create and run our own benchmarks. So this article is based on this premise. I recommend you to do your own homework to …
I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.
You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.[Read more]