Choosing innodb_buffer_pool_size

On Redis, Memcached, Speed, Benchmarks and The Toilet The internet is full of things: trolls, awesome programming threads, p0rn, proofs of how cool and creative the human beings can be, and of course crappy benchmarks. In this blog post I want to focus my attention to the latter, and at the same time I'll try to show how good methodology to perform a benchmark is supposed to be, at least from my point of view. Why speed matters? In the Web Scale era everybody is much more obsessed with scalability than speed. Stressing scalability so much put somewhat in shadow what in the 80's was one of the most interesting meters of software coolness, that is: raw speed. Back in the initial days of home computing this was so important since CPU power was a limited resource. Raw speed is bound to queries per watt. Systems for the toilet Apparently I'm not alone in reconsidering the importance of raw speed. You should check it yourself reading the original post, but this is why the sys/toilet benchmark is ill conceived. Redis VS Memcached Methodology #! # BIN=.

Tuning MySQL - On the Path In the past two weeks, I have been called in for some near-emergency tuning of two rails applications that recently went into production. One was for a state-level government agency, the other was for a startup that recently went into production and found performance problems as their app grew in popularity. In both cases, the first place I looked was the innodb table settings for mysql - and in both cases, I found things that could immediately help the application in question. I'm going to walk you through that thought process now, potentially teach you something about your own rails app, and hopefully improve the performance of your rails application. Innodb - the background While this advice comes from two deployed rails applications, the advice I'm giving here applies to any application using mysql and the innodb storage engine. One of the coolest things about mysql is its ability to swap in different storage engines. Innodb - the knobs and dials Buffer Pool Size Log Buffer Size

TutorialCachingStory - memcached - This is a story of Caching - Memcached ed note: this is an overview of basic memcached use case, and how memcached clients work Two plucky adventurers, Programmer and Sysadmin, set out on a journey. Together they make websites. Websites with webservers and databases. Users from all over the Internet talk to the webservers and ask them to make pages for them. One day the Sysadmin realizes that their database is sick! Our plucky Sysadmin eyes his webservers, of which he has six. "So now what?" Our adventurous Programmer grabs the pecl/memcache client library manual, which the plucky Sysadmin has helpfully installed on all SIX webservers. $MEMCACHE_SERVERS = array( "10.1.1.1", //web1 "10.1.1.2", //web2 "10.1.1.3", //web3); Then he makes an object, which he cleverly calls '$memcache'. $memcache = new Memcache();foreach($MEMCACHE_SERVERS as $server){ $memcache->addServer ( $server );} Now Programmer thinks. Are the results of this select in memcache? // use $huge_data_for_front_page how you please Programmer pushes code.

Indexing the Web—It’s Not Just Google’s Business Interface responsiveness is one of many details web developers must consider in their quest to deliver a good user experience. An application that responds quickly enhances the user’s sense of control. In working to maximize application speed, though, it’s easy to look in the wrong places. There’s another, often-overlooked element, however, that can affect performance more than almost anything else: database design. Web databases do much more than passively store information. Does it really matter? Yes! articles articles_categories categories comments users Imagine that our blog is relatively new. First, let’s do a simple query to find all the articles by a particular author, using his e-mail address as the search term. SELECT * FROM articles INNER JOIN users ON articles.user_id = users.id WHERE users.email = 'john.doe@example.com'; 0.01 seconds Not surprisingly, this query runs very quickly. Let’s take a more complex example. Again, the query takes almost no time at all.

Instagram Engineering • Storing hundreds of millions of simple key-value pairs in Redis Unit Testing A Database | MySQL Expert | MySQL Performance | MySQL Scalability In a recent job interview I was asked the question regarding Unit Testing/Automated Testing of a Database? An interesting question and indeed an interesting problem. I thought it was a good topic to describe what I’ve done in the past, and where I would go for a more complete testing environment given the opportunity of a entire XP project. This is the approach I have implemented successfully in the past. It’s not a complete solution, however at the time with the client it provided appropriate coverage. I don’t use a framework such as dbUnit to load data via XML, or specifically test data. Within this process an automated build test would first reset the database to a known set of data. This does lead to two paths necessary for creating a schema, but this can also be tested adequately in an automated way. I also split my application tests suites that use the sample data into two buckets. Sample Data So we are creating a pre-configured data set, and an extensive one when possible. Summary

Instagram Engineering • Sharding & IDs at Instagram MMM 1.x [MMM for MySQL Wiki] Introduction MMM (MySQL Master-Master Replication Manager) is set of flexible scripts to perform monitoring and management of MySQL Masrter-Master replication cluster configurations. Details Generally, MMM offers MySQL monitoring and automatic failover for master-master pairs, it also has scripts for data backups, resynchronization between nodes etc. In two node master-master setup, MMM uses five IPs: single permanent IP for each node that is never changed, 2 _reader_ IPs (read-only) and 1 _writer_ IP (updates). Normally (no replication failures, no replication delay etc) active master has 2 IPs (reader and writer), standby master - 1 IP (reader). Documentation for MMM 1 Internals - Generic MMM internals informationFAQ - Questions and Answers about MMMTODO - Our development plans and roadmap

Shard (database architecture) Some data within a database remains present in all shards,[notes 1] but some only appears in a single shard. Each shard (or server) acts as the single source for this subset of data.[1] A heavier reliance on the interconnect between servers[citation needed]Increased latency when querying, especially where more than one shard must be searched.[citation needed]Data or indexes are often only sharded one way, so that some searches are optimal, and others are slow or impossible.[clarification needed]Issues of consistency and durability due to the more complex failure modes of a set of servers, which often result in systems making no guarantees about cross-shard consistency or durability.[citation needed] In practice, sharding is complex. Where distributed computing is used to separate load between multiple servers (either for performance or reliability reasons), a shard approach may also be useful. This makes replication across multiple servers easy (simple horizontal partitioning does not).

Luke Melia » Redis in Practice: Who’s Online? Redis is one of the most interesting of the NOSQL solutions. It goes beyond a simple key-value store in that keys’ values can be simple strings, but can also be data structures. Redis currently supports lists, sets and sorted sets. This post provides an example of using Redis’ Set data type in a recent feature I implemented for Weplay, our social youth sports site. The End Result Weplay members were told us they wanted to be able to see which of their friends were online, so we decided to add the feature. Properties of a Set First a quick overview of Redis’s “set” data structure. * 0 to N elements * Unordered * No repeated members In practice, this is nice because adding a value to a set does not require you to check if the value exists in the set first. Redis Set Operations You can see a full list of the set operations Redis supports. here are the ones were going to use: The Approach The idea is to have one active set per minute. The Code Here’s the relevant code.

11 Common Web Use Cases Solved in Redis In How to take advantage of Redis just adding it to your stack Salvatore 'antirez' Sanfilippo shows how to solve some common problems in Redis by taking advantage of its unique data structure handling capabilities. Common Redis primitives like LPUSH, and LTRIM, and LREM are used to accomplish tasks programmers need to get done, but that can be hard or slow in more traditional stores. A very useful and practical article. Show latest items listings in your home page. The take home is to not endlessly engage in model wars, but see what can be accomplished by composing powerful and simple primitives together. Please read the original article for more details.