Cleaning ListManager Data Before Exporting

Export and import processes can take a very long time, especially if the database is large. Here are some tips to reduce the length of time it will take you to export and import your data.  The more of these you do, the faster the Export/Import process will go.

All SQL commands below can be performed using Utilities : Administration : Server : Database : View and Edit Data : Run Your Own SQL Query

1. Make sure that all mailings have been completed.

2. Execute the ‘Incremental update of mailing stats’ task under “Utilities : Administration : Server : Server Settings : Scheduled Tasks” to clear out the lyrUnsummarizedRecips table.  If any data remains in that table after running Incremental update, you should delete those rows. 

3. Use LM to delete any lists you no longer need (do this after upgrading to 9.3g which has several fixes for bugs in the Delete List process).  This will remove entries from the other larger tables as well.

4. Use LM to delete old mailings that contain obsolete data or are otherwise unnecessary. This will remove entries from the other larger tables as well.

5. Remove data from the lyrMetricEvents table. This table contains server performance data such as server sending speed which is only visible to the server admin. This table can be cleaned out using the SQL: “truncate table lyrMetricEvents."

6. Delete unwanted members such as held, unsubscribed or expired members. 

• You can View members by type and then use the “Delete All In This View."

• Or you can use SQL which will delete this info across all lists:
  delete from members_ where membertype_ = 'held' or membertype_ = 'unsub' or membertype_ = 'expired'
  [and List_ = 'list name']  - add this to limited the member deletion to a specific list.

7. Remove data from the wordmessage_ table.  This table is used for searching via the MRI.  If this function is unimportant to you this should be cleaned up.
   Go to “Utilities : List Settings : Discussion Forum Interface : Message Reading”, and set Enable archive searching to NO.  This will stop data from going into the wordmessage_ table.  You must do this for each list.
   This table can be cleaned out using the SQL: “truncate table wordmessage_”

8. Clear out old data from the messages_ table.  This table contains all messages and list postings.  It is used for %%url.mailing%% and MRI searches. If there are many older mailings in there that can be deleted, this will improve your export/import time.

• Under “Utilities : List Settings : Automatic Maintenance, Purging Archives tab” – change each of these to some number (like 7 for 1 week of archives or 14 for 2 weeks of archives).

• Execute the ‘Remove archives’ task under “Utilities : Administration : Server : Server Settings : Scheduled Tasks” to clear out the data older than 7 or 14 days in the messages_ table.
  

9. When exporting a POSTGRES database:

• Check for any NULL constraint violations in your database by running this script:
  <listmanager directory>/sql/shared/CheckIllegalNulls.sql
  FIX any problems found with this script before exporting or the Import could fail to import all your data

• Perform a "vacuum" on the database before exporting. This will compress the database and speed up disk operations

10. If BILLING tables have been created but there is no data in the customer_ table, the billing tables will not be recreated on Import and that will result in SQL errors.  If you are not using Billing tables, delete them before Export to avoid these SQL errors.  If you are using Billing tables, be sure you have at least one customer in the customer_ table in order to get the data exported and imported properly.
    

This is a list of all the Billing tables:

additionalchargegroups_
additionalcharges_
billinghistory_
customers_
groupobjects_
groups_
hostingplangroups_
hostingplans_
log_ (this one is there already)
objecthierarchy_
objectids_
objecttypes_
servers_

Next: Exporting the Data