by Aurimas Mikalauskas | Apr 7, 2011 | Insight for DBAs, MySQL
I don’t use many tools in my consulting practice but for the ones I do, I try to know them as best as I can. I’ve been using mk-query-digest for almost as long as it exists but it continues to surprise me in ways I couldn’t imagine it would. This...
by Justin Swanhart | Apr 4, 2011 | Insight for Developers, MySQL
“The least expensive query is the query you never run.” Data access is expensive for your application. It often requires CPU, network and disk access, all of which can take a lot of time. Using less computing resources, particularly in the cloud, results...
by Aurimas Mikalauskas | Mar 28, 2011 | Insight for DBAs, MySQL
Have you ever seen BIG weird numbers in mk-query-digest report that just seem wrong? I have! Here’s one report I got today: ... # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time...
by Justin Swanhart | Mar 23, 2011 | Insight for DBAs, Insight for Developers, MySQL
If you know me, then you probably have heard of Flexviews. If not, then it might not be familiar to you. I’m giving a talk on it at the MySQL 2011 CE, and I figured I should blog about it before then. For those unfamiliar, Flexviews enables you to create and...
by Morgan Tocker | Dec 25, 2010 | Insight for DBAs, MySQL
Inspired by Baron’s earlier post, here is one I hear quite frequently – “If you enable innodb_file_per_table, each table is it’s own .ibd file. You can then relocate the heavy hit tables to a different location and create symlinks to the...
by Aurimas Mikalauskas | Nov 9, 2010 | Insight for DBAs, MySQL
Before I start a story about the data recovery case I worked on yesterday, here’s a quick tip – having a database backup does not mean you can restore from it. Always verify your backup can be used to restore the database! If not automatically, do this...
by Morgan Tocker | Nov 8, 2010 | Insight for DBAs, MySQL
I have a 5G mysqldump which takes 30 minutes to restore from backup. That means that when the database reaches 50G, it should take 30×10=5 hours to restore. Right? Wrong. Mysqldump recovery time is not linear. Bigger tables, or tables with more indexes...
by Morgan Tocker | Oct 4, 2010 | Benchmarks, Insight for Developers, MySQL
The title is SEO bait – you can’t do it. We’ve seen a few recurring patterns trying to achieve similar – and I thought I would share with you my favorite two: Option #1: Use a table to insert into, and grab the insert_id: CREATE TABLE option1...
by Aurimas Mikalauskas | Sep 6, 2010 | MySQL
Few days ago I was working on a case where we needed to modify a lot of data before pushing it to sphinx – MySQL did not have a function to do the thing so I thought I’ll write MySQL Stored Function and we’ll be good to go. It worked! But not so well...
by Morgan Tocker | Aug 10, 2010 | Insight for Developers, MySQL
If you read Percona’s whitepaper on Goal-Driven Performance Optimization, you will notice that we define performance using the combination of three separate terms. You really want to read the paper, but let me summarize it here: Response Time – This is the...
by Morgan Tocker | Jul 24, 2010 | Insight for Developers, MySQL
I recently had a run-in with a very popular PHP ecommerce package which makes me want to voice a recurring mistake I see in how many web applications are architected. What is that mistake? The ecommerce package I was working with depended on caching. Out of the box...
by Justin Swanhart | Jun 15, 2010 | Insight for Developers, MySQL
While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “explain extended” which was added in MySQL 4.1 EXPLAIN EXTENDED …can show you what the MySQL optimizer does to your query. You might not know this, but...
by Justin Swanhart | May 19, 2010 | Insight for Developers, MySQL
MySQL supports two different algorithms for views: the MERGE algorithm and the TEMPTABLE algorithm. These two algorithms differ greatly. A view which uses the MERGE algorithm can merge filter conditions into the view query itself. This has significant performance...
by Justin Swanhart | May 6, 2010 | Insight for DBAs, MySQL
MySQL 5.1 introduces row-based binary logging. In fact, the default binary logging format in GA versions of MySQL 5.1 is ‘MIXED’ STATEMENT*; The binlog_format variable can still be changed per sessions which means it is possible that some of your binary...
by Vadim Tkachenko | Apr 2, 2010 | Hardware and Storage, MySQL
I often hear suggestion to use fadvise 1 fadvise system call to avoid caching in OS cache. We recently made patch for tar 1 tar , which supposes to create archive without polluting OS cache, as like in case with backup, you do not really expect any benefits from...
by Morgan Tocker | Jan 21, 2010 | Insight for Developers, MySQL
A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do...
by Morgan Tocker | Jan 9, 2010 | Insight for Developers, MySQL
There was a discussion on LinkedIn one month ago that caught my eye: Database search by “within x number of miles” radius? Anyone out there created a zipcode database and created a “search within x numer of miles” function ? Thankful for any...
by Devananda van der Veen | Dec 4, 2009 | Benchmarks, MySQL
I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here. The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for...
by Morgan Tocker | Nov 16, 2009 | Insight for Developers, MySQL
I wrote a post a while back that said why you don’t want to shard. In that post that I tried to explain that hardware advances such as 128G of RAM being so cheap is changing the point at which you need to shard, and that the (often omitted) operational issues...
by Matt Yonkovit | Nov 12, 2009 | Benchmarks, Cloud, MySQL
This is part 3 of my Tyrant extra’s, part 1 focused on durability, part 2 focused on the perceived performance wall. #3. Tokyo Cabinet Can have only a single writer thread, bottlenecking performance When writing an application using Tokyo Cabinet only one...
