In July several Monty Program employees attended the Community Leadership Summit and O’Reilly’s Open Source Convention (OSCON), myself included. While there we spoke to developers, partners, users, and interested bystanders of a number of SQL servers: MariaDB, MySQL, Postgres, and others.

The week started with the Community Leadership Summit. First, a big thank you to Jono Bacon for organizing a fantastic unconference, and allowing us to participate! Monty Program considers it our privilege to have helped to sponsor the event, and we look forward to attending and …

This note describes how network IO is done using MySQL 5.0.84 on a modern Linux distribution. This description might be incorrect for other platforms because the code has many ifdefs. The functions my_real_read and net_real_write in sql/net_serv.cc read/write data to/from a socket. I describe the implementation of my_real_read but net_real_write is similar.

I am interested in this code because it uses the pthread mutex LOCK_alarm and there is a lot of contention for that mutex. I think this code can be made much more efficient. The basic problem is that when MySQL is compiled without defining NO_ALARM, the server uses an alarm thread rather than SO_SNDTIMEO and SO_RCVTIMEO to implement read/write with a timeout. For server-side code, NO_ALARM is only defined for Windows builds.

I think the server should be compiled so that SO_SNDTIMEO and SO_RCVTIME are used. Hopefully someone can comment on the history of this.

Pseducode …

Here's a quick list of common mistakes that I've seen regarding indexing in MySQL.

1. Not using an index. Enable your slow query log (and consider setting long_query_time and log_queries_not_using_indexes too) and watch for queries that aren't using an index.

2. Using CREATE INDEX. While CREATE INDEX is not inherently bad, it only allows you to do one thing: add a single index to a table. It is mapped to an ALTER TABLE, so you might as well just use ALTER TABLE and then you have the benefit of being able to do multiple things in the same statement (e.g., add an index and remove an index or add 2 indexes).

3. Misusing a composite index. Take this example:
CREATE TABLE customer (
custid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(30),
lname VARCHAR(30),
INDEX (lname, fname)
);

The composite index on (lname, fname) can be used for searches …

This is the 158th edition of Log Buffer, the weekly review of database blogs.

SQL Server

Simon Sabin has a TSQL Challenge – counting non zero columns. He says, “I’m working on a project where I need to cycle a flag amongst a set of columns. To achieve this I am storing a position value in each column which allows me to cycle them . . .  So the challenge is to find out the how many non zero columns there are, the twist is to use as little code as possible.”

On a cue from Simon, Aaron Bertrand shares …

Peter Fenton’s open source investment tips. Boxee raises $6m. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

*451 CAOS Links will return on August 25*

Digesting the VMware-SpringSource deal
In the wake of VMware’s acquisition of SpringSource the WSJ examined Peter Fenton’s open source investment portfolio and what he looks for in an open source vendor. Meanwhile Matt Asay noted that VMware’s purchase of SpringSource is the first big acquisition of a company based on an Apache license, while Charles Babcock reported …

Things are growing yet again at work and with it the number of people needed to keep things flowing smoothly. This time it’s the Sales Engineer team that needs a new person, working in the Baltimore/Washington area (our home office):

Responsibilities

• Provide exemplary pre-sales technical expertise through technical and product presentations, product demonstrations, pilot implementations, beta program administration, consistent communication, and on-going technical consultation.
• Translate complex technical problems for non-technical clients as well as translating non-technical specifications into precise technical requirements.
• Meet with clients to evaluate their current systems and needs and make recommendations for software and hardware and integration.
• Travel approximately 30% in support of sales and customer activities.
• Respond to RFIs, RFPs and serve as …

IMPORTANT:
This article is out of date and the replication API has been updated. Please see the follow-up article for the most up to date information! OK, so here is the next installment in the Drizzle replication article series. Today, I'll be talking about the flow of the Command message object through the CommandReplicator and CommandApplier APIs. If you missed the first article about the structure of the Command message and Google Protobuffers, you may want to read that first. We'll only be talking in this article about what happens on one server. We will be discussing the Command Log in the next article, and then discuss how messages are passed from one …

As you probably have experienced yet MySQL does not always provide all internal information as you might want to have them and as you are used to have from other RDBMS.

MySQL plans to improve this implementing the/a performance schema and its probably already partly done in MySQL 5.4. But who knows when this will be finished and what it contains at all...

What is not provided to me I want to gather myself... But how? Other RDBMS provide interfaces to attach applications directly to their memory to retreive information. But MySQL does not. So I was looking for a way to read an other process memory.

I have no clue about programming and thus changing MySQL code was out of focus. Further I am looking for a solution you can use immediately on a running systems at consulting gigs. Some tries to read /proc/<pid>/mem with a little php script failed.

An article by Domas M. helped me. I do not have to write …

1. Page2Pub -- harvest wiki content and turn it into EPub and PDF. See also Sony dropping its proprietary format and moving to EPub. Open standards rock. (via oreillylabs on Twitter)
2. SQL Pie Chart -- an ASCII pie chart, drawn by SQL code. Horrifying and yet inspiring. Compare to PostgreSQL code to produce ASCII Mandelbrot set. (via jdub on Twitter and Simon Willison)
3. …

As you probably have experienced yet MySQL does not always provide all internal information as you might want to have them and as you are used to have from other RDBMS.

MySQL plans to improve this implementing the/a performance schema and its probably already partly done in MySQL 5.4. But who knows when this will be finished and what it contains at all...

What is not provided to me I want to gather myself... But how? Other RDBMS provide interfaces to attach applications directly to their memory to retreive information. But MySQL does not. So I was looking for a way to read an other process memory.

I have no clue about programming and thus changing MySQL code was out of focus. Further I am looking for a solution you can use immediately on a running systems at consulting gigs. Some tries to read /proc/<pid>/mem with a little php script failed.

An article by Domas M. helped me. I do not …