This is the first of a series of posts about business intelligence tools, particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP tools are a part of the larger topic of business intelligence, a topic that has not had a lot of coverage on MPB. Because of this, I am going to start out talking about these topics in general, rather than getting right to gritty details of their performance.

I plan on covering the following topics:

1. Introduction to OLAP and business intelligence. (this post)
2. Identifying the differences between a data warehouse, and a data mart.
3. Introduction to MDX queries and the kind of SQL which a ROLAP tool must generate to answer those queries.
4. Performance challenges …

After successfully installing MySQL on Oracle Enterprise Linux installing a LAMP (Linux/Apache/MySQL/PHP) stack can also be performed with a single command:

$ yum install -y httpd php php-mysql
# Start the Apache Httpd Process
$ /etc/init.d/httpd start

To test and confirm Apache Httpd and PHP, we can use the CLI browser lynx:

$ yum install -y lynx
$ echo "<? phpinfo() ?>" > /var/www/html/phpinfo.php
$ lynx http://localhost/phpinfo.php

If successful, you will find a web page that contains the following.

phpinfo() (p1 of 31)

   PHP Logo 

PHP Version 5.1.6

   System Linux localhost.localdomain 2.6.18-164.el5 #1 SMP Thu Sep 3 04:15:13
   EDT 2009 x86_64
   Build Date Feb 11 2010 19:07:36
   Configure …

This post is the third of a series that started here.

From the previous of this series, we now have two working EC2 instances that are EBS based. The first instance is the monitor, usually an m1.small type instance and the second instance is hamysql, a large instance type. So far, we have configured Heartbeat for communication between the nodes. Now, it is time to configure the HA resources in Pacemaker. Here are the requirements for the HA resources:

On Monitor

• Kill the other instance
• Free the share resources
• Start a new hamysql instance
• Reconfigure the clients
• Give away the resources

On hamysql

• Run MySQL
• Monitor MySQL
• Get out …

This article was initially inspired by Vadim's post on mysqlperformanceblog.com comparing MySQL 5.5.4 and XtraDB under TPCC-like workload. Vadim's post has opened an interesting discussion about InnoDB I/O capacity feature in general, and particularly - tunning / settings regarding 5.5.4 and XtraDB. However, going more in depth I've discovered more interesting things than I've expected initially :-) and here is a kind of summary of my observations..

The following stuff is mainly covering heavy Read+Write workloads as I/O capacity was added to InnoDB to improve page writes rather reading ;-)

First of all - why I/O capacity in InnoDB?..

InnoDB I/O Capacity

Initially InnoDB was designed to flush in background only 100 pages per second. The value of 100 pages was fixed within a code …

One problem with MySQL Cluster we are starting to see quite often is to do with the current generation of Xeon processors.  This post outlines the problem and how to avoid it.

With the Nehalem based Intel Xeons (and also in some older AMD CPUs) they add a technology called NUMA (Non-Uniform Memory Access).  This basically gives each CPU its own bank of memory instead of all CPUs accessing all the memory.  For many applications this means much faster memory access. You should be able to see if NUMA is on by looking for it in dmesg.

So why is this a bad thing?

MySQL Cluster data nodes typically require a large portion of the memory, this means very often that one CPU will need to access the memory from another other CPU.  This in general is quite slow, on a busy cluster we have seen this access take 100ms - 500ms!  MySQL Cluster is real-time and is not a happy bunny when there are things stopping it …

A little while back we did a webinar on using Pacemaker to fully automate MySQL replication. We now have a recording of that webinar available from our web site.

We’re well into summer and almost at our 200th edition of Log Buffer, a blog of blogs about the database world.

Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note outlining why you think that particular post would be of value to other DBAs, or what you learned from reading it.

Now on to our weekly reading in Log Buffer #194:

Oracle Exadata is a topic that is getting hotter and hotter. Following Pythian’s announcement of Exadata services Oracle’s Greg Rahn …

Recently we had couple posts dedicated to performance monitoring, i.e. Color code your performance numbers, Performance Optimization and Six Sigma, so you may understand we consider stability of performance numbers as one of important area for database management.

That's why we decided to add histogram of queries response times into Percona Server, and our software engineer Oleg Tsarev implemented this feature. The feature adds new INFORMATION_SCHEMA table QUERY_RESPONSE_TIME
( full docs are on page response_time_distribution), which looks like

time count
0.000001 53
…

As some might remember I made a visual representation of the Joomla 1.5 database schema back in 2006. I have now set up an EER representation of the Joomla 1.6 database schema too, that you can download for free. This schema was made after the 1.6 beta 2 release, and it was built using MySQL Workbench, which is both GPL and available as a free download for multiple platforms.

First of all: If you find any errors in this schema, make sure you leave a comment for me at the bottom. Because of the lack of conventions for naming primary key columns, I am left to do a lot of guesswork here when drawing up table relations, so there may be errors. Pretty much the only table …