mySQL DBA

mySQL Column Types and Why it Matters.

2012-01-17T10:49:00.000-08:00

MySQL is awesome at converting stringsto integers when comparing column lvalues with converted rvalues. So much sothat many of us take this fact for granted. When does this assumption breakdown? When does passing in the wrong value cause problems in mySQL?

Let's take a table EmailLookup forexample.

CREATE TABLE `EmailLookup` (
`userId` bigint(20) unsigned NOT NULL,
`email` varchar(128) NOT NULL,
`emailCrc32` int(11) unsigned NOT NULL,
`createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`emailCrc32`,`userId`),
KEY `createDate` (`createDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The primary key is emailCrc32, userIdfor a key size of 12 bytes (4 bytes for int, 8 bytes for bigint). Since this is a compound key (key with twocolumns), following the rules of Left Most Prefix I get two index lookup typesfor one. emailCrc32 and userId is a unique index lookup while emailCrc32 is alsoan index lookup. Thus I can do

SELECT email FROM EmailLookup WHEREemailCrc32 = ? and userId =?

SELECT email FROM EmailLookup WHEREemailCrc32 = ?

What happens if I pass emailCrc32 anumeric string. i.e.

mysql> SELECT email FROMEmailLookup WHERE emailCrc32 = '1';

Empty set (0.00 sec)

So cool, works and comes back superquick.

What happens if I pass emailCrc32 areal string. i.e.

mysql> SELECT email FROM EmailLookupWHERE emailCrc32 = 'a';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level | CodeMessage |
+---------+------+---------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a' for column 'emailCrc32' at row1 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

Comes back quick.

What happens if I pass the column a big int

mysql> SELECT email FROM EmailLookup WHERE emailCrc32 = 100003256490710;
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level | Code |Message |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'emailCrc32' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Comes back quick. But, what if I do aDELETE on a 64-bit server?

mysql> DELETE FROM EmailLookup WHEREemailCrc32 =  '100003256490710';

 Query OK, 0 rows affected (2 min14.16 sec)

WHAT? A 2 min query for animpossible DELETE? Notice that '100003256490710' is a string.

What is happening to INNODB

mySQL thread id 90620794, query id3745441316 10.170.22.169 schoolfeed updating
DELETE FROM EmailLookup WHERE emailCrc32 = '100003256490710'
TABLE LOCK table `Shard1`.`EmailLookup` trx id 2AB524B6D lock mode IX
RECORD LOCKS space id 160 page no 5626 n bits 248 index `PRIMARY` of table `Shard1`.`EmailLookup`trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 7580 n bits 240 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 6039 n bits 280 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 455 n bits 352 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 3174 n bits 288 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 5997 n bits 304 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 1486 n bits 296 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 5607 n bits 280 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
RECORD LOCKS space id 160 page no 2729 n bits 312 index `PRIMARY` of table`Shard1`.`EmailLookup` trx id 2AB524B6D lock_mode X locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

Yikes this is bad.

Is this a bug? Maybe but is also acondition that should not happen if types are respected. The moral of the story is if your application respects column types mySQL will respect you :)

This is from Server version: 5.1.57-rel12.8-log Percona Server (GPL), 12.8, Revision 233

The Effect of using Cloudfront and why it matters

2011-12-20T11:29:00.000-08:00

For years (12+) I have been building systems on every tier of the web. Everything from low-level OS optimizations, mySQL internals, interpreted language performance tricks to static content optimization.

Building CDN's are easy, but what makes Akami or Cloudfront attractive-presences known as edge nodes-they have around the world to syndicate your content closest to the requester.

Their smart DNS servers send people to the closest edge node to serve content. This is great for serving Javascript, Images CSS (Video) because its static.

Here is a good example. Your system can serve content in less then 5ms if the network is not involved. With the network overhead that content is served in 10ms time if you are close to the DC (say about 1200 miles). Yet your users on the east coast (assuming your dc is in the west coast) or better yet your users in Europe see this content in 355 ms. Around 300 ms or so users start to notice the lag; as a result this lag is proportional to increase in chances that the user will bounce. People hate waiting. Now do you optimize the backend to serve the content faster or do you put the content closer to the end user?

Put the content closer to the end user to reduce the 350ms back down to 10ms. This is what cloudfront-an amazon product does for you. Here is a good wiki page to setup cloudfront. I've expanded on this to add some Apache Mod Rewrite rules to automate cache invalidation so I don't have to call an API to purge the CDN cache. Below is the mod rewrite rule

RewriteRule ^/static/(\d+)/(.*)? /static/$2 [NC,QSA,L]

RewriteRule ^/static/(\w+)/(\d+)/(.*)? /static/$1/$3 [NC,QSA,L]

RewriteRule ^/static/(\w+)/(\S+)/(\d+)/(.*)? /static/$1/$2/$4 [NC,QSA,L]

what this says is

given a url

http://domain/static/12345/main.js

serve from

http://domain/static/main.js

The dynamic url is generated by taking the abs crc32 of the file contents of each file. So if a file changes on disk so does the url breaking the cache and forcing cloudfront to refetch the content to display to the user. All this is calculated once during the deploy process.

For instance

http://d1wuzpn2rb4qzi.cloudfront.net/static/jquery/240184024/jquery.min.js

http://d1wuzpn2rb4qzi.cloudfront.net/static/jquery maps to http://your.schoolfeed.com/static/jquery

240184024 - is the cache breaker by doing this during the deploy process

$hashes[$file] = abs(crc32(file_get_contents($file)));

then generating php code that is global to the templates

$str = "";

Now when building the reference link

script type="text/javascript" src="{cloudfront file='/static/jquery/jquery.min.js'}"

{cloudfront} is a smarty function that takes the input file and splits the directory putting 240184024

into the path for the cloudfront url

So what has this done on the system.

Around the 5pm hour we see a drop in www_accesses, that's due to switching to Cloudfront. There is nearly 60% in savings. schoolFeed has a lot of javascript files. Additionally there is still another optimization that can be done to group javascript files together to reduce the amount of GETS.

Here we see a 35% drop in bytes out as a result of the change.

And the affect on mySQL is about 3-5% more traffic on the backend as users stick around longer since things are snapper.

Always keep this in mind. As one tier becomes faster or more performant the other tiers should have the capacity to keep up with demand.

Using live code interrupts to produce stats which in turn improves code

2011-11-22T12:55:00.001-08:00

How do you know that your code is fast? Is it fast for your test cases or is it fast for every case? When changes are made how does that affect your customers? How do you know over a period of time if the system is faster or slower.

The same stat system which is used to track new installs, viral clicks, impressions, rates, funnels, page flows, gauges, counters, etc is also used to let me know how fast code blocks are performing. How is this done?

Since a Front Controller Design Pattern is used for my AJAX calls, I am able to wrap all calls in time deltas to produce a centralized stat on how fast all service calls are taking. So, each code change that is pushed; I can see if that code change slowed something down, or broke something all together.

Here is the setup. Each Service Call Response Time is placed into buckets; less than 200ms, 201ms to 500ms, 501ms to 1 second, 1.001 second to 2 seconds and greater then 2 seconds. Anything over 500 ms is bad.

On the 13th of November I see that service calls for the 2 second+ bucket are on the rise. Now the next step is to look at independent data sources to determine is this a system issue or a code issue. This is the tricky part because code issues can cause system issues. I use ganglia to separate out the system stats from code interrupt stats and interpret the results.

Now the System stats I look at to help me drill down to the issues are things that encapsulate the problem that I am investigating. For instance, service calls are a product of CPU and Memory usage from the www teir. These system calls use network resources that talk to databases, memcache and queues. Thus I ask myself what are some high level system metrics to help me figure out if there is a system issue? Well apache has a lot of high level stats like busy workers and requests per second. Busy workers are a product of memory, cpu and network resources. This is a good stat.

Here busy workers are seen to be increasing but not all that much. What is the slow down? Let's look at another ganglia stat - Requests per Second.

Wow the request per second is going up, Busy (above) is growing slightly; with hiccups - thus what I need to look at is the data tier since the wwws are scaling well (Higher request rates with the same www busy rate).

Unfortunately the graphs for that are plagued with huge spikes caused by long term issues in RRD, so they are not show-able. A long story short, an increase in requests are from a sudden spike of new users and returning users; causing more concurrency on the backend exposing memcache evictions because of more active users. Thus I added more memcache servers and scheduled a rebalance of database data onto new servers; this should lower overall the service times on average without code changes.

In summary, realtime code insights are just as valuable as knowing the number of installs or clicks from emails. You can do all sort of stuff like draw custom dots for code deployments that correlate with response times. Having this data is invaluable to keep your site fast at very little cost while giving you knowledge of the entire system.

Handling the Hockey Stick Growth

2011-10-25T12:38:00.000-07:00

The term hockey stick is used to describe the effect of an app that suddenly goes viral. Take a look at the graph to the left. There is modest growth and suddenly, the app goes viral and takes off. It looks like a hockey stick, ala the term.

This article is briefly going to touch on the points of how to handle the sudden growth at the lowest cost possible with a site that I helped build: schoolFeed.com-a social network that reconnects classmates for Free.

The main features of schoolfeed.com is reconnecting classmates, ensuring that each classmate is well connected, a feed to keep classmates in touch with one another and interests that each classmate share. Additionally there is a photo experience to share with your online yearbook and more features to come.

To handle the growth, enable rapid feature development, keep the site up without waking me up, and keeping it cheap means a set of structure needs to be put in place.

Suggestion #1: Keep the system architecture simple

The architecture consists of PHP on the front end, Memcache to front database queries, a database on the backend, a queue service-Gearman to handle offline processing in parallel; finally sendgrid to handle mail.

Suggestion #2: Keep the development environment simple

The development environment did not start off too abstracted. A simple MVC model is used where the Model fronts the PDO database objects structure. The Controller is the service layer which is a Front Controller design pattern, and the php entry points to handle the model inputs. The View is in smarty because keeping the presentation layer separate from the business logic is pivotal. Additionally this View is separated enough to replace smarty and or internationalize the strings in the future. Also JQUERY is used to make life so much simpler when supporting IE.

Suggestion #3: Monitor everything

I use Nagios for alerting (Icinga), Ganglia for Trending, and a custom stat system backed by mySQL for reporting on code interrupts, click through rates, feature adoption, K-Factor, DAU per feature, MAU, WAU, Facebook Platform Health, site response time, site api response time, email send rate.

Suggestion #4: Design every layer to be distributed.

If I run out of apache threads, I add more www servers. If my memcache eviction rate is to high, I add more memcache servers. If I need more database transactions per second I add more database servers and each layer is controlled from a config file enabling rapid deployment of servers to handle spikes in traffic. Since the database connection logic is controlled by the application, I drop a definition in an array and new traffic starts hitting a new database server. If the existing database server is loaded to much and I need to move data off of it. I take a xtrabackup of the server replicate it to a new server, set the pointer for a % of that traffic to the new server and clear up the old data on the original server. Or I can migrate individual entities. An entity is a user/school/interest/url/facebook id/etc.

Suggestion #5: Don't optimize to soon.

The goal is to make each feature super fast, but building a super abstracted layer to support 1000s of devs is only necessary when you have 10s of devs :). Please don't interprete this as me advocating being sloppy-I'm saying its cool to allow your team to interact with SQL and write their own :). Additionally building custom servers to handle specific tasks, changing languages to get a specific feature is really not necessary in the beginning. Supporting the product and ensuring the features do not take more then 200ms to generate or weeks to build said feature. This should be the focus to enable the hockey stick. In the early stage of a hockey stick; technology rarely is the cause for the growth-its building what your users may want and when your wrong throw that stuff away and actually build what they want. A helpful tool is to build features in a way where the feature or parts of the feature can be turned it off with a config change. This will save you a ton of headache without having to take the site down, while enabling pushing code out quickly and watching to see if its adopting prior to optimizing.

Suggestion #6: Plan for things to break and set up procedures to handle outages

Things will break. The goal is to hide this fact from users or inconvenience them as little as possible. Schedule maintenance windows to fix the heavy stuff. Have a playbook to handle outages, if the play does not exist-write the play down. Finally automate common tasks. Remember if you don't want any user experiencing an outage-that costs a lot of money. Redundancy is expensive. Multiple Redundancy in multiple datacenters is even more expensive.

I hope these steps help you in your projects in the future. I have had the pleasure of handling multiple hockey sticks and following a basic rule/suggestion set has helped me each time. The end goal really is to give a great experience for your users, build a clean environment for your devs with your devs input and improve the product rapidly.

Some stats: 3-5 web servers, 2 job boxes 2 database servers we are able to handle well over 100K DAU.

Facebook should launch FBCloud and compete directly with Amazon, Google and others

2011-10-20T18:17:00.000-07:00

AnotherFacebook should do post by someone outside of Facebook; but it’s a moneymakerthat Facebook has not tried and probably has the best chance of succeeding at(not like deals har har - jab, jab). Some of the best DEV Ops work at Facebook.Facebook knows scale. Facebook knows system management. Facebook built the mostadvance data-center on the planet. This stuff is great it shows that they can doit, but what’s the motivation for the app developer to deploy in a FacebookCloud? Simply put Latency. This is the real issue, for me, really a selfish desire tohave my app move as fast as Facebook's Apps while using Facebook Data; a seamless integration if you will. For Facebook, it’s good because they can help me make my users happier whilemaking tons of Cash. If my app is in the same data-center as the center of data,my app can move faster thus giving my users a better experience.

Hereis an example. When doing a graph call for Facebook friends, the backendsystems can do it in ms time yet the JSON reaches the caller in the 100ms timeframe over the WAN from my servers in EC2-west1c to Facebook Servers in Oregon.If I'm in the data-center that holds the data (Oregon) my app speedsup 10 times, since that 100ms R(t) turns to 5-10ms.

AdditionallyFacebook houses some of the most advance tech that lots of people around theweb use. Such as MEMCACHE. Facebook could manage that for you. In fact theyhave PETA BYTES of memory for their own app with automatic key management between DCs (wow). Offer Facebook Hosted MySQL with Flash Cache forHigh density IOPS. Each Facebook DB server has Solid State Disks, use that tobuffer IOPS for subscribed developers to FBCloud. With their tools to automatically migrate datato another server, building new Instances would be a snap without having to usea SAN. Although you could use SSDs to buffer SAN writes/reads for easier management with great R(t).Facebook stats on HBASE, Facebook Varnish, a fast CDN-everything that they do ascommercial product. I've seen their tools, its better then enterprise qualityand nearly all of them have an API. Facebook Culture is platform focused. Iassume if you don't build an API for your tool your mocked.

Howwould Facebook make money? Charge on CPU resources just like Amazon. Charge onIOPS, charge on managed Memcache size, charge on Data size. Charge for BCP.With this adding to Facebook's platform, Facebook could make money on the FrontEnd from Ads, on currency and finally on the API indirectly while giving theEnd User an entire platform guaranteed to be fast and redundant in multipledata-centers.

Ihave an entire vision that would make a ton of cash, but really would make myusers and me as a developer happier.

PS This post went out to fast, with grammar and spelling mistakes. Should be fixed now, my apologies.

Stump the Murph: ulimit, pam and linux

2011-09-21T10:58:00.000-07:00

There is a game that a small group of friends and I have been playing since my Friendster years. It's called Stump the Murph. Basically if there is some weird problem in Linux mainly but it's in a variety of subjects-that we can't figure out we pass it to one of our friends Kevin Murphy. In 8 years I believe I stumped him once but I can't remember what it is so it doesn't count.

Here is the problem

SQLSTATE[HY000] [1135] Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

"Obviously" this means you need to raise the ulimit for the process running mysql. I say "obviously" because this error means different things. In most cases it means that the server ran out of memory. perror 11 says OS error code 11: Resource temporarily unavailable, yet when there is enough memory there may be a pam_limit issue. In my case there is.

So I did the following

in /etc/security/limits.conf I added this

mysql   soft    nofile  10240
mysql   hard    nofile  1537454
mysql   soft    nproc   32768
mysql   hard    nproc   65535

yet when I test the changes su - mysql
I get

su: pam_limits(su-l:session): Could not set limit for 'nofile': Operation not permitted

So my next course of action is to check

/etc/pam.d/system-auth

wait a second it has

session required pam_limits.so

and

/etc/pam.d/su calls

session         include         system-auth

thus I don't need to addsession required pam_limits.so

Now the game of Stump the Murph begins:

In about 1/2 hour Murph figured out the solution! He deduced that since

cat /proc/sys/fs/file-max
1537454

you can't set the hard limit of nofile to 1537454 because in theory you could starve the kernel from file descriptorsthus from murph's suggestion I did

mysql      soft    nofile  10240

mysql      hard    nofile  768727
mysql      soft    nproc   32768
mysql      hard    nproc   65535

Thanks Murph!

Amazon EBS mySQL, Disk Throughput and the Dual Edge of Software Raid

2011-09-15T10:19:00.000-07:00

Amazon's EBS system is just a nice interface to a SAN subsystem, which manages the attachments of SAN LUNs. The problem with SAN when compared to Local SAS drives is latency and the shared controller, which caches IOPS for very distinct load profiles. Each load profile has an "optimized" cache profile from the SAN's redundant controller system. You may be able to attach petabytes of disks, but this system cannot utilize the true throughput when compared to small locally attached SAS Drives. Now the management portion of awesome. I love having the ability to mount more disk but I rarely need more space, I need speed.

How to get Speed out of Amazon's EBS volumes: Software RAID it!

mdadm --create /dev/md1 -v --raid-devices=8 --chunk=256 --level=raid10 /dev/xvdk /dev/xvdl /dev/xvdm /dev/xvdn /dev/xvdo /dev/xvdp /dev/xvdq /dev/xvdr

Take 8 EBS 125 GB volumes create a raid10 array with a 256KB chunk size. After various and mind numbing benchmarks I found that 256K is a good sweet spot. Feel free to do your own benches. The results have to be interpreted because of the nature of using a shared resource.

What I end up with is a 500GB partition, and I am roughly able to get around 22-25 MB of second of random I/O from 20 threads. To compare this to an 8 DISK 15K RPM PERC-6 2.5" SAS system I am able to get around 44 MB of second at a constant 1-2 ms response time for the same physical space. EBS volumes Response time per iop range from 6ms to 200ms. This sucks. Note: these numbers are based on RANDOM I/O 16KB Page size (4 iops per block write), what INNODB uses not sequential I/O.

Here is some iostat numbers from a live box with this configuration

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.83    0.00    1.75   22.32    0.08   74.01

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
xvdap1            0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
xvdh              0.00     0.00    0.00    1.00     0.00     8.00     8.00     0.00    0.00   0.00   0.00
xvdk              0.00     0.00   34.40   26.40  1100.80  1503.20    42.83     0.49    8.01   6.39  38.88
xvdl              0.00     0.00   13.20   26.40   422.40  1503.20    48.63     0.27    6.71   4.38  17.36
xvdm              0.00     0.20   32.40   27.00  1036.80  1524.20    43.11     0.30    5.13   4.19  24.88
xvdn              0.00     0.20    9.40   27.00   300.80  1524.20    50.14     0.15    4.11   2.48   9.04
xvdo              0.00     0.00   30.20   27.40   968.00  1496.80    42.79     0.45    7.76   6.56  37.76
xvdp              0.00     0.00   14.60   27.40   478.40  1496.80    47.03     0.22    5.26   3.92  16.48
xvdq              0.00     0.00   31.20   25.60   998.40  1501.60    44.01     0.38    6.73   5.32  30.24
xvdr              0.00     0.00    9.80   25.60   313.60  1501.60    51.28     0.16    4.50   2.35   8.32
md1               0.00     0.00  174.80   98.60  5606.40  6009.80    42.49     0.00    0.00   0.00   0.00

So, now that I have acceptable speed what is the drawback? A weekly cron job that runs a check across the raid array. On Amazon’s EBS system it cuts my throughput in 1/2

For my Amazon Linux system the cron job is located

-rwxr-xr-x 1 root root 2770 Jan 16  2011 /etc/cron.weekly/99-raid-check

It essentially runs

echo check > /sys/block/md1/md/sync_action

Yet, the check lasts for around 9000 min or 6.25 days! Thus I will only have .75 days of full throughput.

So to stop this I must run
echo idle > /sys/block/md1/md/sync_action

I do not recommend turning off the check, its needed. Now to find out a way to make this check happen faster.

Virtual Currency and Tracking What is Given Where

2011-09-07T18:47:00.000-07:00

VirtualCurrency is currency, which is used inside games, websites, applications, etc. Itcan be bought and traded and in many cases has a conversion to real currency.For instance Facebook Credits, this is virtual currency and each credit isworth 10 cents.

Acommon problem I run into when building Currency Sinks or earns is; what partof the application gave out the currency and why? Some techniques I used tomanage this problem are to create a constants file of a bunch of action ids andused the constants wherever the application writes updates to the balance.

Thiscauses a problem. Developers will just reuse the same id for very distinctreasons. When debugging situations I have to trace through a bunch of code.

Additionallyproblems can arise on deploy, because the use of currency change maybe pushedout yet the constants file is not. This causes data issues and or evenpreventable exceptions disrupting the customer experience. The laterpossibility is much more offensive to me personally then data corruption. I goabove and beyond to prevent any negative views a customer may have. My workshouldn't piss anyone off it should enable them.

I'vetackled the problem by manually managing where the currency was deducted from,but this is the last time. If you do the same thing more then once and it caneasily be solved by automation, automate it to make your life easier -- mymotto.

Howam I tackling the problem now?

Bydefault I am adding the calling Objects class and method to my CurrencyHistoryTable with each Currency Transaction and top app name. Now on the row basis Ican track what class and method changed the balance and where it was called. Belowis a table structure used to track currency and history of the currency balancechange.

--
-- Holds the Balance of a users currency
--
DROP TABLE IF EXISTS Currency;
CREATE TABLE `Currency` (
  `userId` bigint unsigned NOT NULL,
  `regular` int NOT NULL,
  `premium` int NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB;

--
-- Holds the info on How the balance is generated
--
DROP TABLE IF EXISTS CurrencyHistory;
CREATE TABLE CurrencyHistory (
    transactionId BIGINT UNSIGNED NOT NULL COMMENT "Globally unique Id"
    userId BIGINT UNSIGNED NOT NULL COMMENT "User Identifier",
    itemId bigint unsigned NOT NULL DEFAULT 0 "Unique Identifier on what was bought or sold",
    itemOwnerId bigint unsigned NOT NULL DEFAULT 0 "User that was the last owner",
    regularAmount INT NOT NULL "How much was earned or sold",
    premiumAmount INT NOT NULL "How much was earned or sold for premium currency",
    createDate TIMESTAMP NOT NULL "When this event occurred",
    callStackId DEFAULT NULL "What part of the code base contains the code that changed currency the lookup table is generated on the fly from the json string and stored offline"
    PRIMARY KEY(userId, createDate, transactionId)
) Engine=InnoDB;

Now to modify Currency here is some PHP code:


  /**
    * Update user's balance
    * 
    * @param int $userId
    * @param int $regularDelta
    * @param int $premiumDelta
    * @return bool
    */
    public function CurrencyDB_updateBalance($userId, $regularDelta = 0, $premiumDelta = 0,  $itemId = 0, $itemOwnerId = 0) {

        $this->validate->id($userId);
        $this->validate->int($regularDelta, $premiumDelta);
        if( $regularDelta == 0 && $premiumDelta == 0 ) {
            return 0;
        }

        $this->shard($userId)->beginTransaction('updateBalance');
        try {
          
            if( $regularDelta < 0 || $premiumDelta < 0 ) { 
                $row = $this->shard($userId)->selectRow("SELECT * FROM Currency WHERE userId=? FOR UPDATE", array($userId));
                if( $row->regular < -$regularDelta || $row->premium < -$premiumDelta ) {
                    throw new CurrencyDBException("Insufficient funds", CurrencyDBException::INSUFFICIENT_FUNDS);
                }
            }
            
            // modify the balance
            $query = "INSERT INTO Currency(userId, regular, premium) VALUES(?, ?, ?) ON DUPLICATE KEY UPDATE regular=regular+VALUES(regular), premium=premium+VALUES(premium)";
            $this->shard($userId)->update($query, array($userId, $regularDelta, $premiumDelta), $this->cacheKey($userId));
            
            // get the GUID and record the details of the transaction
            $transactionId = $this->getTicket();
            $query = "INSERT INTO CurrencyHistory(transactionId, userId, itemId, itemOwnerId, regularAmount, premiumAmount, createDate, fromWhere) VALUES(?, ?, ?, ?, ?, ?, NOW(), ?)";
            $callStackId = $this->getCallStackId(); // get the backtrace as an ID
            $this->shard($userId)->update($query, array($transactionId, $userId, $itemId, $itemOwnerId, $regularDelta, $premiumDelta, $callStackId));
            
            $this->shard($userId)->commit('updateBalance');

        } catch (Exception $e) {

            $this->shard($userId)->rollback('updateBalance');
            throw $e;

        }
        return 1;
    }

 /**
    * Return an Id for the code stack
    * 
    * @return bigint
    */
   private function getCallStackId(){
        if ($GLOBALS['cfg']['disable_feature_backtrace_sql'] != 1){
        
            $trace      = debug_backtrace();
            $notdone    = 1;
            $numLoops   = 0;
            $maxLoops   = 10; // hope its not nested 10 levels deep
        
            while($numLoops++ < $maxLoops){
            
                $test = (strpos($trace[0]['class'], 'CurrencyDB') === FALSE ? 0 : 1);

                if (!$test){
                    break;
                }
                array_shift($trace);
            }
        

            $loc = array();
            
            
            $loc[] = substr(str_replace("/var/www/html","",$_SERVER['PHP_SELF']), 1);
            $loc[] = ($trace[0]['class'] ? $trace[0]['class'] : 'main');
            $loc[] = $trace[0]['function'] ? $trace[0]['function'].'()' : 'called_from_script';
            return $this->getCodeLookupEntityId(json_encode($loc));
                
        }
        
        return 0;
 }


mysql> select * from CodeLookup;
+----------------------+------------------------------------------------------------------------+
| entityId             | json                                                                   |
+----------------------+------------------------------------------------------------------------+
| 17113479085265723743 | ["test\/dathan\/test_CheckBonus.php","CheckBonus","CheckBonus_give()"] |
+----------------------+------------------------------------------------------------------------+

Replacing sendmail and /var/spool/mail/mqueue with Gearman

2011-08-31T14:45:00.000-07:00

I am obsessed with making things faster without spending an extraordinary amount of time reconstructing the wheel. I ran into an issue with sending mail-that I couldn't work around with a pure sendmail configuration. I needed to write the body of mail and have it queue, then sendmail send it. From the way sendmail works I couldn't force mail to queue then send without doing ugly hacks like blocking outbound port 25 connections. I tried all the common tweaks to make sendmail send faster, such as

disabling sendmail from using dns
Using options such as FEATURE(`accept_unresolvable_domains') and FEATURE(`nocanonify')

but none worked. In fact I was generating so much traffic that sendmail would refuse to accept anymore commands. Thus I lost emails. Let me describe some background of the setup and the growth rate to explain the problem in more detail.Sendmail is configured to dump all mail to sendgrid, a Cloud provider that handles your email relationship for you. Thus there is no need to reverse dns or do any checks since Sendgrid does it for me. All I need is to dump the mail as quickly as possible to SendGrid.

I found that sendmail is not fast enough to dump mail to SendGrid. I could of used other transfer agents but I don't want to configure more things.The environment is very simple. PHP talks to localhost port 25 to dump the generated email to sendmail. Sendmail will try to connect to sendgrid, if so, block the client (Apache) until the mail is sent. The average send time is 1 second, thus the apache server blocks for 1 second per email sent. Blocking is bad especially with this case, since a social application could contact 1000s of users from a logged-in user's action. Let's imagine that one user generated event can contact 5000 users. If each email takes 1 second that takes 5000 seconds for that process to finish.Now that the problem is defined, lets find a good solution which can be handed off.

I picked Gearman. I now build the email and write to a gearman queue. Around 30 workers are listening to the queue and will dump the mail directly to smtp.sendgrid.net via the PHP module Swift. What was gained in doing this?

Contacting 5000 email recipients takes 200ms since I am writing to memory
Apache does not block for to long (220ms).
I do not use localdisk since sendmail is bypassed. /var/spool/mail/mqueue is not used
I save on diskio and cpu cycles
I can now send at a higher rate with hardly any errors.
I can scale with more workers

Yet with all these gains I am exposed to loosing email at this moment in time. If sendgrid goes down and the queue builds up-I can run out of memory. There are ways to work around this such as setting up persistent storage with Gearman, so this exposure is mitigable.

This image shows how large my gearman queue gets. Its largest size is 400 at any given time, for 30 workers. As a result I am sending 300K emails a day and the email event is no more then 1 min old from when it was created.

Automated Innodb Hot Backup to S3 from ec2 with a simple bash script, innobackup and jets3t

2011-07-29T14:23:00.000-07:00

Backing up data is always necessary, especially for Disaster Recovery and Business Continuity Planning (BCP). One rule of thumb for me is let the computer do the work by automating repetitive tasks. Running things by hand over and over sucks, so if I do something more then once I typically automate the process. Backing up INNODB data is a good example of a solution that requires automation.

I always start off with what are the requirements and limitations when deciding to automate something. My requirement was simple. Every night at 2am backup the database and upload it to s3 without downtime. The limitation is to avoid downtime, I must keep in mind that the database cannot go down and the tables cannot be locked. Thus I use xtradbbackup provided by Percona.

The second part of the requirement is to upload the data to s3 automatically. This is not hard, but there is a limitation. Uploading files > 5GB is not possible, and typically anything over 200MB has an increased error rate on average. Good news is the guys that make JetS3t, a java based uploader tool, solved this for me.

JetS3t works on NIX and Windows, there is a shell and bat script for your OS flavor. It supports huge file uploads via the amazon multipart upload api, and can upload data in parallel.

Ideally I would stream a backup directly to S3 all in one step, yet I don't have the time to code that. Maybe in the future. So the next best thing is to do it in roughly two steps. Below is a BASH script that backups the mysql database, uploads the backup to S3.

#!/bin/bash

INNOBACKUP="/usr/bin/innobackupex"
INNOBACKUP_OPTIONS="--parallel=4 --user=backup --password=****"
BACKUPDIR="/sqldata/backups"

S3BUCKET="dbprodbackups"
JETS3="/usr/local/jets3t/bin/synchronize.sh UP $S3BUCKET $BACKUPDIR"

echo "Removing old local backups"
cd $BACKUPDIR
find . -type d -name "." -prune -o -type d -atime +3 -exec rm -rf {} \; -print

echo "Starting INNODBACKUP:"

echo "$INNOBACKUP $INNOBACKUP_OPTIONS $BACKUPDIR" 
$INNOBACKUP $INNOBACKUP_OPTIONS $BACKUPDIR 

echo "Sleeping for a 1 min 10 seconds"
sleep 70

S3_DIR_UPLOAD=`find . -maxdepth 1 -type d -cmin +1 -print |grep ./ |cut -d. -f2 |cut -d/ -f2|xargs`

echo "Dir to Synchronize are $S3_DIR_UPLOAD"

for dir in $S3_DIR_UPLOAD; do 
   echo "Executing $JETS3/$dir"
   $JETS3/$dir
done

Configuring JetS3t is simple, its a matter of modifying a properties file.

The jets3t directory (my case /usr/local/jets3t/) a directory config contains all the configs. In synchronize.properties define your accesskey and secretkey for your s3 account.

Line 8 says, synchronize the current folder and push it to s3 bucket dbprodbackups. JetS3t handles region automatically (Amazon tools do not).

Running the script you'll see something like this for the innodb backup

[02] Copying ./ShardLookup/MainLookup.ibd
to /sqldata/backups/2011-07-29_20-13-38/ShardLookup/MainLookup.ibd
>> log scanned up to (99395152227)
>> log scanned up to (99395249621)
>> log scanned up to (99395366299)
>> log scanned up to (99395373016)
>> log scanned up to (99395382850)

and something like this for the jets3t upload

...

N 2011-07-29_20-13-38/xtrabackup_binary
N 2011-07-29_20-13-38/xtrabackup_binlog_info
N 2011-07-29_20-13-38/xtrabackup_checkpoints
N 2011-07-29_20-13-38/xtrabackup_logfile
Large upload parts: 32/43 - 76% of 38.88 GB (15.89 MB/s - ETA: 8 1/2 minutes)

In about 1 hour I am able to backup 40GB and upload it to s3.

What a web start-up engineering team should set-up pre-launch.

2011-07-19T21:49:00.000-07:00

When asked this question a flurry of thoughts came to me and I stuttered to get them out. Here is my list and the reasons why. Note the basics of my reasoning comes from the desire not to wake up at night to fix a bug.

Agree to a base language:

Syntactic sugar should be agreed upon. Building things in different languages at the start without the teams acknowledgement is just building a walled garden.

Agree to use the same code format:

The team needs to agree to make the code look the same. Make a decision on tabs over spaces, DOS or UNIX format, class / directory layout, MVC or procedural etc.

Agree to a source code repo:

Revisions are good, use source control

Agree to a bug tracking system:

Don't just use email or spreadsheets use a free open source bug database and make an interface for users to add bugs. If a user is frustrated enough to write a bug, the least an engineer can do is take the time to read their problems.

Agree on what to launch with as your base architecture. (Doesn't mean it can't change)

How is the data stored? How is it accessed? What is the payload size? Where is the data coming from? What is the latency? How can I monitor it? When does it break?
These are some sample questions that each team member can answer (at least a majority of them).

Build tools to push code via one or two clicks.

Build general tools about your users, your data, management for uploading to CDN, packing JS etc.

Build a stat system to record exceptions, page load times, stats from apache, stats from the database or system in general and if you are uber make it the same stat system used for Business Intelligence.

The same system that tells me how many unique returning users also reports what the mysql system resources are.

Stats are crucial for any organization, know when and why (realtime) when a user experiences a problem as well as when a new user joins.

Have fun, learn and be productive for your users sake :)

What else would you add?

AMQP PHP for RabbitMQ to talk to Node.js

2011-06-15T16:22:00.000-07:00

There are really no examples on how to use PHP's AMQP PECL module, yet the documentation at php.net is still great and easy to understand. So, after reading the documentation in 5 mins I was able to put together a PHP Producer and reuse my Node.js Consumer.

Here is an example starting with PHP


//
// connect
//
$cnn = new AMQPConnection();
$cnn->connect();

//
// exchanges talk to queues set a fanout up
// 
$ex = new AMQPExchange($cnn);
$ex->declare('notifExchange2', AMQP_EX_TYPE_FANOUT, AMQP_AUTODELETE);

//
// add stuff to a queue for me to look at later (not needed)
//
$queue = new AMQPQueue( $cnn );
$queue->declare('myqueue');
$queue->bind('notifExchange2', 'routingkey');

//
// pure strings with no format are boring let's use json
//
$data =  array('message' => "helloworld");
$str = json_encode($data);

$ex->publish($str, 'routingKeyWhichIgnoredBecauseOfFanout', 0, array('Content-type' => 'text/json'));

Now for Node.js

#!/bin/env node

require("./amqp.connection");
require("./utilities");


connection.addListener('ready', function () {
   
    var e = connection.exchange('notifExchange2', {type: 'fanout'});
    var q = connection.queue('100224', {autoDelete: false});

    q.on('queueDeclareOk', function (args) {
        puts('queue opened: Message Count:'+ args.messageCount + " ConsumerCount: " + args.consumerCount);
        q.bind(e, "#");
        q.subscribe(function(message) {
            puts("Got a message: " + var_dump(message));
        });
    });
});

Pretty straight forward. When the connection is ready setup the exchange, declare the queue, once the queue is declared bind it to the exchange and on q.subscribe var_dump the message out. Notice var_dump in javscript that's defined in utilities.

Easy-peasy.

Building a realtime Feed with RabbitMQ Node.js AMQP and mySQL

2011-06-14T20:09:00.000-07:00

Being a backend programmer, I rarely code in JavaScript. Today I code a lot of cross browser JavaScript with the fantastic lib jquery and its various extensions. Front end programming is hard to get correct since IE sucks but that's another Blog post that is out of scope. I certainly have more of an appreciation for the people that focus in this area. I can finally say I am really well rounded in doing CSS, JavaScript, Server tuning, DBA, PHP, C etc since I work the entire stack. Now for the setup and disclaimer. This post is not meant to provide a How-To but to explain the approach. If you would like a How To let me know on what.

With Node.js the gap between the discipline of Front-End programming and Backend Programming is narrowing. To code in Node you need to know JavaScript. Node is great at handing off data and pumping data to the requester in an event based model that is fantastically fast and small. I love node, especially since node has access to the browser cookie.

My use of node is very simple.

Browser makes a long polling jsonp connection (json with padding basically the server can call code loaded on your browser) to Node.js.

Node.js will send back any data that is waiting for the client, or is sent to the client during the connection lifetime, which recycles every 50 seconds. Node source of data is from RabbitMQ.

RabbitMQ an erlang server which talks the AMQP protocol (shared by many message queue servers) is a message queue that can be made into a distributed fault tolerant system and hands off the message payloads to Node.js which sends the message to the users who are online and subscribe to an exchange. The source of data for RabbitMQ comes from the database commit in Apache application space (PHP).

MySQL is the persistent store that handles page reload or init requests, while the previous two components enable the real-time feed.

Memcache holds a persons friend list.

Notice Flash is not used since I wanted a pure JavaScript version and reduce the amount of download needed to service the request. Additionally web sockets are not finalized yet and does not work across all browsers while long polling do, Socket.io is cool but requires flash as well.

So the ascii diagram shows this flow:

Browser --> Apache+PHP --> DB
if DB commit ok
Apache+PHP-->RabbitMQ-->Node.js-->Friends of Browser

Since Node is a gateway lets focus more on RabbitMQ which handles the logic. I'm using RabbitMQ in a pub-sub model. To understand Rabbit knowing its vocabulary is important.

Producer: Produces the message
Exchange: The bridge between Producer and the Queue(s). Note the producer has no idea what the queues are in concept, all it needs to know is the exchange will take the message from the producer and send it to the correct queue(s).
Queue: On disk queue or in memory (distributed) queue based on durability settings holds the message from Producers
Consumer: Node.js it reads all events that Node subscribed to and hands it off to the connected user.

I wrote a benchmark in node to test the capability of this setup and its quite impressive: let's take a quick look:

require("./amqp.connection");

 connection.addListener('ready', function() {
   puts("connected to " + connection.serverProperties.product);
   var e = connection.exchange('[userid]_feedExchange', {type: 'fanout'});
   e.on('exchangeDeclareOk', function(data){
       e.publish('routingKeyWhichIgnoredBecauseOfFanout', {message: "This is the message payload"});

       setTimeout(function () {
          // wait one second to receive the message, then quit
          connection.end();
       }, 1000);
});

The require (line 1) sets up the connection: global.connection = amqp.createConnection(options);
Line 3 sets up a listener which fires off code when the connection is established.
Line 5 declares the exchange. If it doesn't exist create it, else keep going. Notice that the exchange is dynamically created from the userId, so queue binds can happen against this exchange
Line 6 listens for another event exchangeDeclareOK, if OK send the message which is on line 7
Line 9 sets a timeout to cleanly close the connection else you will get


net.js:392
    throw new Error('Socket is not writable');
          ^
Error: Socket is not writable
    at Connection._writeOut (net.js:392:11)
    at Connection.write (net.js:378:17)
    at Connection._sendMethod (/usr/local/node/lib/node_modules/amqp/amqp.js:1011:8)
    at Object.cb (/usr/local/node/lib/node_modules/amqp/amqp.js:1799:21)
    at Exchange._tasksFlush (/usr/local/node/lib/node_modules/amqp/amqp.js:1306:10)
    at Exchange._onMethod (/usr/local/node/lib/node_modules/amqp/amqp.js:1772:8)
    at Exchange._onChannelMethod (/usr/local/node/lib/node_modules/amqp/amqp.js:1338:14)
    at Connection._onMethod (/usr/local/node/lib/node_modules/amqp/amqp.js:900:28)
    at AMQPParser.onMethod (/usr/local/node/lib/node_modules/amqp/amqp.js:807:12)
    at AMQPParser._parseMethodFrame (/usr/local/node/lib/node_modules/amqp/amqp.js:454:10)

shell returned 1

Since this is a fanout approach, RabbitMQ is sending the message to each "User" that subscribed to the exchange.

Here is an example of a consumer in node:


#!/bin/env node

require("./amqp.connection");
require("./utilities");


connection.addListener('ready', function () {
        puts("connected to " + connection.serverProperties.product);
        var e = {};
        var q = {};
        var qNames = {};
        e = connection.exchange('[userid]_feedExchange', {type: 'fanout'});
        for(var i = 0; i < 1000; i++){
            var queueName = "_" + 100223 + i;
            q[queueName] = connection.queue(queueName, {autoDelete: true});
            var f = function(){
                var k=i; //variables are scoped by function and not brace so to get a copy of i we need to send the k at the time k is declared for the local lambda
                q[queueName].on('queueDeclareOk', function (args) {
                    q[args.queue].bind(e, "*");
                    puts(k+': queue opened: ' + args.queue + ' Message Count:'+ args.messageCount + " ConsumerCount: " + args.consumerCount);
                    q[args.queue].subscribe(function(json) {
                        console.log(k + ": " + json.message);
                    });
                });
            }();
        }
});

This consumer binds 1000 queues starting from 100223 to the [userid]_feedExchange in the producer section

On line 15 I create a hashmap of queues to access later when line 18 event queueDeclareOK is thrown.
Line 19 binds each queue to the exchange
Line 21 subscribes each queue to any message sent from the exchange (if we used routing we can listen to certain types of messages - another post).

Very simple, it is fast and works. On a single processor to send a message to 1000 consumers takes

real 0m0.301s
user 0m0.220s
sys 0m0.028s

using very little memory

==16049==
==16049== HEAP SUMMARY:
==16049== in use at exit: 1,275,122 bytes in 140 blocks
==16049== total heap usage: 3,798 allocs, 3,658 frees, 11,036,111 bytes allocated
==16049==
==16049== Searching for pointers to 140 not-freed blocks
==16049== Checked 505,492 bytes
==16049==
==16049== LEAK SUMMARY:
==16049== definitely lost: 1,600 bytes in 74 blocks
==16049== indirectly lost: 1,048,736 bytes in 31 blocks
==16049== possibly lost: 172,080 bytes in 3 blocks
==16049== still reachable: 52,706 bytes in 32 blocks
==16049== suppressed: 0 bytes in 0 blocks
==16049== Rerun with --leak-check=full to see details of leaked memory
==16049==
==16049== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 33 from 8)
--16049--
--16049-- used_suppression: 33 dl-hack3-cond-1
==16049==
==16049== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 33 from 8)

Installing GearmanD on Amazon's EC2 Linux AMI Small Instance

2011-05-16T19:01:00.001-07:00

Linux AMI is very close to Centos/RedHat but its Amazon's own distro. Here are some quick steps on installing Gearmand on your system. I am currently using it to distribute jobs across many instances, to run asynchronously or synchronously where the Apache CPU isn't blocked on long running procs, like fetching data from a website realtime, massaging the data and returning it to the browser or resizing images.

#
# put the stuff in /var/tmp
#

cd /var/tmp;

#
# get the source
#
wget http://launchpad.net/gearmand/trunk/0.20/+download/gearmand-0.20.tar.gz

#
# setup libs required for the source to complie
#

yum install -y libevent-devel.i386
yum install -y gcc-c++.i386
yum instal -y boost-devel.i386 // C++ libs
yum install -y libuuid-devel.i686 -- do not install the i386 version, it puts uuid.h inside /usr/include not /usr/include/uuid/
yum install -y memcached-devel.i686

#
# extract the source
#

tar xvzf gearmand-0.20.tar.gz

#
# configure / make / make install
#

cd gearmand-0.20
./configure --prefix=/usr
make && make test
make install

#
# add the user and run it
#
adduser gearmand
/usr/sbin/gearmand -u gearmand

#
# need a client
#

Now install PECL GearMan

#
# client is not stable, thus use beta
#

pecl install channel://pecl.php.net/gearman-0.7.0

you should now see

Build process completed successfully
Installing '/usr/lib/php/modules/gearman.so'
install ok: channel://pecl.php.net/gearman-0.7.0
configuration option "php_ini" is not set to php.ini location
You should add "extension=gearman.so" to php.ini

After following the directions of extension=gearman.so

php --info |grep gear
gearman
gearman support => enabled
libgearman version => 0.20

now restart apache

Tada now for some client code:

One thing that I hate having to do is restart services when pushing code. I just want it to work. So using a Wrapper/Bridge design pattern in conjunction with restartd (using supervised pyton kit is just not possible) My new code is ready as soon as it makes it to disk.

Here is how I did it:

Three classes

GearmanJobSubmitter.php

GearmanJobPerformer.php

GearmanJobGeneric.php

The performer delegates the job to GearmanJobGeneric and has a method called JobsWrapper(GearMan $job)

JobsWrapper by looking at the workload is able to determine which Job to call. If the file that contains the meat of the Job's mtime has changed jobs wrapper will throw an exception and ext, otherwise execute the job.

If the wrapper killed itself, restartd then sees that the worker is not running and starts it back up.

Currently I have GearmanD managing file uploads, stat logging, data collecting etc.

Building an email system on EC2 from top to bottom with SendGrid

2011-04-20T16:41:00.000-07:00

Email is a beast. Sending email is easy but getting it pass spam filters when you’re a legitimate service is rather hard. Sending good email is especially hard when on EC2. The reason is due to spammers using and abusing elastic IPS. So, for startups your best bet of sending out a lot of email and getting it to the user is to use a service. I picked Sendgrid. It's cheap, fast, has good email tracking and builds all the appropriate email headers to get the mail white listed and into the destinations inbox. Sendgrid is the sender (think of them as an extension to sendmail). This is the easy part but to make a true email system that protects your users you need to take some things into consideration.

All HTML mail needs a Text counter part. Some people just like mutt or pine over HTML email. Thus when sending email send out mail in HTML format and Text format with mime headers so what ever email client is used can see a good formatted email.

All HTML links should be encrypted and encoded when passing identifying information. This needs to be done to make sure that the person that the link is intended for is clicked by that person. For instance

http://www.example.com/?enc=Ujcrq3uW8oU%2BpkW8bPJirwfczkMBnaWMObHlzCK8taau9PAjEQhCIZToj302zjVRs2f61bt7dddT7v21kpbhw6ZR5B1%2BoBIZNAznoLceK7z%2B%2BBm%2FS7%2FHKx0zfYah2Du%2FdaxP9dGel67SyQBp9ZJurXomrkyqkeLJiPioKMCaoygHruI%2FcJ83DvmOBNhqOjNQLyVMIHdjEWx3yYTMTsSZRUDdNPdaBfuTD3InspKINsQBBON0fPe890l3%2Bpb6p%2F4GtA%3D%3D&utm_source=sendgrid.com&utm_medium=email&utm_campaign=website

Now I can track retention and since the enc value is encrypted using AES-256 people are not going to break this encoding with out the Private key. Personally I am using this data for two purposes. The primary purpose is to ensure that the click comes from the intended person; the next purpose is to pass data around for what the app needs to fetch.

An example. XYZ commented on your status update. Click here to see the comment. When the person clicks I need to pull that specific activity to generate the message. Thus the link allows for that with no storage overhead. Here is some example code


public function encrypt( $data, $forUserId='' ){
        # 
        # open cipher module (do not change cipher/mode)
        #
        $this->openCipherModule();
        $this->createIV();
        $this->setUserKey($forUserId);


        $msg = json_encode($data);

        $this->init();

        $encoded = $this->doEncryption($data);

        $this->closeEncryption();

        return $encoded;
}

Now that I have sending down, links down, we need to put it all together. I am using sendmail as my mail transfer agent (MTA) and here is what is needed on EC2 to get it to work.

yum install sendmail

yum install sendmail-cf

vim /etc/mail/sendmail.cf and add define(`SMART_HOST', `smtp.sendgrid.net')dnl *says send all localmail to sendgrid*

vim /etc/mail/access and add AuthInfo:smtp.sendgrid.net "U:sendgrid username" "P:sendgrid pass for your account" "M:PLAIN" *when sending mail through sendgrid use your sendgrid account info*

m4 /path/to/m4.cf /etc/mail/sendmail.mc > /etc/mail/sendmail.cf *"compile the changes"

makemap hash /etc/mail/access.db < /etc/mail/access *encode the pass*

/etc/init.d/sendmail restart

I choose to send mail locally to queue incase sendgrid goes down, which happens often this is why I don't make a socket connection to their servers realtime.

Next we need to configure PHP's SWIFT class to sendmail locally


        $transport = Swift_SmtpTransport::newInstance('localhost', 25);
        $this->swift = Swift_Mailer::newInstance($transport);

Now the only thing left to do is building a table to record all the clicks that people do to unsubscribe from getting email



 CREATE TABLE `DoNotEmail` (
  `userId` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'userId that is getting the email',
  `emailAddr` varchar(255) NOT NULL COMMENT 'Denormalized email address',
  `emailAddrHash` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'emailAddr in our numeric format',
  `createdDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'when the email entered the system',
  PRIMARY KEY (`emailAddrHash`,`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Any time a person clicks unsubscribe a row is inserted into this table. Anytime email is ready to be built and sent a query is performed on this table by emailAddrHash which is 8 bytes instead of 50+ bytes for email. I like to keep my keys small.

Most of the time will be spent building your email templates and this is just an abbreviated list of steps things to consider to move the process faster.

Operations is the corner stone of a Web company

2011-04-18T17:12:00.000-07:00

Operations defined as the guys/gals who deal with servers have many different names. They are called Dev OPS, Sysadmins, System Engineers, Capacity Planners, Server Monkey (if you are a jerk), DBA, the guy in the corner with an Anarchy Symbol on his Shirt etc.

OPS is the front line defense and Cavalry. They know more about the system then the developer who installed software on OPS managed servers. Because they see the crash happen in the wild. One word of advice, your organization is going to die if you piss off the guy with ROOT access. If you don't know what that is - you really really need to be nice to OPS.

I have been at many layers of a company. I've been QA, Lab Tech, System Tuner, Server Installer, Web Developer, Software Engineer, DBA, Director of Engineering, Chief Architect, Business interface, yet I am just the dude that can get stuff done with the help of the people around me.

In many places I have seen ops treated, as Second Class Citizens-this is where I step in. I pride myself as being a member of OPS. I pride myself, as being a member of DEV but what I really pride myself in is being the Dude that is the bridge between OPS and DEV. The guy that says hey dude this is your 5th Custom Server type your messing with OPS puppet profile, can we standardize? The guy that will consolidate underutilized servers when OPS let’s me know or when I catch it myself. I am the guy that does what OPS want when OPS wants it done. The guy that looks at the same charts that OPS looks at to find what's broken. The guy that suggests in Engineering meetings which excludes OPS to include OPS. I am also the guy that suggests DEVs should be on call as well as OPS.

Value OPS! They are on your team, and if they do not look busy that means they are really good, DEVs and OPS is jelling and your team is going to WIN! If your OPS team is not happy make them because do you know how to log into the power strip and bounce the rack?

Building a Facebook Feed Like system on a Sharded mySQL System

2011-04-01T09:44:00.000-07:00

Building a Feed can be broken down into a few key questions.

Who can see what?
How many people can see it?

These key answers really dictates the design of data structure from a global read method to a global write method. A global read method can be summarized using some sql.

SELECT * FROM Activity WHERE userId IN (?,?,?,?,?) AND createDate > NOW() - INTERVAL 2 WEEKS;

Above is actually not an optimal SELECT, what is really done in my case is foreach '?' do a parallel query to each shard group of

SELECT * FROM Activity WHERE userId = ? AND createDate > NOW() - INTERVAL 2 WEEKS;

The reason why the 1st query is not optimal is due to the fact that their are two ranges in the 1st query. The IN clause is a range and createDate is a range thus you can't use a composite key (userId, createDate) the query is only using userId.

A global write method can be summarized with the following query:

SELECT * FROM Activity WHERE rowOwnerId = ? AND createDate > NOW() - INTERVAL 2 WEEKS.

Now you may be wondering if you are doing a global write, why is there a select? For each friend that is following the person who has activity done to said person which either they initiated or is acted upon, write a row for said person and their friends. Thus one action can create 5000+ writes such that the number of writes are proportional to the number of friends that is following the person being acted upon. Writes are N+1.

Global writes allows for you're view of the Feed to be fast but is really hard to keep it in real-time in sync, additionally its very expensive since a copy of the pointer can be copied N times and to get real speed and to avoid additional reads, the content is copied for each friend write. In global writes you have to create queues which succumbs to queuing theory. Additional to these considerations edits and deletes are very hard to keep in sync as well as need special consideration for new friends joining. These things can expose system lag constantly. That being said it can still be done, it's a lot of work that is expensive in terms of hardware cost and developer time.

The system I have built supports both yet currently implements a global read method. The mysql table structure follows.

userId bigint unsigned NOT NULL DEFAULT 0 - this is the person being acted upon.
parentOwnerId bigint unsigned NOT NULL DEFAULT 0 - this is the person who created the content
parentId bigint unsigned NOT NULL DEFAULT 0 - this defines the pointer for the actual content
parentType smallint unsigned NOT NULL DEFAULT 0 - this defines that the content is a wall post or a friend event or

itemType smallint unsigned NOT NULL DEFAULT 0 - this defines the actual action, which could be a comment to the parent Type or just the parent itself
itemId bigint unsigned NOT NULL DEFAULT 0 - this defines the pointer to the item for content retrieval
createDate timestamp not NULL DEFAULT 0 - this indicates when the event entered the system
modifiedDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - this defines when the item was added to for a later feature.

The primary key in this mix is
userId, itemType, parentId

this defines that the user acted upon will only have 1 row foreach type of a parentId. This means if there are 1000 comments to a status update, the activity table will have 1 row for that user that made the comment even if that same user was the person who made 1000 comments. Note that this activity table was updated 1000 times because modifiedDate is a timestamp that gets updated on every action.

As you can tell the Activity Table is centered to the entire system and the actual content tables are what I call branches to Activity. Let's take an example to explain this.

user 1000 adds a status update, status update is a table called WallPosts.

WallPosts.posterId - 88888
WallPosts.itemOwnerId - 1000
WallPosts.createDate - NOW()
WallPosts.posterId - 1000
WallPosts.post - This is my status update.

Activity.userId - 1000
Activity.parentOwnerId - 1000
Activity.parentId - 88888
Activity.parentType - 1
Activity.itemType - 1
Activity.itemId - 88888
Activity.createDate - NOW()
Activity.modifiedDate - NOW()

So now friends of 1000 will query userId 1000's shard for the WallPost. Now let's have userId 2000 leave a comment in a table called Comments

Comments.commentId - 88889
Comments.itemOwnerId - 1000
Comments.itemType - 1
Comments.commenterId - 2000
Comments.createDate - NOW()
Comments.comment - " I left a comment on your status update!"

This says that friend 2000 left a comment on userId's 1000 (their shard) of "I left a comment on your status update!"

Activity.userId - 2000
Activity.parentOwnerId - 1000
Activity.parentType - 1
Activity.parentId - 88888
Activity.itemType - 2 //(left a comment)
Activity.itemId - 88889
Activity.createDate - NOW()
Activity.modifiedDate - NOW()

This says that friends of userId -2000, 2000 left a comment on UserId 1000's status update where the comment and the status update both reside on 1000's shard. The content can be reached via

88889 == CommentId

and friends of 2000 now know that 2000 left a comment for 1000. Thus the viral effect within 1/2 degree's of userId 1000.

There is a lot more going on behind the scenes like Children of parents, Using the combined read throughput of memcache, sharded mysql system and permission filtering but this is the general idea.

I am going to continually update this post, so keep checking back here as I add diagrams, flows, links and details looks at grouping events.

Note: By no means am I saying that a global write or Fan-Out write is not as good as a Fan-Out on Load or global read. I'm taking an approach to make it cheap and not trying to optimize to soon.

Here are some good links that I found after writing this post:

http://www.quora.com/What-are-the-scaling-issues-to-keep-in-mind-while-developing-a-social-network-feed

http://www.quora.com/What-are-best-practices-for-building-something-like-a-News-Feed?q=news+feed

Left RockYou to do new things

2011-03-24T11:11:00.000-07:00

I decided to leave Rockyou because after three years of working on the Facebook platform, Reach Applications, Viral Loops, Games, Scaling, and Management. Being a Director of Engineering was cool yet management decayed my skills as a system builder-ideally in the future I can find a good balance between coding which I love and management of a process. Since I focused on management, it's like being a C++ programmer for years; this programmer does not code for a while and then is asked to build an optimal hash table within a few days. This programmer knows if he/she was in the thick of things as a Coder he/she could do it. Since he/she is rusty it will take he/she much longer. This suddenly became apparent as I was asked softball questions and I did not answer them quickly enough. Fortunately I am taxing my skills and as a result they are flooding back. My mojo is here. I am learning new things and one thing I can say is JQUERY is awesome.

To my past co-workers, friends, Rockyou family I deeply honored of our time together. We built some amazing things. Even at one point in time we surpassed Facebook in Daily and Monthly Uniques! Rockyou has a world class OPS organization that was pivotal in keeping 99.999% uptime. My team, which was the bridge between operations and development one word I can use to describe them: AWESOME.

Now for the next things I will focus on. I was going to be a speaker at Collaborate 2011 in Florida but I cannot speak now since I do not work for Rockyou as they where going to sponsor my trip to Collaborate; thus I will post what I was going to talk about:

How one person can manage hundreds of mySQL servers doing 400K TPS combined

Future posts:

Building a Fast Feed

Long Polling and controlling the effects on your backend systems

HandlerSocket mySQL's NoSQL, PHP and Webscale

2010-12-16T09:50:00.000-08:00

HandlerSocket is a server plug-in that interfaces with InnoDB directly bypassing for the most part the core mySQL server all together. Using Handler socket you do not connect to the traditional port 3306 or use the mySQL protocol to communicate with the mySQL server, you use 9998 reads, 9999 writes and talks a different protocol (much smaller) to an epoll-based service. This awesome addition means that you can have many many connections with very little overhead. The core mySQL server does a good job, but can do better. I'm sure the reason why epoll is not in the core engine is probably due to licensing issues or some other reason I am not aware of, like it would be a big deal to add it.

Why bypass the server? Really bypassing the sql parser is what was desired. The sql parser ads a significant amount of slowdown at a huge transaction rate, additionally mutex contention is involved prior to reaching the storage engine with malloc overhead (although Monty has fixed this in MariaDB). Even though you can easily get 45-70K selects a second through the mySQL SQL parser layer for InnoDB primary key lookups, this number falls short of Memcache's 600K Gets per second or various other NoSQL solutions that keep data in memory.

HandlerSocket just like, Memcache, Cassandra, MongoDB accesses the data by a key, with the fastest access on Primary Key since that's how InnoDB structures it data, sorted by the primary key. I've seen benchmarks that show Handler socket doing 750K Transactions per second on a single server. I'm now in the process of benching it and let me say I think I can get a better number. This by the way blows away ALL OTHER NoSQL benchmarks with the added benefit of an ACID compliant DATABASE. These numbers and durability really shows the power of InnoDB this is why I believe it's the best Storage engine in the world. (Plus the code is clean).

Percona released a XTRADB server version with HandlerSocket a plug-in created by the engineers of Dena and immediately I started testing it.

Installation of HandlerSocket is detailed here in case you want to have HandlerSocket on a vanilla mySQL source base. The latest version prior to the version above of XTRADB, I ran into a big stall and had to roll back to a vanilla install of mySQL to get rid of it. When I have some time I'll try to duplicate the stall in a controlled environment and use PMP to track down where the stall is. The versions that I dealt with are drastically different so it may just be an new InnoDB stall or something else.

The PHP Client that I am using is php-handlersocket which is a PECL type version (C driver with exposure to PHP). It does the job but needs some work that I'm doing now.

Here is some rough code and output of data.


#
# make a php connection
#

$hs = new HandlerSocket($host, $port);
if (!($hs->openIndex(1, $dbname, $table, HandlerSocket::PRIMARY, 'facebook_id,shard_id,shard_lock'))){
    die($hs->getError() . "\n");
    
}

#
# execute a query on the primary key and return the columns from the 5th parameter on openIndex
#

$retval1 = $hs->executeSingle(1, '=', array($uid),1,0);
init_funcs_log_info("HandlerSocket", "ExecuteSingle:" . var_export($retval1,1), 'socket_handler_query');


#
# prepare a friend query
#

$socket_handler_commands = array();

foreach($list_of_users as $userid){
    $socket_handler_commands[] = array(1, '=', array($userid));

}

#
# execute the friend query
#

$retval2 = $hs->executeMulti($socket_handler_commands);

init_funcs_log_info("HandlerSocket", "ExecuteMulti:count(" .count($list_of_users) . ")\n" . var_export($retval2,1), 'socket_handler_query');

Back from my European Vacation

2010-11-29T10:46:00.000-08:00

I am finally a citizen of the world. I beat out the 85% statistic in which American's from the USA do not venture out of the USA. I flew into Paris spent 5 days, then went to Brussels (1 day), Amsterdam (2 days), flew to Barcelona had to stay there a day because Venice was on strike, then to Venice (2 days). From Venice to Rome (5 days), then to (day trip) Naples, Pompeii, back to Rome then back to Paris and finally home.

I can go into detail of how awesome this trip was but I am going to focus on how recharged I am. Man am I! During my travel times I geeked out and read the MySQL 5.1 Plugin Development book by Sergei Golubchik and Andrew Hutchings, which is AWESOME!

Now that I am back I hope to put the things I learned from Plugin Development into action and write posts about them.

Some Posts in the next couple of weeks that I am going to focus on. SQL optimizations, Optimizer problems in 5.1, Stalls, performance performance performance.

Facebook Live: Running MySQL at Scale Tech Talk by Facebook

2010-11-03T00:31:00.000-07:00

Thank you to Domas, Mark, the cool team of DB Dudes at Facebook and to Facebook for hosting a spectacular event of beer, wine and of course mySQL.

Here are some notes that I'd like to share and here is the full video recording of the event.

Before I start, let me say that I'm super impressed with the tools that the Facebook DB Teams put together. They are to say the least better then commercial grade. The tool set covers all the staples like graphing show global status, InnoDB stats, real-time trending to see what the norm is and how things flux in a professional designed interface with drill downs to the actual root cause of the end query messing things up.

Facebook has three (main?) database teams.

Operations who fix the problem right now.
Performance who fix the problem today or tomorrow.
Engineering who don't fix the problem fast enough *joke*

Even though there are disciplines the database teams all gel.

Now for some stats:

Query response time R(t): 4ms reads, 5ms writes
Network bytes per second: 38GB @ peak
queries per second: 13M
Rows read per second: 450M @peak
Rows changed per second: 3.5M @peak
InnoDB disk ops per second: 3.5M

What does it mean? WOW this is webscale.

For Facebook, network latency is killer. Cross country queries hurt, especially done serially. For instance

start a transaction
add a new row to one table
increment another table
end a transaction

each command is a network trip, the network latency can take up to 100ms yet the sql command is less then 10ms. Why put in all that effort of tuned servers, queries, code to give a bad user experience of 100ms.

Using a feature in the mySQL client API, they can send multiple statements in the time it took for the 1st query delta. This API flag for mysql_connect is CLIENT_MULTI_STATEMENT.

Why not just use triggers or stored procs? Because managing them is a nightmare, I've talked about this many times and I'm glad Facebook agrees - this is a great trick for reducing the cost of cross datacenter db calls producing dynamic database stored procs. I doubt that triggers or stored procs are fully gone.

Then OSC, they talked about how much time this saved the company a few weeks ago and its staggering. What use to take days or longer is reduced to hours. I'm so impressed by this, that I asked my team to make this into a web app that can execute these commands across an arbitrary set of servers. My newest team member Einav finished it and we now use it in production (screen shots and a follow up post coming).

Facebook is surprisingly open and really is fostering / giving back to the community. Keep up the great work!

MongoDB the Definitive Guide by Kristina Chodrow and Michael Dirolf

2010-09-30T11:28:00.000-07:00

The kind folks at O'Reilly sent me a fantastic book about MongoDB. This was a great read since it’s suited for people who do Operations and Development and Performance tuning (me). I've been using Cassandra for quite some time now (months lol) and the thing that has irritated me about Cassandra is the documentation for it. Cassandra documentation sucks, its hard to speed up on the internals. This MongoDB book is written by the most active participants that are developing MongoDB and the knowledge shows. What I like is it starts out on how to quickly get it up, add/get/update data to the DB. Then progresses to more advance topics-that talk about GridFS and MongoDB drivers. Personally I would like to see more elaboration of this facet in terms of motivation of why do this, what the win is and how it fits into the "Fast by Default" mantra. Each step is organized perfectly, and detailed with nice graphics that illustrate the document store or the flow of data from a systems view. When looking at the documentation on mongodb.org I see the same sort of clarity in this book. Comparing other NoSQL information, I do not see this transparency, which is rather frustrating because the learning curve is much larger. I'm so impressed with the info, and test results around the web that I'm moving to add this to my environment. Does this mean I'll get rid of my current Cassandra deployment? Probably not since its working great for my needs now.

Overall, great book, great info, intelligently presented with a straightforward explanation of how MongoDB works.

Cassandra and Ganglia

2010-09-03T15:42:00.000-07:00

I finally got some time to do some house cleaning. One of my nagging low-hanging fruit jobs was to stop using jconsole as my monitor. I created a ganglia script to graph what is above. The image illustrated above I am showing all the Cassandra servers and their total row read stages completed in the last hour as a gauge. In essence I am graphing the delta of the change between ganglia script runs.

How I have it set up is:

All data exposed by JMX to produce tpstats and cfstats is graphed via ganglia. The pattern for each graph is as follows

cass_{stat_class}_{key}

stat_class - tpc, tpp, tpa means complete, pending, active respectively
key - would be message deserialization for instance.

For column family stats I graph the keyspace stats as well as the specific column family stats exposed by cfstats. For instance below:

If you’re interested in the scripts I'll send it to you or put it up on code.google.com, its written in perl OOP perl and takes the same approach of packaging that maatkit tool kit for mySQL by Xarb and crew does (puts all the "classes" in the file as the application).

GmetricDelegate is the parent package
GmetricCassandra extends GmetricDelegate and overloads getData as well as defines what is an absolute stats vrs a gauge.

As you can see the pattern I also have
GmetricInnoDB
GmetricMySQL

and so on.

then on each server I run

/usr/bin/perl -w /home/scripts/ganglia_gmetric.pl --module=GmetricCassandra

this then talks to Ganglia through gmetric to report the stats.

Update: I uploaded an alpha version to http://code.google.com/p/gangliastats/ - be warned sparse comments I'll have another check in with documentation soon.

Review of MySQL High Availability by Charles Bell, Mats Kindahl and Lars Thalmann

2010-08-18T15:28:00.000-07:00

The kind folks at O'Reilly sent me MySQL High Availability by Charles Bell, Mats Kindahl and Lars Thalmann . In summary, the book is awesome. Personally I didn't think that Replication was enough of a feature in mySQL to fill up 500+ pages about it, BUT, they did and not with fillers or info that is NOT actually important to you as the end designer. In Mark Callaghan's forward he states that this book "adequately" explains MySQL replication, this is a huge low-ball, I dare say if you did not look at the replication code (prior to row-based replication there was not a lot of it) your questions can be answered with this book. There are three parts to this book: Replication (roughly 50% of the book), Monitoring and Disaster Recovery, then finally High Availability Environments. Each part is well written and accurate. I have been using mySQL before Replication existed. When it was first released I have been using it and been to many talk about it, from the original author talks to Brian Aker’s talks to asking Jeremy Zawodny about it to giving my own talks about Scale-Out, replication, etc.
My experience with mySQL Replication is rather robust. I broke holes in it, found bugs with it, and even wrote a quick C client (when it was still statement based replication only on the 4.1 branch) that would act as a proxy to gather all the replication feeds of all boxes onto a single box (it was a proof of concept). The book goes into clear detail about the common approaches and use cases of replication scaling out the apps reads. It even talks about data sharding that I must say I am an expert at (done it for Flickr/RockYou, for friends etc). The examples are clear although I would not recommend deploying their examples verbatim. The reason you never want to mod based on the number of shards in your system, because if you add more shards you are going to have to move your data all around. Central dictionaries are perfect to control balance. They go into this, but not how to lock a user to migrate to a lightly loaded shard. The example that they have creates holes in the flow, race conditions as well as downtime to move data, this is a nitpick, the book is good.

I do not want to give away the book, it is a good read and the quality is what you expect from O’Reilly. If you want to know about various replication techniques, replication shortcomings, building fail-over systems, and exploring other technologies that are comparable to replication, this is a great book for you. Oh and of note, they even go into exotic features that I rarely use in production and how that effects replication. Awesome.

Upgrading Cassandra 0.5.1 to 0.6.3

2010-07-08T12:58:00.000-07:00

Every month or so a node randomly dies


EQX root@cass01:/opt/cassandra/bin# ./nodeprobe -host localhost -port 8181 ring
Address       Status     Load          Range                                      Ring
                                       facebook_1301003235_1301003235             
10.129.28.22  Down       15.77 GB      9ZehBzpHHwnxiPJU                           |<--|
10.129.28.23  Up         7.59 GB       facebook_100000471858343_1514390063        |   |
10.129.28.14  Up         4.59 GB       facebook_100000846936312                   |   |
10.129.28.20  Up         12.94 GB      facebook_1301003235_1301003235             |-->|

Trying to get info from the host, the reads timeout.
java.net.SocketTimeoutException: Read timed out

Doing an lsof -p on the java proc I see that it is holding open a bunch of sockets. So the node itself is hanging on something internal is my assumption.

Looking at /var/log/cassandra/system.log I see that the last rotation happened Jun 8th over a month ago and no new log is being written to. THe issue is the node just died today. So this seems like a bug to me.

Now since Cassandra does not tell me what the problem is, I assume that there is a bug in this version and searching Cassandra Jira bug database I see that a lot of stuff is fixed as well as added. So might as well as upgrade.

Before I upgrade I wanted to do research to see if anyone else has. To my surprise there doesn't seem to be any blog talking about upgrading from 0.5 to 0.6.3

I know its rather easy but there is some new stuff in 0.6.3 that is turned on by default: So let's see what changes in the conf

diff /opt/cassandra/conf /opt/apache-cassandra-0.6.3/conf

I see that in storage.xml there is some new XML attributes for the ColumnFamily tag such as RowsCached, new tags called HintedHandoffEnabled, Authenticator, DiskAccessMode, RowWarningThresholdInMB.
Additional to this I noticed that a lot of XML tags are missing. A rolling upgrade is just not possible and is mentioned in NEWS.txt

Thus in my application I set this $GLOBALS['cfg']['disable_nosql_feature'] = 1; I have about 40 toggles to play with, a very helpful process to enable dynamically code with out breaking your site.

now time for an upgrade without the service running:

Steps:

Shut down Cassandra: dsh -g cassandra "pkill java" # same thing as stop-server

rpm -e cassandra-0.5.1

rpm -ivh cassandra-0.6.3.rpm

/opt/cassandra/bin/cassandra

Done. Note what the hell is cassandra-0.6.3.rpm, it's an rpm I created that has my storage-conf.xml
log4j.properties
cassandra.in.sh

After Upgrading:


***************************************************************
WARNING: ./nodeprobe is obsolete, use ./nodetool instead
***************************************************************
Address       Status     Load          Range                                      Ring
                                       facebook_1301003235_1301003235             
10.129.28.22  Up         11.75 GB      9ZehBzpHHwnxiPJU                           |<--|
10.129.28.23  Up         3.04 GB       facebook_100000471858343_1514390063        |   |
10.129.28.14  Up         2.33 GB       facebook_100000846936312                   |   |
10.129.28.20  Up         4.4 GB        facebook_1301003235_1301003235             |-->|

Now what is left to do it change my ganglia scripts / nagios scripts to use nodetool instead of nodeprobe.

dsh and TABLE CHECKSUM

2010-06-25T01:02:00.000-07:00

So running through some various tasks, I'm finally on the section of work where I can resurrect a script that finds inconsistent data between master-master pairs.

Let's get a quick summary to find our problems to target the script at.


dsh -ef 20 -w dbfacebook9b,dbfacebook9a -s ~/bin/execute_query.sh "FacebookShard \"CHECKSUM TABLE RollingUserLog\""
dbfacebook9b: *************************** 1. row ***************************
dbfacebook9b:    Table: FacebookShard.RollingUserLog
dbfacebook9b: Checksum: 538386033
dbfacebook9a: *************************** 1. row ***************************
dbfacebook9a:    Table: FacebookShard.RollingUserLog
dbfacebook9a: Checksum: 538386033

not bad.

CHECKSUM TABLE uses a ACCUM algorithm to determine the table checksum. This gives a good fast snapshot in a shared lock mode so writes do not stop while doing a checksum (this is in the context of INNODB). RollingUserLog gets nearly 700 writes per sec per box (No alerts).

But what if there is a problem


# dsh -ef 20 -w dbfacebook38b,dbfacebook38a -s ~/bin/execute_query.sh "FacebookShard \"CHECKSUM TABLE SimulatedProblem\""
dbfacebook38b: *************************** 1. row ***************************
dbfacebook38b:    Table: FacebookShard.SimulatedProblem
dbfacebook38b: Checksum: 660032421
dbfacebook38a: *************************** 1. row ***************************
dbfacebook38a:    Table: FacebookShard.SimulatedProblem
dbfacebook38a: Checksum: 2533654621

Well let's see if there is a row count mismatch


# dsh -ef 20 -w dbfacebook38b,dbfacebook38a -s ~/bin/execute_query.sh "FacebookShard \"SELECT COUNT(*) FROM SimulatedProblem\""
dbfacebook38b: *************************** 1. row ***************************
dbfacebook38b: COUNT(*): 64358
dbfacebook38a: *************************** 1. row ***************************
dbfacebook38a: COUNT(*): 64358

Ah so the data is inconsistent, we are not missing rows but 1 or more rows have different values. Now time to find it.

To resurrect my script now that I know what table is messed up:

Algorithm is this:

open connection to both servers - fork
compare the data by scanning the table via a index walk
crc32/md5/hash the rows
compare columns
if (dbfacebook38b.hash != dbfacebook38a.hash)
mark row and record primary key to track position

print report

but wait why polish up my script when I can use a formal one that does the job great!

@see

mk-table-checksum
mk-table-sync

# fixes the issue


mk-table-sync h=dbfacebook38a,u=$USER,p=$PASS,D=FacebookShard,t=SimulatedProblem h=dbfacebook38b --execute --no-check-slave

maatkit is awesome. Nearly every problem that I run into and built a script for has been formalized. I'm going to muster up some time and contribute to this toolkit like add my binary log rotate which makes sure that the slaves are caught up to the binary file that is being purged.

Review of High Availability MySQL Cookbook by Packt Publishing

2010-06-07T12:07:00.000-07:00

A few months ago, I reviewed MySQL Admin Cookbook. Today I am reviewing High Availability MySQL Cookbook from Packt Publishing by Alex Davies. Overall, I found the book to contain some good hidden Gems.

The book is a mixture of MySQL Cluster (NDB), Replication schemes, some performance tuning, some minor kernel tweaking, and some more exotic approaches to common High Availability problems. Overall, I found this book very informative and a good read.
Now the specifics, the book starts out on NDB and stays focused on this fact for about 60% of the book. The next 20% is on mySQL replication then about 10% of the book is on tweaking kernel, mysql, network settings to get the most out of the system. The last 10% is a mixture of uses of exotic systems such as GFS, Conga, ISCSI and how to use these shared storage techs with mySQL.

High Availability MySQL Cookbook, is a good read, and jogged my memory on NDB (since I do not use it on a day-to-day basis). If you are looking for a good reference on how to get an HA system up then this is a good book for you. If you are looking for why to use NDB over mySQL multi-master replication, this is not a book for you. The “why-to-use-this-over-that” is not the scope of the book. The drawbacks and concerns of what technology to use are not a focus of this book, and should not be. It is assumed that the reader knows which direction to go into for the most part or gives the reader enough information to set up and environment to see which way to go.

In conclusion, I like this book. I think it is rather concise, and right to the point, which either gets you started into building HA systems or gives you a good reference for an existing HA environment.

Mastering the art of indexing

2010-04-29T13:43:00.001-07:00

Check out this SlideShare Presentation: I'd have to say that this is the coolest presentation on Indexing and how it relates to INNODB. I have written on this subject many times in the pass but this presentation covers all the bases and does a great job at explaining WHY you should do certain statements over others.

More mastering the art of indexing

View more presentations from Yoshinori Matsunobu.

Review of MySQL Admin Cookbook from PACKT Publishing

2010-04-13T14:52:00.000-07:00

PACKT Publishing sent me titled "MySQL Admin Cookbook" to review and I told them that I would be brutally honest about it. They said cool and well here, we go.

Overall, the book is cool if you are starting out in MySQL administration and want to get a box up and running. If you are looking to scale MySQL or make your application faster this is not the book for you. If you are worried about consistency and getting the most out of your hardware-this is not the book for you. If you are trying to figure out what the best index combination is-again-this is not the book for you. If you want to know how to add users, or set up replication, or dump a CSV format text file of data then this is the book for you.

Some things that annoy me from this book is all of the GUI cut and paste screen shots. Explaining stuff with a GUI screen shot really sucks IMHO since by the time you read the book, the GUI changed. I personally stick with command line interfaces or write my own GUI layouts to administration actions since I know what the various ADMIN commands do. Let me stress again that GUI explanations really go out of date fast and is only pertinent for when the book is made. For instance if you ever used Eclipse, a common IDE for various languages (mainly Java), between Eclipse builds the GUI changes. The overall interface for the MySQL command line client has stayed the same since the very beginning. To be fair though the book does show some mySQL command line examples, like for handling NULLs but consistency is key to getting your ideas across.

Another pet peeve of mine is the book has a tag line 99 great recipes for mastering MySQL configuration and administration yet I couldn't confirm 99 recipes since the book is not actually structured this way IMHO. It is structured in the format of "How to do it", "How it works", and "there's more..." for certain actions and there is just not enough meat for Mastering MySQL configurations – like what is a Star Replication Schema and how to do it? How do you rotate in new servers when in a circular MySQL config? Where is MySQL clustering? Why are file sorts so slow? How is MySQL using the disk subsystem with this config ... etc.

IN conclusion, would I recommend this book to readers? If you need a starting point to ask Google for some more complicated questions-this is a good start. For experienced administrators, no it is not for you.

Some kernel tweaks to aid Cassandra under a high concurrency environment

2010-03-26T10:45:00.000-07:00

For the past couple of weeks I have been trouble shooting some Cassandra issues where data would not make it to Cassandra.

The image above graphs all the exceptions that are produced from Cassandra. The two big lines are

Transport Exceptions (te) - meaning that Cassandra could not answer the request think of this as MAX Connection errors in mySQL.

Unavailable Exceptions (ue) - meaning that Cassandra could answer the request but the "storage engine" cannot do anything with it because its busy doing something like communicating with other nodes or maintenance like a node cleanup.

So how did I get the graph to drop to 0? After looking at the error logs, I saw that Cassandra was getting flooded with SYN Requests and the kernel thought that it was a SYN Flood and did this

possible SYN flooding on port 9160. Sending cookies.

To stop this the puppet profile was changed to have

sysctl -w net.ipv4.tcp_max_syn_backlog=4096
sysctl -w net.ipv4.tcp_syncookies=0

Next looking into the Cassandra log which I defined to exist in /var/log/cassandra/system.log

WARN [TCP Selector Manager] 2010-03-26 02:46:31,619 TcpConnectionHandler.java (line 53) Exception was generated at : 03/26/2010 02:
Too many open files
java.io.IOException: Too many open files

Then noticed that
ulimit -n == 1024

thus I changed
/etc/security/limits.conf so that It's at a server setting by adding this:


*                                         -              nofile                   8000

Now my Transport Exceptions and Unavailable Exceptions are gone and data is being written to it consistently.

There are many other ways of doing the same thing, I could have modified my init script or did some other stuff but I choose this way. Default Distros set kernel and limits fields too low: settings for desktop levels.

Cassandra is my NoSQL solution but..

2010-03-23T18:36:00.000-07:00

In the past few months, I have tested many NoSQL solutions. Redis, MongoDB, HBase yet Cassandra is the Column Store DB I picked because of its speed (on writes), reliability, built in feature set that makes it multi-datacenter aware. The one other personal reward for Cassandra is it is written in Java. I like reading and writing in Java more than C++ although it really does not matter for me personally in the end.

Let us talk about the reason why I am introducing Cassandra into my infrastructure and some of its drawbacks I have noticed so far.

Why it is being introduced:
We have a feature where we record every single click for 50 million Monthly Active Users (real-time) and storing this in mySQL is just waste of semi-good hardware for data that is only looked at for the past 24 hours. Over the course of some time (couple of months) more than 3 billion rows accumulated, which translated into a 3.5 TB distributed INNODB datafile. So purging/archiving this data just sucks.

Now introducing user clicks into Cassandra was rather easy. I researched various sites, asked my Digg buddy and then figured out the rest. Within two days I was up and running with Cassandra and had a great understanding about Column Families and SuperColumns.

Developing with a Cassandra Data layer:

Now that the infrastructure is up, I needed to add a data model to /opt/cassandra/conf/storage.conf. The SQL that drove this functionality consisted of two main SQL statements.
Add click
Get a Range of Clicks

For these operations, mySQL rarely takes 0.001 seconds (1 ms).

Cassandra for writes is rather fast, but for reads, Getting Range Clicks
i.e. using
recv_get_slice

it takes .02 seconds (20 ms).

What does this mean? MySQL is a hell of a lot faster! Is it because of my CF design? No.
for instance, take this slug (keys separated by a delimiter to make a distinct key name).

{$clicker}_{$pageowner} => [ $object_id_clicked0 => $value,$ts, ... $object_id_clicked =>N $value,$ts ]

The slug says that clicker A, clicked on page B and what is stored are columns of distinct clicks - remember this is a column store DB.

So
{$clicker}_{$pageowner} == the KEY for the COLUMN $object_id_clicked with the value being a $value and the free extra value the $timestamp.

Here is the php code


$columnPath = $this->getColumnPath($objectid);
              $this->getCassandraConnect()->insert(self::KEYSPACE, $this->getKey($clicker, $pageOwner), $columnPath, $this->today_ts, microtime(true), ConsistencyLevel::ZERO);

Now I want a list of items clicked.


$data = $this->getCassandraConnect()->get_slice(self::KEYSPACE, $this->getKey($clicker, $pageOwner), $this->getColumnParent(), $this->getSlicePredicate(), ConsistencyLevel::ONE);

This says give me the last N logged clicks that the clicker A made for page owner B. This is a hash lookup (Big-O(1)) but a sorted list of columns (O(nlogn)) and return the last N elements.

Why is Cassandra sooo slow on reads. Is it because my memory config is not enough?
No. 7 GB of data is allocated for data that fits in memory ( for now ).

Is it because my disk is saturated?


avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.21    0.00    2.85    0.48    0.00   84.47

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.01    14.01  0.23  6.61    22.25   165.03    27.34     0.33   47.57   7.35   5.04
sda1              0.00     0.00  0.00  0.00     0.00     0.00    20.83     0.00    7.74   6.40   0.00
sda2              0.01    14.01  0.23  6.61    22.25   165.03    27.34     0.33   47.57   7.35   5.04
dm-0              0.00     0.00  0.24 20.63    22.25   165.03     8.97     0.72   34.27   2.41   5.04
dm-1              0.00     0.00  0.00  0.00     0.00     0.00     8.00     0.00    5.28   0.22   0.00

No its not.

My hunch is the slowdown is a combo of the low thread read pool and in the inherent sorting and there doesn't seem to be a way to turn it off, so without digging in the code I couldn't say (I will be able to once I get an understanding of the code layout).

In conclusion, I like Cassandra, it’s very fast in writes, slow (for my taste but fast enough) in reads and what takes 2 lines of SQL takes 250 lines of PHP code interfacing with the Cassandra.thrift suite. I am going to use it still because it is good enough and I love the built in HA of it. Additionally the performance is improving very quickly.

Talking at the University of Utah

2010-03-10T15:56:00.000-08:00

Giving a talk at the University of Utah on everything from scaling, clustering, mysql, mysql internals, noSQL (Cassandra) to how to manage all this stuff. If you are there at University I'm bringing some Swag!

Also I will upload the slides and put them here.

NoSQL explained to DBAs

2010-02-25T11:01:00.000-08:00

Brian Aker, a brilliant helpful duder, who I learn a lot from. Gives a great talk about what is NoSQL explained in a way for database guys. I warn you, there are some points in this video where you can't hear Brian due to the audience "participation" but you should get the content.

innodb_file_per_table, shrinking table spaces and the data dictionary

2010-02-04T11:24:00.001-08:00

INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.

If you are running innodb_file_per_table, you will notice in your database directory

db.opt - database characteristics file.

tablename.frm - the table structure.

tablename.ibd - the actual innodb table space file

Imagine that you have a table with 10 million rows and you delete say 5 million rows in multiple chunks around 400K chunks, because deletes are slow. Next, you notice that the table space file did not shrink. So what do you do? OPTIMIZE tablename, tada all the wasted space is reclaimed, but here is the PROBLEM the ibdata file grew!

ibdata stores all of the UNDO LOGS thus GROWS due to the deletes and space is never reclaimed.

Ok, lets try coping the tablespace file to another directory and re-import the tablespace file after wiping the data dictionary (ibdata).

For instance
/etc/init.d/mysql stop
cd /var/lib/mysql/DB
cp * /tmp/hold
rm /var/lib/mysql/ib*
cp /tmp/hold/* /var/lib/mysql/DB
/etc/init.d/mysql start // create the ibdata file

ALTER TABLE tablename IMPORT TABLESPACE
ERROR 1146 (42S02): Table 'DB.tablename' does not exist

Really, there is nothing that you can do to force innodb to purge the undo logs in the ibdata file without a full dump of the data to text and then reimport the data.

So here are the steps to shrink all table spaces and the ibdata file

mysqldump --all-databases (or use mk-parallel-dump)
stop mysql
rm -f /var/lib/mysql/ib* /var/lib/mysql/DB/*
start mysql
mysqlimport (or use mk-parallel-restore)

Takes a bunch of time but there is no other recourse. If you know of another way please share :)

Scaling to 200K Transactions per Second with Open Source - MySQL, Java, curl, PHP

2010-01-08T16:21:00.000-08:00

I'll be giving a web presentation Jan. 19th 2010. If you would like to check it out please do!

Scaling to 200K Transactions per Second with Open Source - MySQL, Java, curl, PHP

I thought it went well. I will post the link to the slides here :)

Scaling to 200K Transactions per Second with Open Source - MySQL, Java, curl, PHP

Asynchronous Queries verses Synchronous Queries

2009-11-09T16:58:00.000-08:00

In a procedural language without the use of threads (or Inter Process Communication via forks), to execute I/O requests they are done one after another. Synchronous Queries produce at best a Big-O of N such that N is an element of I/O communication (queries) and N equals the number of queries needed to achieve the requested dataset.
With IPC or threads we can speed up common O(N) problems to reduce the N with parallelism, its still functionally a O(N) yet from a single instance point of view N is much less because threads (IPC) takes that Serial computing component and executes the code in parallel. To better explain what I am talking about lets look at some PHP code:


foreach($friends as $friend){
     $data[] = getMySQLData(“SELECT * FROM AccountData WHERE userid = $friend);
}

The Primary key for the AccountData table is userid. Assuming that you have 5000 friends, the query has to be executed 5000 times.
We can reduce the O(N) and change it to a O(nlogn) (Binary Tree - doesn't take into account other factors) by switching the query to


$data = getMYSQLData(“SELECT * FROM AccountData WHERE userid IN (….)”);

We just sped up the retrieval of the data significantly, yet we just introduced a bottle neck on the datalayer. Our architecture requires that the data is located in a single location.

What if AccountData’s data is spread across many servers federated by userid? This means that userid belongs to a server, so the server contains a shard of the AccountData’s Data.

Now we are back to a O(N) where each query needs to be executed on the corresponding shard. The logical next step is to group queries per shard and run across them all. For instance


$multiShardIDs = $genericShard->getMultipleShardIDs($objIds);
foreach ($multiShardIDs as $shardID => $shardUserIDs) {
           if (stripos($orgQuery, " WHERE ") !== false){
               $query = $orgQuery." AND {$column} IN (".implode(',', $shardUserIDs).") ";
           }               
           else{
               $query = $orgQuery." WHERE {$column} IN (".implode(',', $shardUserIDs).") ";               
           }

           $shard_to_sql[$shardID] = $query;

           .... more stuff ....

Yet this is still a O(N) its just that N is smaller. Each query is still executed serially.
Let’s look at some stats of synchronous queries of SELECT 1; This query is executed across 35 shards and the timings are from PHP point of view.

Field	End Value	Start Value	Delta
ru_oublock	0	0	0
ru_inblock	0	0	0
ru_msgsnd	0	0	0
ru_msgrcv	0	0	0
ru_maxrss	0	0	0
ru_ixrss	0	0	0
ru_idrss	0	0	0
ru_minflt	9872	9865	7
ru_majflt	0	0	0
ru_nsignals	0	0	0
ru_nvcsw	11344	11114	230
ru_nivcsw	977	968	9
ru_nswap	0	0	0
ru_utime.tv_usec	865054	849053	16001
ru_utime.tv_sec	16	16	0
ru_stime.tv_usec	556097	552097	4000
ru_stime.tv_sec	1	1	0
Total Execution Time			0.18323707580566

As you can see, to execute this from PHP it took 100 ms, 100s pages reclaimed and 200s voluntary context switches to query 35 servers.

Now let’s look at Asynchronous execution of SELECT 1; // the query generation is from PHP yet the execution is performed on a server that executes the query in parallel

Field	End Value	Start Value	Delta
ru_oublock	0	0	0
ru_inblock	0	0	0
ru_msgsnd	0	0	0
ru_msgrcv	0	0	0
ru_maxrss	0	0	0
ru_ixrss	0	0	0
ru_idrss	0	0	0
ru_minflt	9131	9121	10
ru_majflt	0	0	0
ru_nsignals	0	0	0
ru_nvcsw	3891	3889	2
ru_nivcsw	290	290	0
ru_nswap	0	0	0
ru_utime.tv_usec	596287	596287	0
ru_utime.tv_sec	4	4	0
ru_stime.tv_usec	460028	460028	0
ru_stime.tv_sec	0	0	0
Total Execution Time			0.019363880157471

As you can see from the table above executing the query asynchronously produced results with less context switching, less pages reclaimed and almost 10 times execution improvement over the synchronous query counterpart.
How is the asynchronous query executed? Lets take a look at the figure below.

So A user comes through the firewall / load balancer with a HTTP Request to the www pool that runs PHP. PHP now makes a CURL request to the Async Shard Servers (through a LB same LB different PORT). The HTTP Request to the Async Shard Server contains the SQL we wish to execute. The Async Shard Servers has a thread per shard and executes the request in parallel. The results are merged and sent to the calling CURL process via JSON. The returned JSON is then converted into a PHP object. This is a typical three-tier environment.

When having to query multiple servers using an Asynchronous Tier is dramatically faster; in fact its as fast as the slowest server. This is the main sticking point of why asynchronous queries are faster then synchronous queries (in this context) since the total execution time for serial queries is the SUM of all the query execution.

The current version of the server is used for Friend Query execution across the datalayer. Its been solid for a few months now, and I'm currently getting permission to release it as an Open Source Product. The features this server contains:

Lightweight

CPU bounded

Scales Linearly

A Timer Thread to keep the database config up to date in memory and fetching the config from PHP so if PHP changes connections to the shards so does Java

Uses Java-6 Executor Service

Merges the result set prior to sending it to the calling process

Communicates via JSON

Uses MySQL Connector/J

Supports a high concurrency

Optimized thread usage

Steps I take before upgrading mysql

2009-10-09T15:34:00.001-07:00

I am not a fan of upgrading mySQL unless I need to. I am of the mind if it is not broke don't fix it, but when I do upgrade I follow these general steps.

If I have run into a mySQL bug, I look to see if that bug is fixed by searching the mySQL bug database.

If I've notice a performance bottleneck, I look to see if the performance bottleneck has been fixed by searching the same database.

I will NOT upgrade to the latest and greatest version of mySQL (5.4) I stay within my branch (5.0).

These are my three general motivations that drive my upgrade decisions. Anytime I upgrade I also make a list of things that might affect my environment for the stuff I use.

Check the change log
Ignore all the NDB changes... I don't use it and that's the majority of fixes. This is also, why I do not use it.
List the changes that will affect the production environment
Deploy the version that I picked on a few servers running my original config
Do data corruption tests (make sure my checksum scripts return the same data)
Verify that the problem I'm trying to fix is fixed
Deploy to more boxes
Let the new server bake for a period of no less than a week
Deploy everyplace

So now, I'm upgrading from 5.0.56 to 5.0.86. What I'm trying to fix is mysql memory overhead at high levels of ram.

For instance, I have a slew of 48GB boxes. I set the bufferpool to 40GB; the OS uses 1 GB of memory (roughly) leaving an overhead of 7GB for the system cache and various spikes of sort buffers. Over a period, I see that mySQL will consume and hold onto 47GB of memory for an unknown reason even with some tight my.cnf settings. (I'm certain they are tight since I know what each buffer does). Therefore, testing some later versions of mySQL we found that these later versions do not grow past the settings defined yet performs the same.

Next, since I decided that upgrading is a good solution, now it’s time to list all the changes that fixes things.

5.0.58 - INNODB performance fix
5.0.60 - various problems that I should be affected by but havn't noticed so it’s fair to assume that said problems were introduced after my build.
5.0.62 - nothing major noticed the sp releases that's why I wait.
5.0.64 - nothing major
5.0.66 - security fixes and fixes to fix the bugs introduced from this build.
5.0.67 - two INNODB performance fixes and crash bug fixes.
5.0.68 - changes show status and fixes an innodb crash bug.
5.0.70 - fix another INNODB crash bug and security fixes
5.0.72 - more general bug fixes
5.0.74 - more stuff I don't care about
5.0.75 - stuff given to Enterprise users now in community
5.8.76 - more bug fixes that I do not need
5.0.78 - more bug fixes I do not care about (run MS Access on windows not mySQL)
5.0.80 - problem with error messages for concurrency limits that caused an assert failure
5.0.82 - Fixes to fix fixes for this build.
5.0.83 more minor fixes that I don't seem to have a problem from
5.0.84- more bug fixes for INNODB and latches
5.0.85 - looks like windows fixes
5.0.86 - fixes that I'm not having problems with

Therefore, overall, upgrading should give me a boost in performance. My own internal testing sees some tighter memory usage, even though this is not fixed explicitly, the product has matured overall so I can account for the reduction in memory to that.

Nagios Event Handlers - Love them

2009-09-09T11:30:00.001-07:00

What is Nagios? Nagios IMHO is the best Open Source monitoring system out there. It supports hosts checks, a level to determine on a host level if a box is considered "up". It supports service check, a level to determine if a particular service such as mySQL is up. It has features to log all events to a flat file or to a DB. It can notify you when a service is in a warning state, error state or unknown state.

For the purpose of this article, I am going to talk about handling events such as a clearing up swap.

First, let us look at some configuration of Nagios. We are going to define a command, then service acting on that command. Let us assume that the nagios install is in /usr/local/nagios.

Therefore, in /usr/local/nagios/ a few configuration files are key:
- /usr/local/nagios/etc/objects/commands.cfg - the command file where the checks are defined
- /usr/local/nagios/etc/hosts/*/hosts.cfg - the services file where the checks are defined for execution based on other directives in this file.

A command:


# 'check_local_swap' command definition
define command{
        command_name    check_local_swap
        command_line    $USER1$/check_swap -w $ARG1$ -c $ARG2$
}

This says that check_local_swap executes check_swap with a warning threshold of $ARG1 and a critical threshold or $ARG2

Next when defining a service for a host


define service{
        use                             generic-service; Name of service template to use
        host_name                       dbfacebook34b ; hostname
        service_description             SYS:Swap ; what shows up in alerts
        is_volatile                     0 
        check_period                    24x7 ; threshold when to check (all the time)
        max_check_attempts              4 ; threshold to check before marking state
        event_handler                   handle-swap ; handle an event (another command)
        normal_check_interval           5 ; in seconds
        retry_check_interval            1 ; only try once before reporting the state
        contact_groups                  itops ; contact group to send notifications to
        notification_options            w,u,c,r ; need to look this up for all defs
        notification_interval           600 ; retry sending notifs every 8 mins
        notification_period             24x7 ; keep sending them
        check_command                   check_nrpe!check_local_swap!80%!55% ; execute the event handler and warn like hell
}

Lots of goodies as you can see. Let us look at the event handler


define command{
        command_name    handle-swap
        command_line    /home/scripts/handle_swap.pl
}

This means execute this script whenever any event for swap occurs (I decided to make this simple and not put a threshold on this).

What does handle_swap.pl do - well it’s a perl script that looks at free memory and if only a few 100K of swap is in use, swapoff -a; swapon -a;

In this case, it is a bit safe to do this. Why do this? Why not just turn of swap. I have talked in depth about this subject-but for a minor recap. Linux needs swap else, kswapd will freak out. Swap in DB's is bad so I clean it up automatically since O_DIRECT on my SAN is not an option.

Why not just run a cron job? Nagios keeps a log, I like to review what is happening from a central location, and nagios is freaking COOL.

Back From Vacation and Man do I feel great.

2009-08-21T11:02:00.000-07:00

For the first time ever I took a two-week vacation. How can a person who has 100s of database servers, 100s of web servers, and a system that supports 100K tps across 20 TB of data is able to take a vacation? Easy, I have a great team that is very competent in managing the platform by following our cookbook routines and guidelines for new application interaction with the databases.

Where did I go? I went to NYC-stayed in Tribeca and only ate Pizza, Hotdogs, White Castle and Hala Food from vendors that are nearly on every street corner. I also lost 6 pounds! After three months of half-ass dieting to prepare for my Pizza diet, who would have known that just going for it was the key to success! How did I do it? Well, first I walked to Little Italy, bought 2-4 slices, ate them, and then walked around the city. That is it. As a Database Dude, I do not do much walking except for when I am too tired to run on the treadmill for 20 mins. I was averaging around 10 miles a day for a 12-hour period walking around NYC taking pictures, enjoying the sites, eating, drinking, and then repeat. I even walked from Canal Street all the way to the Staten Island Ferry just because. Man it was great.
After NYC, I went to Puerto Rico for the second time. My family and I had a freaking awesome time. We went to the only US National Park that is a Rain Forest. Went to Vieques to the Bio Luminous Bay, where the single cell organisms light up when kinetic energy is applied to them. Went to Culebra, to Flamenco Beach – which is off the hook: much better than any other beach I have been to. Drove around the entire Main Puerto Rican Island stopping off in Rincon and eating every 3-4 hours.
I am fully rested and feel like a 20 year old again. So, expect a lot more posts. I think the order will be:

Migrate 20 TB of data into a new Format without downtime.
How to make Friend Queries work with a database.
Throughput increase from Asynchronous Queries.

mysql UC 2009 Talk

2009-06-29T13:29:00.000-07:00

Scribe is a bit buggy with displaying this presentation:

Scaling a Widget Company

PHP mysql connect and using APC to recover

2009-06-11T16:06:00.000-07:00

So, when you connect on the fly to a database your subject to a variety of issues, like when the db is not available and when the db does not have a route.

One of the main reasons why a dev may want to connect on the fly is because they have too many front ends to hold a persistent connection on the backends. Since mySQL does not use libevent, holding open threads to mySQL is much more costly. Threads == Memory.

But, that's here nor there. The main purpose of this post is to talk about how to recover from failed connections that block apache threads.

Common Failures:
No route to Host
Flapping NIC
Locked Tables
Recovering from a Crash
more of the same.

My Environment:
I have a bunch of webservers (200+) that all have 300 possible threads (60000 possible connections to a single DB) behind a load balancer that uses the LB least connections protocol to distribute load across all 200+ webservers.

Since PHP is used in a stateless mode (no guarentee that the same user will hit the same server), I have to have some way of telling all other apache procs for said box that a server is down. I use stateless on the fly connections, so each apache proc will test the connection.

Here in lies my most common problem. If a box in the backend dies, all apache threads will block for a predefined time-out.

In /etc/php.ini (it could be in /etc/php.d/mysql.ini) I set

mysql.connect_timeout = 5 // the default is 60

In my common_db class when connecting to a database, the connect routine returns a database handle object


if (PEAR::isError($dbh) && $delta_to_connect >= 5){
      
      #
      # mark ip as dead for 15 min
      #
      
      apc_store($ip, array('DEAD'), 900);
      return false;
}

return $dbh;

Now PRIOR to calling the database connect code I check to see if the IP is up.


$status = apc_fetch($ip);
if ($status[0] === 'DEAD'){
   return false;
}

... do connect ...

But, there could be a variety of issues that can call false positives, like network flaps, someone blocking the db for some time etc. So, I'll allow one request .1% of the time bypass the status check to try again.


if ($status[0] === 'DEAD' && (mt_rand(0, 1000) != 1)){
    return false;
}

But, if your app can't connect to the DB aren't you down anyway?

Yes, although I don't have to restart all the httpds.
Also each DB has a redundant pair to when returning false, the app code will try the other set of servers.

I have a variety of methods to deal with these scenarios but this is the quickest to implement.

Multi Direction Sorts and avoiding a file sort

2009-05-19T12:26:00.000-07:00

There are two PRIMARY directions to sort data in SQL: Ascending (ASC) and Descending DESC.
When these two sort definitions are put together in a single statement a filesort is produced.

Why do we want to avoid filesorts?

Filesorts are bad. 1st they tickle a thread based buffer called sort_buffer_size. Additionally filesorts reads the data twice, unless max_length_for_sort_data limit is reached and as a result the Filesort runs slower to reduce disk I/O. If you want filesorts to run faster at the expense of the disk increase the default max_length_for_sort_data. You can read the filesort algorithm here.

So, here is an example


 CREATE TABLE `ABCD` (
  `A` int(10) unsigned NOT NULL default '0',
  `B` int(10) unsigned NOT NULL default '0',
  `C` int(10) unsigned NOT NULL default '0',
  `D` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


mysql> explain SELECT * FROM ABCD WHERE a=1 AND b=1 ORDER BY c DESC, d ASC\G      

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ABCD
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 2
        Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

Notice the filesort? So how does one get around this filesort?

Well

Let's define some roles for columns C and D. C is the parent while D is the child.

We want all the latest parents (C)

We want all the oldest children (D)

We require pagination of all the PARENTS (show 10 parents per page) so Queries like this is PRODUCED


SELECT * FROM ABCD WHERE A=? AND B=? ORDER BY C DESC
explain SELECT * FROM ABCD WHERE a=1 AND b=1 ORDER BY c DESC LIMIT 10\G   
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ABCD
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const,const
         rows: 2
        Extra: Using where; Using index
1 row in set (0.00 sec)

Now

FOREACH($C_parent as $i => $c_id) {

$C_parent[$i] = SELECT SQL_CALC_FOUND_ROWS * FROM ABCD WHERE A=? AND B=? AND C=$c_id ORDER BY D ASC LIMIT 1;

}

So, we changed 1 query into 11 queries (10 parents per page) to make the page load happen faster, by getting rid of the filesort.

What 11 queries is faster then 1? Yes, for this case it is. The reason is because filesorts are SLOOOOW, they chew up a lot of limited resources and they should be avoided. I've see filesorts take close to 50-60% of the query time.

Restoring a backup of a Circular Replication config

2009-04-23T12:23:00.001-07:00

So say you have two mySQL servers called A and B. A and B are in a circular replication ring meaning

A replicates from B
B replicates from A

In addition, log-slave-updates is on, so if one server of either A or B goes down you can recover all the data from either side.

Therefore, here are some steps.
Take a snapshot of a server in the ring, with your favorite backup tool (mine is ibbackup)

When either server A or B dies, restore from said backup. Here are the steps. Let us assume server A goes down, and backups are done from server B.

restore the backup of server B to server A

On server-A turn off log-slave-updates (you will see why on the next line)
Add replicate-same-server-id on server A to my.cnf

CHANGE MASTER TO to the position and file of the binary log that is reported from your tool on server-A.

IF the binary log did not get corrupted on server-B your cool, else flip the binary log to the next binary log from server-A on server-B.

Let server-A catch up
Stop server-A
Add log-slave-updates on server-A
remove replicate-same-server-id on server A
Start server-A

done.

Recover from No-Route to Host or Storage Engine Plugin Crashes

2009-04-08T13:11:00.001-07:00

There are a finite set of cases where mySQL clients will hang on a connection-for a small period of time (seconds) or indefinitely. Most notably no-route to host causes a timeout to occur which in most clients are on the order of seconds to recovery.

In a web environment if a database is connected to on the fly, each connection made should take milliseconds-when the net is healthy. However, when a database server crashes a timeout for each connection takes seconds and there are cases where connections can just hang (recovery of INNODB as an example). For instance have you ever had a ssh session hang and a kill (SIGHUP..) does not work? This happens for mySQL client connections too.

I want the front ends to recover gracefully from a no-route to host, or more notably a hanged connection condition. I want to avoid that damn timeout all together on stateless connections. Why? Because if you use up all your worker httpd threads requests Fail-things crash, bosses get mad, everyone wakes up and you have a meeting about it later. So what are ways around this?

Use a Load balancer

This is cool and all, but is expensive since two or three are needed for HA purposes. In addition, this increases the complexity of managing servers, and most LBs are optimized for HTTPD traffic, not raw TCP traffic.

Use a memcache layer to keep DB state

This is cool as well, but the state server, which collects the state of your servers and updates memcache, is now a single point of failure-and a lot of time is spent to make this resilient. Not to mention all clients across Programming languages (bash scripts, Java, PHP, Python, etc) need to have access to this memcache layer and logically handle the connection the same. This is doable, but there is an easier way.

Use IRON DNS:

IRON DNS is a term I use to build an HA Resolver. IF a box fails a health check, a nagios event handler can tell IRON DNS to set the domain names IP address to 127.0.0.1 or a routable interface that produces a Connection Refused (111 instead of 110).

For an internal network, all database entries should be able to fit in memory. All resolves should take less then 2ms, and updates to DNS entries can happen in just a few seconds across your entire farm. The only drawback is if the DNS server fails to respond your site is boned. Making DNS Failure proof is rather easy-which is another post entirely.

For my solution, I am using DNS. This allows me to recover servers in a shard on different boxes without having to change code. This also allows my environment to recover from blocked I/O events that can spike load on the front-end that make requests slow.

No Route to Host
INNODB recovery
Disk Fails (disk fills up)
Switch Fails / Flaps
Plug-in storage engine crashes, yet mySQL is up
DNS Resolve fails

If you have a better method, or want to advise me on flaws I should watch out for, drop a comment.

What do you think about adding ZLIB to memcache storage

2009-03-31T11:05:00.000-07:00

Memcache is a fantastic Hash table-very fast and one of the great successes of Brad Fitzpatrick-who in my opinion has done more for the open social movement as an individual then anyone else. I use memcache quite extensively, now I am thinking about adding ZLIB native to compress the value of each key-much like how INNODB does with the Barracuda file format. The theory is with a CPU hit, we can store more data per memcache instance. I've talked to the Northscale guys and they love the idea. What do you think?

Update: Well what do you know

http://us3.php.net/manual/en/function.memcache-setcompressthreshold.php

for PHP for instance compresses the data on the client side.

There still might be some value compressing the data on the server-side, but now I'm not as motivated.

What might be a good alternative is to compress keys into 8 byte longs in memcached automatically instead of the actual string that can be huge. To give some more detail,
8 byte longs is a 64-bit int. A string can easily be converted into a big int by bit manipulation - and the address space is huge so key conflict is effectively removed.

Cloud computing without spending money

2009-03-23T10:38:00.000-07:00

Cloud computing is the big thing now days, weather you are an app developer using EC2 or the Google APP engine, or a new company trying to build your own cloud product. If you are hosting, or using a platform it costs money. I hate to spend money, especially money that is for my company. If I find an idle server, I use it to 100% utilization (prior to the saturation point).

I needed to build a new application that periodically crawls a website to update various lists. Building a crawler is expensive, especially from scratch. First, you have to define the amount of lag that is allowed from the crawl copy and the real copy. Of course the Project Manager does not want any lag, all events must be caught and near real-time without overloading the source of the data-but I am not hating, it is a challenge. Next, what technology to use, what language to write the app in-what considerations are left to be defined? How does one crawl Gigs, Tera, and amount of data in a guarantee period? On top of that, how much additional hardware is this going to cost. In addition, to be a cloud it needs to have an API so app developers can set, and get consistent data in an expected period. This is a lot of freaking requirements.

Therefore, to solve this issue, I know that mySQL will store the data, but getting the data is the hard part. This is what is going to cost money, lots of it. I looked around common architectures and found that nothing would do what I wanted to do in a cost effective manner. So, I designed my own using Seti @ Home as the basis for the design.

Get to the point already Dathan:

I have turned ever user who views my applications into a collector, using idle bandwidth without knowing who is collecting the data. My user base on spare cycles will fetch a feed of my choosing, and sends that data to my servers without any personal information. Instead of using an Amazon or Google service, I have turned my user base into a cloud to service their needs.

It is rather awesome-I must say. I am able to service the needs of more than 60 million users at the cost of development time, and NO NEW HARDWARE. The cloud does not have to be a service provider-it can be the end user as long as the end user is not impacted by the requests. BTW the team that I manage is freaking awesome-they built my vision with trial and error and a hand waved spec.

Currently the system scales as long as there is enough end users. If I lose all my users then well I am boned, but to support the feeds all I need is 100K nodes at the current rate. With 60 million end nodes, I am cool.

Imagine if Google with Adsense used this install base to tell Google if the data has changed for an arbitrary web address. All it needs is a few people to hit the same url, inform Google that the web address in question has a different checksum, then at that point Google’s crawlers can go fetch it, instead of constantly crawling data that doesn't change. Google would be able to reduce overall server cost significantly, if it just knew what data has changed instead of guessing what data has changed.

By next years Velocity conferance I hope to have a full disclosure on what technology my team used, how my team get around cross domain issues, and how to compute checksums to validate the data.

PS - I designed this, with my team we made it much better and one person implemented it and owns the product from this point on.

Walking an INNODB table Fast

2009-03-12T13:58:00.000-07:00

Walking a table means, traversing each row, commonly used in building queues, fixing data, or dumping a table. I've recently ran into a problem-caused by an assumption, where walking a table was taking way to long using the method


$pos = 0;
do {

    $result = SELECT col FROM TABLE LIMIT $pos, 1000;
    $pos += 1000;
} while ($result);

The assumption was since INNODB uses a cluster index, this would traverse the table using the PRIMARY key. This is not the case, its not a problem in INNODB but a bad assumption, that I fell victim to. A table scan to each $pos occurs producing a Big-O of N^2. So, when the query:

SELECT col FROM TABLE LIMIT 1000000, 1000 is executed mySQL will scan all the rows up to row position 1001000 and for each subsequent iteration.

This is SLOOOOW. IMHO since the table is sorted by the primary key, mySQL should optimize this case - but it does not and will not. So, to walk an INNODB table fast, and keep liner time or a Big-O of N an alternative is


$last_id = 0
do {

  $result = SELECT col FROM TABLE USE INDEX(PRIMARY) WHERE pkey_part > $last_id LIMIT 1000
  $last_id = $result[count($result) - 1]->pkey_part

}while($result);

This dumps a table very fast, almost as fast as doing a count(*) on the PRIMARY KEY.

Another method is to

SELECT col INTO OUTFILE "/dir/file.ids" FROM TABLE;

but the data is local to the database - thus the need for the application to grab data. Another draw back of this method is that the dump produces more disk IO then walking a table off of a key, slowing down access to this table.

In conclusion, even if the storage engine keeps the table order consistent like INNODB does, do not assume that LIMIT 100000, 1000 is equivalent to a file seek of position 100000, without telling the Optimizer to use an index.

Speaking at Mysql Conf 2009: Architecture and Technology, Cloud Computing, LAMP, Replication and Scale-Out

2009-02-09T15:32:00.000-08:00

I'll be going into detail what is Sharding, how to Shard, pitfalls of Sharding, performance/throughput gains, shard roles, and performance scaling in general. I hope to make this the most comprehensive talk to date on the subject in 45 min.

The topic is called Scaling a Widget Company. I'll detail how I setup the data layer for Rockyou. How many transactions per second Rockyou is at, what the infrastructure is comprised of, how 99.999% uptime is achieved and hopefully get into BCP which I probably will not have time to go over.

If you want me to focus on specific aspects on the subject of shard'ing let me know and I will :).

How to reduce load and response time in PHP in five minutes

2009-02-03T13:56:00.000-08:00

Request time is proportional to server load. If the application response time is big so will be the server's load. To reduce server load, reduce the wait time in the application's response time to serve the request. Below are some steps that I took to remove 1.8 ms overhead on every request to my web server farm.

Tools Needed:
vi
strace
top

Use vi to look at your include path in php.init.
Next use top to find which apache process is consuming the most cpu resources.
Use strace -p [TOP HTTPD PROCESS] -T (-T is for deltas).

In my example the include path is
/usr/share/pear:/usr/lib64/pear:.:/var/www/html/httdocs/


lstat("/usr", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000033>
lstat("/usr/share", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000034>
lstat("/usr/share/pear", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000034>
lstat("/usr/share/pear/ams", 0x7fbfff1690) = -1 ENOENT (No such file or directory) <0.000033>
open("/usr/share/pear/ams/include/FreqCapInfo.php", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000036>
lstat("/usr", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000037>
lstat("/usr/lib64", {st_mode=S_IFDIR|0755, st_size=65536, ...}) = 0 <0.000034>
lstat("/usr/lib64/php", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000034>
lstat("/usr/lib64/php/pear", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0 <0.000038>
lstat("/usr/lib64/php/pear/ams", 0x7fbfff1690) = -1 ENOENT (No such file or directory) <0.000037>
open("/usr/lib64/php/pear/ams/include/FreqCapInfo.php", O_RDONLY) = -1 ENOENT (No such file or directory) <0.000038>
open("/var/www/html/ams/include/FreqCapInfo.php", O_RDONLY) = 24 <0.000043>
fstat(24, {st_mode=S_IFREG|0775, st_size=6707, ...}) = 0 <0.000031>
stat("./ams/include/FreqCapInfo.php", 0x7fbfff4778) = -1 ENOENT (No such file or directory) <0.000037>
stat("/usr/share/pear/ams/include/FreqCapInfo.php", 0x7fbfff4778) = -1 ENOENT (No such file or directory) <0.000038>
stat("/usr/lib64/php/pear/ams/include/FreqCapInfo.php", 0x7fbfff4778) = -1 ENOENT (No such file or directory) <0.000036>
stat("/var/www/html/ams/include/FreqCapInfo.php", {st_mode=S_IFREG|0775, st_size=6707, ...}) = 0 <0.000040>
close(24)                               = 0 <0.000033>
mlock(0x552b876be0, 24)                 = 0 <0.000092>
mlock(0x552b8df910, 10624)              = 0 <0.000038>
munlock(0x552b876be0, 24)               = 0 <0.000036>
munlock(0x552b8df910, 10624)            = 0 <0.000032>
mlock(0x552b876be0, 24)                 = 0 <0.001707>
mlock(0x552b8df910, 10624)              = 0 <0.000009>
munlock(0x552b876be0, 24)               = 0 <0.000007>
munlock(0x552b8df910, 10624)            = 0 <0.000007>

Looking at the strace, 15 unneeded system calls are made on every request, each request roughly takes 30 micro seconds, for a total of a few ms wasted on every request. False positives are adding overhead to ever requests since the include path is not optimized. A Bloom Filter in Shared Memory would be perfect for for this part of PHP-but that's besides the point.

So, change your include path
.:/var/www/html/httdocs/:/usr/share/pear:/usr/lib64/pear

For my example I changed the include path to the above. In my environment we don't do many PEAR loads, so it makes sense to use our directories 1st.

By doing this 15 erroneous system calls have been removed. Note: If you use the php feature __autoload make sure to protect your calls with file exist or you may be doing a require once on a file that is in a different directory which is a PHP fatal Error.

UPDATED: Some more goodies to reduce load on PHP boxes:

For PHP 5.2 there is a nice new feature that you can play with

realpath_cache_size. This is a directive native to PHP by default it's set to 16K, this means that PHP expects there to be very few files, but big files. In most environments I have been in this is not the case. The case is there are a lot of files, a lot of directories thus 16K is not enough.

I've tested a few values but 128K seems to be my sweet spot.

realpath_cache_size = 128K

Shard Types && innodb_io_capacity, innodb_write_io_threads, innodb_read_io_threads

2009-01-27T15:30:00.000-08:00

Another project that I am doing introduces a new shard type. First, think of a Shard as RAID-10 on your database layer. The data is striped across N servers and mirrored for failure recovery.

Shard Types are Shards that serve specific purposes. For instance, I have an archive shard. This shard keeps data the is rarely ever read on even cheaper hardware-with slower disks and the data is naively compressed using the innodb_file_format=Barracuda
option. If and/or when a person requests data that is archived, my software layer detects this case and migrates the archive data from the super crappy RAID-5 servers to the less crappy RAID-10 servers with 32 GB of RAM, all within 5-10 seconds.

Another shard type is the sub-shard. I use this terminology to define a different way of federating data for a predefined global object_id. A predefined global object_id is what you are federating by, for instance userid.

userid X maps to Shard 3

What if you had an application that is expected to take up 8TB of data? Sticking all of userid X's data on a single shard is very costly since current size of the cluster + 8TB means more servers are needed and is costly (more data, larger ranges, slower the avg query speed). So why not put it on another set of servers that have big slow'ish disks, i.e. the Sub-Shard.

Now for userid X

userid X maps to Shard 3
userid X maps to SubShard 1

Since some shard profiles do not require 100% uptime, I can run builds of mySQL that is cutting edge.

Using the Percona 5.1 build, I ran across new tweaks for XtraDB. The patch is from the Google Patch made by Mark Callaghan's Google Team. Mark's architecture is purely disk I/O bound, and from his tests, INNODB does not use I/O effectively. He added a bunch of code dealing with the I/O performance bottlenecks that innodb native has. The three new tweaks addressing the I/O bottleneck specifically are


innodb_io_capacity = 100 // If running without the patch this is the equivalent default behavior
innodb_write_io_threads = 1
innodb_read_io_threads = 1

From what I gather, this means that DEFAULT INNODB uses one write thread 1 read thread and will only work on 100 pending iops at a time-which is probably why INNODB takes sooo long to shutdown since by default 90% of the buffer pool contains dirty pages.

If your servers have a large innodb_buffer_size, on the order of a few Gigs and the server has many spindles (greater then 1) then increasing these params may help your application purge dirty pages faster. Be warned; do not increase this to high. If your box has a lot of pending I/O and this new code does not have the bandwidth to flush based on your settings, the box will freeze. That means you will have to remove traffic off of the server, let the pending io finish, lower the settings and restart.

So, what is a good size to set this to?

Percona says 100*Number of spindles you have on the db server. That seems a bit high for me. I would say 100*Number of spindles / 2 - to start out with, then over time ramp it up.

Currently I am about to run 10 Shard or 20 servers with the Percona build. This will purely I/O bound load.

Optimizer tricks

2009-01-07T18:16:00.000-08:00

When you write good SQL, that use indexes properly there is one more obstacle that can slow down your app. The mySQL optimizer. From versions 3.23 to 5.1 the optimizer has been a problem for me. In mySQL 6.0 SUN/mySQL has resources improving it.

I wrote a post detailing how to pick indexes to get the most out of mySQL here.

Here is a post about the mySQL optimizer and what you can do to speed up your SQL SELECT statements.

What I would like to share with you today, is that UPDATE and DELETE statements can also use optimizer tricks that SELECT uses. Its not documented on the mysql.com but it is possible to do something like


UPDATE [YOUR TABLE] USE INDEX(`your_index_name`) SET col='val' WHERE [columns that satisfy your index].

To see if you have problems with your UPDATE statements taking to much time I recommend 1st running INNODB as your storage engine since SHOW INNODB STATUS will indicate what index your long running UPDATE is using. This can be achieved by looking for /RECORD LOCKS space id.* index/ of SHOW INNODB STATUS.

Another indicator that you might need to force the optimizer to do the right thing is to track "Deadlock detected" from concurrent update statements; look at the where clause of your UPDATE statement.

Next solve your issues by telling mySQL to use the correct index when setting exclusive locks via the USE INDEX statement above.

Hope this helps.

Logging to DB after PHP is done generating content

2008-12-16T18:07:00.000-08:00

Oh how I love register_shutdown_function of PHP. This bad baby will execute at the end of the scripts in call order. So, how is this useful?

Say you want to log some action, but that logging DB is on another server. Additionally you do not want to take into account that writing to the other server may break your transaction because of timeouts if logged in the middle of a transaction. Another use case: you do not want to change a bunch of code in different places to batch all logging routines up.


public function __construct($platform){
        parent::__construct($platform);
        register_shutdown_function(array($this, 'afterProcess'));
}


public function afterProcess(){

    foreach($this->getDataThatChange() as $key => $values){
             $insert_data[] = $values;
    }
    
    $this->getDBClass()->DB_logserver_insert_query("LoggingTable", $insert_data, 'delayed');


}

The function afterProcess will batch all the changes and do a bulk insert into a myISAM table so it can used the DELAYED functionality.

This is done outside of all transactions, and at the end of the script as the data is returned.

But, why not just use __deconstructor() in PHP? I want it to happen before the deconstructor is called.

http://us3.php.net/manual/en/language.oop5.decon.php#76710

I have been using register_shutdown_function for years now, especially to clean up connections at the end of script execution. Since someone asked me if this was possible in PHP, I decided to post this quick usage of a cool php method.

INNODB Shared Locks, Exclusive Locks and INSERT INTO SELECT FROM

2008-10-29T10:22:00.000-07:00

I wrote an application that is able to send out 3-8 million messages an hour with only 10 CPU's. This application is a part of an Offline Task system that scales linearly.

How is this done, I'll go into detail hopefully at the mySQL conference if they accept my proposal.

The scope of this blog post is to go over building the "Task Queues". Currently I have 13 Queues, one queue for each Shard that I run. The data is federated by user or randomly federated with a GUID that lives as long as the job. A request came in to add 20 million jobs to the queue all at once. The problem is with this list, will I cause deadlocks in innodb as I add the jobs to the queue as one transaction? Can live traffic still write to this queue?

To verify that Deadlocks will not occur - having an understanding about how locks work in INNODB is key. I suggest reading this page.

To build the queue I dumped the data source by


SELECT identifier, 16 INTO OUTFILE "/data/mysql/BuildQueue.log" FROM SOURCE_TABLE WHERE CONDITION.

The isolation level is REPEATABLE-READ; I'm setting a shared lock to get the most current version of the data. Writes are not blocked.

Next:

I create a table on each shard where the queue is located.


CREATE TABLE IF NOT EXISTS OfflineTasksHold (
    object_id bigint(20) NOT NULL DEFAULT 0,
    object_type smallint unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY(object_id,object_type)
) ENGINE=INNODB;

Then I issue a command on each Shard


LOAD DATA INFILE "/data/mysql/BuildQueue.log" INSERT INTO TABLE OfflineTasksHold;
START TRANSACTION;
INSERT INTO OfflineTasks (object_id, object_type) SELECT * FROM OfflineTasksHold;
COMMIT;

Each shard is getting around 4-5 million rows, while accepting real-time traffic of 20-60 tasks a second to the OfflineTasks table. The OfflineTasksHold table does not have any real-time requirements and is solely used to keep the queue in Primary Key order, plus there is a CHANCE that LOAD DATA could set an exclusive lock on the OfflineTasks table-shutting down adding data to the table by the live site. The INSERT sets an exclusive lock on the rows that are being added, so the Offline Task Sheppard - the process that pops tasks off the queue is blocked for a small period - which is acceptable. Why are they blocked? Well, the massive insert sets an Exclusive Lock. The Sheppard is trying to grab the rows that are locked waiting up to 50 seconds, until innodb_wait_timeout is reached. This condition is acceptable. All other inserts are able to go into the queue without a slow down.

In summary, the job queue is built and can be automated with confidence knowing that death to the various app will not occur. Processing slows down for a bit but speed right back up.

mySQL Disk Benchmarks with Sysbench. When will mySQL / INNODB stop scaling?

2008-10-20T10:54:00.000-07:00

Today's "commercial quality disks" are amazing but they follow the same limitations as yesterday's disks. mySQL scales very well, but disks do not. So if you're IO bound when will your expectation of speed fail?

Test setup:

DELL 2950 PERC-6 HWRaid BBC 6 DISK 15K RPM 3.5" RAID-10 256K stripe across two channels-using WRITE THROUGH CACHE on mkfs.ext3 -T largefile4 Linux Filesystem.

The theory is that the outer part of the spindles is the fastest, and the inner portion is slower - since the outer is where the data starts (thanks for the info Benjamin Schweizer). Thus one can conclude that the more disk space your application(s) use the slower the throughput, since the heads have to move more. Brad F. my co-worker did a benchmark to prove this. Our goal is to find out at what is the saturation point if our expectation is to have 22 MB / sec of random access.

Why do we want 22MB / sec of random access throughput? We want to guarantee a certain level of performance when adding new apps to a common backend-which is I/O bound: we need to know when things will break.

Here is what Brad found: Total disk size for our RAID-10 setup =~ 800G. What point does it FAIL to achieve our expectations of sustained 22MB/s?


rndrw test across 100G test / 750G LV =~ 35 MB/s # outer part of the spindles
rndrw test across 100G test / 300G LV =~ 32 MB/s # outer part of the spindles
rndrw test across 250G test / 300G LV =~ 24 MB/s # sweet spot
rndrw test across 350G test / 384G LV =~ 21 MB/s # saturation point
rndrw test across 750G test / 800G LV =~ 14 MB/s # waste of space

In conclusion these test show that even though a RAID-10 setup with 800G of space is available, the expected performance drops when data exceeds the sweet spot of 250G-300G of 800G usable-data array.

Disclaimers: There are many factors that can raise or lower the bar, like different file systems, different I/O schedulers, flushing. For my setups I like

Deadline I/O scheduler
256K Stripe
few inodes (don't need them)
ext3 since that’s what stable and available.

How do you know when you need more memcache servers?

2008-09-19T10:44:00.001-07:00

Let me first start of with the disclaimer, that I do not use memcache to scale, I use it to reduce latency. I'm of firm belief that the database layer should be able to handle the requests, while memcache is used to keep frequent requests returning in a consistent time frame i.e. reduce I/O spikes.

Capacity planning is key to making sure your site can serve the requests to users. If the site is slow, or down that is loss revenue in any revenue model used to monetize your product.

How to determine when you need to add more memcache servers.

The stats I look at are system stats and memcache stats.

Memcache is Memory / network heavy. CPU spikes are very low, and if the CPU starts maxing out that is probably due to some sort of network driver issue or huge context switching or large values stored in memcache.

So on the system side I look at vmstat


[root@memcached1 ~]# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0    208 207400  41452  50552    0    0    10   198    0     0  2  9 87  2
 0  1    208 207408  41452  50552    0    0     0    81 15617 17671  3 13 84  0
 0  0    208 207352  41452  50552    0    0     0    56 15508 17514  3 13 84  0
 1  0    208 207248  41452  50620    0    0     0   310 15295 16762  3 12 84  0
 0  0    208 207248  41452  50620    0    0     0    31 15512 17167  2 13 84  0
 0  0    208 207256  41452  50620    0    0     0     3 15925 18214  3 14 84  0
 0  0    208 207264  41452  50620    0    0     0     0 15456 16923  3 13 85  0
 0  0    208 207264  41452  50620    0    0     0   213 15782 17604  3 13 84  0
 0  0    208 207264  41452  50620    0    0     0    40 15860 18036  2 13 84  0
 2  0    208 207272  41452  50620    0    0     0   214 15926 18248  3 14 84  0
 0  0    208 207288  41452  50620    0    0     0    77 15781 17617  3 13 84  0

This server dedicated to memcache. The context switching is huge due to all of the constant requests-but we are talking about modern day CPU's which can context switch like crazy. The thing that is bugging me is that requests are starting to go into the run queue, not at a alarming rate but still this is an indication of some possible issue.

This is something that is graphed on ganglia. If the run queue on average starts increasing, there is some problem.

Next stats from memcache.


      /**
        * @desc get extended status from all servers
        */
        public function CacheGetStats(){

            if ($GLOBALS[cfg][disable_feature_memcache]){

                return true;

            }

            return $this->memcache_obj->getExtendedStats();
       }

I have a class called Cache which is a wrapper around memcache class calls. Cal Henderson would kill me if I was using classes at Flickr. Don't get me wrong I agree with Cal 100% but the environment I am in now requires classes-so I have to use it. The reason why we don't like classes is for another post.

So the output.


            [pid] => 17696
            [uptime] => 2748911
            [time] => 1221850214
            [version] => 1.2.2
            [pointer_size] => 64
            [rusage_user] => 135944.231335
            [rusage_system] => 420733.419798
            [curr_items] => 6012187
            [total_items] => 2362145406
            [bytes] => 4737438938
            [curr_connections] => 654
            [total_connections] => 4128179078
            [connection_structures] => 7293
            [cmd_get] => 12681552588
            [cmd_set] => 2362145408
            [get_hits] => 9880855733
            [get_misses] => 2800696855
            [evictions] => 0
            [bytes_read] => 2564412782739
            [bytes_written] => 12893067371405
            [limit_maxbytes] => 5242880000
            [threads] => 4

Notice on this server we have a good hit rate and no evictions. Yet looking at one server is not good enough, look at them all- the reason more memcache servers means more memory to store data for your application. The CRC32 hash that the PHP memcache client uses is not very even and some keys may be requested more.



            [pid] => 13956
            [uptime] => 4228079
            [time] => 1221850213
            [version] => 1.2.2
            [pointer_size] => 64
            [rusage_user] => 268369.193681
            [rusage_system] => 711491.537845
            [curr_items] => 5219411
            [total_items] => 3686853272
            [bytes] => 4751658935
            [curr_connections] => 675
            [total_connections] => 4154000955
            [connection_structures] => 9981
            [cmd_get] => 19489963453
            [cmd_set] => 3686853275
            [get_hits] => 15062084538
            [get_misses] => 4427878915
            [evictions] => 11210410
            [bytes_read] => 3908139025173
            [bytes_written] => 10744393525089
            [limit_maxbytes] => 5242880000
            [threads] => 4

Take a look at this server. The evictions are high, indicating that memcache needs to make room for new objects. This is not good, its an indication that the LRU is evicting objects out faster then their expire time. Additionally the memcache gets are much greater then the hits. This is an indication that memcache is not really working as good as it can.

But one server is not an indication that there is a problem. Looking at the system as a whole is to determine if a problem exists. My rule of thumb is if the 30-40% of the servers have a high eviction rate, its time to add 30-40% more servers or memory.

Now allot of this can be tuned by changing the slab size, but learning from John Allspaw, don't make a plan based on a possible gain, make a plan based on the current usage. Then if the possible gain works your golden.

How do you base your stats on adding more memcache servers?

DB Designer -> MySQL workbench

2008-09-19T10:44:00.000-07:00

I had a lot of good experience using fabforce's DB designer. Now that I upgraded to mySQL workbench, I have nothing but good things to say.

In 5 mins I was up and running after install. I took my mysqldump of my schema

mysqldump -d --no-add-drop-tables

Clicked File->Import->Reverse Engineer MySQL Script

Tada a picture of my schema.

Pictures are always nice to represent what your conveying to individuals who may not be as experienced with the db structure as yourself. Just for this feature alone I recommend using Work Bench.

Can't initialize function 'INNODB'; Plugin initialization function failed

2008-08-20T15:38:00.000-07:00

So running mysql-5.1.24rc with INNODB-Plugin-1a I ran into "Can't initialize function 'INNODB'; Plugin initialization function failed", and noticed a more serious issue with the whole plugin architecture.

Before without plugin if the engine died so did mysqld. Now, mysqld can stay up and accept connections. But what if you have 1000 mysql instances, and all of your Nagios checks where to test if mysqld was up and accepting connections? This entire test is now invalid. I now need a Nagios check to test if the plugin-engine is up, by doing a write to the engine.

Now to the cause of the issue:

Here is my condition. 5.1.24rc with INNODB-Plugin was killed by OOM killer (mysqld memory leak?). When it came back, my expectation was for innodb to go through a recovery process, it didn't. Strange. Then I got this message from replication.

Slave: Unknown table engine 'InnoDB' Error_code: 1286

So I did a show plugin command and innodb showed that it was installed. Since I'm out of ideas I thought that I would unload the various INNODB plugins.

Tada found the problem, I could not uninstall them something is wrong with the plugin. So, to make everything smooth for a reinstall of the plugin.

1. TRUNCATE mysql.plugins // I don't know if this is needed
2. restart mysql
3. INSTALL PLUGIN INNODB SONAME 'ha_innodb.so';

Now I have an error
InnoDB: mmap(26600275968 bytes) failed; errno 12
InnoDB: Fatal error: cannot allocate the memory for the buffer pool

Weird

I look at whats allocated. mysqld is consuming 11GB when nothing is loaded, and the various key buffers are sized very low. So, just to test a theory I lower the innodb bufferpool from 26GB to 19GB. Since 19+11 = 30GB and I have 32GB total.

1. start mysql
2. INSTALL PLUGIN INNODB SONAME 'ha_innodb.so';

Now it hangs. But, now this looks a familiar failure, I think its now recovering, so I look in the mysql error log and indeed it is recovering!

INNODB Plugin, mysql 5.1 innodb_file_format=Barracuda

2008-08-13T16:05:00.001-07:00

I put this bad boy into production, and its working great.

So, I had an app that was sticking data into S3 when the data was marked or qualified as warehouse data. The problem with this method was when retrieving the data from S3 it was not chunked but appended to. So pulling 100M over the wire, parsing, sorting and adding to the db would fail since the process consumed more then its allowed share of resources. Plus grab data from S3 has a very high response time, plainly put its slow.

So, since the warehouse data is prone to error-the main reason why I needed a new solution, I decided to use ZLIB compression that is native to Innodb's Barracuda build.

To install innodb plugin here is a good write up I will not duplicate it.

Configuring the plugin you must set in your my.cnf

skip-innodb
innodb_file_format=Barracuda
innodb_file_per_table=1

LOAD the INNODB plugin via a few commands and tada your done.

Next define your table with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

I use utf8 for everything, KEY_BLOCK_SIZE will define how much extra overhead the CPU will have to work, think of this as a compression level. I use 4K since some rough benchmarks I've done shows that this is the sweet spot for my INSERT rate + Select rate.

Finally I stick the entire dataset on a slow RAID-5 array. I use RAID-5 because when I want the data-I want to get it fast and put it into the production dataset real-time. I need the disk space and redundancy. The writes are not extraordinary and not very expensive from a I/O perspective. I'm getting 60% reduction in the INNODB table space. On top of that the format of the data is the same format as the production system. So I can pull this data fast write to the shard in a single httpd process. I can migrate archive data to production quality in less then a second for every 3000 rows. I qualify this as a win. We'll see how stable this is over the next few weeks.

So in conclusion, I've replaced S3 with a cheaper solution, faster solution, I put archived user data on cheap servers, and as a result the price per user goes down, since my capacity plan is based off a function of disk growth-on my fast disk cluster. Next I can store my backups on S3 instead of semi-live data :)

Capacity Planning, Architecture, Scaling, Response time, Throughput

2008-08-07T12:13:00.000-07:00

First of all let me start off saying that I learned a lot of Capacity Planning from two people. Jozo Dujmovic, and John Allspaw-who by the way is coming out with a book.

Capacity != Performance. You may have the capacity to do a bubble sort but a bubble sort is still a bubble sort.

Really to Scale you need to know when your application will break. I have a tool set to help determine what application is producing what SQL and use that to figure out which SQL is producing the most load on the system. Some common tricks I do is put the execution path automatically as a SQL comment, then sample the FULL Processlist to build a graph on what application, function, SQL pattern is the top load.

On top of that I use Ganglia to trend the use of each mysql box. Key metrics that I use to determine capacity.

From iostat -x

I/O wait
atime
svctm

If the service average is trending towards 20% I/O wait I know that that is a hard-limit for my server configuration that will cause slave lag.

Jeremy Cole has a good write up and a tool for getting I/O stats that iostat itself does not expose.

If the atime (Response time) is growing, I know that the overall SAN LUNS are saturated. On top of that SAN LUNS typically have larger svctm cutting overall throughput with how MYSQL works. On a side NOTE I despise using SANs for mySQL. Why well that's another post.

Then I have ganglia configured to monitor everything for SHOW GLOBAL STATUS, but really I only look at the following

Com_delete
Com_insert
Com_replace
Com_select
Com_update
Key_reads
Questions
Connections
Threads_created
Slow_queries
Handler_read_next
Handler_read_rnd
Handler_read_rnd_next
Handler_rollback
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_pages_created
Innodb_data_pending_reads
Innodb_buffer_pool_read_ahead_rnd
Innodb_data_read
Innodb_data_written
Innodb_row_lock_time_avg
Innodb_row_lock_time
Table_locks_immediate
Table_locks_waited
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan

Next I take the techniques I learned from John Allspaw and build a 3rd order Polynomial and verify that my R^2 is in the 98%tile to see when I need to add more servers. So far so good. Now I have a rough idea when I need to add more servers-a capacity plan. (The techniques involved are various ratios of Users per Application per Shard, busy time, more junk like that)

Now your Architecture allows you to Scale, by ensuring a High Throughput at a low Response Time.

I personally use a architecture that I've started on since 1999-Shard'ing. Brad Fitzpatrick when building Live-Journal really made this concept popular.

With my Federation strategy I've been able to scale some of the most toughest dynamic applications linearly by just arbitrary adding more servers. It takes 5 min. to deploy new DB servers.

So, in summary to capacity plan you need to know how the system works, monitor it and trend it. To scale: your database architecture needs to meet the needs of the app. Are you read or write heavy or both? Do you have a lot of concurrency? Does your app do a lot of sorts? Does your app do a lot of ranges? Is it all of the above? Design to meet the needs, benchmark, know when it will break, and have a plan to recover before it does.

What should I use MYISAM or INNODB?

2008-07-30T17:12:00.001-07:00

I get this question allot, and my response is "it depends", people rarely like this general response. To pick a storage engine, the user must understand the gains and losses of using the storage engine, basically understand what INNODB and MYISAM do, and how they behave under different load patterns.

When picking a storage engine I go through a checklist-below is a quick list to get an idea of the thought process.

Do you require transactions?

If yes use INNODB else you may still want to use INNODB?

Are you doing a lot of big queries that Scan 20-30% of the rows?

If yes use MYISAM. It's better at doing large queries where the query requires a full table lock. INNODB will lock each row as it scans through it which hurts query throughput.

Are you building an app to store 1 row and access said row really fast and at a high concurrency?

See Tickets explanation of the example of an application that does this. Innodb hits a lock bottle neck when operating on the same row from many different threads, so it may not be advisable to use INNODB.

Are you building an app that stores a lot of blob data?

Now this is a grey area. With INNODB native zlib compression in 5.1+ INNODB may be a good choice over myISAM, while myISAM historically uses less diskpace then its INNODB counter-part. So, don't be afraid to create a single INNODB table and 100s of MYISAM tables that hold the blob data for INNODB.

Why do this? Because a single table in MYISAM will likely lock slowing down blob insertion / removal.

Do you require a lot of reads and writes where the ratio is not 90% reads 20% writes but more like 60/40?

Use INNODB over myISAM. If you don't believe me take a look at


show global status like 'table_locks_waited';   
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Table_locks_waited | 82721 |
+--------------------+-------+

These are generic common questions that personally help me find out whats best to use. In reality to pick the correct storage engine, you should experiment and find out whats best for your app. Understand the storage engine-do some testing, then its easy to pick which is best.

ALTER TABLE, SELECT AND INNODB

2008-07-14T16:28:00.000-07:00

Let's assume you have a 512MB table, and you decide to alter the table to add an index to make queries faster.

How long would you expect this alter to take? Hours? Days?

Even with 7200 RPM-slow disks the alter should of finished in less then 1/2 hour.

I ran across an alter that was running for 4 days-on 512MB datasize. The reason why it ran so long is because there was a SELECT that was running preventing mySQL from performing "rename table", the last leg of the ALTER TABLE process.

Killing that SELECT released the shared lock allowing the alter to finish.

Do not KILL THE ALTER when stuck in this SHARED LOCK STATE; Do NOT then remove the temporary tablespace file "#sql-320f_106f99a2.*".

What will happen if you do remove the #sql* file by hand?

Well for one INNODB will crash the mysql instance saying it could not find the temporary table space. It failed to open it. Then on recovery the original table gets unlinked from the filesystem and you just lost all data for that tablespace.

Why?

Here are roughly the order of events for an alter:

Lock all writes from said table
Make a temporary table #sql - file
Copy all data from the old file to the new file
Do a quick consistency check between the two files
unlink the old file
rename the temp file into the old file name

Each step operates on the data dictionary pointers for the two tables. Issuing a filesystem rm command for the step right before unlink, will cause INNODB to crash and on recovery unlink the old file and of course fail on the rename.

Group by ORDER by Optimization part II

2008-07-07T18:36:00.000-07:00

In my previous blog post I talk about GROUP BY and ORDER BY optimizations. A member asked a great question that I'd like to share with everyone.

But what if the query was:

SELECT c1, c2, c3, SUM(c4) FROM T WHERE c1 = ? GROUP BY c2 ORDER BY c3 DESC LIMIT 10;


That query would produce a temp table and a filesort.
explain  SELECT c1, c2, c3, SUM(c4) FROM column_test WHERE c1 = 1 GROUP BY c2 ORDER BY c3 DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: column_test
         type: ref
possible_keys: c1
          key: c1
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

The reason the index is c1,c2,c3,c4

So where c1=? and the group by of c2 would use that index, but to order the data properly you would need to do


WHERE c1 = ? GROUP BY c2, c3 ORDER BY c1 DESC, c2 DESC, c3 DESC

explain  SELECT c1, c2, c3, SUM(c4) FROM column_test WHERE c1 = 1 GROUP BY c2,c3 ORDER BY c1, c2\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: column_test
         type: ref
possible_keys: c1
          key: c1
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

to get rid of the temp table or filesort. Filesorts and temp tables takes about 50% of the query time-so avoid these when the query is requested at a huge rate.

Also note that to get rid of the temporary table and filesort, the query changed and does not answer your question without post processing the data in PHP or some other layer.

The reason: the data is ordered by the index. For innodb the entire table is ordered by the PRIMARY key, each index has a reference to. The mysql implementation of group by does not know how to traverse and sort the data in the 1st pass of part of the key. The optimizer needs a lot of work. So, what is done from the mysql level is to automatically create a temp table and sort on that instead of using the index which it already traversed.

I believe in 5.1 that this case is being worked on in the optimizer level to get rid of this common slowdown.

How to pick indexes for order by and group by queries

2008-06-11T13:53:00.001-07:00

First some of the things that you need to use and understand

Explain Syntax

Order by Optimization

Group by Optimization

Update: Updated errors.

Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, and tight index scan.

Loose index scan, scans the entire table index, while tight index scan uses some sort of constraint. For large datasets that are accessed often and require some sort of group by, tight index scans are better.

So how to pick columns to create the optimal indexes. Here is a list of practices (rules) that I personally follow:

1. What is the question asking?
2. What current indexes are on the table?
3. Can the query be re-written to use an existing index?
4. What is the overhead of adding a new index?
5. Follow left-most prefix rules
6. Build indexes that remove filesorts and temporary tables, for all query types.
7. Use statements that reduce the rows examined, and keep each index small. Note: each secondary index in INNODB requires its own page.

So lets look at a table

T(c1,c2,c3,c4)
PRIMARY(c1,c2);

A question asking

SELECT c1, c2, c3, SUM(c4) FROM T WHERE c1 = ? GROUP BY c1,c2,c3 ORDER BY c3 DESC LIMIT 10;

Instinct is to add an INDEX

IDX(c1,c2,c3,c4); Since c1 is the constraint, grouped by c2,c3 SUMMING c4.

BUT when running this condition through explain you'll see that in the EXTRA column

Using where; Using temporary; Using filesort

This is bad, it means WHERE is constraining the clause but the GROUP BY and ORDER BY is producing a temporary table, and a second pass to sort the data.

So your query time is find the rows - put them in temp table which can hit disk, sort them randomly tickling a few thread based buffers. Queries such as these do not scale and can hog up memory.

Let's breakdown what the question is asking.

Give me all the results for c1 where c1 == ?, flatten the results, ORDER the results by the MAX of c3, SUM c4.

Now that we know what the question is asking, lets "re-word" the question

SELECT c1, c2, c3, SUM(c4) FROM T WHERE c1 = ? GROUP BY c2, c3 ORDER BY c3 DESC, c2 DESC LIMIT 10;

This says

Give me all the results for c1 where c1 ==?, flatten the results by the MAX of c3, SUM c4 and if c3.rowN-1 == c3.rowN the tie breaker will be c2.

This is the same asking question reworded. Since GROUP BY AND ORDER BY is sorting the MAX of c3-c2 column refers to the same c2 values as the original query output.

The IDX is now

IDX(c1,c3,c2) and explain produces an extra of

Using WHERE

This is still not good enough-since there is an additional seek to return the columns asked for. Also SUM is being done on the data and not on the IDX.

To fix this IDX should be

IDX(c1,c3,c2,c4) and now explain produces an extra of

Using WHERE; Using index.

What does Using index mean? This means that mysql will not have to do an additional seek to read the actual row.

Now group by and order by have been optimized with adding an additional key.

How did I know to re-word the question and it would produce the same result?
Lets look at order by: That is the ending statement that changes the data. So, since c3 is also used to flatten the constraint I knew that c3 is all that is needed to refer to the same rows, c2 is used to be a tie-breaker when c3RowN-1 == c3RownN.

These are the sort of hints you can use to optimize SQL.

How to cause deadlocks in INNODB and fix them

2008-05-20T17:48:00.000-07:00

So imagine you have a table and for every row it contains an INT that represents a user, a medium int that represents an activity number, and a ENUM that represents state.



CREATE TABLE UserStack (
id int unsigned not null default 0,
activity medium int unsigned not null default 0,
state ENUM('ready','processing','processed', 'error'),
PRIMARY KEY(id),
INDEX (state, activity)
) ENGINE = INNODB;

Now you have an application that is spread across many servers which pops a set of users off the stack and sets them to a processing state.



START TRANSACTION

SELECT * FROM UserStack WHERE state='ready' ORDER BY activity DESC LIMIT 10 FOR UPDATE

foreach id 
   
   Mark them Processing

If multiple threads do this at the same time; the 1st thread will get the 10 ids while 99% of the others will fail with "Deadlock Detected try restarting transaction"

The reason: from innodb's perspective many different clients are asking to perform actions on the same data all at the same time-thus a deadlock is detected. To exacerbate the problem the data is ordered differently from the cluster index-so in essence the entire table is scanned for this example. The table has 30 million rows.

I've tested transaction isolation levels:

READ COMMITED
REPEATABLE READ
SERIALIZED

How to get around this:

Lets add a column to make the rows unique for the calling process; lets add pid (add server int unsigned if you want to run the process from many servers).

The access pattern for the table is now going to involve pid and state. The column pid nor the combo with state is unique, so the user id which defines the user.

ALTER TABLE UserStack DROP PRIMARY KEY, ADD pid int unsigned NOT NULL DEFAULT 0, ADD PRIMARY KEY(state,pid,id), DROP INDEX state;

Then change SELECT ... FOR UPDATE to an UPDATE statement. UPDATE operations for some reason are better at concurrency then SELECT FOR UPDATE. The update scans the PRIMARY index and updates the selected amount of rows defined by the LIMIT.

UPDATE UserStack SET pid=getmypid(), state='processing' WHERE pid = 0 AND state='ready' LIMIT 10;

SELECT * FROM UserStack WHERE pid = getmypid() AND state='processing';

foreach user
process
mark as completed

Rinse and repeat.

The desired affect is complete. Each thread can grab its own work and each thread is guaranteed a unique user or block of users to process. The act of marking rows (marking their territory) and grabbing the marked rows takes a fraction of seconds as it should.

A good write ups on deadlocks

a-little-known-way-to-cause-a-database-deadlock

Net Settings mySQL & Memcache

2008-05-13T10:59:00.000-07:00

Ever see this

TCP: drop open request from 10.209.23.142/43407

Well lets start with a more specific example:
Memcache is tightly coupled in your code: Every request caches the response from the database so a lot of quick calls to memcache is made. Then you start adding full HTML to memcache instead of just caching the raw data; so now your load pattern is bigger blobs of data still at a high request rate.

Now suddenly the memcache port hangs-you verify this by ssh to the box and then telneting the the memcache box port 11211 and see that ssh works (port 22) yet 11211 does not. As a result all your front ends fall over because they are hanging on the memcache port.

THIS IS NOT A MEMCACHE PROBLEM. Its a kernel problem. Default installs of Linux set the TCP window buffer size to a desktop setting and not a server setting.

So I run this script.


#!/bin/bash

sysctl -w net/core/rmem_max=8738000
sysctl -w net/core/wmem_max=6553600

sysctl -w net/ipv4/tcp_rmem="8192 873800 8738000"
sysctl -w net/ipv4/tcp_wmem="4096 655360 6553600"
sysctl -w vm/min_free_kbytes=65536

I found this out by going here

This is the first kernel setting that I have seen make a real big difference.

So my list of changes so far to the kernel default settings are (getting lazy in detail)

vm.swappiness=0
run the deadline scheduler

On the filesystem side

mount the datadir noatime
use O_DIRECT
if you have cache on a hardware raid card set the cache for writes (make sure you have a BBC)
use Raid-10 or if you have the money + can take a hit on I/O RAID-6
stripe size 128-256K

I have some other tweaks that I'm forgetting but when I find them I'll post them.

UAE Broken Proxys - how to work around them

2008-05-08T13:42:00.000-07:00

In the UAE there is a goverment enforcement of all traffic to go through goverment proxy servers. All requests are proxied and cached. What does this mean in the mysql world? Imagine you have a web application that records comments to a media object. Then a single person from the UAE makes a comment to that media object. The UAE Cache Proxy caches the HTTP-POST. Now every person in the country that visits your domain causes a HTTP-POST to that same media object. From a outside view it looks like content is being added to the system and the numbers are good. But ALL THE CONTENT is the SAME which is not valuable to the media owner nor the DBA who has to manage that data due to the BROKEN-CACHE-PROXY.

So how do you bust the BROKEN-CACHE-PROXY?

A simple method is to look at the contents of the last few posts and see if the same data is being added prior to insert-but this can be expensive at high scale-the scale that I deal with (billions of transactions per day).

A simple approach is to just add a hidden variable to the POST with your webservers time. If you webserver time on the post >= hidden variable time + 120 seconds then you know its a bad post.

I just pre-ordered my High Performance MySQL: Optimization, Backups, Replication, and More

2008-05-05T12:27:00.000-07:00

Did you pre-order yours?

Pre-Order yours today

Peter and Byron are really smart guys and very methodical in their tests to make sure the conclusions produced are rock solid. I don't know whats in the book, but if these guys made it, its going to be good-that's how much faith I have.

Linux 64-bit, MySQL, Swap and Memory

2008-05-01T17:36:00.000-07:00

The VM for Linux prefers system cache over application memory. What does this mean? The best way I can explain is by example.

Imagine you have 32 GB of RAM
MySQL is set to take 20 GB of RAM for a process based buffer and up to 6M for the various thread buffers.

Over a period of time the box swaps. The only thing that is running is mysql and its memory size is around 21GB for resident memory. Why does swap grow when there is plenty of memory? The reason is when a memory alloc is needed (thread based buffer is tickled) the VM will choose to use swap over allocating from the system cache, when there is not enough free memory.

DO NOT TURN OFF SWAP to prevent this. Your box will crawl, kswapd will chew up a lot of the processor, Linux needs swap enabled, lets just hope its not used.

So how do you stop Nagios pages because of swap usage? Well if you have a few choices.

reboot the box

or

stop mysql && swapoff -a;swapon -a;

or just

swapoff -a;swapon -a;

Doing the latter command is rather scary and fun at the same time. Because you can either crash mysql or not. I just did the swap* commands live, I was very certain nothing was using swap and it worked. YAY no more pages and I didn't have to shut down the service!

Avoid storing Markup (HTML) in the database

2008-04-23T18:21:00.000-07:00

I see this to often; Storing HTML in the database. Then UI wants to change the HTML, but the data grew to 100 GB, so the really only feasible way to change the HTML is via a post process after the database fetch. This post process produces a huge list of preg_replace statements to rebuild the HTML on display. This consumes a lot of memory over time and slows down the APP, plus its time consuming to debug.

Store URLS if the apps need to. Or better yet build the schema to store the bare minimum the app needs to generate the HTML.

Here is a compelling reason why not to store HTML:

The data needed to generate the HTML is 10 bytes, but the TEXT field consumes 1024 bytes, all of which is the same text.

Thus for an app that should cost very little to maintain now costs 10 times as much.

If your building a search engine strip out the HTML and store the text. If its a quick app, take this saying into account

The is nothing more Permanent then a temporary solution

A exception that I don't mind: php serialize - this is markup that (rarely) does not change.

mySQL uc2008 presentation

2008-04-22T00:43:00.000-07:00

mySQL UC 2007 Presentation File

2008-04-21T16:13:00.000-07:00

Doing Billions of Queries per Day

Common Steps to Scale Linearly

2008-04-21T13:39:00.001-07:00

Whenever I work at a place I do the following.

Get a rundown of what the application is, what its demands are, what does the company expect the application to be a year from now - like how many users are going to use the application. 10 million, 20 million, 100 million?

Then I find all the slowdowns:
- What are the my.cnf settings?
- What are the most active tables?
- What type of SQL is being used?
- How is the data accessed?
- Who/What owns the data?
- What is the Read-Write Ratio?
- How many servers are used now to handle the site load, and how many are needed within a few months.
- What is the reads per second, connections per second, writes per second
- How does the data grow? MxN, MxNxO, N^4 etc.

Once I get this down (a few days) then I change everything :)

If the data is small and doesn't change often I don't bother federating that at 1st. I go for the meat of the product. My goal is to run mean, lean, cheap, fast, and easy to maintain. I love my sleep.

So steps on federating:

What is the main object?
What are the mappings to this main object?
Spread data out by this main object
Cache lookups to the pointer where the main object data is.
Build everything around the main object(s).
Use a versioning system
Document a global view of how things work, and make cookbooks-so someone else can wake up in the middle of the night. I love my sleep :)

At the same time get

dsh working
nagios working
ganglia working
custom tools working

What you have is an easy to use, maintained system that scales linearly as long as that main object is being referenced.

Above is the easy stuff. The time-consuming part of the procedure is rewriting all the code to work for old and new, and migrate the new. This is needed to make sure no one if affected by upgrading to the new system.

Take a Vacation

2008-04-14T10:18:00.000-07:00

Are you a mySQL DBA that will not take a vacation because your the only person to fix a disaster if one will occur? Do you take a vacation and stay at home with your pager on you? Ask you self when was the last time you went someplace for 9 days without a pager or computer? If work is more important to you then your own personal time, then its time to take a vacation.

I just took a 9 day vacation in Hawaii, 4 days on the Big Island and the rest on Oahu. Man I haven't been this rested in years. I feel like a new man! Prior to my vacation I felt run-down and I was just going through the paces, now I'm rejuvenated-the ocean waves and hot air has given me new ideas on how to design back-end database data layouts, how to scale to new levels at less cost. I feel like I did when I graduated college-ready to take on the world-but better!

Expect many new posts! Because the original Dathan is back better then evar!

Hang-Loose Brutha!

I write DB scripts in PHP

2008-03-25T18:27:00.001-07:00

Lots of people like PERL, Python, Java or C to write database backfills, data repair, etc. I like writing in all these languages but what I don't like doing is writing the same code over and over again, so I write my DB code in the same language as the environment that I'm using. This means I write backend DB scripts in PHP, I get to reuse common DB paths, classes and functions and improve things when I see them.

But, PHP if not written correctly will use up 2GB of memory easily from loosing reference to arrays or setting globals and forgetting about them; stuff like that. This is especially visible in long running applications.

PEAR is not immune to these memory leaks. So, to get around reference problems in PEAR I do

$skiptrace =& PEAR::getStaticProperty('PEAR_Error', 'skiptrace');

$skiptrace = false;

This prevents this error
PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 76 bytes) in /usr/share/pear/PEAR.php on line 872

from long running processes.

Thought I pass it along.

MySQL 2008 UC Presentation Scaling a HUGE volumn of concurrent writes

2008-03-25T14:46:00.000-07:00

The details can be found

http://en.oreilly.com/mysql2008/public/schedule/detail/588

I've since moved on from Flickr to a new Job, but Flickr is still allowing me to give this talk. Flickr is so cool!

The talk encompasses capacity planning and scaling for a heavy concurrent write and read environment, and when it makes sense to split resources out to a single application.

mySQL PHP PEAR::DB

2008-03-12T18:44:00.000-07:00

So, I just fixed a behavior in PEAR::DB. In isManip, a regular expression is called on every query that goes through the PEAR::DB layer. That sucks btw, but the purpose of function is to tell the classes that inherit from DB that the query passed is a query that affects data.

The code is as follows


 function isManip($query)
    {
        $manips = 'INSERT|UPDATE|DELETE|REPLACE|'
                . 'CREATE|DROP|'
                . 'LOAD DATA|SELECT .* INTO .* FROM|COPY|'
                . 'ALTER|GRANT|REVOKE|'
                . 'LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $manips . ')\s+/i', $query)) {
            return true;
        }
        return false;
    }

Then in mysql.php there is this



function affectedRows()
    {
        if ($this->_last_query_manip) {
            return @mysql_affected_rows($this->connection);
        } else {
            return 0;
        }
     }

Which is not ideal IMHO. I changed mysql.php to just return the results of mysql_affected_rows which is better anyway IMHO.

What DB wrapper do you use? What do you think about PDO?

Personally I like to write my own, bare bone wrappers around mysql_* functions for php but to make things compatible I'm using PEAR::DB.

MySQL multi datacenter HOT / HOT BCP

2008-03-04T10:46:00.000-08:00

In a previous post I explained about BCP. I have just finished my latest and nearly final test, and all worked as expected. For about an hour certain front end servers where hitting a database shard in 1 datacenter while the rest of the front ends hit the same shard in another datacenter.

What makes this incredible is that now data from mySQL can be close to geo-graphic locations of the end user, without having to make changes to the front end application. So fail over is silent from a database perspective if an entire datacenter is down. Actions outside of the application are also replicating seamlessly and in order. Latency is high but the goal is not to have a WWW in one datacenter talk to a shard in another datacenter. So, latency is in effect not an issue especially if one uses Akami-DNS to geo-graphically loadbalance your user base.

This is a great simple solution that scales, and it only took 1 week to implement from the ground up.

Other solutions that I have seen have a Proxy layer infront of the actual database that write data to one datacenter and synchronously writes data to another datacenter. So, your entire transaction time is the SUM of the transaction, for each server plus the SUM of the latency to talk to another datacenter. Also if that "Proxy Layer" goes down, or the stunnel goes down the application goes down. This is not ideal for me.

The solution which I designed and was implemented+tweaked by our master java engineer: removes these layers and makes the data transfer independent of the application. So, if that layer dies the application does not die-it just gets restarted and catches up to the latest events.

I love when things work.

The oldest component is finally finished

2008-03-03T11:32:00.000-08:00

I have finalized the design for Federating Connections and part of the design has been implemented so far with amazing results. Overnight the dependency on replication has been reduced. Feed updates no longer are lagging and query load doubled without the need for new hardware.

Social connections (not mySQL connections) at Flickr in particular are directly responsible for permission levels. This allows members to see into a another members photostream. This global requirement means that every logged in page viewed on Flickr requires a database read if the page is not the members own page. So if the cluster is down, all access to photostreams default to the most restrictive state, i.e. public photos only.

As a result, the service needs to be extremely responsive on reads, since possibly every page view on Flickr could produce a realtime query on the contact cluster.

Next, the data has to be redundant and always available. This is very hard to do, when you have no spare servers, and only two servers to do this entire procedure-yet we did it. We recorded all photo permission change events, created the new method, backfilled and applied all change events to the new schema layout.

Finally the new design allows for more features and more requests to the system with the ability to spread data across N severs.

Two servers are all that is needed now, with the next phase spreading the data across more servers - with NO memcache or cache layer at all.

BCP mySQL without loosing speed

2008-02-25T12:37:00.000-08:00

BCP stands for Business Continuity Planning - basically a fancy name to describe handling the situation when a DC goes offline. Since we use dual master replication for our servers - putting a master in another DC is not possible - without a special layer. The reason: A slave can have only one master, while a master can have many slaves. So, mysql replication for a dual master setup is not valid, unless there is a replication ring from one DC to another DC. You do not want to do this due to latency.

To get around these limiting factors in mysql, we have developed an application, using my design, to get write events from one DC to another DC over a stunnel.

To get data from one DC to another DC we need to do it encrypted. If there is a man in the middle, we don't want them sniffing our traffic-so we use stunnel to encrypt the data. I will not use SSL encrypted replication since managing that requires a mysql restart. For something as simple as replication data, we shouldn't have to restart mysql.

stunnel setup

here is a good writeup

For the version of stunnel that we are running I needed to add this: wait_for_readable = 0 - which tells the accepting server NOT to wait for headers to be sent.

Now to make sure it's fast, we need to transfer as little data as possible-I will not go into details of how this is implemented yet. If people are interested I may release a generic version of the application that will get data from one location to another location, consistent, ordered etc-which is application independent. The last statement means that the application does not need to change for this to work.

Adding Capacity is FUN

2008-02-01T15:02:00.000-08:00

At Flickr adding capacity is easy. Since we are able to scale at a function of user growth, independent of hardware classes. This makes adding new hardware easy. I'm able to adjust the server weight on the FLY to give more users to a certain class of hardware over another.

Changing schema is easy as well and now I do it much safer. In the pass I would turn off a master for each partitioned dataset all at the same time: then stay up for 20+ hours and execute an alter across the entire server farm, that was off line.

Now I do it in stages and do it throughout the week. Its a little slower but I get to sleep. Additionally with the new method I am able to do more all at the same time-like rebuild the entire dataset for a partition.

Here are my steps for doing a change that takes more then 10 hours:

remove the servers from the site config
push my.cnf.maintenance to the servers not taking on site traffic
- this starts mysql on port 3307 and turns off replication and binary logging
- this step is crucial to tell our backend bots not to do stuff on the servers in maintenance.

mkdir -p /var/lib/mysql/restore
restart mysql
DUMP all data using mysqldump and SELECT *,[NEW FIELD DEFAULT VALUE1...VALUE-N] INTO OUTFILE '/var/lib/mysql/restore/[TABLE].txt 8 processes at a time

stop mysql
remove all of innodb datafiles
copy data with 8-20 processes to the filer to sustain 50MB a sec. Pretty nify script
umount /var/lib/mysql
mkfs.ext3 -Tlargefile4 -LMYSQL [device]
mount -a

copy some of the data back (everything except the txt files)
start mysql
LOAD data in parallel about 8 processes at a time

verify that utf8 data was loaded properly
push my.cnf.production
restart mysql
wait about 45 mins for 12 hours of events to catch up.
put it back into the site config

I do these steps across 6-10 servers at a time-using a dsh like application

Your question may be why are you rebuilding the filesystem and using ext3? Well EXT-3 is my only option and it fragments really bad-messing with performance. So if I am going to do a massive alter-I throw that in there for good measure-I plan on doing this once a year.

I do not do this procedure for alters that last less then 10 hours, but if I alter Flickr's main table which holds most of the data-I take the opportunity to shrink all the datafiles.

Flickr is Hiring for a DBA position!!

2008-01-30T11:39:00.000-08:00

We're looking for an experienced and motivated MySQL DBA to help make things go at Flickr.

We're looking for a DBA with strong LAMP background.

Specific Duties:
• Work with engineers on performance tuning, query optimization, index tuning.
• Monitor databases for problems and to diagnose where those problems are.
• Work with Senior DBA to maintain a scalable, reliable, and robust database environment.
• Build database tools and scripts to automate where possible.
• Support MySQL databases for production and development.
• Provide 24x7 escalated on-call support on a pager rotation.

Requirements & Education:
• 3-4+ years MySQL experience.
• 2+ years of experience as a MySQL DBA in a high traffic, transactional environment.
• 2+ years working in a LAMP environment, particularly PHP/MySQL combination.
• Proficient with database performance strategies.
• Proficient tuning MySQL processes and queries.
• Experience in administration of InnoDB
• Experience with MySQL Replication, with both Master-Slave and Master-Master replication.
• Ability to work cooperatively with software engineers and system administrators.
• Excellent communication skills
• Exceptional problem-solving expertise and attention to detail.
• BS in Computer Science or equivalent.

Bonus Points For:
• Experience with Data Sharding and federated architectures.
• Experience with multi-datacenter MySQL replication.
• Experience working in a social media environment.

Send me an email with your resume.

filefrag a DBA's Best Friend

2008-01-24T13:44:00.000-08:00

EXT3 has performance problems as the filesystem gets fragmented-although this is counter intuitive to the design of EXT3-fragmentation really happens.

Really there is no safe de-fragmenter tool out there for ext3. There is this one but I will not use it.

Fragmentation can get so bad that performance from EXT-3 can drop 7 fold!!

Here is some good info on fragmentation and a comparison of various other file systems.

Now for filefrag, I use this to see how bad an innodb file is fragmented.

TABLE.ibd: 4020 extents found, perfection would be 298 extents

Our wiz of a system administrator Kevin M., who I am teaching to become a mySQL DBA got me hooked on this utility: and he came up with a good method to fix this.

cp TABLE.ibd to a new location
rm TABLE.ibd
cp TABLE.ibd from new location back to the Database Directory

Tada fragmentation is nearly gone. FSCK will help as well.

O_DIRECT + EXT3 Update

2008-01-18T11:34:00.000-08:00

A few days ago I wrote about O_DIRECT + EXT3 not working.

This is not a wide-spread problem, and may be isolated to 2.6.9. So, it makes sense that others who run O_DIRECT with EXT3 do not see the issue. I will use this post for future updates.

Ok here is the research that I did, and found the cause of my O_DIRECT problem

RHEL Bug ID	Description
161985	O_DIRECT on RHEL v4 may not return correct number of bytes when concurrent I/O
178084	Last AIO read of a file opened with O_DIRECT returns wrong length
178720	O_DIRECT bug when reading last block of sparse file
191736	CVE-2004-2660 O_DIRECT write sometimes leaks memory

So, I can't use O_DIRECT

MySQL support == AWSOME

2008-01-15T16:59:00.000-08:00

Well, none of my issues have been fixed yet but MySQL support is on top of it.

I've ran into many S1 bugs: all at the same time. Support has been able to help me identify them. Some of them have proposed fixes, some fixes are being tested in
5.0.54.

MySQL is by far the best Open source Database on the planet-support reflects that fact. I highly recommend getting a support contract to trouble shoot issues that make it into production, less learning the entire mysql code base and doing it yourself. (I know alot about the code-base but the 5 issues I am tracking was to much for me to debug alone. On top of that I don't know enough of the code base to make fixes to some of the bugs.)

If you do more then 30K selects per second across all your servers, get piece of mind that someone will do there best to address any issues that you can't figure out. Get a MySQL support contract today.

MAJOR Problems in mysql-5.0.51

2008-01-14T10:52:00.000-08:00

mysql-5.0.51 causes huge blocking locks under high load.

It also causes relay-log corruptions. For instance, how can a syntax error make it into replication, the reason is mysql-5.0.51 is truncating the stream.

The only work around is to rebuild the relay log.

The BUG is listed here for replication problems. I suspected IO_CACHE corruption as the cause.

update: this is the cause of the huge blocks


InnoDB: Warning: a long semaphore wait:
--Thread 1173899616 has waited at btr0cur.c line 424 for 292.00 seconds the semaphore:
S-lock on RW-latch at 0x2df5159f58 created in file buf0buf.c line 497
a writer (thread id 1173899616) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0cur.c line 424
Last time write locked in file buf0buf.c line 1768
InnoDB: Warning: a long semaphore wait:
--Thread 1172302176 has waited at btr0cur.c line 424 for 295.00 seconds the semaphore:
S-lock on RW-latch at 0x2df50706a8 created in file buf0buf.c line 497
a writer (thread id 1172302176) has reserved it in mode  exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0cur.c line 424
Last time write locked in file buf0buf.c line 1768
InnoDB: Warning: a long semaphore wait:
--Thread 1182951776 has waited at trx0trx.c line 1627 for 283.00 seconds the semaphore:
Mutex at 0x2a9eac52b8 created file srv0srv.c line 872, lock var 0
waiters flag 0
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 1, pwrites 0

Additionally mySQL is putting self in swap for a memory config that has not changed from my previous version.

I'm thinking that there is some memory leak in 5.0.51

DO NOT USE O_DIRECT with EXT3

2008-01-14T10:46:00.000-08:00

O_DIRECT under high load causes these issues

This is a symptom

Losing some ticks... checking if CPU frequency changed.
warning: many lost ticks.
Your time source seems to be instable or some driver is hogging interrupts
rip __do_softirq+0x4d/0xd0
ttyS1: 1 input overrun(s)
ttyS1: 1 input overrun(s)
ttyS1: 1 input overrun(s)
ttyS1: 1 input overrun(s)
ttyS1: 1 input overrun(s)

BUT THE REAL PROBLEM is it Locks up the partition that the ibdata file is on.

Systems where the server locked up.

2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 x86_64 x86_64 x86_64 GNU/Linux


          total       used       free     shared    buffers     cached
Mem:      16412760   16389976      22784          0      82440     686368
-/+ buffers/cache:   15621168     791592
Swap:      8393952        144    8393808

I had O_DIRECT running in production for over a month on some pretty loaded servers, but once I put it on some older servers, all hell broke loose.

Pretty Loaded is defined as

1000 qps mainly selects mixed with large ranges at a high concurrency of 30 threads.

CPU WIO is around 10-15% (acceptable thresholds)

If you insist on running O_DIRECT I recommend

1. Test O_DIRECT on ever OS version in your farm
2. Test O_DIRECT by producing so much load that it's unrealistic.

What would you like me to write about?

2008-01-08T21:17:00.001-08:00

Flickr Stats how is it built

2007-12-13T15:53:00.000-08:00

All Collection is done realtime
MYISAM and INNODB is used
The data is spread across 6 clusters (12 servers-6 used, 6 for fail over) mainly for data storage requirements
Memcache is not used at all in the core of the product.

In summary this was the longest project that I worked on, other then rebuilding the backend for Flickr when I first came on. The inner workings are very complex to achieve real-time collection-while not affecting page load times of a photo page. Most of my time was spent on creating a distributed lock once my DB design was solid.

Things that would really make life easier:

MYSQL AB gets rid of MYISAM and makes PBXT its replacement. I don't need all the great features of INNODB but I would like some. I'll go more into this later.

Additionally INSERT DELAYED worked with ON DUPLICATE UPDATE. Currently it does not.

Finally cross Engine Transactions would be cool but really not required.

Distributed Locking Solving it with mySQL

2007-12-11T13:41:00.000-08:00

At Flickr we use a Federated Architecture, where the WHOLE of the data is spread across many servers. This means that when you load a photo page the dynamic data generated is hitting a server that holds that data, while another photo page view may hit another server for content.

Now to do complex operations to control the flow of what operates on data, the complex operation must lock the data. Let's call this a global lock, which is used to coordinate many jobs. Now to make these operations fast, lets run many processes across many servers. Essentially we needed to solve a hard problem in parallel computing: distributed locking.

This took a bunch of dedicated time to get done properly. We solved the issue by using 3 database clusters.

First I used the coolest mysql feature out there; GET_LOCK and IS_FREE_LOCK. These two mysql function are the heart of the distributed lock.

Why use them both?

GET_LOCK will lock a string in MEMORY on mysql, and anything else trying to get a lock on that string will have to wait until TIMEOUT is reached.

Since the TIMEOUT is in seconds, I don't want to wait and slow down everything, so we call IS_FREE_LOCK first which will return in a fraction of a ms

How do we use it?

We lock an entire Account classification by setting the lock on the owners Cluster.
An account classification is defined as ACCOUNT_123_TASK - which is the string for the lock, this is hashed to the same SERVER where the account is located.

Once the lock is established check another cluster to ensure that we have not performed the action already-this ensures that the event that entered the system doesn't corrupt data if the message to process made it into the system more then once.

Finally operate on the data, if any of the operations fail ROLLBACK all changes, log and continue.

The cool thing about this is that the locks and data are all in the same place. So, if the lock is not achievable then the data is unaffected.

When starting out building an application should I use Complex Joins?

2007-11-27T15:30:00.000-08:00

I get questions on how to build mysql application from time to time, and I wanted to share the response to the question below.

In the beginning, should a developer try and stay away from complex JOINS? Is it better to use many smaller queries and cache the results (using something like memcached) rather than 1 larger query requiring multiple table JOINS?

Needing to scale for millions of users is a great problem to have, but should I worry about it in the beginning? Whats the best approach to db design for day 1?

Well IMHO build the application, build it to spec. When the scope changes (and this will always happen) flow with it. This is in no means a knock against Product-I actually look forward to this, because things are being discussed and if something needs to change lets do it!

Now how to build the application to scale: Look at the scope of a page and gauge the frequency of access of that page. For instance on a page displaying 1 photo-very few if any ranges and/or group-by and/or order-by and/or joins would be used to generate this page since a photo page needs to generate fast or loose the interest of the page viewer. On the other hand a page displaying the accounts holder account information thats accessed at a order of magnitude less rate-this page can have tougher queries. (Note: watch out for contention)

Once the feature set has been decided upon; take the application apart and find the bottle necks. Highlight the issues that will not scale.

This can be figured out by filling your dev-database with bogus information and running mysqlslap of common select queries to test stability, response time, speed etc.

Take into account how scale can be achieved before changing code.

Such as is the hardware ok to run all my services?
Can I split some services out like move WWW specific traffic to a separate box from the database? (You will be surprised how many people combine the servers)
Am I allocating mysql resources properly? If not how can I tell?
What is my system reporting as the lack of resources? Am I running out of CPU or IO?

The real performance gains come from laying out the data properly and reducing the amounts of sorts, ranges, joins needed to generate your dynamic pages.

In some cases I've had to add tables where data in said tables represent start points to range in other tables. Then on top of that spread that core-logic across MANY individual machines just to get a page to load in less then 300 ms.

Finally add Nagios to alert you when things go wrong such as too many procs running on this database server and Ganglia to chart system resources over time.

Sure, but here is a rule of thumb-the harder the query is the less it can be used.

If traffic ramps up, look to see what the hardest queries are and their frequency of use. Try to reduce these execution times by spreading the data out to more tables or more servers, only if benchmarks show that this is the way to go.

If traffic really ramps up because the application is the next best thing since Flickr, then look at the larger picture of what should be done to scale.

INNODB file_per_table growth based on Data Added

2007-10-10T14:33:00.000-07:00

How does INNODB in mysql 5.0 tablespace per file grow?

I can just add data and hope to catch when the table space grows, but I really want to understand the internals of INNODB and build an accurate modal on how big the table space will grow when X rows are added.

Data stored in INNODB is stored as pages with a default size of 16KB.
INNODB adds a page for every secondary INDEX

INNODB has some overhead of about 18 bytes (maybe more) per row

First let's set up a test environment


delimiter //

CREATE PROCEDURE resettest()

 begin  
        DROP TABLE IF EXISTS testvarchar;
        CREATE TABLE `testvarchar` (
                        `id` int(10) unsigned NOT NULL,
                        `data_key` varchar(255) default NULL,
                        `data_nokey` varchar(255) default NULL,
                        PRIMARY KEY  (`id`),
                        KEY `data_key` (`data_key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


 end;
//

delimiter ;

Now set up some inserts to populate the table


delimiter //

CREATE PROCEDURE populate(IN param1 INT)

 begin  

   declare i int; 

   set i = 0;

   start transaction;

   while i < param1 do

     insert into testvarchar (id, data_key, data_nokey)

            values (i, repeat("-", 1), repeat("-", 250));

     set i = i + 1; 

   end while;

   commit;

 end;
//

delimiter ;

Now from this I know that the size of each record is
id - 4 bytes
data_key - 2 bytes (varchar there is a pad of a single byte)
data_nokey - 251 bytes (ditto)
TOTAL: 257 bytes

Since there data_key AND it's not the PRIMARY INDEX-a page is allocated just for it. So let's sum up the data.

id - 4 bytes //PK
data_key - 2 bytes //Secondary Index
Total: 6 bytes

for a total 257 bytes for the table plus 6 bytes for the index for a total of 263 bytes.

Now there is some overhead on INNODB tables, there is a Record Header which is 5 bytes and the secondary index references the PRIMARY KEY (the clustered index), Plus a transaction-id which is 6 bytes and a roll pointer which is 7 bytes for the undo log

So the record size is 263 (INDEX && DATA) + INNODB OVERHEAD (17) = 280 bytes.

Now since the INDEX takes a page and data takes a page I need to take


32768/280 == NUMBER OF RECORDS to make the TABLE SPACE GROW 32 KB
This == 117 rows;

The start of the datafile is 112K which is 96K + Page for INDEX at create time


 ls -lrh
total 132K
-rw-rw----  1 mysql mysql 112K Oct 10 22:00 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:00 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

Adding 117 rows

call resettest();
call populate(117);


 ls -lrh
total 164K
-rw-rw----  1 mysql mysql 144K Oct 10 22:16 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:16 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

call resettest();
call populate(234); // 64K / 280 =~ 234


//GROWS another 32K
ls -lrh
total 196K
-rw-rw----  1 mysql mysql 176K Oct 10 22:17 testvarchar.ibd
-rw-rw----  1 mysql mysql 8.5K Oct 10 22:17 testvarchar.frm
-rw-rw----  1 mysql mysql   65 Oct 10 00:16 db.opt

All though this is may all look right, in fact it's not. This may be a good approximation, but I am not taking into account the additional overhead added by INNODB to force the datafile to grow prior to this calculation at a much earlier row count. I have not tracked down what that is yet. It may be that innodb needs to reserved space based on the 2xheight of the binary tree to make sure there is enough space to undo leaf splitting. I'm sure that INNODB doesn't allocate the full 255 bytes for varchar in indexes or does it? Maybe you can shed some light?

Developing a new application

2007-09-26T16:53:00.000-07:00

So, I'm developing a new application that's in the capacity planning phase. The application is designed to scale linearly and scale is very easy to do just by adding another database server. But, at what point do you upgrade? What point in the applications life do you add new servers to serve the expectation of the users?

To figure out these questions, thresholds need to be defined. When these thresholds are exceeded then upgrades need to occur.

Here is a process (my process) of questions to ask to define thresholds: First I start out with generic questions listed below.

What is the expected amount of users who are going to use the product initially?

What is the expected usage pattern? For instance if they are adding data what is the add rate? If they are reading data what is the read rate?

Given that the first two questions are answered now ask when is it going to break. If you can answer this your golden. This is the hard part. How do you know when it's going to break and under what conditions without putting the service live. The best thing to do IMHO is to benchmark the system under typical usage patterns then double that.

In my case, I know for sure that it will work on launch and for months there after but I don't know when it's going to fail. Since getting hardware here is an involved process I need to know ahead of time when things will fail-it makes us all honest.

To get an idea on when it will break, I'm gauge'ing the passive additions that are in production now to get a baseline on how much data grows from day to day. In my case the dataset grows 10GB per day spread across 5 servers, thus 2GB per day from a single point of server view. Now I have a base line. In how many days will the application fail to perform under thresholds previously set? The thresholds set, are defined as-all data retrieval and addition must not take longer then 300ms for all components involved. So, when the passive additions on average take 20% of 300ms then I know the application is about to hit my own personal saturation point, thus I must ask for new equipment.

But, the problem is not as clear cut, really I need to answer the question does InnoDB performance degrade at O(nlogn) when adding strings to it? If not what is the degradation of string addition and retrieval when the dataset is HUGE like @40-200+ GB

(Where did I get log(n) from? Well, indexes in INNODB are B-Tree's-I/O performance should degrade at nlogn as data grows.)

Some good reading mySQL Insert-Speed

This is a little old but it's a good approximation.

Things of note:

innodb_buffer_pool_size=12000M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2

I must watch that the merged records count stays within a few hundred thousand of the inserted record count in the insert buffer and adaptive hash index part of SHOW ENGINE INNODB STATUS IF it doesn't then INNODB is hitting it's own limitation.

The mySQL Optimizer and your applications

2007-09-14T14:34:00.000-07:00

What is the mySQL Optimizer? Well it's the part of the mysql query engine that determines what index to use, based on given sql. It sucks in every version that I tested, which is

3.23
4.0
4.1
5.0
5.1

I find that in nearly every app that I've end up writing I need to create my own optimizer to determine what index to use.

Why?

Well, imagine this. Your table has 30 indexes and many of the prefixes of compound indexes are the same. The reason is because given a question you want to sort the data quickly in different views. The optimizer sucks at figuring out which index to use under these cases (and many others)

For example:

SELECT * FROM SomeTable WHERE owner_id = ? AND perms IN (0,1) AND karma IN (0,1) ORDER by date_create DESC, photo_id DESC limit 10

Now the table contains an index on

owner_id, perms, karma, date_create,photo_id
owner_id, date_create, photo_id
and 28 other index.

Which index should mysql pick?

Well since perms is a range and karma is a range then using the index (owner_id, perms, karma, date_create, photo_id) to avoid a filesort from an ORDER BY is not possible. The reason to avoid filesorts especially in cases of large amounts of data, is because a filesort is 60% of the query time. So, the course of action is to pick the index where a filesort is avoided, thus

owner_id, date_create, photo_id should be picked.

But in fact mysql doesn't pick the correct index, ever. Innodb especially from query to query execution of the same type query the index changes over and over due to how innodb determines cardinality.

Now, you may be thinking why not just run analyze table? Still mysql will pick the wrong index, and Analyze table for INNODB only dumps the innodb_buffer_pool which sucks.

How does one get around this?

Write your own optimizer! Here is some PHP snippits



function sometable_get_index($where, $sort) {
     if(strpos($where, 'owner_id') !== FALSE) {
                        if (strpos($where, 'karma') !== FALSE) {
                                if (strpos($sort,'date_create') !== FALSE &&
                                        strpos($where, 'IN') !== FALSE) {

                                        return 'USE INDEX (owner_datecreate_id)';
                                }

                                if(strpos($where, 'perms') !== FALSE && !$sort) {

                                        return 'USE INDEX (owner_perms_karma)';
                                }

                         }

                        MORE Parser logic....


}

Why use strpos? Well, it's faster then preg_match and strstr and I only care about the existence of SQL fields in the where and sort clause, not the contents of the rvalue of each column.

InnoDB: Number of pending reads 128, pending pread calls 0

2007-08-23T16:34:00.001-07:00

InnoDB: Number of pending reads 128, pending pread calls 0
InnoDB: Error: InnoDB has waited for 50 seconds for pending
InnoDB: reads to the buffer pool to be finished.

What does this mean? Well searching yahoo search I couldn't find anything other then pointers to source code where this message generated. I assumed it was a disk problem because performance was super crappy.

My disk monitoring software which probes MegaRaidCli didn't report a failed disk. Linux didn't report a failed disk or any errors. So, what is happening? Was I wrong?

Well, in fact it was a failed disk, but not in a state that could be classified as failed by the controller or any other component including the OS. With the help of my bud we found that a disk in the array was in a critical state-the disk itself just had a bunch of sector errors, yet the controller, os, everything would not mark it as dead - so the end result was to remove the drive and swap it with another.

So in essence this message means something is wrong with your disks and or filesystem.

Oooh really bad bug in 4.1.23, 4.1.24b INNODB only

2007-08-17T19:11:00.000-07:00

Bug: 30485

[miguel@skybr 4.1]$ bin/mysql -uroot db77
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
    ->   `code` varchar(32) collate utf8_bin NOT NULL default '',
    ->   `used_by_id` bigint(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 |
+------+------------+
1 row in set (0.00 sec)

mysql> update GiftCodes set used_by_id='1' where code='foo';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 |
+------+------------+
1 row in set (0.00 sec)

Replication Syncing MASTER_POS_WAIT

2007-07-20T14:00:00.000-07:00

MASTER_POS_WAIT

This function is useful for control of master/slave synchronization. It blocks until the slave has read and applied all updates up to the specified position in the master log. The return value is the number of log events the slave had to wait for to advance to the specified position. The function returns NULL if the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if the timeout has been exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is waiting, the function returns NULL. If the slave is past the specified position, the function returns immediately.

If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.

This is great for applications which auto promote masters, but one has to account for race conditions.

For example, dbmaster1 and dbmaster2 are in bi-directional replication displayed below.

dbmaster1 <----> dbmaster2

dbmaster2 has slaves called dbslaves1 and dbslaves2. We want to replace dbmaster2 with dbslave1.

On dbmaster1 issue:

FLUSH LOGS;

On dbmaster2 stop external writes to it and execute

SELECT MASTER_POS_WAIT('dbmaster1-bin.000002', 4); SLAVE STOP;


db3>show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: 
                Master_Host: dbmaster1
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: dbmaster1-bin.000002
        Read_Master_Log_Pos: 3696603
             Relay_Log_File: dbslave-relay.000005
              Relay_Log_Pos: 597702176
      Relay_Master_Log_File: dbmaster1-bin.000002
           Slave_IO_Running: No
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 805
            Relay_Log_Space: 601376092
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL

Notice Exec_Master_Log_Pos is at POS 805. This is a small window of events that passed. So, make sure to account for it.

Now on dbmaster2 issue:

FLUSH LOGS

Do the same steps for dbslave1-2 with MASTER_WAIT_POS but for the position on dbmaster2 not dbmaster1.

Make dbslave1 a master by adding

log-bin to my.cnf

Issue CHANGE MASTER TO on dbslave1 to dbmaster1 // this moves dbslave1 to dbmaster1


CHANGE MASTER TO MASTER_HOST='dbmaster1', MASTER_LOG_POS=805, MASTER_LOG_FILE='dbmaster1.000002';

Issue CHANGE MASTER TO on dbslave2 to dbslave1 // this moves dbslave2 to dbslave1


CHANGE MASTER TO MASTER_HOST='dbslave1', MASTER_LOG_POS=4, MASTER_LOG_FILE='dbslave1-bin.000001';

Enable writes on dbslave1

done.

I wrote an application that does this all for me, using IPC, fork, db connections and the "algorithm" above. If your interested in it. I'll post it here.

INNODB and strings

2007-06-25T16:16:00.000-07:00


mysql> CREATE TABLE innodb_string_test (h varchar(512) ) TYPE=INNODB;
Query OK, 0 rows affected, 2 warnings (0.55 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------+
| Level   | Code | Message                                                                  |
+---------+------+--------------------------------------------------------------------------+
| Warning | 1246 | Converting column 'h' from CHAR to TEXT                                  |
| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |
+---------+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Technically innodb supports defining varchar's greater then 255 characters (utf8 are double bytes so don't assume that 255 characters mean bytes-FYI).

MySQL the server doesn't allow this.

INNODB as of mysql-5.0.3 store stings in COMPACT format. This increases CPU usage slightly but saves nearly double the diskspace, thus given an application twice the memory. (Smaller data, more of it fits in memory).

To turn this feature off, define table ROW_FORMAT=REDUNDENT.

For more details on the Physical structure of strings in INNODB go here

In summary: use varchar over char.

[edited for correctness - thanks Ken Jacobs]

Looking at this slide from INNOBASE.com - in 5.1 they will start using a COMPRESSED format based off of zlib. Here are some numbers and details:

mysqluc2007

	Uncompressed	Compressed
File Size	2.8GB	1.4 GB
Insert/sec	1300	1000
CPU Usage	5%-50%	15%-50%

Unique strings in a text field

2007-06-12T17:18:00.001-07:00

So, I want to reduce data usage of a text field, by storing unique strings separated by a delimiter. So to do so I came up with this:

Given a table


CREATE TABLE `hmm` (
  `a` int(10) unsigned NOT NULL default '0',
  `b` text NOT NULL,
  `c` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO hmm VALUES (1, 'hmm8',1) ON DUPLICATE KEY UPDATE b = IF(FIND_IN_SET('hmm8', b)>0, b, CONCAT_WS(',',b, 'hmm8')), c = c+1;

SELECT * FROM hmm;
+---+--------------------------+---+
| a | b                        | c |
+---+--------------------------+---+
| 1 | hmm4,hmm5,hmm6,hmm7,hmm8 | 7 |
+---+--------------------------+---+

Let's break apart the INSERT statement, if column 'a' produces a duplicate ON DUPLICATE KEY UPDATE will issue an IF statement

FIND_IN_SET will return the position of the found string. The logic of IF say if FIND_IN_SET is is greater then 0 i.e. the string was found, update column b with b itself (do nothing) else concat the column with the column contents and the new string separated by a comma. Additionally in all cases increment column c by 1.

Why do it this way? Well I removed the need to do a SELECT and provided a solution to store distinct text values instead of the raw value, thus saving space and resources on network transfers etc.

A little quick statement that might be useful for others.

Multi-Master Replication, looking over the code-base

2007-06-11T14:21:00.000-07:00

I'm running into a situation, where I need real multi-master replication, yet as we all know this doesn't exist (yet) in mysql.

So, as a result I'm investigating how I could implement multi-master replication. Fortunately mySQL has documented some of the source code and hosts this documentation online.

http://dev.mysql.com/sources/doxygen/mysql-5.1/slave_8cc-source.html

There are already references and hooks for multi-master replication in slave.cc



 /*
      TODO: replace the line below with
      list_walk(&master_list, (list_walk_action)end_slave_on_walk,0);
      once multi-master code is ready.
 */

Of all the ideas that I have batted around, I've come to a common conclusion, modify mysql source to enable multi-master replication or make an external daemon that would essentially do the same thing.

It would need to read multiple replication events

It would need to log these multiple replication events

It would need a command interface to dynamically add,remove,change hosts

I could either build a method for pushing replication events to a list of hosts, or build a subscribe method which I'm leaning towards.

INNODB Disk setup and mount options

2007-06-02T15:57:00.000-07:00

Assumptions:

RHEL
x86_64
EXT3
RAID

What Raid to use?
RAID-10

Why?
It's faster. RAID-5 offers more disk space but the parity bit messes things up, unless you have some uber hardware-raid card that caches that operation. Personally I am not a fan.

Stripe Size:
128K - this is really good for INNODB, you'll see a huge boost in responsiveness by making your Stripe Size 128K. I had a 64K stripe size, and I was blown away by the improvement of 128K

Mount options:

mkfs.ext3 -T largefile | mkfs.ext3 -T largefile4

Unless your going to have millions of files, this is a good option.

Make sure /etc/fstab mounts the mysql partition or the data that mysql resides on with noatime.

atime is accesstime: this is a huge boost in performance, tracking each time your ibdata file was last access is worthless, so don't do it.

From other benchmarks that I ran, I think that IBM's open source File system may be better then ext3 for certain workloads. Once I have more information I will post it here.