Currently looking into a whole load of performance related issues with an application at work. Basically, how to make it go faster and support more users online at any given time.

Currently our setup has a MySQL 5.0.x master running with quite a few slaves. The setup is OK but isn’t as scalable as we’d like. The master isn’t suffering load wise so we don’t need to look at distrubtion masters or get into pyramid replication which is just a bit too complex. The main hang up is searching. We have members, each member has loads of related details, photos, payments, messages, profile information etc and currently it’s spread out over a number of tables. We’ve looked at sphinx, hadoop, solr / lucence but aren’t sure if those kinds of things can be used for multi faceted objects. We don’t want a plain text search because the data is mostly lots of numbers like height, age, hair colour all picked from lookup tables but maybe we can combine our data into some form of index that is better for searching? Currently we flatten the most searched data out into single tables (split on certain categorizations) and search those the details in them are updated regularly by triggered actions in the application. Memcache is an option as we use it to store data already.

MySQL 5.4 looks great in the performance enhancements it could bring but that’s now not looking like it’s going to be coming until around December. I hope Sun release it sooner however as the inital peek at it we’ve had seem to back up Sun’s claims.

Any ideas / suggestions / experience would be appreciated. Comment or email me.

I had a need, a need to change some tables in MySQL from MyISAM to InnoDB. I did a few via regular SQL and then thought there must be an easier way and indeed there is.

Maatkit (which if you haven’t heard of go Google it) can help. The following one liner will neatly switch all the tables in one go. Lovely.

mk-find -uroot wld --engine MyISAM --tblregex '^payment' \
--exec "alter table %D.%N engine=InnoDB" --print

FYI you can get this from a “yum install maatkit” as well.

Announced today and rapidly spreading over the blogosphere a post by Jonathan Schwartz that Sun have acquired MySQL.

Hopefully this will lead to MySQL’s GUI tools coming on and better support all round.

Really simple comment for phpMyAdmin. Trying to install it on XP with MySQL 4.1 & Apache2. Setup the php.ini & httpd.conf file OK, php files rendered correctly all was going well. Set the path to my c:phpext dir and tried restarting but, kept getting errors:

Turns out that unless you copy libmysql.dll from the php root dir into c:windowssystem32 it won’t work.

Following my previous post MM haven’t shipped the MySQL J 4 driver with CF7 as it’s not entirely stable and there seems too be some licensing issues. NewAtlanta have also not shipped this driver with Bluedragon.

If you need to install it with CF7 you can do, there is both a technote and a post over on Steven Erat’s blog.