How to clean up a big database for version 3

Once you've installed phpList... ask questions here!
Forum rules
Please help the volunteers to help you by supplying the version of phpList you are using, browser & version and if possible, a link to your phpList installation.

How to clean up a big database for version 3

Postby ruslyrossi » 9:22am, Mon 24 Nov, 2014

Hi,

i would like to do some maintenance for my phplist, because now every campaign it took around 10hours for 80k and my server always crash. I just go through and read some threads in this forum but all is for old version(v2).

Below is my database table:
Image
Full Image: http://i.imgur.com/8OqpMpJ.png

Almost 1GB

This server is using VPS and keep crash in this few days, i believe maybe because of my database. So which table should i delete or remove to make it smaller.

And below is my config setting:

Code: Select all
<?php
define('PLUGIN_ROOTDIR','plugins');
define("PHPMAILERHOST",'');
define("TEST",0);
define("CHECK_SESSIONIP",0);
define("EXPORT_EXCEL", 1);
$bounce_protocol = 'pop';
define ("MANUALLY_PROCESS_BOUNCES",1);
define ("MANUALLY_PROCESS_QUEUE",1);
$bounce_mailbox_host = 'localhost';
$bounce_mailbox_user = 'popuser';
$bounce_mailbox_password = 'password';
$bounce_mailbox_port = "110/pop3/novalidate-cert";
$bounce_mailbox = '/var/mail/listbounces';
$bounce_mailbox_purge = 1;
$bounce_mailbox_purge_unprocessed = 1;
$bounce_unsubscribe_threshold = 5;
define("USEFCK",0);
define("UPLOADIMAGES_DIR","lists/uploadimages");
$bounce_mailbox_port = "110/pop3/novalidate-cert"; 
//previous setting
define("MAILQUEUE_BATCH_SIZE",300);
define("MAILQUEUE_BATCH_PERIOD",8);
define('MAILQUEUE_THROTTLE',0);
# Don't look like a spammer.
define('USE_DOMAIN_THROTTLE',0); 
define('DOMAIN_AUTO_THROTTLE',0); 
# Don't forget!
define("MAXLIST",20);
define ("TEST",0);
$commandline_users = array();


Any advice and suggestions will be greatly appreciated.

phplist version: v3.0.5
ruslyrossi
phpLister
 
Posts: 11
Joined: 6:38am, Tue 10 Dec, 2013

Re: How to clean up a big database for version 3

Postby duncanc » 8:48am, Tue 25 Nov, 2014

The overall database size is not likely to be the cause of the server crashes or the slow sending rate.
But you can reduce the size of the two largest tables using phplist:
1) On the event log page you can delete events older than 2 months. This will reduce the size of the eventlog table.
2) phplist doesn't allow you to delete "sent" campaigns, but you can install the Manage Campaigns plugin to do that
See https://forums.phplist.com/viewtopic.php?f=7&t=40584
Deleting campaigns will reduce the size of several tables including the usermessage table. But delete the oldest campaigns first to see the effect.

Afterwards you might need to use phpmyadmin to optimize the tables to reclaim unused space.
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London


Return to Questions and Problems after Installing

Who is online

Users browsing this forum: No registered users and 1 guest