Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO
…

Maximizing Sysbench OLTP performance for MySQL

Sysbench is a popular open source benchmark used to measure performance of various parts of the operating systems and (one) application (database). Since we are talking about MySQL, I will concentrate on the database part of the test.

The oltp test in the sysbench benchmark creates a single table and runs a set of queries against it. Each row in the table is around 250 bytes and by default it creates 1000 rows. For our experiment we used 10 million rows. Allan has blogged about the details regarding the experiments; I will present an alternate view to those experiments.

The trick to getting good numbers with Sysbench and MySQL is very simple

1. Maximize CPU utilization
2. Reduce delays due to IO
…

In this blog I'm sharing the results of a series of tests designed to explore the impact of various MySQL and, in particular, InnoDB tunables. Performance engineers from Sun have previously blogged on this subject - the main difference in this case is that these latest tests were based on Linux rather than Solaris.

It's worth noting that MySQL throughput doesn't scale linearly as you add large numbers of CPUs. This hasn't been a big issue to most users, since there are ways of deploying MySQL successfully on systems with only modest CPU counts. Technologies that are readily available and widely deployed include replication, which allows horizontal scale-out using query slaves, and memcached, which is very effective at reducing the load on a MySQL server. That said, scalability is likely to become more important as people increasingly deploy systems with quad-core processors, with the result that even two processor systems will need to …

In this blog I'm sharing the results of a series of tests designed to explore the impact of various MySQL and, in particular, InnoDB tunables. Performance engineers from Sun have previously blogged on this subject - the main difference in this case is that these latest tests were based on Linux rather than Solaris.

It's worth noting that MySQL throughput doesn't scale linearly as you add large numbers of CPUs. This hasn't been a big issue to most users, since there are ways of deploying MySQL successfully on systems with only modest CPU counts. Technologies that are readily available and widely deployed include replication, which allows horizontal scale-out using query slaves, and memcached, which is very effective at reducing the load on a MySQL server. That said, scalability is likely to become more important as people increasingly deploy systems with quad-core processors, with the result that even two processor systems will need to …

While I invited Kris Buytaert to present at the Drupal developer room at FOSDEM, I couldn't make it to his presentation as it was scheduled for the last presentation of the day. Oops! Fortunately, Luc Van Braekel recorded almost all Drupal presentations at FOSDEM so I'm sharing the video of Kris's presentation to make up for it. It's is well worth a look, and no, we are not family.

SANTA CLARA, CALIF. — Sun Microsystems, Inc. (NASDAQ: JAVA) today announced it has completed the acquisition of MySQL AB, developer of the world's most popular open source database, for approximately $1 billion in total consideration. Sun also unveiled the immediate availability of MySQL's complete portfolio of products and enterprise services backed by its 17,000-strong global sales and services organization and its extensive international network of authorized distribution channels. Sun now provides a single, secure choice for customers and developers seeking to put MySQL into deployment on a global basis. Users can get started today with free downloads at dev.mysql.com/downloads.

Bruce Perens, the man who created the Open Source definition on February 9, 1998, writes about the past and present of Open Source. In his State of Open Source Message, he labels the past and the future of Open Source. In his own words:

"Friday, February 8 is the last day of Decade Zero of Open Source. Saturday, February 9 is the anniversary of Open Source and the start of Decade One. It's a computer scientist thing. We always start counting from zero :-)"
The article talks about the rise of Open Source from Red Hat to most recently Sun's acquisition of MySQL. He also re-iterates the need for non …

BCP stands for Business Continuity Planning - basically a fancy name to describe handling the situation when a DC goes offline. Since we use dual master replication for our servers - putting a master in another DC is not possible - without a special layer. The reason: A slave can have only one master, while a master can have many slaves. So, mysql replication for a dual master setup is not valid, unless there is a replication ring from one DC to another DC. You do not want to do this due to latency.

To get around these limiting factors in mysql, we have developed an application, using my design, to get write events from one DC to another DC over a stunnel.

To get data from one DC to another DC we need to do it encrypted. If there is a man in the middle, we don't want them sniffing our traffic-so we use stunnel to encrypt the data. I will not use SSL encrypted replication since managing that requires a mysql restart. For …

Over the past couple of days Sun has been getting a lot of feedback on it's behaviour with open source.

So there is Amanda McPherson trying to teach Sun that the L in LAMP really stands for Linux.

And then there was Roy T. Fielding quiting the Open Solaris community.
I'm still wondering why a company that once bought StarDivision because it was cheaper to buy the company than to pay licenses for similar functionality, keeps maintining their own kernel stack rather than contributing to one that is way more popular and as a much larger userbase.
Its not like they have a die hard community they will loose, it's not like they will loose customers over it. When Sun says that Linux is the new …