Posts tagged MySQL

MySQL: Beware of sync_binlog on EXT3

Feb 23rd

Posted by Christian in Life

Well, I just glazed again over my my.cnf for our web-cluster because I just moved a database from one cluster to another and getting quite different performance from it. So, as I expected, there is a slight difference between both configuration files:

@@ -55,8 +58,6 @@
 innodb_log_group_home_dir       = /var/lib/mysql/db
 innodb_log_file_size            = 512M
 innodb_thread_concurrency       = 8
-sync_binlog                     = 1

And apparently, according to the MySQL Performance Blog that’s really, really bad (as well, we’re currently running without write caching, as the battery module of the storage is dead).

, ,

MySQL: Replication and hostname wild cards

Feb 15th

Posted by Christian in Life

Yeah, yeah .. I know, it’s weekend. But I usually can think much better when no one is rattling my cage. So I had another look at my replication problems.

  1. Don’t you never ever change InnoDB settings when migrating between hardware,
    because InnoDB is rather sensitive regarding those parameters.
  2. When you’re setting up the replication (don’t ask me why) and copying over the database to the second replication partner, be aware if you’re using wild cards you’re gonna get seriously bitten in the back.

Now, let’s look at the constellation.

mysql-nodes

As you can see on the graph above (hah, sometimes Visio is rather useful :-P ), we do have two MySQL nodes, each serving as master (as in we’re doing “normal” master-master replication).

Here’s what we’re gonna do first:

  1. Setup the user mysql_repl for mysql%.home.barfoo.org, granting REPLICATION SLAVE.
  2. Setup the user mysql_slave for mysql1.home.barfoo.org and mysql2.home.barfoo.org, also granting REPLICATION SLAVE.

Afterwards, we’re gonna copy the mysql database (either via tar and scp, or just via rssh — which is rsync via ssh) to both nodes. More >

, , ,
12345»