This is one in a series of articles on how to use innotop, a MySQL and InnoDB monitor. In this article I’ll explain how innotop can make it much easier to collect useful information from SHOW STATUS and SHOW VARIABLES into one place. There are three modes in innotop that do this in different ways, so one of them may meet your needs. Note: VividCortex is the startup I founded in 2012.

MySQL has been one of the most popular databases amongst the Pentaho community. We receive questions and comments regularly about setting up and writing solutions for Pentaho on MySQL, which tells me people are putting MySQL to work in the business intelligence space. These questions were the catalyst for our Tech Tips, a collection of very useful how-to's for the Pentaho platform and Pentaho tools.

Today, I published a short tech tip regarding how to move the Pentaho sample data from HSQLDb to MySQL. Not rocket science, yet definitely made much easier with a Kettle transformation donated by Nic Guzaldo - thanks again Nic! Check it out, it's a good example of moving data with Kettle and also is a great utility tool to stick in your Pentaho toolbox. …

From our jobs page:

As a dealnews web developer, you will help maintain our current stable of deal and price-tracking web sites, and build new features and new web sites as we continue to grow. You’ll be part of a small, fast-moving team of developers that are involved at every stage of product development, from concept to rollout.

We use Gentoo, Apache, PHP and MySQL.   While that does not need to be your expertise, it is a plus.  We have a little Perl and Python thrown in as well.  You will need to code on a non-Windows system as we run our development environment on our local machines.  Currently, we all use Macs.

A big plus, however, is disc golf.  We play weekly as an um, team building excercise. Yeah, team …

Problem 1 in the article at IBM developerWorks kind of rub me the wrong way.

1. Using MySQL directly

Why not? How is the second code example in that post superior to the first? What I see is that the first example, that uses mysql_* directly, is more readable. The second snippet is a jumbled mess.

The article recommends using PEAR::DB. Is PEAR::DB even PHP5 ready? I don’t know, I don’t use it. The author could have at least used PDO. Still slower, but not as bad and is the future of PHP database abstraction IMO.

He uses a prepared statement to do the select. First, I have tested and I know is slower for MySQL. Second, it is not the end all be all of stopping cross site scripting that a lot of people believe it to be. You can only pass certain value parts of certain …

Life has been super busy, but I have plenty of posting to catch up on. Fear not, there will be more content soon (after Monday, hopefully); I am working on editing a very relevant book, and I hope to be able to share my excitement after I finish.

Also on Monday is the next Boston MySQL User Group, which will go through how to do capacity planning in MySQL with very little pain and effort. In the beginning we will have 10 minutes for user questions, so we can all benefit from each other’s knowledge. I already have a user with a great question!

We have rebuilding our product all summer, with a deadline of releasing the product in the next 2 months. Our lead developer had put a surrogate key in our new schema about a month ago. He said he needed the surrogate key because “the ORM requires it.” I just accepted it.

My mistake was that I made an assumption. The table already had a primary key, but it was a composite key. I …

If you have used the MySQL Federated engine, you may share my opinion that it's perhaps the most amazing feature introduced in recent versions, far more interesting and promising than other more advertised ones.
If you have used it, though, you must have noticed how skinny is the official manual. There are countless questions popping from your head while testing this feature, and no answers in sight.

Article about database design problems is being discussed by Kristian.

Both article itself and responce cause mixed feellings so I decided it is worth commenting:

1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I have not yet tested PDO but would not expect it to beat MySQLi in speed. It is however bad idea to use mysql_ functions directly as well - I would go for using mysqli object approach. The great things about objects is you can easily overload methods and get debugging and profiling tools, as well as have tools which protect you from …

Here’s a good “Addendum/Appendix/Annotation” to the original article by IBM DeveloperWorks. Along with some updated ways of doing things using MySQL 5 and stored procedures.
Updated: August 11th 11:33 AM Thanks to ds- on #php.thinktank, actual addendum is here.

Tags: php, mysql, performance

Today I've upgraded MySQL Server on the host running MySQL Performance Blog. MySQL 4.1.12 was running here for well over a year before that.

Why Have not I upgraded before ? Well because it just worked fine. Yes I know there were some security fixes but I have dedicated server with remote MySQL access closed by firewall and only trusted people having local access so I was not worried too much about it.

Why did I upgrade now ? Because new forum application was exposing one of the bugs in MySQL 4.1.12 so there was a need for upgrade.

Which Version did I upgrade to ? I Upgraded to MySQL 4.1.21. Why Did not I go with MySQL 5.0 ? There are three reasons. First there are number of changes in 5.0 some of which are incompatible with MySQL 4.1, for example regarding join syntax. I can't be 100% sure none of my applications will be affected and I see no reasons to spend time testing it carefully or downgrading if problem finally …