by Ryan Lowe | Oct 21, 2008 | Uncategorized
We have written before about the importance of using numeric types as keys, but maybe you’ve inherited a schema that you can’t change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have...
by Baron Schwartz | Oct 8, 2008 | Insight for DBAs
The trouble with slave lag is that you often can’t see it coming. Especially if the slave’s load is pretty uniform, a slave that’s at 90% of its capacity to keep up with the master can be indistinguishable from one that’s at 5% of its capacity....
by Baron Schwartz | Sep 24, 2008 | Insight for Developers
A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a “next”...
by Baron Schwartz | Sep 20, 2008 | Insight for Developers
When optimizing queries for customers, the first thing I do with a slow query is figure out what it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative ways to write it, and you can’t do that unless you know...
by Baron Schwartz | Aug 23, 2008 | Insight for DBAs
Yesterday I helped someone who was seeing a lot of “server has gone away” error messages on his website. While investigating this problem, I noticed several things amiss, which appeared to be related but really weren’t. The biggest measurable sign...
by Baron Schwartz | Jul 30, 2008 | Insight for DBAs
I use Maatkit for a lot of grunt work and thought you might appreciate this quick tip. Suppose you have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you...
by Baron Schwartz | Jul 25, 2008 | Insight for DBAs
This article is not meant to malign hosting providers, but I want to point out something you should be aware of if you’re getting someone else to build and host your servers for you. Most hosting providers — even the big names — continue to install...
by Baron Schwartz | Jul 13, 2008 | Percona Events
I just posted slides from a talk I gave at a Facebook application developer conference in Las Vegas this weekend. The talk is titled Outrun the Lions. Our customers run several of the top 10 applications on Facebook right now (as measured by the number of active...
by Baron Schwartz | Jul 3, 2008 | Benchmarks, Insight for Developers
Recently I had a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary log. While this is generally a fast way to load data (especially if you disable unique...
by Baron Schwartz | Jun 23, 2008 | Insight for DBAs
How many of you use the MySQL command-line client? And did you know about the pager command you can give it? It’s pretty useful. It tells MySQL to pipe the output of your commands through the specified program before displaying it to you. MySQL Command-line...
by Baron Schwartz | May 31, 2008 | Insight for DBAs
Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn’t. Don’t let it catch you off guard. Do you really need to restrict MySQL users based on hostnames? If you don’t, you should probably disable...
by Baron Schwartz | May 24, 2008 | Insight for DBAs
Much has been written about the new InnoDB pluggable storage engine, which Innobase released at the MySQL conference last month. We’ve written posts ourselves about its fast index creation capabilities and the compressed row format, and how that affects...
by Baron Schwartz | Apr 22, 2008 | Insight for DBAs
Have you ever run a query in MySQL and wondered how long it’ll take to complete? Many people have had this experience. It’s not a big deal until the query has been running for an hour. Or a day and a half. Just when IS that query going to finish, anyway?...
by Peter Zaitsev | Apr 2, 2008 | Insight for Developers
Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public...
by Aurimas Mikalauskas | Mar 27, 2008 | Insight for DBAs
Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or...
by Peter Zaitsev | Mar 17, 2008 | Insight for DBAs
I posted a simple INFORMATION_SCHEMA query to find largest MySQL tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes: Find total number of tables, rows, total data...
by Peter Zaitsev | Mar 7, 2008 | Insight for Developers
Doing performance analyzes today I wanted to count how many hits come to the pages which get more than couple of visits per day. We had SQL logs in the database so It was pretty simple query: select sum(cnt) from (select count(*) cnt from performance_log_080306 group...
by Peter Zaitsev | Feb 12, 2008 | Insight for DBAs, MySQL
Probably most of MySQL users sometimes had a situation when they would do changes in MySQL config file without restarting the server (maybe matching SET GLOBAL command to do it in run time). This could be because it is misspelled or because the given version does not...
by Alexey Kovyrin | Jan 24, 2008 | Benchmarks
Really often in customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state”, “gender”, “status”, “weapon_type”, etc, etc. Frequently we suggest to...
by Peter Zaitsev | Jan 11, 2008 | Insight for DBAs
When you’re storing text of significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL does not provide compressed BLOB/TEXT columns (I would really love to have COMPRESSED attribute for the BLOB/TEXT columns which would make...
