The latest from the MySQL community

This post is the first in a series of articles which serves to highlight the services of the Launchpad platform which hosts the MySQL Server, MySQL Forge, MySQL Sandbox and Drizzle Server projects. I will be walking you through the various pieces of the platform and provide examples of using each of the services. I will cover in depth the source code management services which all three projects now rely upon. The code management services are the critical piece of the development platform. In addition, I will show you how to use the Blueprints, Bugs, Answers and Translations services that many MySQL ecosystem projects, including the MySQL Sandbox and …

You have a typical rpm installation of mysql, and the process is crashing. Here are the basic steps needed to find out more info about a crash:

• Configure the OS to be able to create corefiles. (Redhat details), (Solaris details)
  
• Tell mysqld to create a corefile by adding the following options to my.cnf:

[mysqld_safe]
core-file-size=unlimited

[mysqld]
core-fileUsually the corefile will be created in the datadir with a name like core.2921 where 2921 was the pid of the running process. The location is configurable on most OS's.

You'll need the following to study the core file:

• exact mysqld binary that created the core file
…

Patrick Galbraith wrote in his blog about switching to innodb_file_per_table.  For those that don't know about this setting, it places the data for the tables into .ibd files within the database dir instead of storing it in the ibdata files in the main datadir.  This is useful if you don't want to babysit your innodb tablespace.  At least, that was my main reason for wanting to use it.  There is still dictionary data stored in the ibdata file(s) so you can't just remove them.

Anyhow, the delima Patrick wrote about is recovering the space used by the ibdata files in the datadir after you have converted the tables to one file per table.  I commented on his blog, but thought it worth a full post to be sure others could find my solution.

1. 
   
2. Backup your data (cuz, you never know)
   
…

I still remember the day. I was working for a large systems integrator on a very major account, and had responsibility for some DB2 databases, but also had the unfortunate duty of also taking care of some legacy IMS databases (hierarchical IBM databases used quite a lot ‘back in the day’), which I knew next to nothing about. One morning I was asked to make a small change to a very large and prominent IMS database – just remove one record – what could go wrong? The guy who knew IMS real well was gone so I put together a program to do the job (yes, you actually had to write a program to do it), and ran it after hours. I came in the next day and tried to access some records in the database. Nothing. All gone, except for – ta-da – the one record I wanted to remove! At the instant I realized my mistake, I was acutely aware of two things: (1) I was in serious trouble if I didn’t have a backup, and (2) I was in desperate need of a complete …

Quite common beginners mistake is not to understand how indexing works and so index all columns used in the queries.... separately. So you end up with table which has say 20 indexes but all single column ones. This can be spotted with a glance view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance. But wait. Do not go ahead and index all combinations. This would likely be poor choice too

Entry posted by peter | 24 comments

Add to: | …

I remember reading Disk is the new Tape earlier this year and how much it resonated. That's probably because I was working for Yahoo at the time and hearing a lot about their use of Hadoop for data processing. In fact, I even did a couple videos (1 and 2) about that.

Anyway, I recently faced the reality of this myself. When I wrote about The Long Term Performance of InnoDB I'd been beating …

One of the approaches we are using to look at MySQL scalability is to identify and if possible eliminate hot locks inside MySQL. For locks that cannot be eliminated, we are looking at ways to reduce the mutex hold times, or replace the critical sections with lock-free algorithms. It won't happen overnight, but it needs to be done if we are to make MySQL more scalable.

Along the way we're planning to blog about how we identified and eliminated performance bottlenecks and provide you with all the gory details about the process and technique. This will hopefully entice you all to contribute to fixing performance issues We can reach our goal of a highly scalable MySQL much faster!. If you any other ideas about how we can scale the MySQL scalability effort (pun intended), or you would like to bust some locks, please feel free to let us know.

Fixing locks is sometimes very tricky as sometimes eliminating a really hot lock can only …

This article explains how to set up a properly functioning C/C++ development environment on Linux. The article is aimed at developers interested in contributing to the Drizzle server project, but the vast majority of the content applies equally well to developers wishing to contribute to the MySQL server or any other open source project written in C/C++

IMPORTANT: This article doesn't get into any religious battles over IDEs or particular editors. IDEs and editors are what you use to edit code. What this article covers is the surrounding libraries, toolchain, and dependencies needed to get into the development or contirbution process. That said, go Vim.

The examples shown use the Debian/Ubuntu methods of obtaining code packages and …

I started using Oracle, a MVCC database, to develop reporting (data warehousing, BI, take your pick) systems years ago.  I’ve come to appreciate the scalability improvements that MVCC provides, particularly for pseudo real-time reporting applications, the ones where loads are occurring at the same time as report generation.  So when people say InnoDB, partly due to MVCC, isn’t as good as MyISAM for reporting I had to look into this in more detail.

What I found is InnoDB is a good engine for reporting.  In some ways, such as performance, it is at times better than MyISAM, and one of the downsides, such as a larger disk requirement, can be mitigated.  The trick is to for the primary key to be the one predominant access path.  In this example, the InnoDB clustered index, is purchaseDate and another column, such as orderId is added to make it unique.  This has a number of advantages.  In my experience, …