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 (id int not null primary key auto_increment) engine=innodb; [...]
Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
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 6 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 10 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 fadvise system call to avoid caching in OS cache. We recently made patch for 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 fadvise [...]
Too many connections? No problem!
March 23, 2010 By 28 Comments
Did that ever happen to you in production? [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?
