Categories
All Business iHeavy Newsletter

Tyranny of a Google vote

Image by Hajo de Reijger, politicallyillustrated.com

For the past year I’ve been seeing headline blogs analyzing the effect of Google’s last algorithm update, dubbed the Panda. There was much talk of unfair relegation from the first page of Google search results, and general indignance by the SEO community.

As with any subject in which I only have cursory knowledge I didn’t think much of it. I thought that as long as I didn’t engage in link-buying and whatever is known as “black hat” tactics, the search engines would be fair. What I didn’t realise with Google was how subjective it has become in ranking websites. I was particularly tripped up in the area of duplicate content.

Some of my articles are syndicated to DZone.com, a hub for tech bloggers. They’d approached me about a year ago asking if I’d like them to carry our content. It seemed like a good way to gain visibility so I agreed.

Recently, I ran a Google search on the actual content that was syndicated and found the following results:

Title Dzone rank iheavy rank
Zero Downtime – What is it? #21 (not in first 5 pages)
Deploying MySQL on EC2 #1 (not in first 5 pages)
Cloud Computing Use Cases #5 (not in first 5 pages)

Now DZone.com’s pagerank is a 6 while iheavy.com is a 3. Google’s algorithm is probably weighing the pagerank of Dzone higher, and serving up those results at the expense of the original. It could be that the algorithms can’t determine which is original but Google’s bots know full well when content is published, so it knows the iheavy.com content was created before.

More likely it cannot supersede the ranking algorithm. It is one based on popularity.

What’s Original Content?

All of this raises the question of the value of original content. In this case we’ve given this site permission to carry our content, not knowing whether that would be good or bad for us. Nevertheless, it does seem to go against common sense, and perhaps what Internet users intuition might tell them, that they were not clicking through to the original creator of some content.

Can Google’s Algorithm Discern Original Work? Is there an incentive to do so?

The update brings up interesting questions about Google’s ranking algorithm. In a world where the popularity indicator is given the highest weight, will we find what we are looking for? In my case, I write articles related to my area of expertise, which is on web architecture, scalability and general tech consulting matters. Of course I want people to find my site when they’re looking for solutions to problems relevant to them. Yet in Google’s calculations, popularity trumps provenance.

Many bigger sites are experiencing the same thing and at an even larger scale. According to SEO Moz, Panda is forcing a change onto the role of SEOs, turning them into that of web strategists. While traditional SEO methods of optimizing for keywords, and putting out quality content still count, design and user experience, shareability, likeability; what’s known as “signals” that could predict the site’s popularity, are influencing the overall results of your site.

As an independent business owner with limited resources my time off hours is invested in writing better articles that appeal to people searching for MySQL or scalability consulting and less about putting in the bells and whistles to raise site popularity. But if that’s what Google favors then I’ll probably have to rethink my approach.

As much as I can squeeze out of a busy schedule, there’s not a high chance that this website can surpass a giant such as Dzone in popularity.

The Internet is meant to be a place where the pint-sized can have a fair chance at making an impression. With the way search algorithms have evolved, things are looking more like a reality TV talent contest where skill alone without good looks and a nice smile are just not enough to win the popular vote.

Categories
All Book Review Book review iHeavy Newsletter

The Power of Habit by Charles Duhigg

Habits. We all have them. The good ones we celebrate, but the bad ones we struggle with. Duhigg’s book may introduce some ideas to those of us less familiar with behavioral sciences but it fails to effectively teach us how to form good habits and break the bad ones.

Filled with pages of stories from successful brands such as Pepsodent which Duhigg credits for turning the brushing of teeth into a daily routine; and perhaps more tenuous ones about leaders such as Paul O’ Neill, the CEO of Alcoa who purportedly turned around the fortunes of an ailing organisation by changing its safety practices.

From cue, routine to reward we must first identify the habit, then in a way that parallels the success of Alcoholic Anonymous, you replace the routine, keeping the cue and reward. In discussing the success of AA and others, he brings up the importance of belief in long term success of habit change. He references William James’ famous quote “Believe that life is worth living, and your belief will help create that fact”.

Still, I couldn’t help thinking that for the average business manager it lacked actionable advice of the kind you might find in a Jim Collins Good to Great or Chip Conley’s Peak. These books also have excellent story telling, but break things down in a very specific set of steps and attributes that an organization or individual can apply today.

Duhigg’s writing is easy to read and that’s probably the book’s greatest strength. Yet with most of it grounded more in interesting anecdotes than credible research, the examples unfortunately give for more entertaining reading than any deep insight.

Categories
All MySQL

Bulletproofing MySQL replication with checksums

bulletproof glass

Also find Sean Hull’s ramblings on twitter @hullsean.

Your MySQL replcas running well? You might not even know if they aren’t. One of the scariest things about MySQL replication is that it can drift out of sync with the master “silently”. No errors, no warnings.

  1. What and Why?
  2. MySQL’s replication solution evolved as a statement based technology. Instead of sending actual block changes, MySQL just has to log committed transactions, and reapply those on the slave side. This affords a wonderful array of topologies and different uses, but has it’s drawbacks. The biggest occur when data does not get updated or changed in the same way on the slave. If you’re new to MySQL or coming from the Oracle world you might expect that this would flag an error. But there are many scenarios in which MySQL will not flag an error:

    • mixed transactional and non-transactional tables
    • use of non-deterministic functions such as uuid()
    • stored procedures and functions
    • update with LIMIT clause

    There are others but suffice it to say if you want to rely on your slave being consistent, you need to check it!

  3. The solution – mathematical checksums
  4. If you’re a seasoned Linux user, you’re probably familiar with the md5sum command. It creates a checksum on a file. You can do so on different servers to compare a file in a mathematically exact way. In fact rsync uses this technique to efficiently determine what files or pieces of files need to be copied across a network. That’s what makes it so fast!

    It turns out that MySQL can checksum tables too. However were we to build our own solution, we might have trouble doing so manually as table data is constantly in a state of flux.

    Enter Percona’s pt-table-checksum tool formerly part of Maatkit. Run it periodically against your master schemas or the entire instance if you like. It will store checksums of all of your tables in a special checksum table. The data from this table then will propagate through replication to all of your connected slaves.

    The tool then has a check mode, which allows you to verify all the connected slaves are ok, or report the differences if it finds any.

  5. Step-by-step Setup
  6. First you’ll need to grab a copy of the percona toolkit. Note that if you previously installed maatkit then you may want to delete those old scripts to avoid confusion. mk-table-checksum if you used maatkit, or pt-table-checksum if you have 1.0 versions. You likely installed using wget or perl Makefile, so you may need to go and remove those manually.

    Assuming you’ve already got the percona repository installed issue:

    $ yum install -y percona-toolkit

    I’ve found some of the maatkit tools to be rather fussy about getting all the options right. The first thing to do which will help simplify this is to add a section in your local user’s “.my.cnf” file like this:

    [client]

    user=root

    password=myrootpassword

    That way the percona tools will look for this whenever it needs authentication credentials. Otherwise we assume localhost for this example, so you should verify you can connect with the mysql client as root from localhost.

    Now let’s checksum the “mysql” system schema.

    $ pt-table-checksum --replicate=test.checksum --create-replicate-table --databases=mysql localhost

    Note the –create-replicate-table option. You only need this option the first time. From there the test.checksum table will exist.

    You should see some output that looks like this:

    TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

    04-24T16:06:45 0 0 0 1 0 0.099 mysql.columns_priv

    04-24T16:06:45 0 0 32 1 0 0.100 mysql.db

    04-24T16:06:45 0 0 0 1 0 0.096 mysql.event

    04-24T16:06:45 0 0 0 1 0 0.096 mysql.func

    04-24T16:06:45 0 0 38 1 0 0.102 mysql.help_category

    04-24T16:06:45 0 0 452 1 0 0.106 mysql.help_keyword

    04-24T16:06:46 0 0 993 1 0 0.096 mysql.help_relation

    04-24T16:06:46 0 0 506 1 0 0.100 mysql.help_topic

    04-24T16:06:46 0 0 0 1 0 0.099 mysql.host

    04-24T16:06:46 0 0 0 1 0 0.104 mysql.ndb_binlog_index

    04-24T16:06:46 0 0 0 1 0 0.107 mysql.plugin

    04-24T16:06:46 0 1 1 1 0 0.115 mysql.proc

    04-24T16:06:46 0 0 0 1 0 0.186 mysql.procs_priv

    04-24T16:06:46 0 1 1 1 0 0.097 mysql.proxies_priv

    04-24T16:06:47 0 0 0 1 0 0.097 mysql.servers

    04-24T16:06:47 0 0 0 1 0 0.096 mysql.tables_priv

    04-24T16:06:47 0 0 0 1 0 0.098 mysql.time_zone

    04-24T16:06:47 0 0 0 1 0 0.097 mysql.time_zone_leap_second

    04-24T16:06:47 0 0 0 1 0 0.100 mysql.time_zone_name

    04-24T16:06:47 0 0 0 1 0 0.100 mysql.time_zone_transition

    04-24T16:06:47 0 0 0 1 0 0.095 mysql.time_zone_transition_type

    04-24T16:06:47 0 1 38 1 0 0.100 mysql.user

  7. How to check slaves
  8. Once you’ve collected all those fancy checksums for your tables, nicely timestamped, you’ll want to verify that your slaves are happily in sync. You can do that with the following command, also on the master:

    $ pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=mysql localhost

    If there’s no differences you’ll see no output. If you have a difference it’ll look something like this:

    Differences on ip-10-15-27-19

    TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY

    mysql.user 1 1 1

    In our case you can see we created some users on the slaves accidentally, hence the differences. It illustrates how easy it is for differences to creep into your environment and also how easy it now is to find them!

  9. Special Cases
  10. Since one of my clients uses Drupal, they’ve had trouble replicating the semaphore table. This tables is a MyISAM table, and unfortunately no one dares convert it to InnoDB. So from time to time some gunk builds up in there, and it fails on the slave. We could clean out the table, but we decided to just filter out this one table. Since Drupal doesn’t use fully qualified schema.table names in it’s code, only “use” we have found this to be safe.

    However the percona toolkit explicitely checks for replication filters and will not run. It’ll stop with an error as follows:

    $ pt-table-checksum --replicate=test.checksum --databases=sean --ignore-tables=semaphore localhost

    04-24T15:59:29 Replication filters are set on these hosts:

    ip-10.15.27.19

    replicate_ignore_table = sean.semaphore

    ip-10-15-27-72

    replicate_ignore_table = sean.semaphore

    ip-10-15-27-18

    replicate_ignore_table = sean.semaphore

    Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-table-checksum line 6166.

The solution is the –nocheck-replication-filters option. Keep in mind that this sanity check is there for a reason, so be sure to skip the relevant tables in your checksum building, and checksum checks.

To build checksums skipping the semaphore table use this command:

$ pt-table-checksum --replicate=test.checksum --ignore-tables=prod.semaphore --nocheck-replication-filters localhost

Now you can check your slaves but ignore the semaphore table:


$ pt-table-checksum --replicate=test.checksum --replicate-check-only --ignore-tables=prod.semaphore --nocheck-replication-filters localhost

We also found a bug which preventing us from specifying multiple tabes on the ignore-tables line. So we used multiple invocations to do different schemas like this:


$ pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=prod1 --ignore-tables=prod1.semaphore --nocheck-replication-filters localhost

$ pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=prod2 --ignore-tables=prod2.semaphore --nocheck-replication-filters localhost

  • Crash Protection
  • If you’ve used MySQL replication for any length of time, you’ve probably seen a server crash. MySQL replication can have trouble restarting if you’re using temporary tables, as they’ll be missing upon restart. Also MySQL before 5.5 leaves syncing the info files to the operating system. So they may be incorrect after a crash.

    1. Use MySQL 5.5 if possible

    There are some new parameters in 5.5 that protect the info files from a crash. These are a great addition, and will make your slave databases more bulletproof.

    sync_master_info = 1

    sync_relay_log = 1

    sync_relay_log_info = 1

    2. Don’t use temporary tables

    After a restart they’ll simply be gone, so queries requiring or running against them will fail.

    The Percona guys’ new book High Performance MySQL third edition, suggests an alternate solution to using temporary tables. Use a special schema to hold your temp data, but create them as normal permanent tables. Be sure your application creates them with unique names, using the connection_id() for example. Lastly have a cleanup process drop tables periodically, based on closed connection_ids.

    Categories
    All Business

    Don't be that guy–Social tips for geeks

    Sheldon CooperAs a tech consultant one of the most interesting parts of the job is being able to observe human relations at work. I’ve learned through the years that because tech people and non-tech people speak different ‘languages’, bridging the communication gap is a critical part of my role as a consultant.

    Sometimes the relationship between tech and other business units is less sweet. The typical complaints are that IT guys are always denying requests, aloof and even downright unhelpful.

    On the other side, the geeks feel frustrated that people “just don’t listen”. We remind people to always use strong passwords, and people still make “password” their password. We train end-users and give specific guidance and instructions but they still commit the fundamental mistakes. Meanwhile, the managers expect IT staff to perform miracles.

    So who can be blamed when this animosity exists? Geeks for their arrogance? Or end-users for not making an effort to improve their understanding of tech concepts? Perhaps both sides can share the blame. But as tech folks we can try to make things better by working on our communication skills. Sliding into aloofness will not only make people resentful but suspicious of our motives too. Don’t be that guy!

    If you’ve found yourself slipping up in the people-skills department, here are a few tips that can help you along. Think of understanding and persuading other people as a puzzle equally complex as the biggest engineering challenge. In that light you can look at it as an ongoing project to improve your communication and charm.

    1. Please Speak My Language
    2. Martha Stewart said “the biggest mistake people make is they expect that others know stuff…”. Amen Martha. In my experience a lot of folks fall into this category. Ever been at a meeting where financial folks are waxing on about the business bottom line, margins, and shareholder value? If talk of capex, opex and other financial terms confound you, then you know the feeling. So why subject others to this when we talk tech? There’s no reason to, and people will love you for using a language that everyone can understand. Use analogies and stories to emphasize an idea or point so it resonates with your audience.

    3. Listen to me
    4. Everyone wants to be listened to; hopefully that’s obvious. But sometimes we get stuck on our own ideas, and focus more on people hearing us. It may sound counterintuitive, but psychologically speaking, listening more to the other person makes them listen to your ideas more. Start by giving plenty of time to speak, and try to repeat the other persons ideas in your own words. You’ll set the tone for a more reasoned dialogue and find your own thoughts heard more too.

    5. Be more positive
    6. Perhaps it is our engineering backgrounds, and the discipline that the scientific method ingrains in us. You may think that being critical is a common way to approach a discussion on issues. However this may come across as negative and stand-offish depending on how you communicate. What’s more if your audience doesn’t see things from your perspective, you may find yourself complaining and condemning proposals.

      Better to find the positive as a common starting point. Speak about all the things that work well first, before working your way around to points of difference.

    7. Speak slowly
    8. Psychologists have found that people sense more confidence and listen more to people who speak slowly. It may seem counterintuitive, after all if you speak quicker, you may be able to get that complicated idea out into the world before you are ever interrupted! What’s more speaking slowly allows you to think about what might come next, anticipating reactions, or even changing direction slightly in mid-stream. It also allows you more time to catch what might be a … or a slip of the tongue.

    9. A few more ideas to chew on…
      1. Smile more
      2. You may not be aware of how often you’re smiling or not. What’s more you may think it insincere to try to smile. But a frown, or other negative face can criticize your audience as much as actual words can. And it can set people off on the wrong foot, so they won’t listen to you either. Better to stay positive, and convey that with a smile.

      3. Remember & use people’s names
      4. People love to hear their own names. Remembering and using someone’s name improves the chances that they will listen to you and your ideas.

      5. Repeat what others say in your own words
      6. This one is really crucial. By repeating someone’s ideas in your own words you do a few things all at once. First you improve communication, as it is so often the case that we misunderstand someone else’s ideas, repeating them in your own words allows them to hear how you’ve digested their point, and allows them to comment or adjust if you missed something. It also shows them you are really listening. If you’re going to critique someone, and they feel you didn’t really get their idea, they’ll be very unlikely to listen.

      7. Try not to say “you’re wrong”
      8. Even in the cases where the other person is completely wrong, this statement may not have the intended affect. It may simple cause them to wall off and not listen to you. Better to point out the sides of what they are saying that you can agree with first, then come around to some differences.

      9. Read Dale Carnegie
      10. The classic book “How to Win Friends & Influence People” should really be required reading for the geek set. Being personable and charming may not be natural to all of us, but a lot of it can be learned with practice. Dale Carnegie has written a sort of bible on the topic, and it’s definitely worth a read.

    Categories
    All MySQL

    5 Ways to fortify MySQL replication

    fort

    Also find Sean Hull’s ramblings on twitter @hullsean.

    MySQL replication technology is powerful and flexible. But it doesn’t do everything perfectly all the time. You may experience trouble with the slaves falling behind the master, or want to scale horizontally by building new slaves automatically. Or you might need to build a slave without blocking the master database.

    All of these goals can be achieved using some powerful tools. Here’s a quick guide to those tools and how to use them.

      1. Build new Replicas without Downtime

    Something we’re sure you need to do quite often is to build new slaves. You can snapshot from another slave by bringing the slave down, copying it’s datadir to an alternate machine, updating the server_id and then starting up. However sometimes you have no slave, or your current slaves are serving data.

    In those cases you’ll need a way to snapshot off the master. With the great xtrabackup tool, you can create a snapshot of your production database without the locking of mysqldump. What’s more the resulting snapshot is a full datadir, so you won’t need to import all the data as you would with mysqldump. Saves yourself a lot of time!

    Take a look at our how-to for building replication slaves using hotbackups.

      1. Autoscale in the Cloud

    We wrote an extensive how-to diving into the nuts and bolts of MySQL Autoscaling.

      1. Use semisynchronous replication

    In MySQL 5.5, the Google code contributions got rolled into the Generally Available version. That brings some great new features and functionality to make your replicas more resilient and improve overall redundancy.

    We have an upcoming article planned for May that digs into configuring semisynchronous replication.

      1. Add integrity checking

    Statement based replication, as powerful as it is, has some serious limitations. As it turns out, many slave databases drift silently out of sync with the master. What’s more they don’t flag errors when they do so. The reason why this can happen has to do with the nature of statement based replication. If you combine transactional & non-transactional tables, for example, and a transaction rolls back, the statements on MyISAM tables will still get replicated, resulting in potentially different rows on the slave. Other cases include various non-deterministic functions such as sysdate which may return different results on the slave.

    Row-based replication begins to address these problems, by offering an alternative which includes the actual data changes replicated over the wire, instead of the statement instructions. Still this technology is new, and situations limit it’s use in some environments.

    The best way to handle this situation is by using the Percona Toolkit checksum tool. This calculates checksums just like you might to compare files at the filesystem, and just as rsync does to sync data over ssh.

    We have an upcoming article planned for May, covering table checksuming in depth. We’ll discuss how to collect the checksums via a cronjob, and then how to write a check which you can roll into Nagios, to monitor your data regularly.

      1. Watch out for the Potholes
      1. Use stored procedures & triggers sparingly or not at all

    Stored procedures can behave strangely with statement based replication, and can easily break things. That’s because stored procedures are fairly non-deterministic by nature. If you want your replication to be bulletproof, avoid them.

      1. Don’t write to both masters

    Sounds straightforward enough, but MySQL does not prevent you from doing so. We recommend you set the read-only flag on inactive masters, to protect you. You could also monitor certain key tables to be further cautious.

      1. Be sure to set unique server_id

    MySQL’s binary logging uses the server_id setting to uniquely identify servers in the replication topology. So it’s crucial that they be set on all servers, and be unique.

      1. Be very wary using temporary tables

    If a slave server dies, it’s temporary tables will be missing upon restart. If you have subsequent queries that rely on them, they’ll fail. Better to avoid them or use them very sparingly.

      1. Avoid MyISAM tables altogether

    InnoDB should really be used for all MySQL environments. Any exceptions are so few, as to be not worth mentioning.

    MyISAM tables are not crash safe, can and will lose data, and can be very slow due to locking problems.

    Do you need to do full text searching, Sphinx comes to mind. It’s more scalable, takes load off the database server, and is lightening quick!

    Lastly MyISAM tables can break replication, and we don’t want that! They’re not transaction safe, so if you mix them with InnoDB, bad things can happen!

      1. Avoid non-deterministic functions.

    Some functions such as UUID() and RAND() will behave differently on different calls. That means they may behave differently when the slave calls the same statement it fetches from the binlog. These types of problems may break things silently, allowing your slave to drift out of sync with your master without error. See integrity checking above for ways to fix this.

      1. UPDATE with LIMIT clause

    This interesting SQL combination relies heavily on the current sort of rows in the table. Suffice it to say it can behave differently on the slave, so be cautious if you have this syntax in your code. It may break replication.

    1. Row-based replication (still fairly new)

    We are not ready to recommend row-based replication yet, as there have been reports of some troubles and surprises with it. It is a fairly new code path and though it’s included in GA release, we don’t see it widely in production yet. It does take a stab at addressing many of the issues with statement based replication though, so the potential is there. We expect to see it used more widely in the future.

    Categories
    All MySQL

    Easy MySQL replication with hotbackups

    Clone army

    Also find Sean Hull’s ramblings on twitter @hullsean.

    Setting up replication in MySQL is something we need to do quite often. Slaves die, replication fails, or tables and data get out of sync. Whenever we build a slave, we must start with a snapshot of all the data from the master database.

    MySQLdump is the tried and true method of doing this, however it requires that we lock all the tables in the database. If we’re dumping a large database, this could be a significant period, where no writing can happen to our database for the duration of the backup. For many environments read-only is still an outage.

    Enter hotbackups to the rescue. Percona comes with a tool that allows you to perform hotbackups of a running MySQL database, with no blocking. It’s able to do this because of Innodb & multi-version concurrency control (MVCC). Luckily we don’t need to dig into the guts to enjoy the benefits of this great technology.

    Here’s a quick step-by-step guide to using xtrabackup to create a slave.

    1. Install xtrabackup
    2. If you don’t have any Percona software already on your server, don’t worry. You don’t need to use the Percona distribution to use xtrabackup. But you will need their repository installed. Here’s how:

      $ rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

      From there simply install xtrabackup:

      $ yum install -y xtrabackup
    3. Snapshot master datadir
    4. The innobackupex utility comes with xtrabackup, and does the heavy lifting. So we can just use that to perform the backup.

      $ innobackupex /data/backup/

      Now we’ll see a new directory created inside /data/backup which looks something like this:

      /data/backup/2012-04-08_04-36-15/
    5. Apply binary logs
    6. The backup which xtrabackup created above is of the current state of the database. However there are transactions which are incomplete, and others which have not been flushed to their datafiles. In other words the backup as-is would be similar to a datadir if your database crashed. Some additional transactions must still be applied.

      To apply those changes, use the following command on the backup directory you created above:

      $ innobackupex --apply-log /data/mysql/backup/2012-04-08_04-36-15/
    7. Copy to slave
    8. $ scp -r /data/mysql/backup/2012-04-08_04-36-15 [email protected]:/data/
    9. Stop MySQL
    10. $ /etc/init.d/mysql stop
    11. Swap datadir
    12. $ cd /data$ mv mysql mysql_old$ mv 2012-04-08_04-36-15 mysql

    13. Adjust my.cnf parameters
    14. At minimum you need to set the server_id to a unique value. The IP address with the periods removed can make a good server_id.

    15. Start MySQL
    16. $ /etc/init.d/mysql start
    17. Point to master & start the slave
    18. One very nice thing about xtrabackup is that it automatically captures the master info, so we’ll easily be able to find out the current log file & log position! That’s a very nice feature.

      Find out where the slave should start from:

      $ cat /data/mysql/xtrabackup_binlog_infolog_bin.000027 2973624

      Now tell MySQL where the new master is:

      mysql> change master to-> master_user=’rep’,-> master_password=’rep’,

      -> master_host=’10.20.30.40′,

      -> master_log_file=’log_bin.000027′,

      -> master_log_pos= 2973624;

      Now start the slave:

      mysql> start slave;

      Lastly verify that it is running properly:

      mysql> show slave statusG;

      You should see the following:

      Slave_IO_Running: YesSlave_SQL_Running: Yes
    19. Test Replication
    20. Once you have replication up and running, you should test it as well. I like to keep a test table installed in the test schema for this purpose. Then you can test as follows:

      master> insert into sean_test values ('xtrabackup is a great way to create a slave with MySQL');

      Then verify that you see that row on your new slave:

      slave> select * from sean_test;

      Once you’ve used xtrabackup a few times, I’m sure you’ll be converted. It makes building a slave much simpler in MySQL. It captures the file & position for you and what’s more there is no dump file to apply – which typically takes a lot of time too! All in all the tool makes you more efficient, and allows you to snapshot slaves anytime you like.

      Now that you have replication working, you should add the icing to the cake. MySQL’s statement based replication is powerful, but even when it’s not throwing errors, the databases can get silently out of sync. In a future article we’ll discuss how to bulletproof your replication setup with a tool that performs checksums on your tables. That will give you professional enterprise class data protection in MySQL.

    Categories
    All Book Review

    Thank You for Arguing – Persuasion for fun and profit

    thank you for arguing cover

    Join 10,000 others and follow Sean Hull on twitter @hullsean.

    I first read about Heinrichs in a Bloomberg Businessweek piece on him. He’s quite a character, with high profile clients like Ogilvy & Mather and the Pentagon. Struck by some of his ideas, I decided to pickup Thank You for Arguing.

    Related: AirBNB Didn’t Have to Fail – With AWS Outage

    48 laws of soft power

    Compiled into 25 very readable chapters, Heinrichs illustrates how to win trust through managing your voice with volume control for positive affect, verbal jousting and calling fouls, and mastering timing. Sure in the real world this is all going to require a lot of trial and error, and practice in the trenches. But his book serves as a very good guide along the way.

    Also: 5 Conversational Ways to Evaluate Great Consultants

    Don’t worry too much about Aristotle, Cicero or the classics you never learned in school. If anything they serve as a colorful highlight to his useful everyday illustrations.

    Some examples worth recalling…

    1. Have a disagreement at a meeting? Diffuse it with “let’s tweak it”.

    2. Pay attention to your tenses:

    o using past tense the conversation is trying to place blame
    o using present tense you’re talking about values
    o using future tense you’re considering choices and solutions

    3. Pay attention to commonplaces – your audience’s beliefs and values

    4. Effective argument works by:

    o appealing to character (pathos) understand your audience’s personality
    o using logic (logos)
    o appealing to emotion (ethos)

    Read this: RDS or MySQL – 10 Use Cases

    [quote]
    I know what I believe. I will continue to articulate what I believe and what I believe–I believe what I believe is right. – George W. Bush
    [/quote]

    He has one whole chapter on Bushisms, which I found intriguing. Bush used code grooming to very strong effect. When speaking to different groups, he emphasized these code words in his sentences. With women, words like “I understand”, “peace”, “security” and “protecting”. With a military group words such as “never relent”, “we must not waver” and “not on my watch” were common. For religious audiences, “I believe” resonated strongly. He quotes a superb Bushism which in this light suddenly begins to sound powerful:

    Check out: A CTO Should Never Do This

    “I know what I believe. I will continue to articulate what I believe and what I believe–I believe what I believe is right.”

    Rhetoric indeed. I’ll be studying this book for months to come!

    Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

    Categories
    All MySQL

    Autoscaling MySQL on Amazon EC2

    Also find Sean Hull’s ramblings on twitter @hullsean.

    Autoscaling your webserver tier is typically straightforward. Image your apache server with source code or without, then sync down files from S3 upon spinup. Roll that image into the autoscale configuration and you’re all set.
    autoscaling MySQL
    With the database tier though, things can be a bit tricky. The typical configuration we see is to have a single master database where your application writes. But scaling out or horizontally on Amazon EC2 should be as easy as adding more slaves, right? Why not automate that process?

    Below we’ve set out to answer some of the questions you’re likely to face when setting up slaves against your master. We’ve included instructions on building an AMI that automatically spins up as a slave. Fancy!

    1. How can I autoscale my database tier?
      1. Build an auto-starting MySQL slave against your master.
      2. Configure those to spinup. Amazon’s autoscaling loadbalancer is one option, another is to use a roll-your-own solution, monitoring thresholds on servers, and spinning up or dropping off slaves as necessary.
    2. Does an AWS snapshot capture subvolume data or just the SIZE of the attached volume?
    3. In fact, if you have an attached EBS volume and you create an new AMI off of that, you will capture the entire root volume, plus your attached volume data. In fact we find this a great way to create an auto-building slave in the cloud.

    4. How do I freeze MySQL during AWS snapshot?
    5. mysql> flush tables with read lock;mysql> system xfs_freeze -f /data

      At this point you can use the Amazon web console, ylastic, or ec2-create-image API call to do so from the command line. When the server you are imaging off of above restarts – as it will do by default – it will start with /data partition unfrozen and mysql’s tables unlocked again. Voila!

      If you’re not using xfs for your /data filesystem, you should be. It’s fast! The xfsprogs docs seem to indicate this may also work with foreign filesystems. Check the docs for details.

    6. How do I build an AMI mysql slave that autoconnects to master?
    7. Install mysql_serverid script below.

      1. Configure mysql to use your /data EBS mount.
      2. Set all your my.cnf settings including server_id
      3. Configure the instance as a slave in the normal way.
      4. When using GRANT to create the ‘rep’ user on master, specify the host with a subnet wildcard. For example ‘10.20.%’. That will subsequently allow any 10.20.x.y servers to connect and replicate.
      5. Point the slave at the master.
      6. When all is running properly, edit the my.cnf file and remove server_id. Don’t restart mysql.
      7. Freeze the filesystem as described above.
      8. Use the Amazon console, ylastic or API call to create your new image.
      9. Test it of course, to make sure it spins up, sets server_id and connects to master.
      10. Make a change in the test schema, and verify that it propagates to all slaves.
    8. How do I set server_id uniquely?
    9. As you hopefully already know, in MySQL replication environment each node requires a unique server_id setting. In my Amazon Machine Images, I want the server to startup and if it doesn’t find the server_id in the /etc/my.cnf file, to add it there, correctly! Is that so much to ask?

      Here’s what I did. Fire up your editor of choice and drop in this bit of code:

      #!/bin/shif grep -q “server_id” /etc/my.cnf

      then

      : # do nothing – it’s already set

      else

      # extract numeric component from hostname – should be internet IP in Amazon environment

      export server_id=`echo $HOSTNAME | sed ‘s/[^0-9]*//g’`

      echo “server_id=$server_id” >> /etc/my.cnf

      # restart mysql

      /etc/init.d/mysql restart

      fi

      Save that snippet at /root/mysql_serverid. Also be sure to make it executable:

      $ chmod +x /root/mysql_serverid

      Then just append it to your /etc/rc.local file with an editor or echo:

      $ echo "/root/mysql_serverid" >> /etc/rc.local

      Assuming your my.cnf file does *NOT* contain the server_id setting when you re-image, then it’ll set this automagically each time you spinup a new server off of that AMI. Nice!

    10. Can you easily slave off of a slave? How?
    11. It’s not terribly different from slaving off of a normal master.

      1. First enable slave updates. The setting is not dynamic, so if you don’t already have it set, you’ll have to restart your slave.
      2. log_slave_updates=true
      3. Get an initial snapshot of your slave data. You can do that the locking way:
      4. mysql> flush tables with read lock;mysql> show master statusG;

        mysql> system mysqldump -A > full_slave_dump.mysql

        mysql> unlock tables;

        You may also choose to use Percona’s excellent xtrabackup utility to create hotbackups without locking any tables. We are very lucky to have an open-source tool like this at our disposal. MySQL Enterprise Backup from Oracle Corp can also do this.

      5. On the slave, seed the database with your dump created above.
      6. $ mysql < full_slave_dump.mysql
      7. Now point your slave to the original slave.
      8. mysql> change master to master_user='rep', master_password='rep', master_host='192.168.0.1', master_log_file='server-bin-log.000004', master_log_pos=399;mysql> start slave;

        mysql> show slave statusG;

    12. Slave master is set as an IP address. Is there another way?
    13. It’s possible to use hostnames in MySQL replication, however it’s not recommended. Why? Because of the wacky world of DNS. Suffice it to say MySQL has to do a lot of work to resolve those names into IP addresses. A hickup in DNS can interrupt all MySQL services potentially as sessions will fail to authenticate. To avoid this problem do two things:

      1. Set this parameter in my.cnf
      2. skip_name_resolve = true
      3. Remove entries in mysql.user table where hostname is not an IP address. Those entries will be invalid for authentication after setting the above parameter.
    14. Doesn’t RDS take care of all of this for me?
    15. RDS is Amazon’s Relational Database Service which is built on MySQL. Amazon’s RDS solution presents MySQL as a service which brings certain benefits to administrators and startups:

      • Simpler administration. Nuts and bolts are handled for you.
      • Push-button replication. No more struggling with the nuances and issues of MySQL’s replication management.
      • Simplicity of administration of course has it’s downsides. Depending on your environment, these may or may not be dealbreakers.

      • No access to the slow query log.
      • This is huge. The single best tool for troubleshooting slow database response is this log file. Queries are a large part of keeping a relational database server healthy and happy, and without this facility, you are severely limited.

      • Locked in downtime window
      • When you signup for RDS, you must define a thirty minute maintenance window. This is a weekly window during which your instance *COULD* be unavailable. When you host yourself, you may not require as much downtime at all, especially if you’re using master-master mysql and zero-downtime configuration.

      • Can’t use Percona Server to host your MySQL data.
      • You won’t be able to do this in RDS. Percona server is a high performance distribution of MySQL which typically rolls in serious performance tweaks and updates before they make it to community addition. Well worth the effort to consider it.

      • No access to filesystem, server metrics & command line.
      • Again for troubleshooting problems, these are crucial. Gathering data about what’s really happening on the server is how you begin to diagnose and troubleshoot a server stall or pileup.

      • You are beholden to Amazon’s support services if things go awry.
      • That’s because you won’t have access to the raw iron to diagnose and troubleshoot things yourself. Want to call in an outside consultant to help you debug or troubleshoot? You’ll have your hands tied without access to the underlying server.

      • You can’t replicate to a non-RDS database.
      • Have your own datacenter connected to Amazon via VPC? Want to replication to a cloud server? RDS won’t fit the bill. You’ll have to roll your own – as we’ve described above. And if you want to replicate to an alternate cloud provider, again RDS won’t work for you.

    Categories
    All High Availability iHeavy Newsletter

    The myth of five nines – Why high availability is overrated

    nine_clock

    Join 38,000 others and follow Sean Hull on Twitter @hullsean.

    In the Internet world 24×7 has become the de facto standard. Websites must be always on, available 24 hours a day, 365 days a year. In our pursuit of perfection, performance is being measured down to three decimal places, that is being up 99.999% of the time; in short, five-nines

    Just like a mantra, when repeated enough it becomes second nature and we don’t give the idea a second thought. We don’t stop to consider that while it may be generally a good thing to have, is five-nines necessary and is it realistic for the business?

    Also: How to hire a developer that doesn’t suck

    In my dealings with small businesses, I’ve found that the ones that have been around longer, and with more seasoned managers tend to take a more flexible and pragmatic view of the five-nines standard. Some even feel that periods of outages during off hours as – *gasp* – no problem at all! On the other hand it is a universal truth held by the next-big-idea startups that 24×7 is do or die. To them, a slight interruption in service will send the wrong signal to customers.

    The sense I get is that businesses that have been around longer have more faith in their customers and are confident about what their customers want and how to deliver it.  Meanwhile startups who are building a customer base feel the need to make an impression and are thus more sensitive to perceived limitations in their service.

    Of course the type of business you run might well inform your policy here. Short outages in payments and e-commerce sites could translate into lost revenue while perhaps a mobile game company might have a little more room to breathe.

    Related: Why generalists are better at scaling the web

    Sustaining five nines is too expensive for some

    The truth is sustaining high availability at the standard of five-nines costs a lot of money. These costs are incurred from buying more servers, whether as physical infrastructure or in the cloud. In addition you’ll likely involve more software components and configuration complexity. And here’s a hard truth, with all that complexity also comes more risk.  More moving parts means more components that can fail. Those additional components can fail from bugs, misconfiguration, or interoperability issues.

    What’s more, pushing for that marginal 0.009% increase in high availability means you’ll require more people and create more processes.

    Read this: The Art of Resistance

    Complex architecture downtime

    In a client engagement back in 2011, I worked with a firm in the online education space.  Their architecture was quite complex.  Although they had web servers and database servers—the standard internet stack—they did not have standardized operations.  So they had the Apache web server on some boxes, and Nginx on others.  What’s more they had different versions of each as well as different distributions of Linux, from Ubuntu to RedHat Enterprise Edition.  On the database side they had instances on various boxes, and since they weren’t all centralized they were not all being backed up.  During one simple maintenance operation, a couple of configurations were rearranged, bringing the site down and blocking e-commerce transactions for over an hour.  It wasn’t a failure of technology but a failure of people and processes made worse by the hazard of an overly complex infrastructure.

    In another engagement at a financial media firm, I worked closely with the CTO outlining how we could architect an absolutely zero downtime infrastructure.  When he warned that “We have no room for *ANY* downtime,” alarm bells were ringing in my head already.

    Also: How do I migrate my skills to the cloud?

    When I hear talk of five-nines, I hear marketing rhetoric, not real-world risk reduction.   Take for example the power grid outage that hit the Northeast in 2003.  That took out power from large swaths of the country for over 24 hours.  In real terms that means anyone hosted in the Northeast failed five-nines miserably because downtime for 24 hours would be almost 300 years of downtime at the five-nines standard!

    For true high availability look at better management of processes

    So what can we do in the real-world to improve availability?  Some of the biggest impacts will come from reducing so-called operator error, and mistakes of people and processes.

    Before you think of aiming for five-nines,  first ask some of these questions:

    o Do you test servers?
    o Do you monitor logfiles?
    o Do you have network wide monitoring in place?
    o Do you verify backups?
    o Do you monitor disk partitions?
    o Do you watch load average?
    o Do you monitor your server system logs for disk errors and warnings?
    o Do you watch disk subsystem logs for errors? (the most likely component in hardware to fail is a disk)
    o Do you have server analytics?  Do you collect server system metrics?
    o Do you perform fire drills?
    o Have you considered managed hosting?

    If you’re thinking about and answering these questions you’re well on your way to improving availability and uptime.

    Read this: How to setup an Amazon ECS cluster with Terraform

    Want more? Grab our Scalable Startups monthly for more tips and special content. Here’s a sample