Dears;
I have been face with a problem… after 3 years, out attachments table has
been grown over 300GB in size and it’s about 15 Million of records.
for performance tuning I Ihad changes it’s Table Engine into InnoDB and
configured MySQL as file_peer_table with Dynamic Row format to bring out
the BLOB data out of the caching space and reduction it’s response time.
but I have always a question in my mine! why RT is storing this much
attached objects in BLOG fields?
why it dose not use a separate file system to store file on and just table
it links ( or auto generated name ) on DB?
in such a way, we will have some better ways to backup this much files…
– features like snapshots on ZFS
Regards,
Ali R. Taleghani
Dears;
I have been face with a problem… after 3 years, out attachments table has
been grown over 300GB in size and it’s about 15 Million of records.
for performance tuning I Ihad changes it’s Table Engine into InnoDB and
configured MySQL as file_peer_table with Dynamic Row format to bring out
the BLOB data out of the caching space and reduction it’s response time.
but I have always a question in my mine! why RT is storing this much
attached objects in BLOG fields?
why it dose not use a separate file system to store file on and just table
it links ( or auto generated name ) on DB?
in such a way, we will have some better ways to backup this much files…
– features like snapshots on ZFS
–
Regards,
Ali R. Taleghani
Hi Ali,
You can check the archives. There actually was a branch developed by
Best Practical that moved the attachments into the filesystem. I believe
the customer was motivated by performance needs. Certainly, keeping all
of the information is a single DB is vastly easier to manage consistently.
Once you get to the size of the DB that you are reaching, care and handling
is a much different beast whether or not the data is in the DB or on a tied
filesystem. You are certainly an inspiration using MySQL as the backend for
an instance that large.
Regards,
Ken
Ali,
Other than modifying RT itself to store attachments outside of the main
database, you could also try InnoDB compression first. It is available in
all recent MySQL versions (5.1, 5.5, 5.6) and it is completely transparent
to queries or applications. Even with some number of larger attachments,
there is always still a lot of plain text in this table (e-mails, comments),
i.e. all the kind of data that should compress very well. You might be able
to reduce the physical table size by 50% or even more this way (the
compression level is actually tunable to some extent, at the expense of
performance of course).
Best regards,
Maciek
MySQL Consulting Services
PSCE | http://www.psce.com/
Dear Ke;
due the type if our attachments which are JPEG files, don’t hope
compression to help me more! but I serious to test it ASAP.
it seems pretty to be able to compress Emails body on side DB engine.
:-)On Wed, Oct 10, 2012 at 10:54 PM, ktm@rice.edu ktm@rice.edu wrote:
On Wed, Oct 10, 2012 at 10:36:22PM +0330, shayne.alone@gmail.com wrote:
Dears;
I have been face with a problem… after 3 years, out attachments table
has
been grown over 300GB in size and it’s about 15 Million of records.
for performance tuning I Ihad changes it’s Table Engine into InnoDB and
configured MySQL as file_peer_table with Dynamic Row format to bring out
the BLOB data out of the caching space and reduction it’s response time.
but I have always a question in my mine! why RT is storing this much
attached objects in BLOG fields?
why it dose not use a separate file system to store file on and just
table
it links ( or auto generated name ) on DB?
in such a way, we will have some better ways to backup this much files…
– features like snapshots on ZFS
–
Regards,
Ali R. Taleghani
Hi Ali,
You can check the archives. There actually was a branch developed by
Best Practical that moved the attachments into the filesystem. I believe
the customer was motivated by performance needs. Certainly, keeping all
of the information is a single DB is vastly easier to manage consistently.
Once you get to the size of the DB that you are reaching, care and handling
is a much different beast whether or not the data is in the DB or on a tied
filesystem. You are certainly an inspiration using MySQL as the backend for
an instance that large.
Regards,
Ken
Regards,
Ali R. Taleghani
Dear Ken;
I will like that branch… and hope it to an alive branch :-/
tnx
Dear Maciek;
due the type if our attachments which are JPEG files, don’t hope
compression to help me more! but I serious to test it ASAP.
it seems pretty to be able to compress Emails body on side DB engine.
tnxOn Wed, Oct 10, 2012 at 11:13 PM, Maciej Dobrzanski < reg.bestpractical@posterus.com> wrote:
Ali,****
Other than modifying RT itself to store attachments outside of the main
database, you could also try InnoDB compression first. It is available in
all recent MySQL versions (5.1, 5.5, 5.6) and it is completely transparent
to queries or applications. Even with some number of larger attachments,
there is always still a lot of plain text in this table (e-mails,
comments), i.e. all the kind of data that should compress very well. You
might be able to reduce the physical table size by 50% or even more this
way (the compression level is actually tunable to some extent, at the
expense of performance of course).****
Best regards,****
Maciek****
MySQL Consulting Services****
PSCE | http://www.psce.com/****
Final RT training for 2012 in Atlanta, GA - October 23 & 24
http://bestpractical.com/training
We’re hiring! Careers — Best Practical Solutions
Regards,
Ali R. Taleghani
Dear Ken;
I will like that branch… and hope it to an alive branch :-/
tnx
I believe that that branch was for a customer and was never released.
You would need to engage Best Practical.
Cheers,
Ken
Dears;
I am thinking about a better backend for ‘attachments’ to be stored on,
instead on Mysql and InnoDB engine.
is there any try out to force things like
couchDBhttp://couchdb.apache.org/into RT?On Thu, Oct 11, 2012 at 4:16 PM, ktm@rice.edu ktm@rice.edu wrote:
On Thu, Oct 11, 2012 at 08:48:36AM +0330, shayne.alone@gmail.com wrote:
Dear Ken;
I will like that branch… and hope it to an alive branch :-/
tnx
I believe that that branch was for a customer and was never released.
You would need to engage Best Practical.
Cheers,
Ken
Regards,
Ali R. Taleghani
Dears;
I am thinking about a better backend for ‘attachments’ to be stored on,
instead on Mysql and InnoDB engine.
is there any try out to force things like couchDB
http://couchdb.apache.org/ into RT?
CouchDB is primarily a document storage engine. RT uses attachments for
two major “types” of data: textual replies/comments/emails and binary
data. For many reasons the textual data should almost certainly stay in
the SQL database as long as the rest of RT’s data is in that database
(access control, searching, performance, etc). That leaves you with the
binary data, which isn’t a very good fit for a document storage engine
like CouchDB.
At the point when you need to seriously consider moving binary
attachments out of the SQL database, it’s very hard to beat to the
filesystem.
There’s a lot you can do to tune MySQL/PostgreSQL/Oracle database
performance and backup strategies before it’s time to look for
completely alternate solutions.