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
…

Adobe has announced that it is sponsoring the SQLite public domain database engine project by joining Mozilla and Symbian on the SQLite consortium. The news is interesting in that it balances Google’s recent sponsorship of efforts to support Photoshop on Linux, while it also raises an interesting question about Microsoft’s attempt to define commercial open source.

SQLite has seen some success recently as the chosen database for Google?s Android project. It also replaced MySQL as the …

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 …

First 'normal' build of Maria is now released; This is a build that went trough our build farm and compiled and passed all tests on many different OS and machine combinations.

You can find the release at http://dev.mysql.com/downloads/maria/index.html

I am quite happy with the Maria releases so far. The Maria team has been able to fix almost all reported bugs (a couple of not repeatable bugs remains) but the best news is that there have been very few bugs.

Now we have started to work on the few remaining features that is needed to be able to shift from Alpha to Beta: The main one is many concurrent insert and many concurrent selects on the same table. We hope to have this done in good time before the MySQL Users conference in April.

For anyone attending the MySQL users conference, I would like to take the …

Reset Forgotten MySQL Root Password

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server.

On the Dev Zone there is a quick poll about your favorite IDE. The results from day one show a surprising preference for a lightweight editor, which happens to be my favorite as well.

Perhaps it's just a chance of the initial poll. We'll see in the next days.
However, it may be a good association of lightweight products. MySQL is a small, ubiquitous database server, compared to other more heavy products that need to be distributed in DVD, and the Vi family (with my favorite vim) is also small and ubiquitous.
What I loved a lot while working as a consultant is the ability of using syntax highlighting even in the most basic environments (i.e. a ssh connection on a terminal emulator) and the availability of vim in every operating …

Reset Forgotten MySQL Root Password

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I?m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server.

A public draft version of the all-new DRBD Users’ Guide has just been released. Feedback is more than welcome.

You may want to read the announcement as well.

One of my favorite features of the Oracle database is support for Materialized Views or 'snapshots' as they used to be called. I've often lamented that MySQL lacks this feature, but everybody I talked to seemed to feel that the feature was just "too big", "too difficult" or "frankly impossible" to implement. Well, frankly, nothing motivates me more than telling me that something is impossible.

*edit* - these restrictions do not apply to my system, this is just what I found "in the wild" when I started

So I started doing some research and came across some rudimentary stored procedure implementations for SELECT-PROJECT (single base table, no joins) views. Their first major drawback is that they require the user to hand-craft a stored procedure, which is not only tedious, but it makes maintenance a nightmare. Second they don't support joins, so their usefulness is very limited. Nonetheless, it gave me a place to start. …

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
…