banyek.net

Category: tech

  • A few words about database checksumming

    MySQL replication is great, and kind of reliable, but sometimes it could be messed up. The good news is that we can handle this.

    Let’s see how replication happens when everything is fine!

    (more…)

  • How to remove database in a safe way

    When you have to drop a large database, you’ll encounter some problems, mainly replication lag. Now I’ll show you how to avoid this.

    What can cause replication lag when you drop a database? First, it takes some disk I/O to unlink the files, and secondly, MySQL will scan through the buffer pool to see if there are pages from that database or not. On a huge (or at least big) database this could take seconds or even minutes, what means your slaves will collect lag for seconds or (of course) even minutes.

    (more…)

  • Mambocollector – MySQL -> statsd data collector

    We love graphs.

    Really love them. I think everyone likes graphs to collect data about the current state of their system, and needless to say, why.

    Of course, sometimes it is painful to create graphs, but graphite could make this process easier, so we use them.

    The graphite ecosystem makes data collection simple, when you send some data to statsd via simple UDP packets it will put them to a carbon database, and graphite will draw the lines. The only thing that can make this hard, is the question of ‘How can I collect my data to send?’

    Well, I’ve checked many ways to solve this problem, but I didn’t find anything simple enough.

    So, I wrote a daemon called ‘Mambocollector’ to deal with this problem. It is far from perfect, it has bugs, and I am not sure if it isn’t used too many resources when we collect a too many data, but it is working fine for my current needs. The project can be found on GitHub, feel free to use it, or participate in that.
    UPDATE: This version was bugging badly, I rewrote it in Go.

  • Syncing differences in MySQL cluster

    In my last post, I showed how to fix replication errors on slaves, but I’ve made a mistake: my current example wasn’t good, after skipping the command or inserting an empty transaction the dataset was different because of a timestamp holding date column which is the CURRENT_TIMESTAMP default. Fixing the error solved the problem of the running replication thread, but the data wasn’t same on the hosts. I decided to leave this as-is, and instead of recreating the test, I rather show how to sync the databases.

    For this, we can use the ‘pt-table-checksum’ and ‘pt-table-sync’ utilities from Percona toolkit.

    (more…)

  • Fixing broken replication in a GTID based scenario

    Every MySQL DBA should deal with the situation, when there were an accidental write on one of the slaves. Changing replication to GTID will change the way how we should deal with that problem.

    Let’s check out!

    (more…)

  • MySQL replication module upgrade

    Yesterday I’ve put some new features into the ansible’s mysql_replication module because we are planning to move to GTID based replication from the good old binlog position based one, and the module wasn’t aware of.

    gtid_replication

    This parameter can be either 0 or 1, defaults to 0. If set to 1 the replication will be threaded as GTID based replication.

    warnings_filtered

    This parameter threats the warnings because of MySQL 5.6 complaints a lot more than the previous versions. (For example, if the replication is not encrypted with SSL/TLS.) This could break our playbooks so you can set it to all, warnings, none (defaults none). Speaks for itself, all means all warnings/errors will be shown, if warnings set, then only the errors will be shown, and the warnings suppressed, and if none then that means, every message will be show up as-is.

    If you need it, and you don’t want to wait until it is merged and released, you can download it from my GitHub repository.

  • GTID based replication showcase

    We are in the middle of switching to GTID based replication from the good old logfile & log position based replication.

    But what is GTID? GTID is an abbreviation of ‘GLOBAL TRANSACTION ID’ what speaks for itself: each transaction of a mysql cluster got its globally unique transaction ID, and the DBA has not spent time with positioning slaves, as well as we don’t have to ‘freeze’ any of the servers because of a master failover. The only thing we have to care about: to know what server should be used as a replication master.

    OK, what was the problem with the old file-based replication?

    (more…)

  • Upgrading MySQL

    Upgrading MySQL is easy…

    Upgrading mysql to a newer version is very simple: just replace the binaries, and run:

    # mysql_upgrade

    watch the error log after restarting the service to check if any configuration parameter has to change because of renaming, deprecating etc. and after all, it could be done in a few minutes.

    (more…)

  • Speeding up compressing with parallel computing

    Creating backups and compressing files are always a time-consuming task, for example, to create the daily backup on the kinja related databases were taken about 6,5 hours every day. The first part of creating the backup itself is about 40 minutes long task – that’s the time of the run of innobackupex, and applying the changed logfiles to db. (I’ll write about this later!) The second part of the backup is to compress files before they will be copied to the storage server…and this step took about 5,5 hours. I have written this part of the backup script with the old-fashioned compression utility – gzip (and tar of course!)

    # Normally this is the way, how you compress a tarfile on the fly:
tar -czf backup.tar.gz /path/to/backupdir

    This is totally awesome in the most of the times, but you have to know one thing: the gzip only uses one processor core during the operation, so if you have a beefy hardware, you can’t even scratch the total throughput what your machine could do.

    So, the solution is to parallelize all the operation.

    Here is a good comparison of parallel compressing software.

    I’ve decided to use pigz, so I’ve modified the regarding part of the backup script like this:

    tar -c --use-compress-program=pigz -f backup.tar.gz /path/to/backupdir

    The result could be read from this article’s head image: the compression itself completes in a half of an hour (this is 11 times faster than before!)

    So my rebuild part of the developer’s database could complete before the devs starting to use it. (hm… I think this will be a new post either.)

  • MySQL replication caveats, or why to be cautious.

    Recently we had a small hiccup in our service; it was caused by our mysql replication configuration and a small mistake which was made a long time before. The lesson was learned, and now I show that to you.

    MySQL replication is simple. The replica set’s primary server (aka. replication master) writes the events which affect the database to a binary log file, and slaves will apply that log on their datasets. The binary log can be statement-based, row-based or in mixed mode, the events can be either DML or DDL queries. Simple enough.

    (more…)