[Gllug] mysql source install upgrade

[Karanbir Singh]

Aaron Trevena wrote:
> It's a bit of rock and hard place - myisam isn't coping with the level
> of writes, but when they tried moving to innodb some queries were
> apparently 10 times slower - unfortunately I haven't been able to
> repeat that with my benchmarks, nobody can remember which query it was
> and can't tell me if that's the real problem or if it was just an
> effect of the old concurrency / thread thrashing bug which was fixed
> several versions ago.

How large is the database ?

One option here might be to bring up another slave with a newer mysql, 
and run that with the db on innodb. Since you are running with MyISAM at 
the moment, there is zero issue of transaction consistency being needed 
since that must be done in the code at the moment.

You can then, on this replica, turn on the slow query log with 
log-slow-queries=/var/lib/mysql/mysqld-slow.log in the [mysld] section 
of /etc/my.cnf and take the time down to 1 second( long_query_time=1 ). 
That should give you something interesting to look at on the new innodb 
replica.

Of-course, you wont get the load from read's and selects - but that 
should not be too hard to artificially create. Or, if the replica is 
not-too-far-in-network-terms, I'd get a mysql-proxy instance up between 
code -> replica master, and have all non-data-update queries be played 
on both replica-master and new-replica-on-innodb, with results from the 
replica-on-innodb being thrown away.

> The old Switcheroo :)
> 
> create new table with different name, "select into" new table, rename
> old and new tables. This is much quicker than dump/restore and
> reversable unlike like in-place.

Why not just do it in a new database, then you can keep the tablenames, 
and just rename the db when you are ready. I guess you have the ability 
to take down production for a short while ? Because you wont be able to 
'flush tables with read lock' if you are doing create foo select * from 
orig_db.foo; at the same time. And you cant lock the orig_db state down 
with a begin;.

> I'm installing the latest mysql from source side by side (/usr/local
> vs /usr) so that we can avoid the old concurrency bugs and/or fall
> back to the previous working (if overworked) set up if problems
> encountered.

if you are on lvm, just snapshot /var/lib/mysql away..

> The tricky bit is sorting out the slaves - my plan is to concurrently
> switch tables on master and slave (reversable and quick), then reset
> replication to start from the same point on the master.

remember, slaves update first :D get the replica's running the version 
you want to get into production before you do any changes to the master. 
  newer version to older version replication works fine, older version 
to newer version master almost never does ( and if you look at the mysql 
replication test suite, they dont even check for this condition ).

The tricky situation is when things break X amount of time AFTER the new 
setup went into production. Since you cant really use the /var/lib/mysql 
snapshot - so a think about how to handle the data change delta if you 
need to failback to 5.0.22 might be a good thing  ( binlog playback 
perhaps ? )

Speaking of binlogs, the default naming scheme for binlogs changes from 
5.0.22 to 5.0.5X from using something with a 'hostname -s' in it, to a 
more generic myslqd-binlog .... so watch out for that, or better still 
just set something in the /etc/my.cnf

phew. long email.

-- 
Karanbir Singh : http://www.karan.org/  : 2522219 at icq
-- 
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug