I was really pleased to see the announcement by Oracle MySQL yum repositories that they have now produced a yum repository from where the MySQL RPMs they provide can be downloaded. This makes keeping up to date much easier. Many companies setup internal yum repositories with the software they need as then updating servers is much easier and can be done with a simple command. For many people at home that means you set this up once and don’t need to check for updates and do manual downloads, but can do a quick yum update xxxx and you get the latest version. Great!  This new yum repository only covers RHEL6 did not include RHEL5 which is not yet end of life and still used by me and probably quite a lot of other people. I filed bug#70773 to ask for RHEL5 support to be …

Read the original article at Limitations of MySQL row-based replication

MySQL offers a few different options for how you perform replication. Statement-based has been around a lot longer, and though it has some troublesome characteristics they’re known well and can be managed. What’s more it supports online schema changes with multi-master active-passive setup. We recommend this solution. Row-based replication is newer. It attempts to address [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. Why does MySQL replication fail?
2. …

Read the original article at Accidental DBA’s Guide to MySQL Management

So you’ve been tasked with managing the MySQL databases in your environment, but you’re not sure where to start.  Here’s the quick & dirty guide. Oh yeah, and for those who love our stuff, take a look to your right. See that subscribe button? Grab our newsletter!

1. Installation

The “yum” tool is your friend.  If you’re using debian, you’ll use apt-get but it’s very similar. You can do a “yum list” to see what packages are available. We prefer to use the Percona distribution of MySQL.  It’s fully compatible with stock MySQL distribution, but usually a bit ahead in terms of tweak and fixes.  Also if …

I explored two interesting topics today while learning more about Postgres.

Partial page writes

PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Trying to reduce the cost of full-page writes by increasing the checkpoint interval highlights a compromise. …

When I was doing data loading tests, I realized that usually low checksum calculation CPU percentage is actually the blocking factor. See, usually when background writers do the flushing, it gets parallelized, but if active query is forcing a checkpoint, it all happens in ‘foreground’ thread, checksum computation included. This is where more Sun-ish wisdom (these people tune kernel with debugger all the time) comes in:

gdb -p $(pidof mysqld) -ex "set srv_use_checksums=0" --batch

Puff. Everything becomes much faster. Of course, one would be able to restart the server with –skip-innodb-checksums, but that would interrupt the whole process, etc. Of course, proper people would implement tunable parameter (5 lines of code, or so), but anyone with Solaris experience knows how to tune stuff with debuggers, hahaha.

Odd though, I …

I created MySQL Table Checksum because I was certain replication slaves were slowly drifting out of sync with their masters, and there was no way to prove it. Once I could prove it, I was able to show that replication gets out of sync for lots of people, lots of times. (If you really want to hear war stories, you should probably talk to one of the MySQL support staff or consulting team members; I'm sure they see this a lot more than I do).

I finally figured out what was causing one of my most persistent and annoying out-of-sync scenarios. It turns out to be nothing earth-shaking; it's just an easy-to-overlook limitation of statement-based replication. You could call it a bug, but as far as I can see, there's no way to fix it with statement-based replication. (I'd love to be proven wrong). Read on for the details.