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:
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 …
[Read more]Although not perfect, MySQL replication was probably the killer feature that made MySQL the default database for web applications some time ago. Since then, MySQL replication has been improved greatly, with such notable changes as row-based replication. At the same time, the replication engineering team has made MySQL replication more conservative and less forgiving of foot-gun errors. These have gone a long way towards helping users avoid some of the problems that made replication sometimes drift out of sync with the master copy, sometimes silently.
In some cases I think the strictness has gone a little too far. One example is the server’s identification of statements that are unsafe for replication because they are nondeterministic. Here is a statement in an application I manage, which is designed to claim some work from a queue. After running this statement, the application checks if any rows were affected, and if so, it then fetches …
[Read more]Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key:
explain select * from tbl1
where (col1, col2) in (
(732727758,102),(732728118,102),(732728298,102),(732728478,102),
(732735678,102),(962074728,102),(964153098,102),(2027956818,102),
(2034233178,102),(2034233538,102))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1379
Extra: Using where
Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table:
explain select * from tbl1
where (col1=732727758 and …