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 …

Recently I had one customer for consulting and aside from mysql optimization, etc they asked me for cacti installation/setup to monitor their pretty generic LAMP application. I’ve started setting up all this stuff and I’ve never thought it could be so painful… lots of different templates for the same tasks, all of them are incompatible with recent cacti releases, etc, etc… So, this post is generally a list of used templates with a fixes I’ve made to make them work on recent cacti release.

(more…)

Thought I’d mention that a new case study featuring Pentaho and Kettle showed up over at the Database Trends and Applications. The name of the paper is called “Loma Linda University Health Care Deploys Pentaho BI” (PDF).

To quote :

With commercial products you don’t know if you are getting what you want, but with open source you can create proofs-of-concept. And the TCO is so much lower.

Until next time!

Matt

Cluster

This blog entry describes how to install MySQL clusters on Solaris.

MySQL cluster consist of 3 seperate types of nodes:

• SQL nodes
• Storage nodes
• Management nodes

The SQL nodes are the nodes that applications can connect to. Internally SQL nodes connect to storage nodes to  process the queries and return the result set to the end client.

The storage nodes are controlled by management nodes. They do most of the work in processing the queries.

Managment nodes manages the entire cluster. They start and stop the data and SQL nodes and manage backups.

Lets start with the simplistic installation where all the nodes of the cluster are on the same box. Of course this is not how you would do a typical MySQL cluster installation...but this is just to get a feel of what is involved in MySQL cluster …

Cluster

This blog entry describes how to install MySQL clusters on Solaris.

MySQL cluster consist of 3 seperate types of nodes:

• SQL nodes
• Storage nodes
• Management nodes

The SQL nodes are the nodes that applications can connect to. Internally SQL nodes connect to storage nodes to  process the queries and return the result set to the end client.

The storage nodes are controlled by management nodes. They do most of the work in processing the queries.

Managment nodes manages the entire cluster. They start and stop the data and SQL nodes and manage backups.

Lets start with the simplistic installation where all the nodes of the cluster are on the same box. Of course this is not how you would do a typical MySQL cluster installation...but this is just to get a feel of what is involved in MySQL cluster …

Cluster

This blog entry describes how to install MySQL clusters on Solaris.

MySQL cluster consist of 3 seperate types of nodes:

• SQL nodes
• Storage nodes
• Management nodes

The SQL nodes are the nodes that applications can connect to. Internally SQL nodes connect to storage nodes to  process the queries and return the result set to the end client.

The storage nodes are controlled by management nodes. They do most of the work in processing the queries.

Managment nodes manages the entire cluster. They start and stop the data and SQL nodes and manage backups.

Lets start with the simplistic installation where all the nodes of the cluster are on the same box. Of course this is not how you would do a typical MySQL cluster installation...but this is just to get a feel of what is involved in MySQL cluster …

Seklos just posted a story on his Oracle blog on how he installed Oracle 10something in less than an hour.

I found the story hilarious. why . because about a decade ago .. when I was still into database & webdevelopment, Oracle first started shipping Oracle for Linus (somewhere in 99)
Back then some collegue had been struggling for a couple of days already to setup up Oracle on a Windows box and was thinking to just use SQL server.
So I went home found the famous CD with the typo (or did I really get the CD that should have been shipped to Finland ?) in my mailbox and as I had a new Siemens Server sitting in my basement, I installed a fresh RedHat and on a machine I never had seen before (that's the ninetees I`m talking about so no fancy just install it on most common hardware and it will work like we have these days) and then went on installing an Oracle …

This month I continued my Performance Analysis talks at the Local NY MySQL Meetup. Previous discussions can be found here.

Our focus was a more in-depth look at gathering and reviewing MySQL Status and your applications SQL statements using MySQL Proxy. Even after preparing the slides over the weekend Jan added more functionality that was particularly interesting. So today while addressing a client issues I further extended this work to do even more funky monitoring.

Today’s monitoring.lua script does:

• Logs to file, Date/Time, Query Time, Response Time, Rows Affected, Normalized SQL and Actual SQL for each query
• Has histogram of tables used with read/write …

I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

1.Create a table to hold the sequence counter and …

Better late then never, this week I finally have the chance to catch up on some overdue posts. At the first MySQL Camp I made a list of memorable Quotes, see Day 1, Day 2, Day 3. I didn’t get as much chance as last time, however here are some pearls of the recent MySQL Camp II.

“I walked in of the street for the free food. I’m here for the free education” - Adrian.

“I’m his boss, I’m here to make sure he’s really here, and not playing hooky.”

“Mashups, collating the worse bugs from multiple API’s all in one place” - OSCON badge

“Compiles 114,000 combinations of MySQL that we are interested in.” Sandro - Skoll Project — You mean to say there are …