Recommendations or Experiences on Archiving Mailing DataBest way to cleanup CiviMail database tables?How can...
Why is the number of local variables used in a Java bytecode method not the most economical?
Is this photo showing a woman standing in the nude before teenagers real?
What do you call a flexible diving platform?
What language is Raven using for her attack in the new 52?
How to get CPU-G to run on 18.04
Do the books ever say oliphaunts aren’t elephants?
What does "see" in "the Holy See" mean?
How much were the LMs maneuvered to their landing points?
If a 2019 UA artificer has the Repeating Shot infusion on two hand crossbows, can they use two-weapon fighting?
The best place for swimming in Arctic Ocean
Are the named pipe created by `mknod` and the FIFO created by `mkfifo` equivalent?
How do I stop my characters falling in love?
Checking if an integer is a member of an integer list
What are the different qualities of the intervals?
Why do all my history books divide Chinese history after the Han dynasty?
To find islands of 1 and 0 in matrix
Why isn't there a serious attempt at creating a third mass-appeal party in the US?
What is the most common end of life issue for a car?
How to tar a list of directories only if they exist
Could the rotation of a black hole cause other planets to rotate?
Why does Canada require mandatory bilingualism in all government posts?
How to check what is edible on an alien world?
Am I allowed to use personal conversation as a source?
Request for a Latin phrase as motto "God is highest/supreme"
Recommendations or Experiences on Archiving Mailing Data
Best way to cleanup CiviMail database tables?How can I provide mailing receipients an “Unsubscribe All” option?Inserting images in mailingCiviCRM Raw Data FilesMailing settings not displayingMulti-client capability und data storageTransferring Data Out of CiviCRMEmail blast mailing through CiviCRMCiviMail Mailing Scheduled But Not SentSplit Data from Wordpress DatabaseHow to Export/Import all CiviCRM data from one website to another one
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
We have an instance that has been using CiviMail extensively for years.
The civicrm_mailing and civicrm_activiy tables far exceed the number of records of any other tables.
It seems likely that these tables could have some ancient history removed from them and make some operations work more smoothly, faster, or less error prone. e.g. back-ups or operations in civicrm involving groups.
Are there any recommendations, best-practices, or gotcha's for archiving/deleting historic data in CiviCRM. Specifically for mailings and activities, but while we are at it: contact or other data as well?
Top database tables:
2.6G ./prod_civicrm/civicrm_mailing_recipients.ibd
2.3G ./prod_civicrm/civicrm_mailing_event_queue.ibd
1.6G ./prod_civicrm/civicrm_activity_contact.ibd
1001M ./prod_civicrm/civicrm_log.ibd
641M ./prod_civicrm/civicrm_mailing_event_delivered.ibd
email database warehouse
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
add a comment |
We have an instance that has been using CiviMail extensively for years.
The civicrm_mailing and civicrm_activiy tables far exceed the number of records of any other tables.
It seems likely that these tables could have some ancient history removed from them and make some operations work more smoothly, faster, or less error prone. e.g. back-ups or operations in civicrm involving groups.
Are there any recommendations, best-practices, or gotcha's for archiving/deleting historic data in CiviCRM. Specifically for mailings and activities, but while we are at it: contact or other data as well?
Top database tables:
2.6G ./prod_civicrm/civicrm_mailing_recipients.ibd
2.3G ./prod_civicrm/civicrm_mailing_event_queue.ibd
1.6G ./prod_civicrm/civicrm_activity_contact.ibd
1001M ./prod_civicrm/civicrm_log.ibd
641M ./prod_civicrm/civicrm_mailing_event_delivered.ibd
email database warehouse
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
1
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago
add a comment |
We have an instance that has been using CiviMail extensively for years.
The civicrm_mailing and civicrm_activiy tables far exceed the number of records of any other tables.
It seems likely that these tables could have some ancient history removed from them and make some operations work more smoothly, faster, or less error prone. e.g. back-ups or operations in civicrm involving groups.
Are there any recommendations, best-practices, or gotcha's for archiving/deleting historic data in CiviCRM. Specifically for mailings and activities, but while we are at it: contact or other data as well?
Top database tables:
2.6G ./prod_civicrm/civicrm_mailing_recipients.ibd
2.3G ./prod_civicrm/civicrm_mailing_event_queue.ibd
1.6G ./prod_civicrm/civicrm_activity_contact.ibd
1001M ./prod_civicrm/civicrm_log.ibd
641M ./prod_civicrm/civicrm_mailing_event_delivered.ibd
email database warehouse
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
We have an instance that has been using CiviMail extensively for years.
The civicrm_mailing and civicrm_activiy tables far exceed the number of records of any other tables.
It seems likely that these tables could have some ancient history removed from them and make some operations work more smoothly, faster, or less error prone. e.g. back-ups or operations in civicrm involving groups.
Are there any recommendations, best-practices, or gotcha's for archiving/deleting historic data in CiviCRM. Specifically for mailings and activities, but while we are at it: contact or other data as well?
Top database tables:
2.6G ./prod_civicrm/civicrm_mailing_recipients.ibd
2.3G ./prod_civicrm/civicrm_mailing_event_queue.ibd
1.6G ./prod_civicrm/civicrm_activity_contact.ibd
1001M ./prod_civicrm/civicrm_log.ibd
641M ./prod_civicrm/civicrm_mailing_event_delivered.ibd
email database warehouse
email database warehouse
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
asked 10 hours ago
ginkgoMZDginkgoMZD
3681 silver badge9 bronze badges
3681 silver badge9 bronze badges
1
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago
add a comment |
1
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago
1
1
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago
add a comment |
2 Answers
2
active
oldest
votes
One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.
The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.
If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)
This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
add a comment |
we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "605"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
noCode: true, onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcivicrm.stackexchange.com%2fquestions%2f31522%2frecommendations-or-experiences-on-archiving-mailing-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.
The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.
If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)
This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
add a comment |
One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.
The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.
If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)
This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
add a comment |
One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.
The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.
If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)
This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
One small gotcha with large deletions: MySQL may not free-up the space in the way you expect.
The exact mechanics and numbers probably depend on the variant of MySQL and its configuration options, but just for a general sense: if the goal is to reduce the disk-usage of those tables on the active system by 75% (8gb to 2gb), then you might try to DELETE the oldest 75% of records. That may not reduce actual disk-usage by 75% -- because MySQL has its own system for freeing/reusing/reallocating space.
If you really need to reduce usage in a major way, then you might have to setup new tables or a new DBMS instance; then copy over the desired subset of recent information and drop the old. (Of course, you don't have to do this first-thing; you might try a simple DELETE; assess the change in disk-usage; and then decide.)
This gotcha is only a consideration when performing one epic shrink. If you follow a practice like Seamus's comment, wherein data is incrementally migrated out (outflow =~ inflow), then the size of the active system can hover around an equilibrium.
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
answered 5 hours ago
Tim OttenTim Otten
5,15911 silver badges18 bronze badges
5,15911 silver badges18 bronze badges
add a comment |
add a comment |
we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
add a comment |
we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
add a comment |
we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
we have a custom overnight routine that copies data from civicrm_mailing_x and civicrm_mailing_event_x tables to duplicate tables with a archive namespace and then deletes the appropriate rows from the main tables. The biggest things is making sure that you adequately understand the relationships between the tables so you don't accidentally delete data that hasn't yet been archived due to a Foreign Key constraint. The other thing to note is that doing it this way means you loose stats from the reports etc for the mailings that have been archived.
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
answered 5 hours ago
Seamus LeeSeamus Lee
1,0094 silver badges6 bronze badges
1,0094 silver badges6 bronze badges
add a comment |
add a comment |
Thanks for contributing an answer to CiviCRM Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcivicrm.stackexchange.com%2fquestions%2f31522%2frecommendations-or-experiences-on-archiving-mailing-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Relevant: civicrm.stackexchange.com/questions/28483/… (similar question) and I'd be interested to compare the results of DB table size queries (see stackoverflow.com/questions/9620198/…) versus the .ibd file sizes cited here. (Likely not a big difference but those files aren't always the whole picture.)
– Chris Burgess
9 hours ago