The usual IT babble
Posts tagged MySQL
MySQL: Beware of sync_binlog on EXT3
Feb 23rd
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
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.
- Don’t you never ever change InnoDB settings when migrating between hardware,
because InnoDB is rather sensitive regarding those parameters. - 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.
As you can see on the graph above (hah, sometimes Visio is rather useful 
), 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:
- Setup the user mysql_repl for mysql%.home.barfoo.org, granting REPLICATION SLAVE.
- 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 >
MySQL: Setting up an InnoDB raw device
Feb 11th
Well, since I had to brood about this (again I might add), I’m gonna write it down this time …
Setting up the InnoDB raw device isn’t that hard, just make sure the device has proper permissions (either add mysql to the disk group or create a udev rule).
KERNEL="sdb2", OWNER="mysql", GROUP="mysql"
Now after that (and a reboot/udevcontrol reload_rules later), you should be able to initialize the InnoDB device. Yes, the InnoDB device needs initializing.
When you create a new data file, you must put the keyword newraw immediately after the data file size in innodb_data_file_path.
The next time you start the server, InnoDB notices the newraw keyword and initializes the new partition.
After that is done, you should be able to start the MySQL service for the first time. It is gonna fail (at least according to the init-script), but ultimatly if you take a closer look at /var/log/mysqld.log it’s gonna be successful.
14:11:07 InnoDB: Setting file /dev/sdb2 size to 50200 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 ... 50200
14:21:26 InnoDB: Log file ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /var/lib/mysql/db/ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
14:21:34 InnoDB: Log file ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
14:21:42 InnoDB: Started; log sequence number 0 0
After that, remove the “newraw” from your /etc/my.cnf. Otherwise, MySQL is gonna reinitialize the volume all over again, as the handbook states.
However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost.
After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw.
Defragmenting all fragmented MyISAM tables
Sep 19th
I just had another look at what I wrote the week before last (you know, being home-sick/on vacation has it’s advantages) and additionally read up on “OPTIMIZE TABLE” again. The comments in the manual mention “SHOW TABLE STATUS“, which gives you a complete list, but it doesn’t allow you to filter certain kinds of things out (like I only wanted to see MyISAM tables in the list, I only wanted database and table).
So I went ahead and looked around in MySQL’s own databases and if you look closely at information_schema, it’s got a list of all databases/tables with an additional pointer whether or not databases are fragmented, the row Data_free. I only found this, because I looked at how the mysqltuner figured whether or not you have fragmented tables.
So, without further ado, here’s the final script I’m gonna torture for the next week:
#!/bin/bash
# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME \
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND \
Data_free > 0' | grep -v "^+" | sed "s,\t,.," )"
for fragment in $FRAGMENTED_TABLES; do
database="$( echo $fragment | cut -d. -f1 )"
table="$( echo $fragment | cut -d. -f2 )"
[ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;\
OPTIMIZE TABLE $table;" > /dev/null 2>&1
done
# vim: set tw=80 ts=2 st=2 et :
I know it ain’t completely bullet proof and it sure as hell isn’t neat, but I think it does the job. Also, if you don’t want to paste it, here’s the file download.
Been a while
Feb 16th
Well, it’s been quite a while since most of the people last heard a word from me. The last few months I’ve been extremely busy with work-related tasks (and as a side-effect of that, didn’t want to spend much time in front of the computer after 9 hours of work). I also started spending more and more time in the gym, like nearly two hours every Tuesday and Thursday.
- I finally fixed our replication issues, we do now have a working! MySQL Multi-Master (1. Node, 2. Node — bear in mind, this boxes are *only* serving MySQL and nothing else, so don’t use these configurations on mixed setups) Replication Setup as database back end for our TYPO3-vHosts.
- all the web nodes are now serving the content from a clustered, shared SAN volume (is that a good thing ? – don’t know yet …)
- our VI environment is getting more and more acceptance (even if you hear some complaints now and then, like “awww, damn that crap my 4GiB RAM, 2×3.0GHz Windows 2008 is running soooo choppy” – simple answer, don’t use Windows Server 2008 and/or Windows Vista!)
- I finished prepping our VM templates (at least the Windows ones)
- we’re still putting together the plans on whether or not invest into a VDI solution.
The next few weeks are gonna be as frantic as the weeks before, I still have to migrate a lot of TYPO3 installations to our new cluster (which sadly needs time, as we need to wait for DNS changes to propagate). Honestly, I might be ending up extending the SAN volume for the MySQL data storage, as even with only three somewhat busy sites, the binary log of the last 5 days is about 2GiB in size. And we still have ~20 other busy sites on a separate box.
Lucky me, I created the MySQL data storage on a logical volume, so I can easily extend the volume in the san-manager semi-online (the fs needs to be unmounted and thus the MySQL process), then extend the physical volume (LVM2 PV) and the logical volume (LV) afterwards, and at last the underlying EXT3 file system.
As some of you know by now, I am on extended leave for now. I don’t have tree access (at my own request), though I’m gonna try to keep up with Chris and 2008.0 … So long!