So it turns out that a VPS server, while inexpensive, is a poor choice for a demo server of a product. So the Kontrollbase demo is now hosted on a Sun X4100 quad-opteron with 8G ram and some nice SAS disks. The new demo site is http://demo02.kontrollbase.com , the old one is located here if [...]

The EXPLAIN command is an important tool to review how a SQL query is executed and in this example includes what indexes are used.

By adding a covering index I ended up with the following EXPLAIN plan I was unable to explain. The end result was a boost in server performance which was the ultimate goal.

mysql> explain select max(md)  from e_r  where email = 'xxxx@gmail.com' and id = '36981';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables …

One of the most asked questions is how to setup MySQL replication. The MySQL Reference Manual provides a good Replication How To as a starting guide on MySQL Replication 101.

MySQL replication has many uses including read scalability, backups, failover, online maintenance, upgrade testing and verification, software upgrades, alternative data or structure for performance queries and even benchmarking to name the popular uses.

When reviewing an installation of MySQL replication I use the following as part of my checklist of steps used for confirming your replication environment is operational.

Master Variables

• server-id – Replication will not work without this correctly set and unique
• log-bin – Pre-requisite for working replication
• log-bin-index
• max_binlog_size
…

I think I have mentioned Picok before on my blog. This is a system that lets users arrange and configure a number of portlets in order to be able to keep themselves up to date with what's going on in various web applications, similar to iGoogle and netvibes. The difference is of course that this system is entirely open source, so companies can install Picok in their intranet and give Picok direct access to all sorts of internal applications that they could not make available to iGoogle or netvibes. What makes this project all the more exciting is that this system was initially developed as a closed source application for the Raiffeisen bank in Switzerland. This was the first time for me, where I had the opportunity to be part of open sourcing such a large chunk of code.

One of the things we open source proponents mention as a plus point for open source is that developers do not easily get away …

Just a quick note to let the masses know that I will be hosting a MySQL University session tomorrow, based on the talk that I gave at the MySQL UC in April - Customizing MySQL Enterprise Monitor.

It will be at 14:00 UTC - so if you are at all interested in MEM, and want to know how to bend it towards your needs, then come along! I’ll see you there.

EDIT: OOOPS, it’s 13:00 UTC

While not a MySQL topic, as most of my readers view my MySQL Blog, my WordPress blog has been hacked? Has yours?

Like many, I’m sure you may have read about it like at Wordpress blogs under attack from hack attack but I was surprised when my custom permlinks did not work.

Being surprised I looked at Administrator accounts, and I found that there was one more number then being displayed in the list. I had to dig into the database to find the problem.

mysql> select * from wp_users where ID in (select user_id from wp_usermeta where meta_key = 'wp_capabilities' and meta_value like '%admin%');
+-----+-------------+------------------------------------+---------------+------------------------------+---------------------------+---------------------+---------------------+-------------+--------------+
| ID  | user_login  | user_pass                          | user_nicename | …

As I outlined in Part 1 MySQL Proxy can be one tool for performing SQL analysis. The impact with any monitoring is the art of monitoring will affect the results, in this case the performance. I don’t recommend enabling this level of detailed monitoring in production, these techniques are designed for development, testing, and possibly stress testing.

This leads to the question, how do I monitor SQL in production? The simple answer to this question is, Sampling. Take a representative sample of your production system. The implementation of this depends on many factors including your programming technology stack, and your MySQL topology.

If for example you are using PHP, then defining MySQL proxy on a production system, and executing firewall rules to redirect incoming 3306 traffic to 4040 for a period of time, e.g. 2 …

Just in case you missed the live event, we have a recording of the ZFS + SSD for databases webcast

Listen Now

You can also download the slides from Slide share. Download Slides

Track your running miles using Apache Wicket, GlassFish, NetBeans, MySQL, and YUI Charts

An earlier blog introduced an application that provides basic tracking of your running distance and generate charts to monitor progress. The idea is to build the same application using different Web frameworks and deploy on GlassFish and then evaluate each framework based upon a pre-defined critieria. The first version was built using JRuby and Ruby-on-Rails. This blog announces the completion of the Apache …

Track your running miles using Apache Wicket, GlassFish, NetBeans, MySQL, and YUI Charts

An earlier blog introduced an application that provides basic tracking of your running distance and generate charts to monitor progress. The idea is to build the same application using different Web frameworks and deploy on GlassFish and then evaluate each framework based upon a pre-defined critieria. The first version was built using JRuby and Ruby-on-Rails. This blog announces the completion of the Apache …