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.
Do you otherwise have other recommendations ?
Thanks !
David Moreau Simard
/IT Specialist/
hi David.
Our instance of RT is around 3.5 million records and the mySQL db about
470GB in size. the biggest performance gain I’ve got was through
partitioning and indexing, i’m very keen to see where this thread goes for
“bigger” deployments as i’m also concerned about that breaking point.
regards
RonaldOn 13 Mar 2012 19:03, “David Moreau Simard” dmsimard@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.
Do you otherwise have other recommendations ?
Thanks !
David Moreau Simard
/IT Specialist/
Hi David,
Our instance is not large, but have you tried profiling the load on
the database to find out where your resource shortages are located?
This will help you target the changes needed to improve the performance
of the backend. As Ronald mentions, the first step would be to use
partitioning to allow the working set of the database to remain in
memory for current queries.
Cheers,
KenOn Tue, Mar 13, 2012 at 07:12:28PM +0200, ronald higgins wrote:
hi David.
Our instance of RT is around 3.5 million records and the mySQL db about
470GB in size. the biggest performance gain I’ve got was through
partitioning and indexing, i’m very keen to see where this thread goes for
“bigger” deployments as i’m also concerned about that breaking point.
regards
Ronald
On 13 Mar 2012 19:03, “David Moreau Simard” dmsimard@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.
Do you otherwise have other recommendations ?
Thanks !
David Moreau Simard
/IT Specialist/
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.
Hi !
Do you know of any successful MySQL proxy implentations with RT ?
It is indeed one of the solutions we had been thinking of but it does
not have a release deemed “stable” at this time, still in beta.
Thanks,
David Moreau Simard
/Spécialiste TI //
IT Specialist/ http://iweb.com
David,
Do you know of any successful MySQL proxy implentations with RT ?
You should not really use any MySQL replication-based solution for database load distribution unless application was designed with that in mind or at least adjusted to work in such architecture. This is even more true for systems where consistency or availability are requirements and I think a ticketing system could be viewed as one of them.
The alternative approach you might want to look at is MySQL/Galera - a synchronous replication engine for InnoDB. They have released version 2.0 recently, which might be worth evaluating if you desperately need such solution asap. But I wouldn’t call it “stable” just yet.
Overall, however, I think pursuing the other options first would be a better idea (moving sessions away, adding some caching layer, partition to pin the working set to a smaller table space). With that many (or more) tickets, if or when database workload becomes disk-bound, it might be worth adding more RAM/growing InnoDB buffer pool size. I am new to RT, so I don’t really know how it uses database or what sort of workloads it typically generates as it grows, nor have I seen any large installations. I can only guess that the use of surrogate keys for primary keys in every(?) table may not work well with very large databases, especially if tickets can receive actions over extended periods of time (hours, days, weeks rather than minutes). These primary keys in tables like Attachments or Transactions, which can grow extremely large, result in poor data locality as rows belonging to different tickets would be appear in “random” places in a data file, simply stored in the order of insertion. Under disk-bound workload reading all attachments for a ticket could generate a lot random I/Os and queries could become slow, so RT would also slow down.
It is indeed one of the solutions we had been thinking of but it does not
have a release deemed “stable” at this time, still in beta.
I don’t think this project will ever become stable.
Maciek
Maciej Dobrzanski
IT/Performance Consultant
http://www.entrypoint.pl/