[Snaplogic-dev] Switching SVN checkout users .
I didn’t actually use it, I wound up blowing away the checkout and redoing it as the proper user, but this looked like a handy link to keep around. And thats really what this blog is, me talking to myself. Also russian comment spammers, hi guys!
So I guess they announced this a week or so back, but Joyent is now selling mysql in a somewhat appliance like fashion as their “mysql accelerator”. Pretty cool overall.
Unfortunatly in an attempt to show it off they put out a benchmark blog post that is inconclusive at best: Blog Joyent | On Benchmarking Databases: MySQL on Joyent versus AWS part 1.
Running MySQL on anything more than trivial hardware with the default “my.cnf” means you’re going to run into any of a number of artificial ceelings such that if you benchmark it that way the results don’t really mean anything. If anything, it means that the people selling you a mysql appliance either don’t know that, or are being that level of soft-misleading that kinda undercuts joyents vibe.
However all that is practically sideshow to this little nugget: MySQL Read-Write Splitting with Zeus Accelerators. Thats right, they’re *selling* a service built on using a loadbalancer/app-proxy to split your reads and writes for you. Thats pretty epic. I have yet to see anyone get that working in a proxy/loadbalancer fashion, let alone get it to sellable-service-product status.
Sorry not trying to make this a MySQL blog.
Jay Pipes is not only checking in the code, but giving an excellent series of posts on how it works starting with this one:
Drizzle Replication – The Command Message – Jay Pipes.
I’ve been using replication for HA and scaling since 3.23 and IMHO it is *the* killer feature of mysql. Drizzle getting this right is crucial for me as a user, and while I’m not C/C++ developer it really looks like Jay is doing quality careful engineering.
Wait… what?
Bullshit, thats backwards.
Thats what I thought. Then my DBA schooled me on two new feathers in my mysql wizards cap:
1.) if you’re very very careful, myisam supports concurrent inserts, which means you can write to a table like crazy without locking it
2.) innodb prior to 5.1.22 does not have a table lock on insert, but it does have a lock on generating the next entry for an auto_increment column, such that any insert into that table that needs a new auto incremented ID is stuck in line waiting for its turn. Which isn’t technically a table lock, but if most of your inserts are new records using that auto incremented column, its a similar result.
So if you combine the two corner cases of an app that almost only ever append-inserts and a particular version of mysql, the conventional wisdom on innodb vs. myisam completely inverts itself.
databases are hard
DO NOT DO THIS
turns out the multipath.conf example from xone is all sorts of wrong, it effectively gets ignored and you’re using the defaults (which it turns out work ok-ish). hopefully I’ll update here when I have two days to blow on the phone support game.
Well if you wanted to you could read the official xiotech docs which in turn reccomend you the redhat docs. That would be a great way to kill a day in frustration while sifting through a lot of noise. Here’s how to get a very basic example working quickly:
[jim@sql1 ~]$ rpm -qa | grep multi device-mapper-multipath-0.4.7-23.el5_3.4
if you do, doesn’t hurt to yum update it. if not, install it.
first comment out the blacklist lines near the top
#blacklist {
# devnode "*"
#}
then append this to the bottom
devices {
device {
vendor "XIOTECH "
product "ISE1400 "
path_grouping_policy multibus
getuid_callout "/sbin/scsi_id -g -u -d /dev/%n"
path_checker tur
prio_callout "none"
path_selector "round-robin 0"
failback immediate
no_path_retry 12
user_friendly_names yes
}
}
[jim@sql1 ~]$ sudo /sbin/chkconfig multipathd on [jim@sql1 ~]$ sudo /etc/init.d/multipathd start Starting multipathd daemon: [jim@sql1 ~]$ sudo /sbin/multipath -v2
notice it returns nothing, the redhat docs say it should, but it doesn’t. its just scanning the paths, if you want to see what it has discovered/conifgured run this:
[jim@sql1 ~]$ sudo /sbin/multipath -ll mpath1 (36001f93000a63000019b000200000000) dm-2 XIOTECH,ISE1400 [size=100G][features=0][hwhandler=0][rw] \_ round-robin 0 [prio=1][active] \_ 2:0:0:1 sdb 8:16 [active][ready] \_ round-robin 0 [prio=1][enabled] \_ 3:0:0:1 sdc 8:32 [active][ready]
take note of the “mpath1″ part there
format the disk (I’m not bothering with fdisk/partitions or lvm in this example, complicate as you see fit)
[jim@sql1 ~]$ sudo /sbin/mkfs.ext3 /dev/mapper/mpath1 <snip> [jim@sql1 ~]$ sudo mkdir /mnt/xio [jim@sql1 ~]$ sudo vi /etc/fstab
and stick this line on the bottom
/dev/mapper/mpath1 /mnt/xio ext3 defaults,noatime 1 1
then you’re pretty much set, mount it
[jim@sql1 ~]$ sudo mount /mnt/xio [jim@sql1 ~]$ cd /mnt/xio/ [jim@sql1 xio]$ ls lost+found [jim@sql1 xio]$ sudo dd if=/dev/zero of=./foo bs=8k count=1310720 1310720+0 records in 1310720+0 records out 10737418240 bytes (11 GB) copied, 24.9054 seconds, 431 MB/s [jim@sql1 xio]$