MySQL@Facebook

MySQL@Facebook

Initial InnoDB Compression Test Results
I've completed an initial round of testing InnoDB with data compression and the results are extremely promising. The testing was performed by setting up two shadows of a production database...
Yesterday at 5:44pm
View all 5 comments
MySQL@Facebook
MySQL@Facebook
Arjen, InnoDB says in general compression is best for a read-heavy workload. Because we use memcache, most reads don't make it to DB and our DB reads and writes are actually fairly balanced. Compression still helps us because the size of our DB is so much larger than available buffer cache. Like any performance changes, your mileage may vary and you'll need to test the benefit for your specific workload.
6 hours ago
MySQL@Facebook
MySQL@Facebook
Matthew, our database shadow system is implemented in the DB wrappers in the application layer. I don't think mysql-proxy currently supports duplicating a single query for multiple backend servers but it may not be too difficult to add support for.
5 hours ago
MySQL@Facebook

MySQL@Facebook

FLIFO scheduling for InnoDB
At the end of this note I describe how InnoDB can be much faster (2.5X) for high-concurrency workloads. However, what we really did is improve the code to not get 2.5X slower. InnoDB uses innodb_thread_concurrency to limit the number of threads that run concurrently...
MySQL@Facebook

MySQL@Facebook

The impact from InnoDB readahead
InnoDB uses background threads to handle readahead (prefetch) requests. Requests are generated when it detects sequential or random access to most blocks in an extent. This is described in the 5.0 MySQL manual with new behavior in the plugin...
November 5 at 3:12pm
Inaam Rana
Inaam Rana
Mark,

Maybe it is worth to test plugin which has innodb_read_ahead_threshold to throttle the aggressiveness of prefetching.
November 5 at 8:23pm
MySQL@Facebook
MySQL@Facebook
@Inaam - we have one person devoted to 5.1 and the InnoDB plugin. Soon we will have good data to share about that. We also need more data from the current code as 24 hours on one server suggests the change might be good, but is not conclusive.
November 6 at 6:35am
MySQL@Facebook

MySQL@Facebook

Increasing innodb_log_file_size
We have servers that run with innodb_log_file_size=256M and some of these servers do a lot of disk writes per second...
November 4 at 2:06pm
Matthew Boehm
Matthew Boehm
So the result is...if you use InnoDB and have lots of writes, increasing innodb_log_file_size will improve performance?
November 4 at 7:50pm
MySQL@Facebook
MySQL@Facebook
Yes, and it will also make crash recovery slower.
November 4 at 8:15pm
MySQL@Facebook

MySQL@Facebook

Hello!
I am Domas Mituzas, and I have just joined Facebook MySQL team. I've been working at MySQL Support before, as well as did Wikipedia data and performance engineering on my free time - and I've blogged about that a bit...
November 3 at 1:39pm
View all 9 comments
MySQL@Facebook
MySQL@Facebook
Matt, keep positions open, and I may come back! (well, not really, hehehe, but hope will help to fill the void I've left in your soul with my departure). -- Domas
November 3 at 6:18pm
Lenz Grimmer
Lenz Grimmer
You will be missed at the MySQL Team. But all the best and congratulations! Keep up the good work and make sure to keep Facebook rocking!
November 4 at 1:44am
MySQL@Facebook

MySQL@Facebook

Choosing the right page size, part 2
InnoDB uses a 16kb page by default. I want to know whether performance improves with an 8kb database page for my workload. Two servers were setup to run a mirror of the production database workload. One used 8kb InnoDB pages and the other used 16kb...
November 3 at 7:48am
Gerardo
Gerardo
I've been reading your articles about page sizes with some curiosity, since you're line of reasoning seems be go against my experience.
In a past life, I used to have to tune a DB engine (not MySQL) by adjusting page sizes, and I would usually get better performance with bigger pages, not smaller ones. However there is a threshold at which this no... Read More longer holds true and it will depend for each workload.

I will have to set up some benchmarks similar to yours to compare the results with smaller (8kb pages) and bigger one (32kb & 64kb pages) with my company's workload.
November 4 at 4:58pm
MySQL@Facebook

MySQL@Facebook

Long running transactions
I was debugging a server that had too many concurrent queries. From SHOW INNODB STATUS output, there were many long running transactions with uncommitted work and there were many more transactions blocked on InnoDB locks...
November 2 at 11:04am
MySQL@Facebook

MySQL@Facebook

Hello and 5.1 Insert Buffering
Hello. I'm Ryan Mack, a new member of the Facebook MySQL team. My first order of business is evaluating MySQL 5.1 and the new InnoDB plugin. So far things look very promising, but I came across one issue worth sharing...
October 23 at 1:00pm
Jeremy Zawodny
Jeremy Zawodny
I did some page compression testing a while back and had VERY mixed results. That was with 1.0.2.

I hope to get more serious about it with 1.0.4 in the fairly near future. I look forward to seeing your results (and I'll publish ours too).
October 23 at 7:39pm
John Bonifas
John Bonifas
http://www.youtube.com/watch?v=KY_EKcuboaI
http://www.youtube.com/watch?v=kTCUZ3s1iIY
October 23 at 8:11pm
MySQL@Facebook

MySQL@Facebook

Hack of the day
My hack of the day for Tuesday was to add the my.cnf option innodb_mmap_buffer_pool that specifies the name of a directory in which a file is created. The file provides the buffer pool memory allocation when opened with mmap...
October 22 at 6:27pm
View all 14 comments
Shirish Jamthe
Shirish Jamthe
I know where to get NOR flash :-)
October 23 at 1:11am
MySQL@Facebook

MySQL@Facebook

More changes in the Facebook patch
I pushed more changes to the Facebook patch on Launchpad. These include replacing SHOW TABLE_STATISTICS with an information_schema table, adding the option time_query_overhead to enable/disable the use of extra timers for performance monitoring and adding an option to measure more mutex contention...
October 19 at 10:55am
View all 3 comments
MySQL@Facebook
MySQL@Facebook
Yes, but I didn't review the diff. I need to figure out what I can reuse for future patches. I am happy that he cleaned/improved parts of it. I hope he didn't inherit the performance problems from the original implementation that were fixed in the v3 Google patch.
October 19 at 3:15pm
MySQL@Facebook

MySQL@Facebook

Work in progress
Harrison and Konstantin (Kostja) did not like my plan for making FLUSH TABLES WITH READ LOCK faster and suggested an alternative that is non-blocking and faster. The new syntax is as beautiful as the one it replaces -- START TRANSACTION WITH CONSISTENT INNODB SNAPSHOT...
October 17 at 9:27am
John Bonifas
John Bonifas
ya, there ya go. Snapshot firehose. I *always* used that cursor at Boeing, especially when pulling from Oracle, to spew out data to the web page reports and to fill the Data Warehouse cubes.
October 17 at 9:54am
Brian Aker
Brian Aker
I'd like to see whatever you come up with. I've thought about adding encapsulation around "outfile" so that we could just allow folks to plug in whatever they want to (might be nice to do filtering as well).
October 18 at 6:19pm
MySQL@Facebook

MySQL@Facebook

Choosing the right InnoDB page size, part 1
How important is page size matter for an IO-bound load? For this note results are limited to a disk-based server. InnoDB uses a 16kb page and you can recompile it to use an 8kb page. After making a few changes to it, I was able to use 4kb pages but I am not sure if that is safe to use in production...
MySQL@Facebook

MySQL@Facebook

A use case for show table_statistics
I deployed a mysqld binary with support for show table_statistics and quickly realized I need to improve it by replacing the column Rows_changed with the columns Rows_updated, Rows_deleted and Rows_inserted. I also need to support this as a table in the information_schema...
October 3 at 12:51pm
View all 7 comments
Sheeri Kritzer Cabral
Sheeri Kritzer Cabral
Gavin -- thanx!

Mark -- I think the binary log may give some of this information too, though it's much more cumbersome to track.
October 6 at 6:19am
MySQL@Facebook
MySQL@Facebook
From a test of sysbench readonly using an 8-core server there was no performance difference comparing binaries with and without support for table_statistics.
October 19 at 4:29pm
MySQL@Facebook

MySQL@Facebook

FORCE INDEX and optimizer overhead
Does a query with FORCE INDEX reduce the overhead from query optimization? For me it did not when using sysbench. I configured sysbench to only do primary key lookup queries and ran it with the client and mysqld on the same server...
October 2 at 2:31pm
View all 3 comments
Harrison Fisk
Harrison Fisk
iirc, the main index hints (force, use) just reduce the optimizer choices by limiting the candidate indexes for the optimizer to choose from and influencing the cost of a full table scan. The optimizer still runs in any case, so the base overhead will always be present.

The point query in sysbench really only has 2 options, use the index or full table scan. FORCE doesn't remove the full table scan option, just makes it very expensive, and hence does not change the number of options at all.

Where it might be a bit faster is when you have more candidate indexes and more tables for join orders. Since a lot of the optimizer join work is O(N!), reducing the N will make a much bigger difference as N gets larger.... Read More

Add a few redundant indexes and a few joins and I would guess you should see a change.
October 3 at 10:18am
MySQL@Facebook

MySQL@Facebook

show table_statistics ported to the Facebook patch
I ported/reimplemented show table_statistics for the Facebook patch on Launchpad. This first appeared in the Google patch. In this patch the Database and Rows_requested columns have been added and the Rows_changed_x_indexes column removed...