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 …

Monitoring flow control in a Galera cluster is very important. If you do not, you will not understand why writes may sometimes be stalled. Percona XtraDB Cluster 5.6 provides 2 status variables for such monitoring: wsrep_flow_control_paused and wsrep_flow_control_paused_ns. Which one should you use?

What is flow control?

Flow control does not exist with regular MySQL replication, but only with Galera replication. It is simply the mechanism nodes are using when they are not able to keep up with the write load: to keep replication synchronous, the node that is starting to lag instructs the other nodes that writes should be paused for some time so it does not get too far behind.

If you are not familiar with this notion, you should read this …

This is a follow-up on my previous blog post about using Lua enabled sysbench. Today I will dive into how to write Lua scripts for sysbench. Look at this simple example:

function prepare ()
  local i
  print("creating table sbtest.t1 ...")
  db_query("create table t1 (c1 int unsigned primary key, c2 int)")
  db_query("begin")
  for i= 1, 1000 do
    db_query("insert into t1 values (" .. i .. "," .. i .. ")")
  end
  db_query("commit")
end

function cleanup()
  db_query("drop table t1")
end

function help()
  print("sysbench Lua demo; no special command line options available")
end

function thread_init(thread_id)
end

function thread_done(thread_id)
  db_disconnect()
end

function event(thread_id)
  db_query("select c2 from t1 where c1=" .. sb_rand(1, 1000))
end

There are 3 functions prepare(), cleanup() and help(). Those are …

A quite common benchmark for MySQL is sysbench. It was written nearly 10 years ago by Alexey Kopytov.

Sysbench has modes to benchmark raw CPU performance, mutex speed, scheduler overhead and file IO performance. The probably most often used sysbench mode is OLTP. This benchmark mimics a OLTP scenario with small transactions hitting an optimized database. There are many variables to play with, most important is the number of simulated application threads (option --num-threads). The OLTP benchmark can be run read-only, then it does 14 SELECT queries per transaction. Or it can be run read-write which adds 2 UPDATEs and one INSERT and DELETE.

The latest release of this official sysbench tree is 0.4.12. Many Linux distributions ship a package for this.

However there is also a newer version of sysbench, that comes as version number 0.5.
…

ScaleArc recently hired Percona to perform various tests on its database traffic management product. This post is the outcome of the benchmarks carried out by Uday Sawant (ScaleArc) and myself. You can also download the report directly as a PDF here.

The goal of these benchmarks is to identify the potential overhead of the ScaleArc software itself and the potential benefits of caching. The benchmarks were carried out with the trunk version of sysbench. For this reason, we used a very small set of data, so the measurements will be fast, and it’s known that caching has huge benefits when the queries themselves are rather expensive. We decided that we would rather show that benefit with a real-world application, which is coming later is this series. And if you’re in the Silicon Valley area, be sure to join us this evening at the …

This is the second part in a two-part series comparing Virident’s vCache to FlashCache. The first part was focused on usability and feature comparison; in this post, we’ll look at some sysbench test results.

Disclosure: The research and testing conducted for this post were sponsored by Virident.

First, some background information. All tests were conducted on Percona’s Cisco UCS C250 test machine, and both the vCache and FlashCache tests used the same 2.2TB Virident FlashMAX II as the cache storage device. EXT4 is the filesystem, and CentOS 6.4 the operating system, although the pre-release modules I received from Virident required the use of the CentOS 6.2 kernel, 2.6.32-220, so that was the kernel in use for all of the benchmarks on both systems. The benchmark tool used was sysbench 0.5 and the version of MySQL used was …

Sysbench OLTP, transactions per second

When dealing with high performance, low latency storage devices, such as SSD cards, one finds bottlenecks in new places. This is a story about such a bottle neck and how to work around it.

One unique feature of InnoDB is the double write buffer. This buffer was implemented to recover from half-written pages. This can happen in case of a power failure while InnoDB is writing a page (16KB = 32 sectors) to disk. On reading that page, InnoDB would be able to discover the corruption from the mismatch of the page checksum. However in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy. Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk, InnoDB writes the page to the final destination. When recovering, InnoDB scans the double write buffer and for each …

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 a look at the data.

With the sysbench fileIO test in asynchronous …

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 are two aspects of this problem:

1) memory allocation is an costly operation and if memory …

MySQL 5.6 vs MySQL 5.5 & the Star Schema Benchmark

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload.  I wanted to test a set of queries which, unlike sysbench, utilize joins.  I also wanted an easily reproducible set of data which is more rich than the simple sysbench table.  The Star Schema Benchmark (SSB) seems ideal for this.

I wasn’t going to focus on the performance of individual queries in this post, but instead intended to focus only on the overall response time for answering all of the queries in the benchmark. I got some strange results, however, which showed MySQL 5.6.10 to be much slower than MySQL 5.5.30 even with only a single connection. I felt these results warranted deeper investigation, so I did some research and …