The latest from the MySQL community

The question I often get is how far MySQL may fall behind and how to keep replication from lagging.

The lag you will see will vary a lot from application to the application and from load to load. Plus what is the most important within same application the lag will likely have spikes - most of applications would have typical lag within few milliseconds while there will be rare cases when replication lags behind several seconds or even longer.

There are multiple reasons why application falls behind and why we see those lag spikes:

Slave Server Load - MySQL Replication goes in single thread so it is really vulnerable to the server load. If you get 100 active queries running on the slave overloading it, slave thread will most likely will not get CPU or Disk resources it needs in order to keep going. So if you want to keep replication lag under control you need to keep Slave load under control and avoid load …

I presented at ZendCon this week on performance MySQL coding techniques. The session was packed, and I got a bunch of great questions from the audience. I figured I would post the slide deck in both ODP and PDF formats. Feel free to download!

• MySQL Performance Coding - From Soup to Nuts (ODP)
• MySQL Performance Coding - From Soup to Nuts (PDF)

Welcome to the 66th edition of Log Buffer, the weekly review of database blogs, raking up the blogs like so many fallen leaves. Fall back, spring ahead… Remember all the fuss last spring about the change in Daylight Saving Time? Well, it’s back, at least if you neglected it the first time. Thanks to [...]

Dear MySQL and PHP users,

we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.

According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.

The second news is that we will strengthen our commitment into PHP …

Dear MySQL and PHP users,

we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.

According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.

The second news is that we will strengthen our commitment into PHP …

Earlier today I was building a test-case in which I wanted to put a lot of Unicode data into a database table. The problem is of-course that I don’t have a lot of data, just a small Excel input file.

So I made a Cartesian product with a couple of empty row generators:

It was interesting to see how fast the second join step was generating rows:

Yes, you are reading that correctly: 717 million rows processed in 165 seconds = 4.3 million rows per second.

For those of you that would love to try this on their own machine. Here is an exclusive present for the readers of this blog in the form of a 3.0.0-RC2 preview of 2007/10/12 (88MB zip file). We’ve been fixing bugs like crazy so it’s pretty stable for us, but it’s still a few weeks until we release RC2. Don’t do anything crazy with this drop! This is purely a …

Dear MySQL and PHP users,

we have two good news for you. First, we are proud to announce that one year after the announcement to develop a new MySQL native driver for PHP (mysqlnd), the new driver has been committed into the 5.3 branch of the PHP project. PHP 5.3 is expected to be the next major release of PHP.

According to a poll on the PHP Internals development list the new driver is among the five most desired features for PHP 5.3. Thank you for your trust in mysqlnd! We will try to continue to listen to your whishes and are looking forward to hear back from you.

The second news is that we will strengthen our commitment into PHP …

We many times wrote about InnoDB scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never in production, that's why we did not escalate MyISAM scalability question. This time working on the customer system we figured out that box with 1 CPU Core is able to handle more queries per second than identical box, but with 4 CPU Cores.

The main query which showed this problem was similar to this:

PLAIN TEXT SQL:

1. SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18;
2. mysql> EXPLAIN  SELECT name FROM t1, t2 WHERE t2.t1_id = t1.id AND t1.stat=1  AND  t2.val = 5 LIMIT 1206,18\G
3. *************************** 1. row ***************************
4.            id: 1
5.   select_type: SIMPLE
…

This post is half-this is how you do it, and half-is there a better way to do it?

So you want to build a system that does jobs. You want the jobs to be able to be run in parallel for speed, but also for redundancy. This system needs to be coordinated so, for example, the same jobs aren't being done twice, the status of every job is easy to see, and multiple servers can run jobs by simply querying the central source.

How can we build code around Innodb to have MySQL be our central controlling scheme?

CREATE TABLE IF NOT EXISTS job_queue(
   id int(10) not null auto_increment,

   updated timestamp not null,
   started timestamp not null,

   state ENUM('NEW', 'WORKING', 'DONE', 'ERROR' ) default 'NEW',

   PRIMARY KEY ( id ),
   KEY( STATE )
) ENGINE=Innodb;

In this schema, our job table has a unique id, started and last updated …

Sun engineers give the inside scoop on the new UltraSPARC T2 systems

[ Update Jan 2008: Sun SPARC Enterprise T5120 and T5220 servers were awarded Product of the Year 2007. ]

Sun launched the Chip-Level MultiThreading (CMT) era back in December 2005 with the release of the highly successful UltraSPARC T1 (Niagara) chip, featured in the Sun Fire T2000 and T1000 systems. With 8 cores, each with 4 hardware strands (or threads), these systems presented 32 CPUs and delivered an unprecedented amount of processing power in compact, eco-friendly packaging. The systems were referred to as CoolThreads servers because of their low power and cooling requirements.

Today Sun introduces the …