I occasionally get in to light arguments healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response has always been: “it depends on how much of a tradeoff you are willing to make for performance. In some situations the cost can be considerable”. .. that’s when [...]
UDF -vs- MySQL Stored Function
September 6, 2010 By 8 Comments
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 really [...]
Why message queues and offline processing are so important
August 10, 2010 By 3 Comments
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 time required to complete a desired task. Throughput – Throughput is measured in [...]
Caching could be the last thing you want to do
July 24, 2010 By 14 Comments
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 it couldn’t serve 10 pages/second unless I [...]
EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with “extended explain” 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 MySQL can dramatically change your query before it actually executes it. This process [...]
A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
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 advantages over TEMPTABLE views. A view which uses the TEMPTABLE algorithm will have to compute the [...]
Debugging problems with row based replication
May 6, 2010 By 12 Comments
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 log entries will be written in a row-based fashion instead of the [...]
fadvise – may be not what you expect
April 2, 2010 By 7 Comments
I often hear suggestion to use
1 | fadvise |
system call to avoid caching in OS cache. We recently made patch for
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 caching. However working on the patch, I noticed, that
1 | fadvise |
with [...]
Too many connections? No problem!
March 23, 2010 By 28 Comments
Did that ever happen to you in production?
1 2 | [percona@sandbox msb_5_0_87]$ ./use ERROR 1040 (00000): Too many connections |
Just happened to one of our customers. Want to know what we did?
When should you store serialized objects in the database?
January 21, 2010 By 47 Comments
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 this for you automatically.
