My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

My mind is playing "Suffering Succotash..."

I have been working on MySQL performance for a while now, and the team I am in have discovered that SysBench could do with a couple of tweaks for Solaris.

Sidebar - sysbench is a simple "OLTP" benchmark which can test multiple databases, including MySQL. Find out all about it here , but go to the download page to get the latest version.

To simulate multiple users sending requests to a database, sysbench uses multiple threads. This leads to two issues we have identified with SysBench on Solaris, namely:

• The implementation of random() is explicitly identified as unsafe in multi-threaded applications on Solaris. My team has found this is a real issue, with occasional core-dumps happening to our …

I recently released some RAID testing I did using the sysbench testing framework.  In light of the recent attention paid to multi-core CPU scalability, I have been working on some related tests trying to identify sources of contention using that same set of tests.

After effectively turning off every single innodb safety setting (like flush_log_at_trx_commit, checksums, doublewrite, etc.), and not seeing any real performance increase, I started to wonder what was going on.  
Surely my test client server wasn't the problem, it had plenty of idle CPU according to top, right?  Wrong.
I've been able to drive more QPS to my mysql test servers by starting up parallel sysbench tests from multiple test servers, but using (more or less) the same number of total test threads.  

read more

I recently released some RAID testing I did using the sysbench testing framework.  In light of the recent attention paid to multi-core CPU scalability, I have been working on some related tests trying to identify sources of contention using that same set of tests.

After effectively turning off every single innodb safety setting (like flush_log_at_trx_commit, checksums, doublewrite, etc.), and not seeing any real performance increase, I started to wonder what was going on.  
Surely my test client server wasn't the problem, it had plenty of idle CPU according to top, right?  Wrong.
I've been able to drive more QPS to my mysql test servers by starting up parallel sysbench tests from multiple test servers, but using (more or less) the same number of total test threads.  

read more

The mysql proxy project has tremendous potential to make mysql administration and usage easier. I decided to throw some load at it to get a feel for how stable and performant it is.

On EC2, I set up 6 “small” images in an example proxy setup:

- One client machine to run sysbench
- One machine to act as a mysql proxy machine, running 0.6.1 (FC4 binary)
- Four identical database servers, running mysql 5.0.45

The database configuration was largely default, with InnoDB configured for 64MB buffer pool (just enough to ensure the sysbench table was entirely in memory), 512MB log files, and 1024 max connections.

mysql-proxy was run with the following command:

mysql-proxy –proxy-backend-addresses=ip-10-251-66-63.ec2.internal:3306 –proxy-backend-addresses=ip-10-251-71-21.ec2.internal:3306 …

sysbench - Linux test bench. Easy as pie to test CPU, memory, threads, mysql, and disk performance.

Full description is available here: http://sysbench.sourceforge.net/docs/

install mysql, mysql-devel
wget http://superb-west.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
tar xvzf sysbench*gz
cd sysbench*
./configure && make install

mysql tests

This will run 10 separate consecutive mysql tests using an InnoDB table type, each with 100 mysql threads, doing a total of 1000 various SQL operations per test. Then it will print the total time they took to finish:

sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \\
  --mysql-host='HOST' --mysql-table-engine=innodb prepare
 
time perl -e "foreach(1..10){print \`sysbench --max-requests=1000 --test=oltp \\
  --mysql-user=USER …