[ensembl-dev] VEP scaling / MySQL load balancing, anyone?

[Dr James A Smith]

On 01/08/2012 17:54, Andy Yates wrote:
> Hey Jan,
> 
> One thing I've noticed with MySQL is that it's very hard to make it go faster than what it does normally. If anything throwing hardware at the the problem just seems to let you maintain a level of service for longer. I'd like to suggest a few things though:
> 
> 1). Take a look at the filesystem you're using. I did a quick google and articles seem to suggest XFS/JFS are good file systems for MySQL (even better for InnoDB servers though).
> 
> 2). Using MyISAM means you are more reliant on disk & OS caches in order to speed up access rather than pushing more into MySQL's memory. Switching to InnoDB lets you give MySQL lots of memory to cache with which _could_ improve performance ...
> 
> 3). Try increasing the table cache. Might seem silly but if your processes are hitting a lot of different tables then the table cache could be ejecting tables too early
> 
> 4). A simple way to load balance is to use MySQL Proxy (http://dev.mysql.com/downloads/mysql-proxy/) which has improved in it's stability over the past few years. It does some automatic round-robin but making it scale automatically with auto-starting VMs could be quite difficult (it can be scripted with Lua so maybe there is a way):
> 
> mysql-proxy \
> --proxy-backend-addresses=narcissus:3306 \
> --proxy-backend-addresses=nostromo:3306
> 
> I've not tried any of these suggestions but hopefully they'll help
> 

The other one is to create a "MySQL cluster" setup - but it may not be
easy to optimze queries for this.