With innodb_file_per_table=1, InnoDB places every table’s data and indexes in a separate .ibd file, but there is still a “global” system tablespace, stored by default in a file named ibdata1. This contains some of each table’s data, such as the undo log and insert buffer. If it is fixed-size, you can fill it up and crash the server, as I’ve mentioned in a few recent blog posts.

In older versions of MySQL, the SHOW TABLE STATUS command showed the amount of space free in the tablespace as an entry in the Comment column. If you weren’t using innodb_file_per_table, you could use this to see how full your tablespace was.

The servers I’m managing use innodb_file_per_table=1, so I thought perhaps I can find out how full the system tablespace is by disabling innodb_file_per_table, creating a table, and enabling it again. …

I previously blogged about work I was doing on automatically finding problems in a MySQL server, with no hardcoded thresholds or predetermined indicators of what is “bad behavior.” I had to pause my studies on that for a while, due to time constraints. I’ve recently been able to resume and I’m happy to report that I’m making good progress.

One of the things I’ve done is a survey of existing literature on this subject. It turns out that the abnormality-detection techniques I’ve developed over the years are well-known in the operations research field. I reinvented some classic techniques used in Statistical Process Control (SPC). These include Shewhart Control Charts, …

The second edition of High Performance MySQL has 27 reviews on Amazon, but the third edition only has 5 so far. By this point I assume many of you have a copy and have read it cover to cover. I’d really appreciate your reviews — when purchasing, people look not only at the star rating but at the number of reviews. You can create a review here. And thanks!

Further Reading:

• Are you sure you’re reading the second edition of High Performance MySQL?
• Update on High Performance MySQL, 2nd Edition …

MySQL is a great database server. It has lots of flaws, but if you work with its strong points and try to minimize its weaknesses, it works fantastically well for a lot of use cases. Unfortunately, if you bang on its weak points, sometimes you get hit by falling bricks.

A couple of its riskiest weak points relate to unavailability of an expected resource, particularly disk space and memory. For example, Stewart Smith has blogged about the interesting results you can get if you intentionally make malloc() fail. I think many of us probably have some experience with filling up the disk and causing the server to hang, breaking replication, or crashing something.

I’m managing a couple of servers that have taught me some interesting new lessons along these lines. They use innodb_file_per_table, but their main (shared) tablespace is fixed-size, and not very big. The tablespace tends to fill up when there are long-running transactions …

I have a fairly lightly loaded MySQL server with a few tables that are updated every five minutes. Other than these updates, there are very few queries run against the database. The data is queried just a few times per month. Ever so often, one of the more complicated queries will result in the process getting hung in the "copying to tmp table" state. To be honest, the queries that get hung aren't even that complicated. Usually there's one or two joins, a GROUP BY, and an ORDER BY.

I was thinking recently about what a DBA does, and decided to blog about what I think a DBA could/should do. Most DBAs I know are mired in day-to-day firefighting and time-consuming tedium. This forces them to operate in reactive mode (because they don’t have enough time to “get caught up”), and keeps them from more valuable things they could be doing. Here’s my short and incomplete list:

1. Working with the developers (programmers) to help architect upcoming projects. If the DBA leaves design to the developers, then suboptimal designs might be found after the fact. This often happens after deploying to production, where the design impacts the business. Without early input, the DBA also has no chance to assess and prepare for future needs.
2. Teaching developers how to work with the database. Many developers struggle to understand databases and SQL, and are unable to fully optimize the queries they write. They …

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo: https://bitbucket.org/themattreid/generic-sql-scripts/src/15c75632f1af/mysql-memory-report-storedproc.sql

##################################################################### …

The servers I help manage have a lot of deadlocks, especially around a few central tables that are important to many business functions. The queries against them are complex, and they crunch a lot of data in some cases. As a result, we have long-running transactions that often deadlock against others, and there are even many short-running jobs that touch only a single row at a time that can’t get their work done sometimes.

I’ve often said that deadlocks are a fact of life in transactional systems. The application must be able to deal with the deadlocks. This is true, but it’s not the whole story. The work needs to be done, unless it’s user-triggered and the user gets frustrated and abandons what they’re trying to do. That’s not the case in the applications I use; if something fails, it will get retried until it succeeds, because work queues and doesn’t go away until it’s completed.

Depending on how long it takes for …

When I kill a query or connection, whoever is running it gets a boring message about what happens. Wouldn’t it be fun and useful to be able to specify the error message the user should see? Imagine the possibilities:

mysql> KILL 10282, "Sorry, no cigar today. Try again tomorrow."

Joking aside, relevant error messages would be great for all involved.

Further Reading:

• An exciting week for InnoDB and MySQL
• MySQL’s ERROR 1025 explained
• The unexpected consequences of SELinux
• …

MySQL 5.1 added a long-needed feature: INFORMATION_SCHEMA tables for inspecting locks held and waited-for inside of InnoDB. With this addition, it became possible to figure out who is blocking whom.

MySQL 5.5 changed a lot more things inside the server. One of the new features is improved metadata locking. This solves a lot of inconsistencies and bugs that were previously possible. The output of SHOW PROCESSLIST is also changed; instead of the venerable Locked status, there are more fine-grained status indicators such as Waiting for table metadata lock.

Unfortunately, the additional locking is not possible for the DBA to inspect. Where InnoDB’s storage-engine-level locking used to be …