The latest from the MySQL community

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, …

While Andrey made good progress with the Connector/OpenOffice.org I wasted my time trying to use OpenSolaris 2008.05 as an OpenOffice.org development platform. OpenSolaris is not on the list of platforms used my the maintainers and developers of OpenOffice.org. Another open technology - JDK 1.5 - has lowered my openness towards new development platforms.

If you are an OpenOffice.org end-user, skip this blog posting: grab a binary package and be happy! That’s better for the world-wide climate because your power consumption will be lower. An Intel Core 2 Duo E6750 …

While Andrey made good progress with the Connector/OpenOffice.org I wasted my time trying to use OpenSolaris 2008.05 as an OpenOffice.org development platform. OpenSolaris is not on the list of platforms used my the maintainers and developers of OpenOffice.org. Another open technology - JDK 1.5 - has lowered my openness towards new development platforms.

If you are an OpenOffice.org end-user, skip this blog posting: grab a binary package and be happy! That’s better for the world-wide climate because your power consumption will be lower. An Intel Core 2 Duo E6750 …

I have recently decided I would like to take advantage of the benefits of InnoDB file-per-table. Namely, that OPTIMIZE TABLE benefits-- defragmentation (Grazr does a lot of writes, both INSERT and DELETE), .ibd files being able to shrink if optimized, etc.

So, the next question is how to convert a ton of data to this setup. What I have tried is this:

1. Add innodb_file_per_table to my.cnf
2. Restart MySQL
3. Alter all InnoDB tables engine=InnoDB

This works great... except, my two 10GB tablespace files remain. I would like to make them smaller, but there is no way of doing this.

Also, there is no trick where you back up the newly created .ibd tables after the alter, move the large table space files to a backup dir, restart and pray that the new smaller tablespace files magically work with your .ibd files. That does *not* work ;)

So, dump all data and restore …

So you need to design highly available MySQL powered system... how do you approach that ?
Too often I see the question is approached by focusing on expensive hardware which in theory should be reliable. And this really can work quite well for small systems. It is my experience - with quality commodity hardware (Dell,HP,IBM etc) you would see box failing once per couple of years of uptime which is enough to maintain level of availability needed by many small systems. In fact they typically would have order of magnitude more availability issues caused by their own software bugs, DOS attacks and other issues.

However as your system growths the reliability goes down. If you have 100 servers with each failing every 2 years this is about a server a week which is bad and if you're into thousands and tens of thousands of servers server failures are becoming common place so it is important to make sure failing server does not affect …