[rt-users] MySQL Scaling for a large instance

[Ruslan Zakirov]

On Tue, Mar 13, 2012 at 20:56, David Moreau Simard <dmsimard at iweb.com> wrote:
> Hi,
>
> Our RT instance is rather large (> 5 million tickets) and I wondered if any
> users have had to deal with a RT database this size and if so, how they
> scaled their infrastructure as a result.
>
> Right now, the web portion is hosted on a cluster and really, this part
> doesn't worry me at all.
> I am really curious what are our options to get the best possible database
> performance at this point.
>
> A thought we had was to split reads and writes since we have significantly
> higher demand for read operations and they can be scaled/load-balanced with
> relative ease.
> Has anyone attempted to do this ? RT doesn't seem to be able to do this out
> of the box.

It wouldn't be too hard to route writes to master and reads to
replicas, but replication is async in mysql. Only mysql 5.5 supports
semisync replication. Probably solutions like mysql proxy can

> Do you otherwise have other recommendations ?

You can move sessions out of mysql. There is a pull request and/or
branch that makes it possible to configure sessions to use other
storages (mostly modern NoSQL DBs) right in the config without
patching code.

There are a few improvements in 4.2 branches that lower number of
reads per request by caching some answers related to user's
preferences. It's especially effective when users have no preferences.

If you're still on 3.x then upgrade to 4.0 would be a win for sure. We
greatly lowered number of queries per page.

Advanced web server setup (see RT-Extension-Nginx for ideas or to
replace) can lower number of requests to RT's code. Any additional
request to RT (for image, css, js) still does a few SQL queries, not
that many like requests for html, but still.

There are a lot of tiny things that can be done to lower pressure on DB.

> Thanks !
> --
> *David Moreau Simard*
> /IT Specialist/



-- 
Best regards, Ruslan.