The latest from the MySQL community

We're proud to introduce the sqlexamples.org community, a resource for database developers and administrators. Our aim is it to improve the availability of free (as in free speech) SQL and NoSQL related database examples of all kinds. We're not just focused on MySQL. Related is for example:

• syntax examples
• database schemata
• database related source code
• <your idea here>

A lot of valuable database related content gets published day by day in countless blogs all over the web. Our aim is it to archive and index this knowledge in a central database, open and accessible for everyone. When you want to help us building such an useful archive, all you have to do is to submit your RSS feed to sqlexamples.org . Additionally, content can be published directly on our platform if you like.

…

1. jsTerm -- ANSI-capable telnet terminal built in HTML5 with Javascript, Websocket, and Node.js. (via waxpancake on Twitter)
2. MySQL EXPLAINer -- visualize the output of the MySQL EXPLAIN command. (via eonarts on Twitter)
3. Google Code University -- updated with new classes, including C++ and Android app development.
4. Cloudtop Applications (Anil Dash) -- Anil calling "trend" on multiplatform native apps with cloud storage. Another layer in the Web 2.0 story Tim's …

The newest release of Percona Server includes a trivial change that I think will be extremely valuable. This is the addition of a new thread state, “Waiting on query cache mutex.” Fixing the query cache to make it scalable is hard. Fixing the server to report when the query cache is a bottleneck is not hard. It has historically been very difficult for users to diagnose what’s wrong with their server when the query cache is locking it intermittently. Now it will be trivial: they will look at SHOW PROCESSLIST and the evidence will be unmistakable.

Related posts:

1. Making Maatkit more Open Source one step at a time
2. …

I collect a lot of configuration values from my database servers and most of these values are stored by date.

So often I end up with values such as:


config_date config_value
2010-09-01  value_1
2010-09-02  value_1
2010-09-03  value_2
2010-09-04  value_3
2010-09-05  value_3
2010-09-06  value_3
2010-09-07  value_3
2010-09-08  value_4
2010-09-09  value_4
2010-09-10  value_1
2010-09-11  value_5
2010-09-12  value_5
2010-09-13  value_5
I´ve been unsuccessfully been trying to figure out how to convert this in SQL into something like the following:


config_from config_to   config_value
2010-09-01  2010-09-02  value_1
2010-09-03  2010-09-03  value_2
2010-09-04  2010-09-07 …

CouchDB is a document-oriented database written in Erlang that addresses a particular “sweet spot” in data storage and retrieval needs. This blog post is an introduction to CouchDB for those of us who have a relational database background.

A CouchDB database doesn’t have tables. It has a collection of documents, stored in a B+Tree. A document is a collection of attributes and values. Values can be atomic, or complex nested structures such as arrays and sub-documents. When you add a document to a database, CouchDB stores it in the B+Tree, indexed by two attributes with special meaning: _id and _rev.

CouchDB lets you store related data together even if it isn’t all the same type of data; you can store documents representing blog posts, users, and comments — all in the same database. This is not as chaotic as it sounds. To get your data back out of CouchDB in sensible ways, you define views over the database. A view stores a …

I had an email after my webinar on NoSQL/SQL for Oracle Development Tools User Group last week (http://www.odtug.com/apex/f?p=500:1:0) from an attendee that was chock full of some questions. I decided to answer them to clarify with this fellow NoSQL and Cloud Systems. I'm pretty happy with my answers. I'd be glad for any thoughts from people about my replies.

Here are my responses (the fellow's name is also Patrick):

Patrick,

You are welcome! Thank you for attending. I put that together a bit hasty but thought it was a good topic to be covering as there are so many organizations that are considering such an architecture.

Patrick Francois wrote:
> Hi,
>
> Thank you for the NoSQL Webinar!
> Not an easy theme. ..kind of "wide open".
> I have recently also tried getting more …

I’ve been studying the source of iostat again and trying to understand whether all of its calculations I explained here are valid and correct. Two of the columns did not seem consistent to me. The await and svctm columns are supposed to measure the average time from beginning to end of requests including device queueing, and actual time to service the request on the device, respectively. But there’s really no instrumentation to support that distinction. The device statistics you can get from the kernel do not provide timing information about device queueing, only a) begin-to-end timing of completed requests and b) the time accumulated by requests that haven’t yet completed. I concluded that the await is correct, but the svctm cannot be.

I just looked at the …

You have a backup, and you have the binary logs between that backup and now. You need to do point-in-time recovery (PITR) for some reason. What do you do? The traditional answer is “restore the backup and then use mysqlbinlog to apply the binary logs.” But there’s a much better way to do it.

The better way is to set up a server instance with no data, and load the binary logs into it. I call this a “binlog server.” Then restore your backup and start the server as a replication slave of the binlog server. Let the roll-forward of the binlogs happen through replication, not through the mysqlbinlog tool.

Why is this better? Because replication is a more tested way of applying binary logs to a server. The results are much more likely to be correct, in my opinion. Plus, replication is easier and more convenient to use. You can do nice things like START SLAVE UNTIL, skip statements, stop and restart without having to figure out …

Bug reports can be fun. They can also be terrible. Either way they can be entertaining. On the Drizzle IRC channel today I saw a couple references to MySQL bug reports: it is stop working and Does not make toast (which reminds me of the Mozilla bug report about the kitchen sink). Got any other favourites1?

1 This one’s for Jay.

Related posts:

1. What are your favorite MySQL replication filtering rules?
2. My new favorite comic: The Adventures of …

ODTUG invited me to give a webinar and I said yes, so this Thursday you’re invited to join me as I talk about MySQL performance. We’ve come a very long way towards a MySQL that can perform well on modern hardware, and there really isn’t broad recognition of this. A lot of the best work has gone into the InnoDB “plugin” storage engine, which was announced after my co-authors and I sent High Performance MySQL to the press. I will explain what you should be doing differently now than you did two years ago, and suggest a performance-in-a-nutshell configuration baseline for MySQL that’s quite different from what I’d have said in 2008. You can register for free through GoToWebinar. See you there.

Related posts:

1. …