I’ve just released innotop version 1.9.0. This version fixes a lot of bugs, makes the tool work better when monitoring dozens of MySQL servers, and adds two new modes: a Health Dashboard and an InnoDB Blockers/Blocked mode.
Further Reading:
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, …
[Read more]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:
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 …
[Read more]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:
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
##################################################################### …[Read more]
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 …
[Read more]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: