I have run into a number of cases recently that all had a similar look and feel. In most of these cases, the symptoms were very complicated, but they boiled down to just a few problems that can be prevented in very simple ways.

If you are not doing any of the following three simple things, you probably should. These are essential practices for building successful applications, which everyone should be doing. And the best part is how easy it is to do them -- read on for the details.

1. Archive or Purge Your Database

Data archiving is crucial. There is a difference between data that your application needs now, and data that your application used to need. Do not co-mingle the two. Plan from the outset to archive or purge data that is no longer needed. Everybody wants to know how to deal with an application that is growing. The simplest way is to prevent it from growing. Archiving and purging …

I’m going to present on Maatkit at the CPOSC conference in central Pennsylvania on Saturday, October 17th 2009. I’ll give an overview of the toolkit, which is no longer an easy task in a single session. I see a number of other interesting sessions have been accepted. It looks like it’ll be a good conference.

Related posts:

1. Speaking at EdUI Conference 2009 I’m
2. Learn about Maatkit at the MySQL Conference I’m
3. Presentation uploaded …

Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices. The task looked trivial: Update a production MySQL server (replication master) with a configuration tuned and tested on a development server. Clean shutdown, change configuration, restart. Unfortunately, the MySQL daemon did not just ‘come back’, leaving 2 sites offline. Thus begins an illuminating debugging story. First place to look is the daemon error log, which revealed that the server was segfaulting, seemingly at the end of or just after InnoDB recovery. Reverting to the previous configuration did not help, nor did changing the InnoDB recovery mode. Working with the client, we performed a failover to a replication slave, while I got a second opinion from a fellow engineer to work out what had gone wrong on the server. Since debug symbols weren’t shown in the stack trace, we needed …

I’ve been thinking recently about the failure scenarios of MySQL replication clusters, such as master-master pairs or master-master-with-slaves. There are a few tools that are designed to help manage failover and load balancing in such clusters, by moving virtual IP addresses around. The ones I’m familiar with don’t always do the right thing when an irregularity is detected. I’ve been debating what the best way to do replication clustering with automatic failover really is.

A broken VIEW was caused by the view’s definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.

So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:

instance 1 has a datadir of /data/mysql/instance1
instance 2 has a datadir of /data/mysql/instance2

Now, the unix tool strings can be used against many types of files. In particular, though, you can use strings on the mysql/user.MYD file to see the username, host, and password hash. (note that strings only shows strings longer than 3 characters, so if your host or username is 3 characters or less, it will not show up in the output of strings. You …

I'm pleased to announce the release of Spider storage engine version 2.1(beta).
http://spiderformysql.com/

The main changes in this version are following.
- Add UDFs "spider_direct_sql" and "spider_bg_direct_sql".
  These UDFs execute hand writing SQLs on remote server by using Spider's table link and store result sets in temporary tables.
  Main usages are following.
  1.Maintenance remote servers.
    (Create table on remote servers for Spider's link tables, etc.)
  2.Use every resources (excluding table) on remote servers.
  3.Mass update of the bulk data. (Update all rows in tables, etc.
    There are next advantages. It is possible to update it in parallel
    with multiple remote servers. The …

Couple of days ago I was looking for a way to improve update performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem with INSERT ON DUPLICATE KEY UPDATE)

As I went from 1 value to 3 or 10 in the batch performance improved, especially for network tests, however going to batches of 100 and 1000 values performance started to degrade badly - the process which was taking 45 seconds with single value statements was taking over 12 minutes with 1000 values in a batch. This was a big surprise for me as I've used batching with great performance gains a lot of times.

I had 2 thoughts - either something is going on with parser or it is something nasty going on on Innodb level, so I tried running the test with MyISAM tables instead. The process completed in 12 seconds for 1000 rows in a batch vs 40 …

I have several side projects when time permits and one is that of benchmarking various MySQL technologies (e.g. MySQL 5.0,5.1,5.4), variants (e.g. MariaDB, Drizzle) and storage engines (e.g. Tokutek, Innodb plugin) and even other products like Tokyo Cabinet which is gaining large implementations.

You have two options with benchmarks, the brute force approach such as Sysbench, TPC, sysbench, Juice Benchmark, iibench, mysqlslap, skyload. I prefer the realistic approach however these are always on client’s private data. What is first needed is better access to public data for benchmarks. I have compiled this list to date and I am seeking additional sources for reference.

• Feebase – Data is in clean loadable format
• IMDB – Not clean, but Roland Bouman is working on …

A goal at MySQL is to support virualization and provide how best and whys related to run MySQL on virtual platforms

So along that line here’s some items of interest.

Talk at VMworld 2009 San Francisco on Tues. Sept 1 – MySQL on VMware: Performance and Deployment Best Practices

https://vmworld2009.wingateweb.com/scheduler/profile.do?SESSION_ID=3408&form=searchform&ts=1251484531567

Alternatively checkout the white paper and webinar on this topic on mysql.com

Webinar – Managing MySQL in Virtual Environments with VMWare …

Hi all,

Last night I spent some time setting up a connection pool in GlassFish's admin console. Here's the backstory: I'm working on a training course for cloud computing, and as part of my module on assembling a virtual data center I ran into a few problems. I have two virtual servers running. One is running OpenSolaris and has GlassFish application server installed on it. The other is running Fedora Linux and has my MySQL database on it. The problems occurred when I tried to ping the database server from GlassFish. I got some strange messages. When a colleague of mine googled the problem, he found out that the problem was OpenSolaris specific. After downloading several .jar files the error message said weren't there, I stumbled upon the solution:

1. The solution was to copy my regular MySQL driver .jar file from my local machine to the glassfish-install/domains/domain1/lib/ext/ directory on my virtual …