Category Archives: OurSQL Database Podcast

Log Buffer #72 — a Carnival of the Vanities for DBAs

















Top 10 MySQL Best Practices

So, O’Reilly’s has published the “Top 10 MySQL Best Practices” at Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.

For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

They perpetuate a myth in #4, “Don’t store binary data in MySQL.” What they really mean is “don’t store large data in MySQL”, which they go into in the tip. While it’s true that there is very little benefit to having binary data in a database, they don’t go into what those benefits are. This means that people can’t make informed decisions, just “the best practice is this so I’m doing it.”

The benefit of putting binary data in MySQL is to be able to associate metadata and other data. For instance, “user 200 owns file 483”. If user 200 is gone from the system, how can you make sure file 483 is as well? There’s no referential integrity unless it’s in the database. While it’s true that in most cases people would rather sacrifice the referential integrity for things like faster database backups and easier partitioning of large data objects, I believe in giving people full disclosure so they can make their own informed decision.

#5 is my biggest pet peeve. “Stick to ANSI SQL,” with the goal being to be able to migrate to a different platform without having to rewrite the code. Does anyone tell Oracle folks not to use pl/sql like collections? Nobody says “SQL is a declarative language, pl/sql is procedural therefore you should never use it”. How about SQL Server folks not to use transact-sql statements like WAITFOR? MATCH… AGAINST is not standard SQL, so I should never use it?

Now, of course, if you’re selling a product to be run on different database platforms, then sure, you want to be platform agnostic. But you’d know that from the start. And if you have to migrate platforms you’re going to have to do lots of work anyway, because there are third-party additions to all the software any way.

And why would *anyone* choose a specific database, and then *not* use those features? I think that it’s a good tip to stick to ANSI SQL if you *know* you want to, or if you have no idea about the DBMS you’re using.

If you want to see how this cripples MySQL, check out Visibone’s SQL chart at: — you can buy it here: I may post later on about my own personal MySQL Best Practices….

Virtualization and MySQL

So, the article at: says:





Statistics Gathering Script

I posted some code to the MySQL Forge at:



An explanation of the code follows:
Continue reading

Option and Variable Matrix

SHOW STATUS and SHOW VARIABLES are very useful features for analyzing server performance, server setup and general debugging. Many times, there are items applicable to a scenario in both lists. Further, sometimes it’s difficult to know whether an option or variable can be changed on the commandline or in an option file.

For example, let’s say you are debugging a lot of aborted connects. You may care about the following items in “SHOW STATUS”:


And the following items in SHOW VARIABLES:


You might compare max_connections to max_used_connections, for example. Let’s say you realize you want to increase max_connections from 100 to 200 — can you do it dynamically on the commandline? Do you have to put it in an option file and restart?

Now MySQL has a matrix of variables and options that easily shows what can be changed where:

As a bonus, many of the items in the list have links to the section that explains what they do.

This is a great reference — the matrix is only for 5.0 and up, but most of it likely applies to below 5.0 — just that some options may not be available. At any rate, you can use the 5.0 matrix, click through to the variable/option’s description, and then use the link on the left-hand side to go to the “3.23, 4.0, 4.1” documentation, which will give you the correct information for that version.

I know this will save me much time.

Good Ideas Implemented Poorly

Please share good ideas implemented poorly you have seen.

For example, in the past 24 hours I have come across 2 such beasts:

1) A company doing vertical scaling based on username. Unsurprisingly, they found that 70% of their users had usernames beginning with 1 of 2 letters. A better idea? Do it by last 2 digits of a user id. That way, even though users are created and perhaps deleted all the time, the distribution is effectively random. Language is not.

With 2 significant figures, you can divide vertically many different ways — 1 large table of all the uids, 2 tables of uids ending in 00-49 and 50-99, 4 tables, 10 tables, 25 tables, 50 tables, 100 tables. This eliminates the need for a separate table for each user.

For example, the online dating site I work for uses this method for messaging. So a user’s inbox is just one part of a table. If the user has uid 12345, their “Inbox” is the result of SELECT * FROM Inbox_45 WHERE toUid=12345. Their “Sent” box is the result of SELECT * FROM Inbox_45 WHERE fromUid=12345. We have over 650,000 active users; I can only imagine what horrors we would have if we had 1 table for each person’s mailbox (much less 1 each for Trash, Sent, Saved and Inbox).

This also helps when using InnoDB tables, as we are — the count(*) query to find the number of messages in your boxes uses the indexes on toUid and fromUid on the tables. 1 table per uid would not take advantage of this index, although it would probably be a bit faster in terms of retrieval. We kept this idea in mind — if we had one table per uid, we probably could use MyISAM tables, and counts would be faster. But again, horrors with 650,000 tables!

Also, you want to keep your code as flexible as possible. Do not hard code which servers have which tables — use SHOW TABLES in scripts and such. We truncate trashes nightly, and simply have a loop that says:

for i in "show tables"
truncate Trash_$i

2) The lead developer on a new project had the rule of “each table has a class”, which led another developer to come to me asking to resolve a problem. You guessed it — tables in different databases that have the same name. We have a static database, with a table called “Preferences”. This is a joining table, that says “preference #1 is always blah”. And in our profiles database for a site, we also have a “Preferences” table — this is where we associate users with their preferences.

The goal is world domination to be able to have multiple sites use similar codebases and data schema. Therefore, it is perfectly reasonable to have databases with identical schemas and different names. Site1 can have an identical schema and codebase to Site2, with storing the data in the “Site1” and “Site2” databases. The reason to have 2 separate databases is that we want to be avoid having to put which site the data refers to as a part of each row.

Any other examples of good ideas implemented poorly? Feel free to make an entry, but if it’s not on please put a link in the comments so I’m sure to actually read it.

Followers may note I fell way short of my goal of 1 post per day in May; in fact, I had 15 posts in May, not the 31 I had hoped. I’m OK with that, and I apologize for the long radio silence. In the meantime, I had a wonderful few weeks, including biking on the island of Martha’s Vineyard, and a wonderful phone conversation with Frank Mash last night.

If anyone cares to know, my Skype name is awfief. I’m happy to talk to folks; I’m GMT -5 (Eastern Standard Time, Daylight Savings when appropriate). I also have a tendency to leave the program on while I’m in the room, so feel free to ring (instead of Skype Chat) to see if I’m around.

InnoDB information

SHOW ENGINE INNODB STATUS shows current InnoDB status, including deadlocks. I was thinking a while back it would be nice to have a log of all deadlocks. Well, I got my answer:

To cause the standard InnoDB Monitor to write to the standard output of mysqld, use the following SQL statement:


The monitor can be stopped by issuing the following statement:

DROP TABLE innodb_monitor;