[rt-users] MySQL Scaling for a large instance

[reg.bestpractical at posterus.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/