Many times I’ve heard people advise on “best practices” for a MySQL database. This often includes routine maintenance, such as “you should run OPTIMIZE TABLE on all of your InnoDB tables once a week to defragment them for better performance.” But this advice is unsubstantiated and could even be detrimental. Here are some of the obvious problems that I can think of: The optimized table compacts the primary key (clustered index) to its default 15/16ths fill factor per page.

I am trying to debug a server with a high rate of Aborted connects reported in SHOW STATUS.This frequently occurs when the maximum number of concurrent connections, max_connections, has been exceeded. Alas, this server does not have too many connections, I have no problem creating a connection and there are connections for all accounts so the password table is probably valid. It is time to read the source code. The aborted_connects counter is incremented when:

• the call to pthread_create in create_new_thread() fails. This creates a new thread to handle the new connection and failure is unlikely.
• the call to my_thread_init() in handle_one_connection() fails. This is unlikely.
• the call to THD::store_globals() in handle_one_connection() fails. This is unlikely.
• the call to check_connection() in handle_one_connection() fails. This is likely.

At this point I assume …

Not quite pop quiz format, but if you enjoyed the ones I published some time ago (almost 2 years ago now… how time flies), you’ll probably be interested to know that “plogi” and “urs” (whoever they are) have picked up on the idea and started on their own set of questions and answers.

Do pay them a visit. You might learn something!

I’ve recently become supremely disappointed in the availability of Nagios checks for RAID cards. Too often, I see administrators rely on chance (or their hosting provider) to discover failed drives, a dying BBU, or a degrading capacity on their RAID cards. So I began work on check_raid (part of check_mysql_all) to provide a suite of [...]

Topics for this podcast:

*Matt Asay moves from Alfresco to Canonical
*GPL fade fuels heated discussion
*Apple’s iPad and its enterprise and open source impact
*Open source in data warehousing and storage
*Our perspective on Oracle’s plans for Sun open source

iTunes or direct download (32:50, 9.2 MB)

For anyone that missed the recent webinar on getting the best performance out of MySQL Cluster then the replay is now available from mysql.com.

Benefits of connection pooling

Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?

Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for …

Welcome, everyone, to the 177th edition of Log Buffer, the weekly review of database blogs. It was another week heavy with technical posts, so let’s waste no time, and get it all started with . . .

PostgreSQL

David Fetter shares his recipe for adding only new rows: “Let’s say you have a table and a data set, and would like to add only those rows in your data set that aren’t already in the table. There are hard ways, but here’s an easy one.”

Simon Riggs, the Database Explorer, offers his thoughts on …

Matt Asay joins Canonical. Paula Hunter joins the CodePlex Foundation. 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.”

# Matt Asay joined Canonical as chief operating officer.

# Paula Hunter was named executive director of the CodePlex Foundation.

# Actuate recorded $6.5m in BIRT-related business for Q4; annual BIRT-related business of $18.2m up 18%.

# Glyn Moody outlined The Great Oracle Experiment.

# The Symbian Foundation …

Even though things have been a tad bit turbulent around here in the recent weeks, our engineers did not rest and churned out an impressive number of updates and new releases of the MySQL Server and related products.

Here's a quick summary of what we released this year so far (in chronological order):

• MySQL Server 5.1.42
• MySQL Server 5.5.1 Milestone 2 aka "Betony"
• MySQL Workbench 5.2.14 Beta 4
• MySQL Connector/J 5.1.11
• …

MySQL Cluster can be used as a general purpose transactional storage engine, but if you convert all your InnoDB tables to it and connect your application straight to it you may not see the performance you were hoping for.  This is because MySQL Cluster was originally designed for real-time telecommunications applications (such as RADIUS servers).  It has slowly been modified to become more general purpose and improvements are being made every day but there are still some performance considerations which go with this.  In some cases tweaking your schema and/or queries can help performance dramatically, so I shall try and outline some of things to watch for here.

Indexes

The fastest type of lookup you can do in Cluster is a primary key equality lookup (ie. SELECT * FROM table WHERE pkey = 2).  This is because the primary key is stored as a hash index as well as an optional ordered index.  This hash index is …